Demo 6
Monimutkaisempia SQL-kyselyjä - vastaukset

1. Kirjoita seuraavat SQL-kyselyt:
   1. Hae kaikki ne memberit, jotka ovat liittyneet aikaisemmin kuin kukaan osoitteessa Meikämannentie 12 asuva.

      
      SELECT *
      FROM Member
      WHERE datejoined < ALL (
           SELECT Datejoined
           FROM Member
           WHERE Address = 'Meikämannentie 12'
      )
      

      

   2. Hae kolme vuokraushinnaltaan kalleinta elokuvaa

      
      SELECT CatalogID, Title
      FROM Catalog AS C
      WHERE 3 > (
           SELECT COUNT(CatalogID)
           FROM Catalog AS Ca
           WHERE Ca.rentalcharge > C.rentalcharge
           )
      

      

   3. Laske paljonko rahaa ovat tuoneet kolme vuokraushinnaltaan kalleinta elokuvaa

      
      SELECT SUM(feepaid)
      FROM Rental, Tape
      WHERE Tape.TapeID = Rental.TapeID
      AND Tape.CatalogID IN (
           SELECT CatalogID
           FROM Catalog AS C
           WHERE 3 > (
             SELECT COUNT(CatalogID)
             FROM Catalog AS Ca
             WHERE Ca.rentalcharge > C.rentalcharge
             )
           )
      

      

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

      
      SELECT TapeID, COUNT(*) as lkm
      FROM Rental 
      GROUP BY TapeID
      HAVING Count(*) >= ALL (
           SELECT Count(*)
           FROM Rental
           GROUP BY TapeID
           )
      

      

   5. Hae lista niistä nauhoista joita on vuokrattu enintään 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 tape.TapeId
      FROM tape LEFT OUTER JOIN rental ON rental.tapeid = tape.tapeid 
      GROUP BY Tape.TapeID
      HAVING COUNT(*) <= 1
      

      
      SELECT TapeID
      FROM Tape T
      WHERE T.TapeID NOT IN (
               SELECT TapeID
               FROM Rental
               GROUP BY TapeID
               HAVING COUNT(*) > 1
             )

      

   6. Laske montako kertaa on vuokrattu nauhoja, jotka ovat 20th Century Foxin toimittamia ja ostettu 1.1.1991 jälkeen. Tee haku ilman liitoksia eli käytä vain alikyselyjä.

      
      SELECT Count(*)
      FROM Rental
      WHERE TapeID IN (
           SELECT TapeID 
           FROM Tape
           WHERE purchasedate > #1-1-1991#
           AND SupplierID IN (
               SELECT SupplierID
               FROM Supplier
               WHERE SupplierName = '20th Century Fox'
               )
           )
      

      

   7. 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 Tape.TapeID
      FROM Tape LEFT OUTER JOIN Rental
      ON Tape.TapeID = Rental.TapeID
      GROUP BY Tape.TapeID
      HAVING COUNT(RentalID) = 0
      

      
      SELECT TapeID
      FROM Tape 
      WHERE TapeID NOT IN ( 
        SELECT TapeID 
        FROM Rental
      )
      

      
      
      SELECT TapeID
      FROM Tape 
      WHERE NOT EXISTS ( 
        SELECT TapeID 
        FROM Rental 
        WHERE Rental.TapeID = Tape.TapeID
      )