Demo 5
SQL-kyselyjen malliratkaisut

Vastauksissa käytetty SQL ei ole aina täysin standardien mukaista vaan ratkaisut on tehty Access 97:ssä toimiviksi.

Mallikyselyt ovat saatavilla myös Access 97 -tietokantana:

• Mallivastaukset (demo5vast.mdb), 164 kb)
• Mallivastaukset (demo5vast.zip), 30 kb)

1. Laske paljonko rahaa on tullut yhteensä kaikista vuokratapahtumista.

   
   SELECT SUM(feepaid) AS Summa
   FROM Rental
   

   

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

   
   SELECT AVG(feepaid) AS Keskiarvo
   FROM Rental
   

   

3. Laske montako vuokraustapahtumaa on ollut.

   
   SELECT COUNT(feepaid) AS LKM
   FROM Rental
   

   

4. Laske montako kertaa member 2 on vuokrannut.

   
   SELECT COUNT(feepaid)
   FROM Rental
   WHERE memberid = 2
   

   

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

   
   SELECT TapeId, COUNT(tapeId) AS LKM
   FROM Rental
   GROUP BY Tapeid
   

   

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

   
   SELECT Tape.tapeID, Catalog.title, tape.purchaseprice, catalog.rentalcharge
   FROM tape, catalog
   WHERE catalog.catalogid = tape.catalogid
   ORDER BY Catalog.title
   

   

7. Tee haku josta selviää kuka on vuokrannut (nimi), minkä elokuvan, mihin hintaan ja milloin.

   
   SELECT member.name, Catalog.Title,  rental.daterented, feepaid
   FROM Tape, Catalog, Rental, Member
   WHERE Tape.catalogid = Catalog.catalogid
   AND rental.memberid = member.memberid
   AND rental.tapeid = tape.tapeid
   

   

8. Montako kertaa Tommi Lahtonen on vuokrannut elokuvia?

   
   SELECT Count(*) AS Tommin_vuokraukset
   FROM Rental, Member
   WHERE member.memberid = Rental.memberid
   AND member.name = 'Tommi Lahtonen'
   

   

9. Tee haku josta näkee kuka (nimi) on vuokrannut kuinkakin monta elokuvaa ja paljonko kukin on yhteensä maksanut vuokrakuluja.

   
   SELECT member.name, COUNT(*) AS LKM, SUM(feepaid) AS Summa
   FROM Rental, Member
   WHERE member.memberid = Rental.memberid
   GROUP by member.name
   

   

10. Selvitä minkä nimisiä ovat ja kuinka paljon rahaa ovat käyttäneet ne jotka ovat vuokranneet elokuvia vähintään kaksi kertaa.

    
    SELECT member.name, SUM(feepaid) AS Summa
    FROM Rental, Member
    WHERE member.memberid = Rental.memberid
    GROUP BY member.name
    HAVING COUNT(*) >= 2
    

    

11. Selvitä kuinka paljon vuokratuloja on tullut kunkin supplierin elokuvista.

    
    SELECT supplier.suppliername, SUM(rental.feepaid) AS Summa
    FROM supplier, tape, rental
    WHERE tape.tapeid = rental.tapeid
    AND tape.supplierid = supplier.supplierid
    GROUP BY supplier.suppliername
    

    

12. 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 member.name, COUNT(rental.feepaid) AS LKM, SUM(rental.feepaid) as Summa
    FROM member, rental
    WHERE member.memberid = rental.memberid
    GROUP BY member.name
    HAVING SUM(rental.feepaid) > 30
    ORDER BY SUM(rental.feepaid) ASC
    

    

13. Selvitä ketkä kaikki memberit asuvat samassa osoitteessa.

    
    SELECT m1.name, m2.name, m1.address, m2.address
    FROM member as m1, member as m2
    WHERE m1.address = m2.address
    AND m1.name <> m2.name
    

    

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

    
    SELECT *
    FROM Member
    WHERE MemberID NOT IN (
          SELECT MemberID
          FROM Rental
    )
    

    

15. Selvitä paljonko yksikseen asuvat ovat yhteensä kuluttaneet rahaa elokuvien vuokraamiseen.

    
    SELECT SUM(rental.feepaid) AS summa
    FROM rental, member
    WHERE rental.memberid = member.memberid
    AND member.memberid NOT IN (
            SELECT m1.memberid
            FROM member as m1, member as m2
            WHERE m1.address = m2.address
            AND m1.name <> m2.name
            )
    

    

16. Laske kuinka montako vuotta kukin member on ollut jäsenenä. Käytä apuna Accessista löytyvää Year-funktiota, joka osaa erottaa Date-tyyppisestä kentästä pelkän vuoden.

    
    SELECT Name, Year(Now()) - Year (DateJoined) AS Jäsen_vuodet
    FROM member
    

    

17. Listaa KAIKKIEN elokuvien nimet ja niiden keräämät vuokratulot elokuvien nimien mukaan järjestettynä.

    
    /* ei toimi Accessissa :-( */
    SELECT catalog.title, sum(rental.feepaid)
    FROM tape LEFT OUTER JOIN rental ON rental.tapeid = tape.tapeid 
    INNER JOIN catalog ON catalog.catalogid = tape.catalogid
    GROUP BY catalog.title
    ORDER BY Catalog.title
    
    /* Accessissa toimiva vaihtoehto */
    SELECT catalog.title, SUM(rental.feepaid) AS Summa
    FROM tape, catalog, rental
    WHERE Tape.tapeID = Rental.tapeID
    AND Tape.CatalogID = Catalog.CatalogID
    GROUP BY catalog.title
    UNION
    SELECT catalog.title, 0
    FROM catalog
    WHERE CatalogID NOT IN (
      SELECT CatalogID 
      FROM Tape, Rental
      WHERE tape.tapeID = Rental.tapeID
    )
    ORDER BY Catalog.title