More advanced SQL queries

Open a connection with ODBC Query Tool to the course databaseserver using database demo4 with username demo4 and password demo4.

• 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

On the exercises number 3 and 4 you have to use INTERVAL-datatype and CAST Näissä demoissa joudut tehtävissä 3 ja 4 hieman tutustumaan INTERVAL-tietotyyppiin ja CAST-funktioon joista ei ole ollut juurikaan esimerkkejä luennoilla.

CAST

CAST-funktiolla voi muuntaa tietoa tietotyypistä toiseen. Voit esim. napata jostakin merkkijonosta osan, joka sisältää kokonaisluvun. Tämän osan voit muuntaa oikeasti kokonaisluvuksi ja käyttää sen jälkeen laskutoimituksissa. Useimmiten tietokannanhallintajärjestelmä osaa tehdä muunnokset automaattisesti mutta joskus ne on itse pakko määritellä.

CAST(data AS tietotyyppi)

Esimerkkejä

CAST('2000' AS INTEGER)

CAST(SUBSTRING(kentta FROM 5) AS DATE)

INTERVAL

INTERVAL on harvemmin käytetty ja tuettu tietotyyppi jolla voidaan esittää aikavälejä. Näitä aikavälejä voidaan esim.vähentää tai lisäillä DATE-tyyppisiin tietoihin

INTERVAL-tyyppinen tieto esitetään heittomerkkien sisällä seuraavien esimerkkien mukaisesti:

'1 YEAR 2 MONTH 3 DAY 3 HOUR 20 MIN 10 SEC'

'3 DAY'

'3 HOUR 5 MIN 10 SEC'

Ehtolauseissa INTERVAL-tietotyyppiä käytettäessä pitää merkkijonon eteen kirjoittaa INTERVAL jotta tietokannanhallintajärjestelmä ymmärtää, että kyseessä ei ole tavallinen merkkijono.

WHERE Paivamaara - INTERVAL '3 MONTH' > '2002-02-02'

Write the following SQL-queries

1. How many movies have been rent on each month? You have to use EXTRACT-function to get the month out of rental date and then group the result with the month.

   kuukausi	count
   5	15
   6	1
   7	1

2. List the surnames of all the members. You must use SUBSTRING- and POSITION-functions. You must find the first space character in the nimi-field and then take the rest of the nimi-field beginning from the position of the first space character.

   substring
   Lahtonen
   Heinonen
   Meikäläinen
   Meikäläinen
   Opiskelija
   Vidiootti
   Leffafani

3. Find out how many days has gone between the first and last rental of each member. You can get some tips from the course mailing list archives (only in finnish).

   jasenid	interval
   2	2 days
   3	57 days
   7	13 days
   8	31 days

4. Count how many times each member has rented during the last five years. You can get some tips from mailing list archive.

   jasenid	count
   2	5
   3	2
   7	8
   8	2

5. 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

6. 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

7. Calculate how much money the three movies with the highest rent out prices have brought to the house.

   Summa
   205

8. How many copies of the most popular tape (nauha) have been rented?

   NauhaID	lkm
   6	4

9. 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

10. 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.

    Lkm
    2

11. 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