SQL-kyselyjen vastaukset - Demo 4

Demo 4 vastaukset

Tässä kyselyjen mallivastaukset. Omat versiosi voivat olla hyvinkin erilaisella tavalla toteutettuja ja silti oikein. Epäselvissä tapauksissa varmista ohjaajalta kyselysi oikeellisuus.

1. Hae Jasen-taulusta kaikki jäsenet ja heidän tietonsa.

   SELECT *
   FROM Jasen
   ;

   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

2. Hae Jasen-taulusta Jasenien nimet ja osoitteet.

   SELECT Nimi, osoite
   FROM Jasen
   ;

   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. Hae Jasen-taulusta kaikkien niiden nimet ja osoitteet, jotka ovat liittyneet ennen 1.5.1998.

   SELECT Nimi, osoite
   FROM Jasen
   WHERE LiittymisPVM < '1998-5-1'
   ;

   Nimi	Osoite
   Maija Meikäläinen	Meikämannentie 12
   Ville Vidiootti	Nörttikuja 3
   Leila Leffafani	Leffatie 1

4. Hae Jasen-taulusta kaikkien niiden nimet ja osoitteet, joiden nimestä löytyy M-kirjain.

   SELECT Nimi, osoite
   FROM Jasen
   WHERE Nimi LIKE '%M%'
   ;

   Nimi	Osoite
   Matti Meikäläinen	Meikämannentie 12
   Maija Meikäläinen	Meikämannentie 12

5. Hae Nauha-taulusta kaikki ne nauhat (kaikki kentät), jotka on ostettu jakelija 2:lta ja joilla on elokuva nro 3.

   SELECT *
   FROM Nauha
   WHERE Ostopaikka = 2
   AND Elokuva = 3
   ;
   

   NauhaID	Ostopaikka	Ostopaiva	Ostohinta	Elokuva
   3	2	1.1.1990	100	3

6. Hae kaikki tiedot kaikista niistä vuokraustapahtumista, joissa on käytetty rahaa vähintään 15 mk.

   SELECT *
   FROM Vuokraus
   WHERE Maksu >= 15
   ;

   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

7. Hae kaikkien niiden elokuvien nimet Elokuva-taulusta, joiden vuokraus maksaa vähintään 10mk, mutta enintään 13mk.

   SELECT Nimi
   FROM Elokuva
   WHERE Vuokrahinta >= 10
   AND Vuokrahinta <=13
   ;

   Nimi
   What women want
   Chocolat
   Enemy at the Gates
   Almost Famous
   Gladiator

8. Hae kaikkien niiden nauhojen hankintapäivämäärät, jotka ovat maksaneet vähintään 100mk mutta enintään 110mk. Käytä BETWEEN-lausetta.

   SELECT Ostopaiva
   FROM Nauha
   WHERE Ostohinta BETWEEN 100 AND 110
   ;

   Ostopaiva
   1990-01-01
   1990-01-01
   1990-01-01
   1998-07-16
   1997-01-15
   1998-07-02
   1999-03-01

9. Hae kaikkien niiden elokuvien nimet, joiden vuokraus maksaa 5mk, 10mk tai 15mk.

   SELECT Nimi
   FROM Elokuva
   WHERE Vuokrahinta IN (5, 10, 15)
   ;

   Nimi
   Proof of life
   Gladiator
   Traffic
   Hannibal
   Remember the Titans
   Clockwork Orange

10. Hae Elokuva-taulusta kaikkien niiden elokuvien nimet, joiden nimi alkaa C-kirjaimella ja joiden arvio on suurempi kuin 6.

    SELECT Nimi
    FROM Elokuva
    WHERE Nimi LIKE 'C%'
    AND Arvio > 6
    ;

    Nimi
    Crouching tiger, hidden dragon
    Clockwork Orange

11. Hae kaikki ne jäsenet joiden nimessä esiintyy M-kirjain tai jotka ovat liittyneet ennen 1.1.1999.

    SELECT Nimi
    FROM Jasen
    WHERE Nimi LIKE '%M%'
    AND Liittymispvm < '1999-1-1'
    ;

    Nimi
    Maija Meikäläinen

12. Hae kaikki ne jäsenet, joiden nimessä esiintyy o-kirjain, mutta ei V-kirjainta tai jotka ovat liittyneet 1.1.1990.

    SELECT Nimi
    FROM Jasen
    WHERE Nimi LIKE '%o%'
    AND NOT Nimi LIKE '%V%' 
    OR Liittymispvm = '1990-1-1'
    ;

    Nimi
    Tommi Lahtonen
    Petri Heinonen
    Leila Leffafani

13. Hae kaikki Jäsenet, joiden nimi alkaa M-kirjaimella ja jotka asuvat Meikämannentie 12:ssa tai jotka asuvat jonkin kadun varrella (eivät tiellä tai kujalla tms) ja joiden nimi loppuu n-kirjaimeen.

    SELECT Nimi, osoite
    FROM Jasen
    WHERE (Nimi LIKE 'M%'
    AND Osoite = 'Meikämannentie 12')
    OR  (Osoite LIKE '%katu%'
    AND Nimi LIKE '%n')
    ;

    Nimi	Osoite
    Petri Heinonen	Kivakatu 2
    Matti Meikäläinen	Meikämannentie 12
    Maija Meikäläinen	Meikämannentie 12

14. Laske paljonko rahaa on keskimäärin tullut yhdestä vuokratapahtumasta.

    SELECT AVG(Maksu) AS Keskiarvo
    FROM Vuokraus
    ;

    Keskiarvo
    19,7058823529412

15. Laske montako vuokraustapahtumaa on ollut.

    SELECT COUNT(*) AS Lukumäärä
    FROM Vuokraus
    ;

    Lukumäärä
    17

16. Laske montako kertaa jäsen 2 on vuokrannut.

    SELECT COUNT(*) AS Lukumäärä
    FROM Vuokraus
    WHERE JasenID = 2
    ;

    Lukumäärä
    5

17. Laske montako kertaa kutakin vuokrattua nauhaa on yhteensä vuokrattu ja paljonko kukin näistä nauhoista on tuonut vuokratuloja.

    SELECT NauhaID, COUNT(*) AS Lukumäärä, SUM(Maksu) AS Summa
    FROM Vuokraus
    GROUP BY NauhaID
    ;

    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

18. Tee kysely, josta selviää jokaisella nauhalla olevan elokuvan nimi, ostohinta ja vuokraushinta. Järjestä lopputulos elokuvan nimen mukaan.

    SELECT Elokuva.nimi, Nauha.Ostohinta, Elokuva.Vuokrahinta
    FROM Elokuva, Nauha
    WHERE Elokuva.ElokuvaID = Nauha.Elokuva
    ORDER BY Elokuva.nimi
    ;

    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. Tee kysely josta selviää kuka on vuokrannut, minkänimisen elokuvan, paljonko vuokrauksesta on maksettu ja milloin nauha on vuokrattu.

    SELECT Jasen.nimi, Elokuva.nimi, Vuokraus.Vuokrauspvm, Vuokraus.Maksu
    FROM Jasen, Vuokraus, Nauha, Elokuva
    WHERE Jasen.JasenID = Vuokraus.JasenID
    AND Vuokraus.NauhaID = Nauha.NauhaID
    AND Nauha.Elokuva = Elokuva.ElokuvaID
    ;

    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. Montako kertaa Tommi Lahtonen on vuokrannut elokuvia?

    SELECT COUNT(*) As Lukumäärä
    FROM Jasen, Vuokraus
    WHERE Jasen.JasenID = Vuokraus.JasenID
    AND Jasen.Nimi = 'Tommi Lahtonen'
    ;

    Lukumäärä
    5

21. Kirjoita kysely josta näkee ketkä jäsenet ovat vuokranneet elokuvia, kuinka monta kertaa ja paljonko kukin vuokrauksia tehneistä on maksanut vuokrakuluja. Kyselyllä pitää selvitä myös jäsenen nimi.

    SELECT Jasen.Nimi, COUNT(*) AS Lukumäärä, SUM(Maksu) AS Yhteensä
    FROM Jasen, Vuokraus
    WHERE Jasen.JasenID = Vuokraus.JasenID
    GROUP BY Jasen.Nimi
    ;

    Nimi	Lukumäärä	Yhteensä
    Leila Leffafani	2	20
    Petri Heinonen	2	130
    Tommi Lahtonen	5	60
    Ville Vidiootti	8	125

22. Selvitä minkä nimisiä ovat ja kuinka paljon rahaa ovat käyttäneet ne jotka ovat vuokranneet elokuvia useammin kuin kaksi kertaa.

    SELECT Jasen.Nimi, SUM(Maksu) AS Yhteensä
    FROM Jasen, Vuokraus
    WHERE Jasen.JasenID = Vuokraus.JasenID
    GROUP BY Jasen.Nimi
    HAVING COUNT(*) > 2
    ;

    Nimi	Yhteensä
    Tommi Lahtonen	60
    Ville Vidiootti	125

23. Selvitä kuinka paljon vuokratuloja on tullut kunkin jakelijan elokuvista.

    SELECT Jakelija.Nimi, SUM(Maksu) AS Yhteensä
    FROM Vuokraus, Nauha, Jakelija
    WHERE Nauha.NauhaID = Vuokraus.NauhaID
    AND Nauha.Ostopaikka = Jakelija.JakelijaID
    GROUP BY Jakelija.Nimi
    ;

    Nimi	Yhteensä
    20th Century Fox	25
    Disney	220
    Finnkino	25
    UIP	65

24. Selvitä kuinka monta elokuvaa on vuokrannut kukin niistä, jotka ovat käyttäneet rahaa vuokrauksiin yli 30 mk ja laita heidät järjestykseen siten että vähiten tuhlannut on ensimmäisenä.

    SELECT Jasen.Nimi, COUNT(*) AS Lukumäärä, SUM(Maksu) AS Summa
    FROM Jasen, Vuokraus
    WHERE Vuokraus.JasenID = Jasen.JasenID
    GROUP BY Jasen.Nimi
    HAVING SUM(Maksu) > 30
    ORDER BY SUM(Maksu)
    ;

    Nimi	Lukumäärä	Summa
    Tommi Lahtonen	5	60
    Ville Vidiootti	8	125
    Petri Heinonen	2	130

25. Listaa kaikki tiedot niistä jäsenistä, jotka eivät ole vuokranneet kertaakaan.

    SELECT *
    FROM Jasen
    WHERE JasenID NOT IN ( SELECT JasenID FROM Vuokraus)
    ;

    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