-
Notifications
You must be signed in to change notification settings - Fork 2
/
DVDRental Database Queries.sql
296 lines (235 loc) · 7.93 KB
/
DVDRental Database 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
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
/* If you are a beginner, refer to the creating and querying an SQL Database file.
Comments here will serve as form of possible questions. Let your curiousity drive you */
-- Show me actor's first_name, last_name that have Nick, Ed and Jennifer as their firstnames.
SELECT first_name,
last_name
FROM
actor
WHERE
first_name IN ('Nick','Ed', 'Jennifer');
-- Show me only last_name of actor whose first names are Ed, Nick and Jennifer
SELECT
last_name
FROM
actor
WHERE
first_name in ('Ed','Nick','Jennifer');
-- Show me all the details of the address table
SELECT *
FROM
address;
-- I want to see district & phone in descending order that are in the address table
SELECT
district,
phone
FROM
address
ORDER BY
district DESC,
phone DESC;
-- From fiilm table, show me title, film_id. from Inventory table, show me inventory_id. Do this when film_id on film table match film_id on the inventory table.
SELECT film.title,
film.film_id,
inventory.inventory_id
FROM
film
INNER JOIN
inventory
ON
film.film_id = inventory.film_id;
-- show me first_name, last_name of actors whose first_name (Ed, Nick and Jennifer) are distinct
SELECT
first_name,
last_name
FROM
actor
WHERE
first_name
IN
(Select
DISTINCT
first_name
FROM
actor
WHERE
first_name IN ('Nick', 'Ed', 'Jennifer'));
-- show me top 5 rows from inventory table and rental table.
SELECT inventory.*, rental.*
FROM inventory
JOIN rental
ON rental.inventory_id = inventory.inventory_id
LIMIT 5;
-- From the rental and payment table, show me just 10 rows of rental_id, rental_date, payment_id and ordedered by amount in descending order.
SELECT rental.rental_id,
rental.rental_date,
payment.payment_id,
payment.amount
FROM rental
JOIN payment
ON payment.rental_id = rental.rental_id
ORDER BY amount DESC
LIMIT 10;
-- from film_category table, film and film_actor table, I want to see 5 rows of film_id, category_id, title, rental_rate from the three tables.
SELECT film_category.film_id,
film_category.category_id,
film.film_id,
film.title,
film.rental_rate,
film_actor.film_id
FROM film_category
JOIN film
ON film.film_id = film_category.film_id
JOIN film_actor
ON film_actor.film_id = film.film_id
LIMIT 5;
-- Show me all the other details in the actor table where actor_id is empty
SELECT *
FROM actor
WHERE actor_id IS NULL;
-- Show me all the other details in the actor table where actor_id is not empty
SELECT *
FROM actor
WHERE actor_id IS NOT NULL;
-- I want to see number of non-empty rows in film table
SELECT COUNT(*)
FROM film;
--I want to see number of film_id in film table
SELECT COUNT
(film_id)
FROM film;
-- Unlike count, sum can only be used for numeric columns. I want to see the sum of amount from the payment table, let the output title be sum_amt.
SELECT SUM(Payment.amount) AS sum_amt
FROM payment;
-- I want to see both Maximum and minimun amount in the payment table
SELECT MAX(amount) AS Max_amt,
MIN(amount) AS Min_amt
FROM payment;
-- calculate the average amount by using the COUNT and SUM command. Show the maximun and minimun amount
SELECT SUM(payment.amount) AS sum_amt,
COUNT(amount) AS cnt_amt,
MAX(amount) AS Max_amt,
MIN(amount) AS Min_amt,
AVG(amount) AS avg_amt,
SUM(payment.amount) / COUNT(amount) AS avg_amt2
FROM payment;
-- show the sum of payment made by each payment_id
SELECT payment.payment_id,
SUM(Payment.amount) AS sum_amt
FROM payment
GROUP BY payment_id
ORDER BY payment_id;
-- from the actor table, show me unique first and last names. PS, DISTINCT can only be used with SELECT
SELECT DISTINCT first_name,
last_name
FROM actor;
-- show the sum of amount by each payment id that is greater then 5.99
SELECT payment.payment_id,
SUM(Payment.amount) AS sum_amt
FROM payment
GROUP BY 1
HAVING SUM(payment.amount) >= 5.99;
--show the sum of rental_rate of films by month
SELECT DATE_TRUNC('month', film.last_update),
SUM(rental_rate)
FROM film
GROUP BY DATE_TRUNC('month', film.last_update)
ORDER BY DATE_TRUNC('month', film.last_update);
--show the sum of rental_rate of films by day of the week
SELECT DATE_PART('dow', film.last_update),
SUM(rental_rate)
FROM film
GROUP BY DATE_PART('dow', film.last_update)
ORDER BY DATE_PART('dow', film.last_update);
-- Show me film.id, film.title, film.description and film_length. categorize film.length into yes(film.length is less than 86) or no (film.length is greater than 86)
SELECT film.film_id,
film.title,
film.description,
film.length,
CASE WHEN film.length = 86 OR film.length <= 86 THEN 'Yes' ELSE 'NO' END AS Not_lnger_than_86
FROM film
ORDER BY film.film_id;
-- Show me the COUNT of the two categories above.
SELECT
CASE WHEN film.length = 86 OR film.length <= 86 THEN 'Yes' ELSE 'NO' END AS Not_lnger_than_86,
COUNT(*) Either
FROM film
GROUP BY 1;
-- SHow me film.id, film.title, film.description and film_length. categorize film.length into 4 categories(over 100, 86-100, 72-86 and under 72)
SELECT film.film_id,
film.title,
film.description,
film.length,
CASE WHEN film.length > 100 THEN 'Over 100'
WHEN film.length > 86 AND film.length <= 100 THEN '86 - 100'
WHEN film.length > 72 AND film.length <= 86 THEN '72 - 86'
ELSE '72 or under' END AS film_length
FROM film
ORDER BY film.film_id;
-- Show me the COUNT of the four categories above.
SELECT
CASE WHEN film.length > 100 THEN 'Over 100'
WHEN film.length > 86 AND film.length <= 100 THEN '86 - 100'
WHEN film.length > 72 AND film.length <= 86 THEN '72 - 86'
ELSE '72 or under' END AS film_length,
COUNT(*) AS movie_length_cat
FROM film
GROUP BY 1;
-- Seperate the first three, last 8 number of phone in the address table into another column
SELECT district,
city_id,
phone,
LEFT(phone, 3) AS first_three,
RIGHT(phone, 8) AS last_8,
RIGHT(phone, LENGTH(phone) - 3) AS last_11_alt
FROM address;
-- view all the columns in city and add two columns to show city as upper and lower case
SELECT *,
UPPER(city),
LOWER(city)
FROM CITY;
/* STRPOS can be used for comma(,), space( ) and fullstop(.) Split the email to show the name in caps before the fullstop(.)
If you omit -1 in the LEFT command, the result will have fullstop(.) at the end */
SELECT first_name,
last_name,
email,
STRPOS(customer.email, '.'),
POSITION('.' IN customer.email),
LOWER(LEFT(customer.email, POSITION('.' IN customer.email)-1)) AS email_name_low,
UPPER(LEFT(customer.email, POSITION('.' IN customer.email)-1)) AS email_name,
LOWER(LEFT(customer.email, POSITION('.' IN customer.email))) AS email_wo_1
FROM customer
LIMIT 5;
-- split the street number from the address column
SELECT address,
district,
city_id,
STRPOS(address, ' '),
POSITION(' ' IN address),
LEFT(address, POSITION(' ' IN address)-1) as strt_number
FROM address
LIMIT 5;
-- Combine first_name and last_name from the customer table to become full_name. PS: You can use either CONCAT or ||
SELECT
first_name,
last_name,
CONCAT(first_name, ' ', last_name) AS full_name,
first_name || ' ' || last_name AS full_name2
FROM customer;
SELECT actor.first_name,
actor.last_name,
actor.first_name || ' ' || actor.last_name AS full_name,
film.title,
film.description,
film.length,
CONCAT(actor.first_name, ' ', actor.last_name ) AS full
FROM film_actor
JOIN actor ON film_actor.actor_id = actor.actor_id
JOIN film ON film.film_id = film_actor.film_id;
SELECT
film.title,
film.length,
CONCAT(actor.first_name, ' ', actor.last_name ) AS full
FROM film_actor
JOIN actor ON film_actor.actor_id = actor.actor_id
JOIN film ON film.film_id = film_actor.film_id
WHERE film.length > 60;