SQL queries
The database needed on this demonstration is in Finnish, so just to help you out:
- 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
Take a connection with ODBC Query Tool to demo4-database in the courses
database server. Use username demo4 with password 'demo4'.
Write the following SQL queries and save every query using the numbering of the excercises in the names.
The correct search results are included in the excercises.
- Search all the members (jasen) and the data associated from the Jasen relation.
| JasenID |
Nimi |
Osoite |
LiittymisPVM |
| 2 |
Tommi Lahtonen |
Nörttikuja 3 |
1999-01-01 |
| 3 |
Petri Heinonen |
Kivakatu 2 |
1998-12-13 |
| 4 |
Matti Meikäläinen |
Meikämannentie 12 |
1999-02-15 |
| 5 |
Maija Meikäläinen |
Meikämannentie 12 |
1998-04-01 |
| 6 |
Olli Opiskelija |
Nörttikatu 15 |
2000-01-01 |
| 7 |
Ville Vidiootti |
Nörttikuja 3 |
1990-04-05 |
| 8 |
Leila Leffafani |
Leffatie 1 |
1990-01-01 |
- Search the names (Nimi) and the addresses (Osoite) of the members from the Jasen relation.
| Nimi |
Osoite |
| Tommi Lahtonen |
Nörttikuja 3 |
| Petri Heinonen |
Kivakatu 2 |
| Matti Meikäläinen |
Meikämannentie 12 |
| Maija Meikäläinen |
Meikämannentie 12 |
| Olli Opiskelija |
Nörttikatu 15 |
| Ville Vidiootti |
Nörttikuja 3 |
| Leila Leffafani |
Leffatie 1 |
- Search from the Jasen relation the names of the members that have joined before 1.5.1998.
| Nimi |
Osoite |
| Matti Meikäläinen |
Meikämannentie 12 |
| Ville Vidiootti |
Nörttikuja 3 |
| Leila Leffafani |
Leffatie 1 |
- Search everyone that as the letter M in their names from the Jasen relation.
| Nimi |
Osoite |
| Matti Meikäläinen |
Meikämannentie 12 |
| Maija Meikäläinen |
Meikämannentie 12 |
- Search all the tapes (Nauha) (that is all the fields) that have been bought from the distributor 2
(jakelija) and contain the movie number 3 (elokuva) from the Nauha relation.
| NauhaID |
Ostopaikka |
Ostopaiva |
Ostohinta |
Elokuva |
| 3 |
2 |
1.1.1990 |
100 |
3 |
- Search all the information about every rent out where at least 15 mk was spent.
| JasenID |
NauhaID |
VuokrausPVM |
PalautusPVM |
Palautettu |
Maksu |
| 2 |
2 |
2000-05-13 |
2000-05-14 |
2000-05-14 |
15 |
| 3 |
5 |
2000-05-16 |
2000-05-17 |
2000-05-18 |
30 |
| 7 |
6 |
2000-05-17 |
2000-05-18 |
2000-05-20 |
30 |
| 7 |
7 |
2000-05-13 |
2000-05-14 |
2000-05-14 |
15 |
| 7 |
8 |
2000-05-13 |
2000-05-14 |
2000-05-14 |
25 |
| 7 |
9 |
2000-05-13 |
2000-05-14 |
2000-05-14 |
15 |
| 2 |
15 |
2000-05-13 |
2000-05-14 |
2000-05-14 |
15 |
| 2 |
12 |
2000-05-13 |
2000-05-14 |
2000-05-14 |
15 |
| 3 |
5 |
2000-07-12 |
2000-07-13 |
2000-07-25 |
100 |
- From the Elokuva (movie) relation, search all the names of the movies that cost at least 10mk but not
more than 13mk to rent.
| Nimi |
| What women want |
| Chocolat |
| Enemy at the Gates |
| Almost Famous |
| Gladiator |
- Search the dates of buying (ostopaiva) for all the tapes (nauha) that have cost at least 100mk but not more than 110mk.
Use a BETWEEN statement.
| Ostopaiva |
| 1990-01-01 |
| 1990-01-01 |
| 1990-01-01 |
| 1998-07-16 |
| 1997-01-15 |
| 1998-07-02 |
| 1999-03-01 |
- Search the names of all the movies that cost 5mk, 10mk or 15mk to rent.
| Nimi |
| Proof of life |
| Gladiator |
| Traffic |
| Hannibal |
| Remember the Titans |
| Clockwork Orange |
- From the Elokuva relation, search the names of all the movies that begin with C and got more than
6 points as the critique (arvio).
| Nimi |
| Crouching tiger, hidden dragon |
| Clockwork Orange |
- Find all the members that have the letter M in their names and that have joined before 1.1.1999.
- Find the members that have the letter o but not the letter V in their names or have joined 1.1.1990.
| Nimi |
| Tommi Lahtonen |
| Petri Heinonen |
| Leila Leffafani |
- Search the members whose names begin with M and who live in Meikämannentie 12 or live in an address with the
ending "katu" and whose name ends with n.
| Nimi |
Osoite |
| Petri Heinonen |
Kivakatu 2 |
| Matti Meikäläinen |
Meikämannentie 12 |
| Maija Meikäläinen |
Meikämannentie 12 |
- Calculate how much money on average one rental affair has brought.
| Keskiarvo |
| 19,7058823529412 |
- Count how many rent outs there has been.
- Count how many times the member (jasen) number 2 has rented something.
- Calculate how many times each tape has been rented and how much each of them have brought money to the house.
| NauhaID |
Lukumäärä |
Summa |
| 2 |
2 |
25 |
| 5 |
2 |
130 |
| 6 |
4 |
60 |
| 7 |
2 |
25 |
| 8 |
3 |
40 |
| 9 |
2 |
25 |
| 12 |
1 |
15 |
| 15 |
1 |
15 |
- Write a query that gives all the movies (elokuva) on every tape (nauha), the cost price
(ostohinta) and the rent out price (vuokrahinta) of the tape. Sort the result of the query by the name (nimi)
of the movie.
| Nimi |
Ostohinta |
Vuokrahinta |
| Almost Famous |
100 |
12 |
| Almost Famous |
100 |
12 |
| Chocolat |
105 |
12 |
| Chocolat |
100 |
12 |
| Crouching tiger, hidden dragon |
115 |
20 |
| Enemy at the Gates |
100 |
12 |
| Gladiator |
127 |
10 |
| Hannibal |
137 |
5 |
| Hannibal |
99 |
5 |
| Hannibal |
100 |
5 |
| Hannibal |
110 |
5 |
| Proof of life |
115 |
15 |
| Proof of life |
120 |
15 |
| Proof of life |
114 |
15 |
| Proof of life |
99 |
15 |
| Remember the Titans |
113 |
15 |
| Remember the Titans |
150 |
15 |
| Traffic |
123 |
5 |
- Write a query that gives who has rented, when, at what price (payment) and which movie (the name of the movie) as a result.
| Jasen.nimi |
Elokuva.nimi |
Vuokrauspvm |
Maksu |
| Tommi Lahtonen |
Chocolat |
13.5.2000 |
15 |
| Tommi Lahtonen |
Chocolat |
14.5.2000 |
10 |
| Tommi Lahtonen |
Traffic |
15.5.2000 |
5 |
| Petri Heinonen |
Proof of life |
16.5.2000 |
30 |
| Ville Vidiootti |
Crouching tiger, hidden dragon |
17.5.2000 |
30 |
| Ville Vidiootti |
Gladiator |
13.5.2000 |
15 |
| Ville Vidiootti |
Traffic |
13.5.2000 |
25 |
| Ville Vidiootti |
Hannibal |
13.5.2000 |
15 |
| Leila Leffafani |
Crouching tiger, hidden dragon |
13.5.2000 |
10 |
| Tommi Lahtonen |
Proof of life |
13.5.2000 |
15 |
| Tommi Lahtonen |
Hannibal |
13.5.2000 |
15 |
| Petri Heinonen |
Proof of life |
12.7.2000 |
100 |
| Ville Vidiootti |
Crouching tiger, hidden dragon |
14.5.2000 |
10 |
| Ville Vidiootti |
Gladiator |
20.5.2000 |
10 |
| Ville Vidiootti |
Traffic |
25.5.2000 |
10 |
| Ville Vidiootti |
Hannibal |
26.5.2000 |
10 |
| Leila Leffafani |
Crouching tiger, hidden dragon |
13.6.2000 |
10 |
- How many times mr. Tommi Lahtonen has rented movies?
- Write a query for the following problem: Which members have rented movies, how many times and how much they
have payed for it altogether? Also find out the names of the members.
| Nimi |
Lukumäärä |
Yhteensä |
| Leila Leffafani |
2 |
20 |
| Petri Heinonen |
2 |
130 |
| Tommi Lahtonen |
5 |
60 |
| Ville Vidiootti |
8 |
125 |
- Find out the names of the persons who have rented films more than twice and how much money they have spent for that.
| Nimi |
Yhteensä |
| Tommi Lahtonen |
60 |
| Ville Vidiootti |
125 |
- Find out how much rental money the films of every distributor have brought to the house.
| Nimi |
Yhteensä |
| 20th Century Fox |
25 |
| Disney |
220 |
| Finnkino |
25 |
| UIP |
65 |
- Find the ones that have spent more than 30mk for renting films. How many movies each of them has rented?
Sort the results: the one that has spent less money should be the firts one in the list.
| Nimi |
Lukumäärä |
Summa |
| Tommi Lahtonen |
5 |
60 |
| Ville Vidiootti |
8 |
125 |
| Petri Heinonen |
2 |
130 |
- List all the information of the members that have never rented a film.
| JasenID |
Nimi |
Osoite |
LiittymisPVM |
| 4 |
Matti Meikäläinen |
Meikämannentie 12 |
15.2.1999 |
| 5 |
Maija Meikäläinen |
Meikämannentie 12 |
1.4.1998 |
| 6 |
Olli Opiskelija |
Nörttikatu 15 |
1.1.2000 |
Hope you had fun on this language lesson :)