-
Notifications
You must be signed in to change notification settings - Fork 42
/
statements.sql
150 lines (133 loc) · 7.07 KB
/
statements.sql
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
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
/*
Statements used in 'Learning SQL Programming'
*/
-- 00_03
SELECT * FROM people;
SELECT first_name FROM people;
-- 01_01
SELECT 'Hello, World!';
SELECT first_name FROM people;
SELECT last_name FROM people;
SELECT first_name, last_name FROM people;
SELECT last_name, first_name FROM people;
SELECT * FROM people;
SELECT first_name, state, company FROM people;
SELECT company, first_name, quiz_points FROM people;
-- 01_02
SELECT * FROM people WHERE state='CA';
SELECT * FROM people WHERE state='FL';
SELECT * FROM people WHERE state='WA';
SELECT * FROM people WHERE state='NY';
SELECT * FROM people WHERE shirt_or_hat='NY';
SELECT * FROM people WHERE shirt_or_hat='hat';
SELECT first_name, last_name FROM people WHERE shirt_or_hat='shirt';
SELECT first_name, last_name, shirt_or_hat FROM people WHERE shirt_or_hat='shirt';
SELECT first_name, last_name, shirt_or_hat WHERE shirt_or_hat='shirt' FROM people; -- Note: This is an invalid statement.
-- 01_03
SELECT first_name, last_name FROM people WHERE state='CA' AND shirt_or_hat='shirt';
SELECT first_name, last_name, team FROM people WHERE state='CA' AND shirt_or_hat='shirt' AND team='blue';
SELECT first_name, last_name, team FROM people WHERE state='CA' AND shirt_or_hat='shirt' AND team IS 'blue';
SELECT first_name, last_name, team FROM people WHERE state='CA' AND shirt_or_hat='shirt' AND team IS NOT 'blue';
SELECT first_name, last_name, team FROM people WHERE state='CA' AND shirt_or_hat='shirt' AND team != 'blue';
SELECT first_name, last_name, team, shirt_or_hat, state FROM people WHERE state='CA' OR state='CO' AND shirt_or_hat='shirt';
SELECT first_name, last_name, team, shirt_or_hat, state FROM people WHERE (state='CA' OR state='CO') AND shirt_or_hat='shirt';
-- 01_04
SELECT first_name, last_name, state FROM people WHERE state LIKE 'C%';
SELECT first_name, last_name, state FROM people WHERE state LIKE 'N%';
SELECT first_name, last_name, state FROM people WHERE state LIKE '%N';
SELECT first_name, last_name, state FROM people WHERE first_name LIKE 'A%';
SELECT first_name, last_name, state FROM people WHERE first_name LIKE 'J%';
SELECT first_name, last_name, state FROM people WHERE first_name LIKE '%J%';
SELECT first_name, last_name, state FROM people WHERE first_name LIKE '%on%';
SELECT first_name, last_name, state FROM people WHERE first_name LIKE '%ch%';
SELECT first_name, last_name, state FROM people WHERE company LIKE '%LLC';
SELECT first_name, last_name, state, company FROM people WHERE company LIKE '%LLC';
SELECT first_name, last_name, state, company FROM people WHERE company LIKE '%LLC' LIMIT 5;
SELECT first_name, last_name, state, company FROM people WHERE company LIKE '%LLC' LIMIT 10;
SELECT first_name, last_name, state, company FROM people WHERE company LIKE '%LLC' LIMIT 10 OFFSET 5;
-- 01_05
SELECT first_name, last_name FROM people;
SELECT first_name, last_name FROM people ORDER BY first_name;
SELECT first_name, last_name FROM people ORDER BY first_name ASC;
SELECT first_name, last_name FROM people ORDER BY first_name DESC;
SELECT state, last_name, first_name FROM people ORDER BY state ASC, last_name ASC;
SELECT state, last_name, first_name FROM people ORDER BY state ASC, last_name DESC;
-- 01_06
SELECT first_name FROM people;
SELECT first_name, LENGTH(first_name) FROM people;
SELECT DISTINCT(first_name) FROM people;
SELECT DISTINCT(first_name) FROM people ORDER BY first_name;
SELECT DISTINCT(shirt_or_hat) FROM people;
SELECT COUNT(*) FROM people WHERE state='CA';
-- 02_01
SELECT first_name, state FROM people;
SELECT first_name, state FROM people JOIN states;
SELECT people.first_name, people.state, states.division FROM people JOIN states ON people.state=states.state_abbrev;
SELECT * FROM people JOIN states ON people.state=states.state_abbrev;
SELECT * FROM people JOIN states ON people.state=states.state_abbrev WHERE people.first_name LIKE 'j%' AND states.region='South';
-- 02_02
SELECT people.first_name, people.last_name, people.state, states.state_name FROM people JOIN states ON people.state=states.state_abbrev;
SELECT people.first_name, people.last_name, people.state, states.state_name FROM states JOIN people ON people.state=states.state_abbrev;
SELECT people.first_name, people.last_name, people.state, states.state_name FROM states LEFT JOIN people ON people.state=states.state_abbrev;
SELECT DISTINCT(people.state), states.state_abbrev FROM states LEFT JOIN people ON people.state=states.state_abbrev ORDER BY people.state;
-- 02_03
SELECT first_name, COUNT(first_name) FROM people;
SELECT first_name, COUNT(first_name) FROM people GROUP BY first_name;
SELECT first_name, COUNT(first_name) FROM people GROUP BY last_name;
SELECT last_name, COUNT(first_name) FROM people GROUP BY last_name;
SELECT state, COUNT(state) FROM people GROUP BY state;
SELECT state, quiz_points, COUNT(quiz_points) FROM people GROUP BY quiz_points;
SELECT state, quiz_points, COUNT(quiz_points) FROM people GROUP BY state, quiz_points;
-- 03_02
SELECT 4+2;
SELECT 1/3;
SELECT 1/3.0;
SELECT 3>2;
SELECT 2>3;
SELECT 5!=3;
SELECT first_name, quiz_points FROM people WHERE quiz_points > 70;
SELECT first_name, quiz_points FROM people WHERE quiz_points >= 70;
SELECT MAX(quiz_points), MIN(quiz_points) FROM people;
SELECT SUM(quiz_points) FROM people;
SELECT team, COUNT(*) FROM people GROUP BY team;
SELECT team, COUNT(*) FROM people;
SELECT team, COUNT(*), SUM(quiz_points), (SUM(quiz_points)/COUNT(*)) AS average FROM people GROUP BY team;
-- 03_03
-- 03_04
SELECT first_name, last_name FROM people;
SELECT LOWER(first_name), UPPER(last_name) FROM people;
SELECT LOWER(first_name), SUBSTR(last_name, 1, 5) FROM people;
SELECT LOWER(first_name), SUBSTR(last_name, 2, 4) FROM people;
SELECT LOWER(first_name), SUBSTR(last_name, 2) FROM people;
SELECT LOWER(first_name), SUBSTR(last_name, -2) FROM people;
SELECT LOWER(first_name), SUBSTR(last_name, -4) FROM people;
SELECT LOWER(first_name), REPLACE(last_name, 'a', '_') FROM people;
SELECT quiz_points FROM people ORDER BY quiz_points;
SELECT quiz_points FROM people ORDER BY CAST(quiz_points AS char);
SELECT MAX(CAST(quiz_points AS CHAR)) FROM people;
SELECT MAX(CAST(quiz_points AS INT)) FROM people;
-- 03_05
SELECT first_name, last_name FROM people;
SELECT first_name, UPPER(last_name) FROM people;
SELECT first_name, UPPER(last_name) AS surname FROM people;
-- 04_01
INSERT INTO people (first_name) VALUES ('Bob');
SELECT * FROM people;
INSERT INTO people (first_name, last_name, state, city, shirt_or_hat) VALUES ('Mary', 'Hamilton', 'OR', 'Portland', 'hat');
SELECT * FROM people;
INSERT INTO people (first_name, last_name) VALUES ('George', 'White'), ('Jenn', 'Smith'), ('Carol', 'Anderson');
SELECT * FROM people;
-- 04_02
SELECT * FROM people;
UPDATE people SET first_name='Martha' WHERE first_name='George' AND last_name='White';
SELECT * FROM people;
SELECT * FROM people WHERE company='Fisher LLC';
UPDATE people SET company='Megacorp Inc' WHERE company='Fisher LLC';
SELECT * FROM people WHERE company='Fisher LLC';
SELECT * FROM people WHERE company='Megacorp Inc';
-- 04_03
SELECT * FROM people;
DELETE FROM people WHERE first_name='Martha' AND last_name='White';
SELECT * FROM people;
DELETE FROM people WHERE id_number IS NULL;
SELECT * FROM people;