-
Notifications
You must be signed in to change notification settings - Fork 1
/
lec0
93 lines (47 loc) · 2.23 KB
/
lec0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
--SELECT
SELECT * FROM 'longlist';
SELECT "title" FROM "longlist";
SELECT "title", "author" FROM "longlist";
--LIMIT
SELECT "title" FROM "longlist" LIMIT 10;
SELECT "title" FROM "longlist" LIMIT 10;
--WHERE
SELECT "title", "author" FROM "longlist" WHERE "year" = 2023;
SELECT "title", "author" FROM "longlist" WHERE "year" = 2022;
SELECT "title", "author" FROM "longlist" WHERE "year" = 2021;
SELECT "title", "format" FROM "longlist" WHERE "format" != 'hardcover';
--NOT
SELECT "title", "format" FROM "longlist" WHERE NOT "format" = 'hardcover';
--OR
SELECT "title", "author" FROM "longlist" WHERE "year" = 2022 OR "year" = 2023
SELECT "title", "format" FROM "longlist" WHERE ("year" = 2022 OR "year" = 2023) AND "format" != 'hardcover';
--NULL
SELECT "title", "translator" FROM "longlist" WHERE "translator" IS NULL;
SELECT "title", "translator" FROM "longlist" WHERE "translator" IS NOT NULL;
--LIKE
SELECT "title" FROM "longlist" WHERE "title" LIKE '%love%'
SELECT "title" FROM "longlist" WHERE "title" LIKE 'The%' ;
SELECT "title" FROM "longlist" WHERE "title" LIKE 'P_re';
SELECT "title" FROM "longlist" WHERE "title" LIKE 'T___';
--AND BEETWEEN
SELECT "title", "year" FROM "longlist" WHERE "year" >= 2019 AND "year" <= 2022;
SELECT "title", "year" FROM "longlist" WHERE "year" BEETWEEN 2019 AND 2022;
SELECT "title", "rating" FROM "longlist" WHERE "rating" > 4.0;
SELECT "title","rating", "votes" FROM "longlist" WHERE "rating" > 4.0 AND "votes" > 10000;
SELECT "title", "pages" FROM "longlist" WHERE "pages" <300;
--ORDER BY
SELECT "title", "rating" FROM "longlist" ORDER BY "rating" LIMIT 10;
SELECT "title", "rating" FROM "longlist" ORDER BY "rating" DESC LIMIT 10;
SELECT "title", "rating", "votes" FROM "longlist" ORDER BY "rating" DESC, "votes" DESC LIMIT 10;
--COUNT,AVG,MIN,MAX,SUM
SELECT AVG("rating") FROM "longlist";
SELECT ROUND(AVG("rating"), 2) AS "averate rating" FROM "longlist";
SELECT MAX("rating") FROM "longlist";
SELECT MIN("rating") FROM "longlist";
SELECT SUM("votes") FROM "longlist";
SELECT COUNT(*) FROM "longlist";
SELECT COUNT(translator) FROM "longlist";
SELECT COUNT("publisher") FROM "longlist";
--DISTINCT
SELECT DISTINCT "publisher" FROM "longlist";
SELECT COUNT(DISTINCT "publisher") FROM "longlist"