Luento 8
Monimutkaisia SQL-kyselyjä

• tietokannat801.mp3 3.9M
• tietokannat802.mp3 3.5M
• tietokannat803.mp3 3.5M
• tietokannat804.mp3 3.6M
• tietokannat805.mp3 4.0M

• Luonti
• Sisältö
• Kyselyt

Liitos

Liitos SQL-92:en mukaan:

SELECT Opiskelija.sukunimi, Tenttii.arvosana
FROM Opiskelija INNER JOIN Tenttii
ON Opiskelija.Sotu = Tenttii.Opiskelija ;

Tai:

SELECT Opiskelija.sukunimi, Tenttii.arvosana
FROM Opiskelija JOIN Tenttii
ON Opiskelija.Sotu = Tenttii.Opiskelija;

Jos liitossarakkeet ovat samannimiset voidaan käyttää myös luonnollista liitosta:

SELECT Opiskelija.sukunimi, Puhelinnumero.puhelinnumero
FROM Opiskelija NATURAL JOIN Puhelinnumero;

-- Tai:

SELECT Opiskelija.sukunimi, Puhelinnumero.puhelinnumero
FROM Opiskelija NATURAL JOIN Puhelinnumero
USIGN (Sotu);

-- Kentän nimeä ei saa näissä tapauksissa varustaa taulun nimellä.

Yhdiste

Yhdisteellä voidaan liittää kaksi tai useampia tauluja siten, että lopputulokseen tulee rivejä useasta taulusta allekkain. SELECT-lauseissa on oltava sama määrä haettavia sarakkeita vastaavassa järjestyksessä Vastaavien sarakkeiden on oltava samaa tietotyyppiä. ORDER BY-käskyjä voi olla vain yksi ja sekin oltava viimeisenä. UNION estää saman rivin toistumisen tuloksessa (vrt. DISTINCT) Jos halutaan säilyttää tuplarivit on käytettävä muotoa UNION ALL

Kaikki jotka ovat saaneet jostakin tentistä 3:en tai joiden sukunimi on Tieteilijä. Toteutettu yhdisteellä.

SELECT O.sukunimi, O.etunimi, arvosana
FROM Opiskelija AS O, Tenttii AS T
WHERE arvosana = 3
AND O.sotu = T.Opiskelija
UNION
SELECT O.sukunimi, O.etunimi, 0
FROM Opiskelija AS O
WHERE sukunimi = 'Tieteilijä'
ORDER BY Sukunimi, Etunimi;

Ulkoliitos (OUTER JOIN)

Tavallinen liitos ei anna meille lopputulokseen mukaan sellaisia kenttiä joille ei löydy vastinparia toisesta taulusta Ulkoliitos (OUTER JOIN) antaa myös vastinparittomat tietueet.

Kaikkien opiskelijoiden tenttisuoritukset, myös niiden tiedot, jotka eivät ole tehneet yhtään tenttiä

SELECT sukunimi, kurssi, arvosana
FROM Opiskelija AS O LEFT OUTER JOIN Tenttii AS T
ON O.sotu = T.Opiskelija;

vrt. tavallinen liitos:

SELECT sukunimi, kurssi, arvosana
FROM Opiskelija AS O INNER JOIN Tenttii AS T
ON O.sotu = T.Opiskelija;

Millä kaikilla kursseilla on opiskelijoita

SELECT Sukunimi, Kurssikoodi
FROM Opiskelija, Tenttii, Kurssi
WHERE Opiskelija.Sotu = Tenttii.Opiskelija
AND Tenttii.kurssi = Kurssi.kurssikoodi;

Listaa kaikki opiskelijat ja kurssit joilla he ovat. Hae näkyviin myös opiskelijat jotka eivät ole millään kurssilla ja ne kurssit joilla ei ole yhtään opiskelijoita

SELECT Sukunimi, Kurssikoodi
FROM Opiskelija FULL OUTER JOIN Tenttii
ON Opiskelija.Sotu = Tenttii.Opiskelija
FULL OUTER JOIN Kurssi
ON Tenttii.kurssi = kurssi.kurssikoodi;

Erotus (EXCEPT)

Haetaan niiden oppilaiden sotut, jotka eivät ole tehneet yhtään tenttiä:

SELECT sotu
FROM Opiskelija
EXCEPT
SELECT Opiskelija
FROM Tenttii ;

Ei toimi Accessissa.

LEIKKAUS (INTERSECT)

Haetaan niiden oppilaiden sotut, jotka ovat käyneet tentissä:

SELECT sotu
FROM Opiskelija
INTERSECT
SELECT Opiskelija
FROM Tenttii ;

Ei toimi Accessissa.

SELECT DISTINCT Sotu
FROM Opiskelija, Tenttii
WHERE Opiskelija.Sotu = Tenttii.Opiskelija;

Alikyselyt

• Alikyselyillä voidaan rajata varsinaista kyselyä käyttämällä toista pääkyselyn sisään kirjoitettua SELECT-lausetta
• Sisäkkäisiä alikyselyjä voi olla monta tasoa
• Alikyselyn suoritus alkaa alimmalta tasolta
• Alikyselyistä voi tulla tuloksena joko vain täsmälleen yksi rivi tai sitten useampia rivejä
• Saataessa useampirivinen tulos tarvitaan seuraavia operaattoreita:
  • IN Alikysely palauttaa joukon josta IN-operaattorilla voidaan testata löytyykö vastaava arvo joukosta IN-operaattori kelpaa jos vaaditaan tarkkaa yhtäsuuruutta
  • ANY, SOME tai ALL -operaattoreita käytetään jos vertailuehtona on <, >, <= tai >=
  • ANY Alikyselyn mikä tahansa arvo täyttää ehdon
  • SOME sama kuin ANY
  • ALL alikyselyn jokainen yksittäinen arvo täyttää ehdon
  • EXISTS Ei palauta rivejä vaan vain TOSI tai EPÄTOSI

Haetaan kaikki ne tenttisuoritukset, joiden tulos on sama kuin korkein tenttisuoritus

SELECT *
FROM Tenttii
WHERE arvosana = (
  SELECT Max(arvosana)
  FROM Tenttii
);

Haetaan niiden sotut, jotka eivät ole tehneet yhtään tenttiä

SELECT sotu
FROM Opiskelija
WHERE SOTU NOT IN (
  SELECT Opiskelija
  FROM Tenttii
);

/* haetaan niiden sotut, jotka ovat käyneet tentissä */

SELECT sotu
FROM Opiskelija
WHERE SOTU IN (
  SELECT Opiskelija
  FROM Tenttii
);

Vähennetään kaikkien vuoden 1998 jälkeen opiskelunsa aloittaneiden arvosanaa miinuksella TIE160 kurssin tenttituloksissa

UPDATE Tenttii
SET arvosana = arvosana - 0.25
WHERE kurssi = 'TIE160'
AND Opiskelija IN (
  SELECT sotu
  FROM Opiskelija
  WHERE aloitusvuosi > 1998
);

Kaikkien opiskelijoiden tenttisuoritukset, myös niiden tiedot, jotka eivät ole tehneet yhtään tenttiä. Vanhanaikainen UNION versio.

SELECT sukunimi, etunimi, kurssi, arvosana
FROM Opiskelija AS O, Tenttii AS T
WHERE O.sotu = T.Opiskelija
UNION
SELECT sukunimi, etunimi, '', 0
FROM Opiskelija OP
WHERE OP.sotu NOT IN (
  SELECT Opiskelija
  FROM Tenttii
)
ORDER BY sukunimi, etunimi;

Haetaan kaikki ne tenttisuoritukset kurssilta tietokannat, jotka ovat parempia kuin mikään oppilas 111112-111P :n suoritus tältä samalta kurssilta

SELECT *
FROM Tenttii T
WHERE t.kurssi = 'TIE150'
AND arvosana > ALL (
        SELECT arvosana
        FROM Tenttii
        WHERE Opiskelija = '111112-111P '
        AND kurssi = 'TIE150'
);

Haetaan niiden sotut, jotka eivät ole tehneet yhtään tenttiä

SELECT Sotu
FROM Opiskelija O
WHERE NOT EXISTS (
  SELECT *
  FROM Tenttii AS T
  WHERE T.Opiskelija = O.sotu
);

haetaan niiden sotut, jotka ovat käyneet tentissä

SELECT sotu
FROM Opiskelija O
WHERE EXISTS (
  SELECT *
  FROM Tenttii AS T
  WHERE T.Opiskelija = O.sotu
);

Etsi kolme suurinta tenttisuoritusta

SELECT *
FROM Tenttii t
WHERE 3 > (
  SELECT COUNT(arvosana)
  FROM Tenttii tt
  WHERE tt.arvosana > t.arvosana
);

-- Etsi kolme pienintä tenttisuoritusta

SELECT *
FROM Tenttii t
WHERE 3 > (
  SELECT COUNT(arvosana)
  FROM Tenttii tt
  WHERE tt.arvosana < t.arvosana
);

Etsi kaikki ne kurssit joita kaikki oppilaat ovat tenttineet

SELECT Kurssikoodi
FROM Kurssi k
WHERE NOT EXISTS (
  SELECT *
  FROM Opiskelija
  WHERE NOT EXISTS (
   SELECT *
   FROM Tenttii
   WHERE Tenttii.Opiskelija = Opiskelija.sotu
   AND Kurssi = k.Kurssikoodi
));

Laske kuinka monta opintoviikkoa yli kakkosen arvosanoilla ovat suorittaneet ne opiskelijat, jotka ovat yhteensä suorittaneet yli 10 opintoviikkoa.

SELECT Etunimi, Sukunimi, SUM(laajuus)
FROM Opiskelija O, Tenttii, Kurssi
WHERE O.Sotu = Tenttii.Opiskelija
AND Tenttii.Kurssi = Kurssi.kurssikoodi
AND Tenttii.Arvosana > 2
AND 4 < (
   SELECT SUM(laajuus)
   FROM Kurssi, Tenttii
   WHERE Tenttii.Kurssi = Kurssi.kurssikoodi
   AND Tenttii.Opiskelija = O.Sotu
   AND Tenttii.arvosana >= 1
)
GROUP BY Etunimi, Sukunimi