LIKE.

sql

1 -- Demonstrates pattern matching with LIKE

2 -- Uses longlist.db
3
4 -- Finds all books with "love" in the title
5 SELECT "title" FROM "longlist" WHERE "title" LIKE '%love%';
6
7 -- Finds all books that begin with "The" (includes "There", etc.)
8 SELECT "title" FROM "longlist" WHERE "title" LIKE 'The%';
9
10 -- Finds all books that begin with "The"
11 SELECT "title" FROM "longlist" WHERE "title" LIKE 'The %';
12
13 -- Finds a book whose title unsure how to spell
14 SELECT "title" FROM "longlist" WHERE "title" LIKE 'P_re';
 LIMIT.sql

1 -- Demonstrates limiting results with LIMIT

2 -- Uses longlist.db
3
4 -- Limits results to first 3 rows
5 SELECT "title", "author" FROM "longlist" LIMIT 3;
6
7 -- Limits results to first 10 rows
8 SELECT "title", "author" FROM "longlist" LIMIT 10;
 NULL.sql

1 -- Demonstrates empty values with NULL

2 -- Uses longlist.db
3
4 -- Finds books without a translator
5 SELECT "title", "translator" FROM "longlist" WHERE "translator" IS NULL;
6
7 -- Finds books with a translator
8 SELECT "title", "translator" FROM "longlist" WHERE "translator" IS NOT NULL;
 ORDER BY.sql

1 -- Demonstrates sorting with ORDER BY

2 -- Uses longlist.db
3
4 -- Finds top 10 books by rating (incorrectly)
5 SELECT "title", "rating" FROM "longlist" ORDER BY "rating" LIMIT 10;
6
7 -- Finds top 10 books by rating (correctly)
8 SELECT "title", "rating" FROM "longlist" ORDER BY "rating" DESC LIMIT 10;
9
10 -- Sorts by more than one column
11 SELECT "title", "rating", "votes" FROM "longlist"
12 ORDER BY "rating" DESC, "votes" DESC
13 LIMIT 10;
14
15 -- Sorts with a condition
16 SELECT "title", "rating" FROM "longlist"
17 WHERE "votes" > 10000 ORDER BY "rating" DESC
18 LIMIT 10;
 SELECT.sql

1 -- Demonstrates SELECT
2 -- Uses longlist.db
3
4 -- Selects all columns from "longlist" table
5 SELECT * FROM "longlist";
6
7 -- Selects "title" column from "longlist" table
8 SELECT "title" FROM "longlist";
9
10 -- Selects "title" and "author" column from "longlist" table
11 SELECT "title", "author" FROM "longlist";
12
13 -- Selects "title", "author", and "translator" column from "longlist" table
14 SELECT "title", "author", "translator" FROM "longlist";
 WHERE.sql

1 -- Demonstrates filtering with WHERE

2 -- Uses longlist.db
3
4 -- Selects all books (titles and authors) nominated in 2023
5 SELECT "title", "author" FROM "longlist" WHERE "year" = 2023;
6
7 -- Selects all books by Fernanda Melchor
8 SELECT "title", "author" FROM "longlist" WHERE "author" = 'Fernanda Melchor';
9
10 -- Selects all books not released in hardcover format
11 SELECT "title", "format" FROM "longlist" WHERE "format" != 'hardcover';
12
13 -- Selects all books not released in hardcover format
14 SELECT "title", "format" FROM "longlist" WHERE "format" <> 'hardcover';
15
16 -- Selects all books not released in hardcover format
17 SELECT "title", "format" FROM "longlist" WHERE NOT "format" = 'hardcover';
 aggregate.sql

1 -- Demonstrates aggregating with aggregation functions

2 -- Uses longlist.db
3
4 -- Finds the average rating of all longlisted books
5 SELECT AVG("rating") FROM "longlist";
6
7 -- Rounds the result
8 SELECT ROUND(AVG("rating"), 2) FROM "longlist";
9
10 -- Renames column with AS
11 SELECT ROUND(AVG("rating"), 2) AS "Average Rating" FROM "longlist";
12
13 -- Finds maximum rating
14 SELECT MAX("rating") FROM "longlist";
15
16 -- Finds minimum rating
17 SELECT MIN("rating") FROM "longlist";
18
19 -- Finds total number of votes
20 SELECT SUM("votes") FROM "longlist";
21
22 -- Finds total number of books
23 SELECT COUNT(*) FROM "longlist";
24
25 -- Finds total number of translators
26 SELECT COUNT("translator") FROM "longlist";
27
28 -- Incorrectly counts publishers
29 SELECT COUNT("publisher") FROM "longlist";
30
31 -- Correctly counts publishers
32 SELECT COUNT(DISTINCT "publisher") FROM "longlist";
 compound.sql

1 -- Demonstrates compound conditions

2 -- Uses longlist.db
3
4 -- Finds books nominated in 2022 or 2023
5 SELECT "title", "year" FROM "longlist" WHERE "year" = 2022 OR "year" = 2023;
6
7 -- Finds hardcover books nominated in 2022 or 2023
8 SELECT "title", "year" FROM "longlist" WHERE ("year" = 2022 OR "year" = 2023) AND "format" = 'hardcover';
9
10 -- Find all books nominated between 2019 and 2022 using many ORs
11 SELECT "title", "year" FROM "longlist"
12 WHERE "year" = 2019 OR "year" = 2020 OR "year" = 2021 OR "year" = 2022;
 range.sql

1 -- Demonstrates range conditions

2 -- Uses longlist.db
3
4 -- Find all books nominated between 2019 and 2022 with a range condition
5 SELECT "title", "year" FROM "longlist" WHERE "year" >= 2019 AND "year" <= 2022;
6
7 -- Find all books nominated between 2019 and 2022 with BETWEEN
8 SELECT "title", "year" FROM "longlist" WHERE "year" BETWEEN 2019 AND 2022;
9
10 -- Selects books with a rating above 4.0
11 SELECT "title", "rating" FROM "longlist" WHERE "rating" > 4.0;
12
13 -- Selects books with a rating above 4.0 and at least 1000 votes
14 SELECT "title", "rating" FROM "longlist" WHERE "rating" > 4.0 AND "votes" > 1000;
15
16 -- Selects books with fewer than 300 pages
17 SELECT "title", "pages" FROM "longlist" WHERE "pages" < 300;