· 6 years ago · May 21, 2019, 10:44 AM
11. Explain a subquery in your own words.
2
3 A subquery is a nested selected statement within a query.
4
52. Where can you use a subquery within a SELECT statement?
6
7 Subqueries can be found in the SELECT, FROM, or WHERE clauses of a query.
8
93. When would you employ a subquery?
10
11 When we know how to search for a value or set of values within a database, but do not know the exact value (membership question)
12 or when the subquery is required to return a subset of the table involved in the query.
13
144. Explain a row constructor in your own words.
15
16 A row constructor allows us to create a "sample" row for our query to use as a comparison when searching our table
17 for data.
18
195. What happens if a row in the subquery result provides a NULL value to the comparison?
20
21 The result of the comparison will be NULL.
22
236. What are the ways to use a subquery within a WHERE clause?
24
25 EXISTS, NOT EXISTS, IN, NOT IN, ANY, SOME, ALL
26
277. Using this Adoption schema and data, please write queries to retrieve the following information and include the results:
28
29All volunteers. If the volunteer is fostering a dog, include each dog as well.
30
31 SELECT first_name, last_name, foster_name FROM
32 (SELECT v.first_name, v.last_name, dogs.name AS foster_name FROM volunteers AS v
33 LEFT OUTER JOIN dogs ON v.foster_dog_id = dogs.id )
34 AS volunteers_with_dogs;
35
36
37 first_name last_name foster_name
38 Rubeus Hagrid Munchkin
39 Marjorie Dursley Marmaduke
40 Sirius Black null
41 Remus Lupin null
42 Albus Dumbledore null
43
44
45The cat's name, adopter's name, and adopted date for each cat adopted within the past month to be displayed
46as part of the "Happy Tail" social media promotion which posts recent successful adoptions.
47
48 SELECT adopters.first_name, adopters.last_name, cats.name AS cat_name FROM adopters, cats WHERE (adopters.id, cats.id) IN (
49 SELECT adopter_id, cat_id FROM cat_adoptions WHERE date > CURRENT_DATE - INTERVAL '30 DAYS');
50
51 first_name last_name cat_name
52 Arabella Figg Mushi
53 Argus Filch Victoire
54
55
56Adopters who have not yet chosen a dog to adopt and generate all possible combinations of adopters and available dogs.
57
58SELECT * FROM
59(SELECT dogs.name FROM dogs WHERE dogs.id NOT IN(
60 SELECT dog_id FROM dog_adoptions)) AS available_dogs
61CROSS JOIN
62(SELECT adopters.first_name, adopters.last_name FROM adopters WHERE id NOT IN
63 (SELECT adopter_id FROM dog_adoptions)) AS adopters;
64
65 name first_name last_name
66 Boujee Hermione Granger
67 Boujee Arabella Figg
68 Munchkin Hermione Granger
69 Munchkin Arabella Figg
70 Marley Hermione Granger
71 Marley Arabella Figg
72 Lassie Hermione Granger
73 Lassie Arabella Figg
74 Marmaduke Hermione Granger
75 Marmaduke Arabella Figg
76
77Lists of all cats and all dogs who have not been adopted.
78
79 All available dogs -
80
81 SELECT dogs.name FROM dogs WHERE id NOT IN (
82 SELECT dog_id FROM dog_adoptions);
83
84 name
85 Boujee
86 Munchkin
87 Marley
88 Lassie
89 Marmaduke
90
91 All available cats -
92
93 SELECT cats.name FROM cats WHERE id NOT IN (
94 SELECT cat_id FROM cat_adoptions);
95
96 name
97 Seashell
98 Nala
99
100
101The name of the person who adopted Rosco.
102
103 SELECT adopters.first_name, adopters.last_name FROM adopters WHERE adopters.id IN (
104 SELECT adopter_id FROM dog_adoptions);
105
106 first_name last_name
107 Argus Filch
108
109
1108. Using this Library schema and data, write queries applying the following scenarios, and include the results:
111
112To determine if the library should buy more copies of a given book, please provide the names and position,
113in order, of all of the patrons with a hold (request for a book with all copies checked out) on "Advanced Potion-Making".
114
115 SELECT patrons.name, h.rank FROM patrons JOIN (
116 SELECT patron_id, rank AS rank FROM holds WHERE isbn = '9136884926') h ON patrons.id = h.patron_id;
117
118 name rank
119 Terry Boot 1
120 Cedric Diggory 2
121
122
123Make a list of all book titles and denote whether or not a copy of that book is checked out.
124
125 SELECT books.title,t.checked_out_date, t.checked_in_date FROM books LEFT OUTER JOIN (
126 SELECT isbn, checked_out_date, checked_in_date FROM transactions WHERE checked_in_date IS NULL) t on books.isbn = t.isbn;
127
128 title checked_out_date checked_in_date
129 Hogwarts: A History null null
130 Fantastic Beasts and Where to Find Them 2019-05-08T00:00:00.000Z null
131 Advanced Potion-Making 2019-05-06T00:00:00.000Z null
132
133
134In an effort to learn which books take longer to read, the librarians would like you to create a list of average checked out
135time by book name in the past month.
136
137 SELECT books.title, t.avg FROM books JOIN
138 (SELECT isbn, AVG(checked_in_date - checked_out_date) AS avg FROM transactions GROUP BY isbn) t on books.isbn = t.isbn;
139
140 title avg
141 Hogwarts: A History 1.00000000000000000000
142 Advanced Potion-Making 1.00000000000000000000
143 Fantastic Beasts and Where to Find Them 2.0000000000000000
144
145
146In order to learn which items should be retired, make a list of all books that have not been checked out in the past 5 years.
147
148 SELECT books.title FROM books WHERE books.isbn IN (
149 SELECT isbn FROM transactions GROUP BY isbn HAVING MAX(checked_in_date) <= CURRENT_DATE - INTERVAL '5 YEARS' AND
150 MAX(checked_out_date) <= CURRENT_DATE - INTERVAL '5 YEARS');
151
152 title
153 Hogwarts: A History
154
155
156List all of the library patrons. If they have one or more books checked out, correspond the books to the patrons.
157
158 SELECT patrons.name, t.checked_out_title FROM patrons LEFT OUTER JOIN (
159 SELECT transactions.patron_id, transactions.isbn, books.title AS checked_out_title FROM transactions JOIN books
160 ON transactions.isbn = books.isbn WHERE checked_in_date IS NULL) t ON patrons.id = t.patron_id ;
161
162 name checked_out_title
163 Hermione Granger null
164 Terry Boot Advanced Potion-Making
165 Padma Patil null
166 Cho Chang null
167 Cedric Diggory Fantastic Beasts and Where to Find Them
168
169
1709. Using this Flight schema and data, write queries applying the following scenarios, and include the results:
171
172 To determine the most profitable airplanes, find all airplane models where each flight has had over 250 paying customers
173 in the past month.
174
175 SELECT flights.airplane_model FROM flights JOIN (
176 SELECT flight_number FROM transactions WHERE seats_sold >= 250 AND date >= CURRENT_DATE - INTERVAL '30 DAYS') t
177 ON t.flight_number = flights.flight_number;
178
179 airplane_model
180 Boeing 787
181 Airbus A330
182 Boeing 777
183 Airbus A380
184
185
186 To determine the most profitable flights, find all destination-origin pairs where 90% or more of the seats have been
187 sold in the past month.
188
189 SELECT * FROM transactions JOIN (
190 SELECT a.seat_capacity AS capacity, flights.flight_number, flights.origin, flights.destination FROM airplanes AS a
191 JOIN flights ON a.model = flights.airplane_model) f ON transactions.flight_number = f.flight_number
192 WHERE (CAST(transactions.seats_sold AS float)/CAST(capacity AS float)) >= .9 AND transactions.date >= CURRENT_DATE - INTERVAL '30 DAYS';
193
194 id seats_sold total_revenue total_cost flight_number date capacity flight_number origin destination
195 2 287 155928.9800 112485.6400 137 2019-05-05T00:00:00.000Z 287 137 LHR JFK
196 3 380 288909.1900 261092.2200 8932 2019-04-28T00:00:00.000Z 400 8932 SFO HKG
197 6 490 342981.5400 251112.8600 57 2019-04-20T00:00:00.000Z 544 57 LHR JNB
198
199
200
201 The airline is looking to expand its presence in Asia and globally. Find the total revenue of any flight
202 (not time restricted) arriving at or departing from Singapore (SIN).
203
204 SELECT total_revenue FROM transactions WHERE flight_number IN (
205 SELECT flight_number FROM flights WHERE origin = 'SIN' OR destination = 'SIN');
206
207 total_revenue
208 250394.7000
209 131992.1200
210
211
212Compare the subqueries you've written above. Compare them to the joins you wrote in Checkpoint 6. Which ones are more readable? Which were more logical to write?
213
214 For the most part, I feel that the subquery versions are more readable than the joins.