-
Notifications
You must be signed in to change notification settings - Fork 0
/
queries.sql
242 lines (200 loc) · 10.2 KB
/
queries.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
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
/******************************************************************************\
* AppAcademy JS/Py Online Week 10 - "Solving the SQL Menagerie"
*
*** Directions *****************************************************************
*
* Phase 1: create a 'travel' database and follow the directions to pipe your
* seed file into this database.
*
* Phases 2, 3, 4 & Bonus - add your queries to this file - the directions for
* each query is listed in a separate block comment below.
*
\******************************************************************************/
-- connect to the correct database - 'travel'
\echo
\c travel
\echo
-- disable pagination
\pset pager off
\echo
---- Phase 2: Write basic SELECT statements. -----------------------------------
-- Retrieve rows from a table using SELECT ... FROM SQL statements.
--------------------------------------------------------------------------------
\echo ========= Problem 2.1 ====================================================
\echo
/*
2.1) Write a SQL query that returns the city, state, and estimated population in
2018 from the "cities" table.
*/
SELECT "city", "state", "population_estimate_2018" FROM "cities";
\echo ========= Problem 2.2 ====================================================
\echo
/*
2.2) Write a SQL query that returns all of the airport names contained in the
"airports" table.
*/
SELECT "name" FROM "airports";
---- Phase 3: Add WHERE clauses ------------------------------------------------
-- Select specific rows from a table using WHERE and common operators.
--------------------------------------------------------------------------------
\echo ========= Problem 3.1 ====================================================
\echo
/*
3.1) Write a SQL query that uses a WHERE clause to get the estimated population
in 2018 of the city of San Diego.
*/
SELECT "population_estimate_2018" FROM "cities" WHERE "city" = 'San Diego';
\echo ========= Problem 3.2 ====================================================
\echo
/*
3.2) Write a SQL query that uses a WHERE clause to get the city, state, and
estimated population in 2018 of cities in this list:
Phoenix, Jacksonville, Charlotte, Nashville.
*/
SELECT "population_estimate_2018", "city", "state" FROM "cities" WHERE "city" IN ('Phoenix', 'Jacksonville', 'Charlotte', 'Nashville');
\echo ========= Problem 3.3 ====================================================
\echo
/*
3.3) Write a SQL query that uses a WHERE clause to get the cities with an
estimated 2018 population between 800,000 and 900,000 people. Show the
city, state, and estimated population in 2018 columns.
*/
SELECT "state", "city", "population_estimate_2018" FROM "cities" WHERE "population_estimate_2018" BETWEEN 800000 AND 900000;
\echo ========= Problem 3.4 ====================================================
\echo
/*
3.4) Write a SQL query that uses a WHERE clause to get the names of the cities
that had an estimated population in 2018 of at least 1 million people (or
1,000,000 people).
*/
SELECT "city" FROM "cities" WHERE "population_estimate_2018" >= 1000000;
\echo ========= Problem 3.5 ====================================================
\echo
/*
3.5) Write a SQL query to get the city and estimated population in 2018 in
number of millions (i.e. without zeroes at the end: 1 million), and that
uses a WHERE clause to return only the cities in Texas.
*/
SELECT "city", ROUND("population_estimate_2018"/1000000.0, 1) || ' million' AS "2018_estimated_population" FROM "cities" WHERE "state" = 'Texas';
\echo ========= Problem 3.6 ====================================================
\echo
/*
3.6) Write a SQL query that uses a WHERE clause to get the city, state,
and estimated population in 2018 of cities that are NOT in the following states:
New York, California, Texas.
*/
SELECT "city", "state", "population_estimate_2018" FROM "cities" WHERE "state" NOT IN('New York', 'California', 'Texas');
\echo ========= Problem 3.7 ====================================================
\echo
/*
3.7) Write a SQL query that uses a WHERE clause with the LIKE operator to get
the city, state, and estimated population in 2018 of cities that start with
the letter "S".
(Note: See the PostgreSQL doc on Pattern Matching for more information.)
*/
SELECT "city", "state", "population_estimate_2018" FROM "cities" WHERE "city" LIKE 'S%';
\echo ========= Problem 3.8 ====================================================
\echo
/*
3.8) Write a SQL query that uses a WHERE clause to find the cities with either a
land area of over 400 square miles OR a population over 2 million people
(or 2,000,000 people). Show the city name, the land area, and the estimated
population in 2018.
*/
SELECT "city", "land_area_sq_mi_2016", "population_estimate_2018" FROM "cities" WHERE "population_estimate_2018" >= 2000000 OR "land_area_sq_mi_2016" >= 400;
\echo ========= Problem 3.9 ====================================================
\echo
/*
3.9) Write a SQL query that uses a WHERE clause to find the cities with either a
land area of over 400 square miles OR a population over 2 million people
(or 2,000,000 people) -- but not the cities that have both. Show the city
name, the land area, and the estimated population in 2018.
*/
SELECT "city", "land_area_sq_mi_2016", "population_estimate_2018" FROM "cities" WHERE ("population_estimate_2018" >= 2000000 OR "land_area_sq_mi_2016" >= 400) AND NOT ("population_estimate_2018" >= 2000000 AND "land_area_sq_mi_2016" >= 400);
\echo ========= Problem 3.10 ===================================================
\echo
/*
3.10) Write a SQL query that uses a WHERE clause to find the cities where the
population has increased by over 200,000 people from 2010 to 2018. Show
the city name, the estimated population in 2018, and the census population
in 2010.
*/
SELECT "city", "population_estimate_2018", "population_census_2010" FROM "cities" WHERE ("population_estimate_2018" - "population_census_2010") > 200000;
---- Phase 4: Use a JOIN operation ---------------------------------------------
-- Retrieve rows from multiple tables joining on a foreign key.
-- The "airports" table has a foreign key called city_id that references the id
-- column in the "cities" table.
--------------------------------------------------------------------------------
\echo ========= Problem 4.1 ====================================================
\echo
/*
4.1) Write a SQL query using an INNER JOIN to join data from the "cities" table
with data from the "airports" table using the city_id foreign key. Show the
airport names and city names only.
*/
SELECT "name", "city" FROM "cities" INNER JOIN "airports" ON ("airports"."city_id" = "cities"."id");
\echo ========= Problem 4.2 ====================================================
\echo
/*
4.2) Write a SQL query using an INNER JOIN to join data from the "cities" table
with data from the "airports" table to find out how many airports are in
New York City using the city name.
(Note: Use the aggregate function COUNT() to count the number of matching
rows.)
*/
SELECT COUNT("city_id") FROM "cities" INNER JOIN "airports" ON ("airports"."city_id" = "cities"."id") WHERE "city" = 'New York';
--------------------------------------------------------------------------------
---- Bonuses:
--------------------------------------------------------------------------------
\echo ========= Problem B.1 ====================================================
\echo
/*
B.1) Apostrophe: Write a SQL query to get all three ID codes (the Federal
Aviation Administration (FAA) ID, the International Air Transport
Association (IATA) ID, and the International Civil Aviation Organization
(ICAO) ID) from the "airports" table for Chicago O'Hare International
Airport.
(Note: You'll need to escape the quotation mark in O'Hare. See How to
include a single quote in a SQL query.)
*/
SELECT "faa_id", "iata_id", "icao_id" FROM "airports" WHERE name = 'Chicago O''Hare International Airport';
\echo ========= Problem B.2 ====================================================
\echo
/*
B.2) Formatting Commas: Refactor Phase 2, Query #1 to turn the INT for estimated
population in 2018 into a character string with commas. (Note: See Data
Type Formatting Functions)
* Phase 2, Query #1: Write a SQL query that returns the city, state, and
estimated population in 2018 from the "cities" table.
*/
SELECT "city", "state", TO_CHAR("population_estimate_2018", 'FM9,999,999') FROM "cities";
\echo ========= Problem B.3 ====================================================
\echo
/*
B.3) Decimals and Rounding: Refactor Phase 3, Query #5 to turn number of
millions from an integer into a decimal rounded to a precision of two
decimal places.
(Note: See Numeric Types and the ROUND function.)
* Phase 3, Query #5: Write a SQL query to get the city and estimated
population in 2018 in number of millions (i.e. without zeroes at the
end: 1 million), and that uses a WHERE clause to return only the cities
in Texas.
*/
SELECT "city", ROUND("population_estimate_2018"/1000000.0, 2) || ' million' AS "2018_estimated_population" FROM "cities" WHERE "state" = 'Texas';
\echo ========= Problem B.4 ====================================================
\echo
/*
B.4) ORDER BY and LIMIT Clauses: Refactor Phase 3, Query #10 to return only one
city with the biggest population increase from 2010 to 2018. Show the city
name, the estimated population in 2018, and the census population in 2010
for that city.
(Note: You'll do the same calculation as before, but instead of comparing
it to 200,000, use the ORDER BY Clause with the LIMIT Clause to sort the
results and grab only the top result.)
* Phase 3, Query #10: Write a SQL query that uses a WHERE clause to find
the cities where the population has increased by over 200,000 people from
2010 to 2018. Show the city name, the estimated population in 2018, and
the census population in 2010.
*/
SELECT "city", "population_estimate_2018", "population_census_2010" FROM "cities" ORDER BY ("population_estimate_2018" - "population_census_2010") DESC LIMIT 1;
\echo ========= (done!) ========================================================