More advanced SQL queries
Write the following queries using the same database as
last time. In case you have forgotten the language lesson, this hopefully is of some help:
- Jasen = member
- Nimi = name
- Osoite = address
- LiittymisPVM = the date of joining
- Vuokraus = renting
- VuokrausPVM = the date of renting
- PalautusPVM = the date of returning
- Maksu = payment
- Palautettu = returned
- Nauha = tape
- Ostopaikka = the seller
- Ostopaiva = the date of buying
- Ostohinta = the cost price
- Elokuva = the movie
- Jakelija = the distributor
- Arvio = the critique
- Vuokrahinta = the rent out price
- Search all the members (jasen) that have joined before anyone living in the address (osoite) Meikämannentie 12.
| JasenID |
Nimi |
Osoite |
LiittymisPVM |
| 7 |
Ville Vidiootti |
Nörttikuja 3 |
5.4.1990 |
| 8 |
Leila Leffafani |
Leffatie 1 |
1.1.1990 |
- Find the top three movies with the most expensive rent out price (vuokrahinta).
| ElokuvaID |
Nimi |
vuokrahinta |
| 5 |
Proof of life |
15 |
| 6 |
Crouching tiger, hidden dragon |
20 |
| 10 |
Remember the Titans |
15 |
- Calculate how much money the three movies with the highest rent out prices have brought to the house.
- How many copies of the most popular tape (nauha) have been rented?
- List all the tapes (nauha) that have been rented once at max including the ones that have never been rented.
Write two queries, with and without outer joins.
| NauhaId |
lkm |
| 3 |
0 |
| 4 |
0 |
| 10 |
0 |
| 11 |
0 |
| 12 |
1 |
| 13 |
0 |
| 14 |
0 |
| 15 |
1 |
| 16 |
0 |
| 17 |
0 |
| 18 |
0 |
| 19 |
0 |
- Calculate how many times tapes distributed by 20th Century Fox and bought after 1.1.1990 (including January 1st)
have been rented. Write the query without joins, only use subqueries.
- Search the ID´s (nauhaID) of all tapes that have not been rented.
Make one query with outer join, another with subquery and a third one using an EXISTS statement.
| NauhaID |
| 3 |
| 4 |
| 10 |
| 11 |
| 13 |
| 14 |
| 16 |
| 17 |
| 18 |
| 19 |