Monimutkaisempia SQL-kyselyjä - mallivastaukset

Mallivastaukset demo 5:n tehtävistä

1. Kuinka paljon elokuvia on vuokrattu minäkin kuukautena?

   SELECT EXTRACT(MONTH FROM vuokrauspvm) AS kuukausi, COUNT(*)
   FROM vuokraus
   GROUP BY kuukausi
   ;
   

   kuukausi	count
   5	15
   6	1
   7	1

2. Hae kaikkien jäsenten sukunimet.

   SELECT SUBSTRING(nimi FROM (POSITION(' ' IN nimi) + 1))
   FROM jasen
   ;
   

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

   SELECT 	jasenid,
   	CAST(MAX(vuokrauspvm) - MIN(vuokrauspvm) || ' DAY' AS INTERVAL)
   FROM vuokraus
   GROUP BY jasenid
   ;
   

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

   SELECT 	jasenid,
   	COUNT(*)
   FROM vuokraus
   WHERE vuokrauspvm > CURRENT_DATE - INTERVAL '5 YEAR'
   GROUP BY jasenid
   ;
   

   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.

   
   SELECT *
   FROM Jasen
   WHERE LiittymisPVM < ALL (
        SELECT LiittymisPVM
        FROM Jasen
        WHERE 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. Hae kolme vuokraushinnaltaan kalleinta elokuvaa

   
   SELECT ElokuvaID, Nimi, vuokrahinta
   FROM Elokuva AS E
   WHERE 3 > (
        SELECT COUNT(ElokuvaID)
        FROM Elokuva AS EL
        WHERE EL.vuokrahinta > E.vuokrahinta
        );
   
   

   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

   
   SELECT SUM(Maksu) AS Summa
   FROM Vuokraus, Nauha
   WHERE Nauha.NauhaID = Vuokraus.NauhaID
   AND Nauha.Elokuva IN (
      SELECT ElokuvaID
      FROM Elokuva AS E
      WHERE 3 > (
        SELECT COUNT(ElokuvaID)
        FROM Elokuva AS EL
        WHERE EL.vuokrahinta > E.vuokrahinta
        )
    );
   

   Summa
   205

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

   
   SELECT NauhaID, COUNT(*) AS lkm
   FROM Vuokraus
   GROUP BY NauhaID
   HAVING Count(*) >= ALL (
        SELECT Count(*)
        FROM Vuokraus
        GROUP BY NauhaID
        );
   

   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.

   
   SELECT Nauha.NauhaId, COUNT(JasenID) AS lkm
   FROM Nauha LEFT JOIN Vuokraus ON Vuokraus.Nauhaid =Nauha.Nauhaid
   GROUP BY Nauha.NauhaID
   HAVING COUNT(*) <= 1;
   
   SELECT N.NauhaID, 0
   FROM Nauha AS N
   WHERE  N.NauhaID NOT IN (
            SELECT NauhaID
            FROM Vuokraus
          )
   UNION 
   SELECT N.NauhaID, COUNT(*) AS lkm
   FROM Nauha AS N, Vuokraus AS V
   WHERE N.NauhaID = V.NauhaID
   GROUP BY N.NauhaID
   HAVING COUNT(*) <= 1;
   

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

    
    SELECT Count(*) AS Lkm
    FROM Vuokraus
    WHERE (((Vuokraus.NauhaID) In (SELECT NauhaID 
         FROM Nauha
         WHERE ostopaiva >= '1990-01-01'
         AND Ostopaikka IN (
             SELECT JakelijaID
             FROM Jakelija
             WHERE Nimi = '20th Century Fox'
             )
         )));
    

    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.

    
    SELECT Nauha.NauhaID
    FROM Nauha LEFT JOIN Vuokraus ON Nauha.NauhaID = Vuokraus.NauhaID
    GROUP BY Nauha.NauhaID
    HAVING COUNT(JasenID) = 0;
    
    SELECT NauhaID
    FROM Nauha
    WHERE NauhaID NOT IN ( 
      SELECT NauhaID 
      FROM Vuokraus
    );
    
    SELECT NauhaID
    FROM Nauha
    WHERE NOT EXISTS ( 
      SELECT NauhaID 
      FROM Vuokraus 
      WHERE Vuokraus.NauhaID = Nauha.NauhaID
    );
    

    NauhaID
    3
    4
    10
    11
    13
    14
    16
    17
    18
    19