Monimutkaisempia SQL-kyselyjä

English version

Ota ODBC Query Toolilla yhteys kurssin tietokantapalvelimella olevaan demo4-tietokantaan. Käytä käyttäjätunnusta demo4 ja salasanaa demo4.

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'

Kirjoita seuraavat SQL-kyselyt

1. Kuinka paljon elokuvia on vuokrattu minäkin kuukautena? Joudut käyttämään apunasi jotakin luennolla 7 esiteltyä skalaarifunktiota. Sinun pitää erottaa vuokrapäivämäärästä pelkkä kuukausi ja ryhmitellä lopputulos sen mukaan.

   kuukausi	count
   5	15
   6	1
   7	1

2. Hae kaikkien jäsenten sukunimet. Joudut käyttämään apunasi kahta luennolla 7 esiteltyä skalaarifunktiota. Sinun pitää etsiä nimi-kentästä ensimmäinen välilyönti ja siitä lukien leikata lopputulokseen loppuosa nimi-kentästä.

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

3. Selvitä kuinka pitkällä aikavälillä (päiviä) kukin jäsen on vuokrannut elokuvia. Vinkkiä voi katsoa postituslistalle tulleista viesteistä. Tarvitset tässä INTERVAL-tyyppistä tietoa. Halutessasi voit muuntaa lopputuloksena saamasi päivien lukumäärän CAST-funktiolla INTERVAL-tyyppiseksi niin saat tismalleen alla näkyvän hakutuloksen. Tässä muunnoksessa tarvitset myös ||-operaattorin apua. .

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

4. Laske kuinka monta kertaa kukin jäsen on vuokrannut viimeisen viiden vuoden aikana. Vinkkiä voi katsoa postituslistalle tulleista viesteistä.

   jasenid	count
   2	5
   3	2
   7	8
   8	2

5. Hae kaikki ne jäsenet, jotka ovat liittyneet aikaisemmin kuin kukaan osoitteessa Meikämannentie 12 asuva.

   JasenID	Nimi	Osoite	LiittymisPVM
   7	Ville Vidiootti	Nörttikuja 3	5.4.1990
   8	Leila Leffafani	Leffatie 1	1.1.1990

6. Hae kolme vuokraushinnaltaan kalleinta elokuvaa

   ElokuvaID	Nimi	vuokrahinta
   5	Proof of life	15
   6	Crouching tiger, hidden dragon	20
   10	Remember the Titans	15

7. Laske paljonko rahaa ovat tuoneet kolme vuokraushinnaltaan kalleinta elokuvaa

   Summa
   205

8. Hae montako kappaletta eniten vuokrattua kasettia/kasetteja on vuokrattu

   NauhaID	lkm
   6	4

9. Hae lista niistä nauhoista joita on vuokrattu enintään yhden kerran (Huom. mukaan saatava myös ne nauhat joita ei ole vuokrattu kertaakaan!). Tee hausta kaksi versiota, toinen ulkoliitoksen avulla ja toinen ilman ulkoliitosta.

   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. Laske montako kertaa on vuokrattu nauhoja, jotka ovat 20th Century Foxin toimittamia ja ostettu 1.1.1990 tai sen jälkeen. Tee haku ilman liitoksia eli käytä vain alikyselyjä.

    Lkm
    2

11. Hae kaikkien niiden nauhojen ID:t joita ei ole vuokrattu. Tee haku käyttäen ulkoliitosta, toinen versio käyttäen alikyselyä ja kolmas versio käyttäen EXISTS-lausetta.

    NauhaID
    3
    4
    10
    11
    13
    14
    16
    17
    18
    19