|
CREATE TABLE books ( |
|
id INTEGER PRIMARY KEY AUTOINCREMENT, |
|
author TEXT, |
|
title TEXT, |
|
words INTEGER); |
|
|
|
INSERT INTO books (author, title, words) |
|
VALUES ("J.K. Rowling", "Harry Potter and the Philosopher's Stone", 79944); |
|
INSERT INTO books (author, title, words) |
|
VALUES ("J.K. Rowling", "Harry Potter and the Chamber of Secrets", 85141); |
|
INSERT INTO books (author, title, words) |
|
VALUES ("J.K. Rowling", "Harry Potter and the Prisoner of Azkaban", 107253); |
|
INSERT INTO books (author, title, words) |
|
VALUES ("J.K. Rowling", "Harry Potter and the Goblet of Fire", 190637); |
|
INSERT INTO books (author, title, words) |
|
VALUES ("J.K. Rowling", "Harry Potter and the Order of the Phoenix", 257045); |
|
INSERT INTO books (author, title, words) |
|
VALUES ("J.K. Rowling", "Harry Potter and the Half-Blood Prince", 168923); |
|
INSERT INTO books (author, title, words) |
|
VALUES ("J.K. Rowling", "Harry Potter and the Deathly Hallows", 197651); |
|
|
|
INSERT INTO books (author, title, words) |
|
VALUES ("Stephenie Meyer", "Twilight", 118501); |
|
INSERT INTO books (author, title, words) |
|
VALUES ("Stephenie Meyer", "New Moon", 132807); |
|
INSERT INTO books (author, title, words) |
|
VALUES ("Stephenie Meyer", "Eclipse", 147930); |
|
INSERT INTO books (author, title, words) |
|
VALUES ("Stephenie Meyer", "Breaking Dawn", 192196); |
|
|
|
INSERT INTO books (author, title, words) |
|
VALUES ("J.R.R. Tolkien", "The Hobbit", 95022); |
|
INSERT INTO books (author, title, words) |
|
VALUES ("J.R.R. Tolkien", "Fellowship of the Ring", 177227); |
|
INSERT INTO books (author, title, words) |
|
VALUES ("J.R.R. Tolkien", "Two Towers", 143436); |
|
INSERT INTO books (author, title, words) |
|
VALUES ("J.R.R. Tolkien", "Return of the King", 134462); |
|
|
|
/* In this first step, select all the authors who have written more than 1 million words, using GROUP BY and HAVING. |
|
Your results table should include the 'author' and their total word count as a 'total_words' column. */ |
|
|
|
SELECT author, |
|
SUM(words) |
|
AS total_words |
|
FROM books |
|
GROUP BY author |
|
HAVING total_words >= 1000000; |
|
|
|
/*Step 2 |
|
Now select all the authors that write more than an average of 150,000 words per book. |
|
Your results table should include the 'author' and average words as an 'avg_words' column. */ |
|
|
|
SELECT author, |
|
AVG(words) |
|
AS avg_words |
|
FROM books |
|
GROUP BY author |
|
HAVING avg_words >= 150000; |
