Demo 3 | Demo 5

SQL queries

Save the file demo4.mdb to the drive U:. Start Access and open demo4.mdb. There you have a database just like the one in the figure below. The database is in Finnish, so just to help you out:

videorekisteri

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. Select Queries|Create query in Design view to write SQL. Close the Show Table dialog and select View|SQL View from the toolbar. After testing your query (Query|Run) select View|SQL View again to get back to the editor.

  1. Search all the members (jasen) and the data associated from the Jasen relation.
    JasenID Nimi Osoite LiittymisPVM
    2 Tommi Lahtonen Nörttikuja 3 1.1.1999
    3 Petri Heinonen Kivakatu 2 13.12.1998
    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
    7 Ville Vidiootti Nörttikuja 3 5.4.1990
    8 Leila Leffafani Leffatie 1 1.1.1990
  2. 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
  3. Search from the Jasen relation the names of the members that have joined before 1.5.1998. In Access any information that is a date needs to be handled in an unstandardized way
    4. Nimi Osoite
    Ville Vidiootti Nörttikuja 3
    Leila Leffafani Leffatie 1
  4. Search everyone that as the letter M in their names from the Jasen relation. The functioning of the LIKE operator in Access is dissimilar to the standard. Neither can Access differentiate between lower case and capital letters (M and m are just the same for Access).
    Nimi Osoite
    Tommi Lahtonen Nörttikuja 3
    Matti Meikäläinen Meikämannentie 12
    Maija Meikäläinen Meikämannentie 12
  5. 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
  6. Search all the information about every rent out where at least 15 mk was spent.
    JasenID NauhaID VuokrausPVM PalautusPVM Palautettu Maksu
    2 2 13.5.2000 14.5.2000 14.5.2000 15
    3 5 16.5.2000 17.5.2000 18.5.2000 30
    7 6 17.5.2000 18.5.2000 20.5.2000 30
    7 7 13.5.2000 14.5.2000 14.5.2000 15
    7 8 13.5.2000 14.5.2000 14.5.2000 25
    7 9 13.5.2000 14.5.2000 14.5.2000 15
    2 15 13.5.2000 14.5.2000 14.5.2000 15
    2 12 13.5.2000 14.5.2000 14.5.2000 15
    3 5 12.7.2000 13.7.2000 25.7.2000 100
  7. 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
  8. 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
    1.1.1990
    1.1.1990
    1.1.1990
    16.7.1998
    15.1.1997
    2.7.1998
    1.3.1999
  9. 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
  10. 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
  11. Find all the members that have the letter M in their names and that have joined before 1.1.1999.
    Nimi
    Maija Meikäläinen
  12. 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
    Olli Opiskelija
    Leila Leffafani
  13. 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
  14. Calculate how much money on average one rental affair has brought.
    Average
    19,7058823529412
  15. Count how many rent outs there has been.
    Number
    17
  16. Count how many times the member (jasen) number 2 has rented something.
    Number
    5
  17. Calculate how many times each tape has been rented and how much each of them have brought money to the house.
    NauhaID Number Sum
    2 2 25
    5 2 130
    6 4 60
    7 2 25
    8 3 40
    9 2 25
    12 1 15
    15 1 15
  18. 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
  19. 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
  20. How many times mr. Tommi Lahtonen has rented movies?
    Number
    5
  21. 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 Number Sum
    Leila Leffafani 2 20
    Petri Heinonen 2 130
    Tommi Lahtonen 5 60
    Ville Vidiootti 8 125
  22. Find out the names of the persons who have rented films more than twice and how much money they have spent for that.
    Nimi Sum
    Tommi Lahtonen 60
    Ville Vidiootti 125
  23. Find out how much rental money the films of every distributor have brought to the house.
    Nimi Sum
    20th Century Fox 25
    Disney 220
    Finnkino 25
    UIP 65
  24. 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 Number Sum
    Tommi Lahtonen 5 60
    Ville Vidiootti 8 125
    Petri Heinonen 2 130
  25. 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 :)

http://
© Tommi Lahtonen ()<URL: http://www.iki.fi/hazor/>
Demo 3 | Demo 5