Monimutkaisia SQL-kyselyjä

Äänityksen äänenlaatu on ikävän huono :-(

• tietokannat08_vara02.mp3 3.1M
• tietokannat08_vara03.mp3 3.8M
• tietokannat08_vara04.mp3 3.3M
• tietokannat08_vara05.mp3 3.7M
• tietokannat08_vara06.mp3 3.7M
• tietokannat08_vara07.mp3 1.2M

• Luonti
• Sisältö

Liitos

Liitos on sama asia kuin leikkaus

Kaikkialla toimiva vanha tapa kirjoittaa liitos:

SELECT Opiskelija.sukunimi, Puhelinnumero.puhelinnumero, opiskelija.sotu
FROM Opiskelija, Puhelinnumero
WHERE Opiskelija.sotu = Puhelinnumero.sotu;

Liitos SQL-92:en mukaan:

SELECT Opiskelija.sukunimi, Puhelinnumero.puhelinnumero, opiskelija.sotu
FROM Opiskelija INNER JOIN Puhelinnumero
ON Opiskelija.sotu = Puhelinnumero.sotu
;

INNER-sana voidaan jättää pois:

SELECT Opiskelija.sukunimi, Puhelinnumero.puhelinnumero, opiskelija.sotu
FROM Opiskelija JOIN Puhelinnumero
ON Opiskelija.sotu = Puhelinnumero.sotu
;

Jos kummassakin taulussa on liitoksessa käyttävällä kentällä sama nimi niin voidaan käyttää USING-määritystä. Lopputuloksessa on vain yksi liitoksessa käytetty kenttä eikä erikseen kummankin taulun versiota.

SELECT Opiskelija.sukunimi, Puhelinnumero.puhelinnumero, sotu
FROM Opiskelija INNER JOIN Puhelinnumero
USING (sotu)
;

NATURAL JOIN on lyhyempi tapa tehdä sama kuin edellä. NATURAL JOIN yhdistää taulut niissä olevien samannimisten kenttien perusteella.

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

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.

LEFT OUTER JOIN

Kaikkien niiden opiskelijoiden nimet ja puhelinnumerot joilla on puhelin (tavallinen liitos)

SELECT etunimi, sukunimi, puhelinnumero
FROM Opiskelija NATURAL JOIN Puhelinnumero
;
-- tai
SELECT etunimi, sukunimi, puhelinnumero
FROM Opiskelija, Puhelinnumero
WHERE opiskelija.sotu = puhelinnumero.sotu
;

KAIKKIEN opiskelijoiden nimet ja puhelinnumerot (ulkoliitos)

SELECT etunimi, sukunimi, puhelinnumero
FROM Opiskelija NATURAL LEFT OUTER JOIN Puhelinnumero
;

Niiden opiskelijoiden nimet ja tenttisuoritukset, jotka ovat suorituksia tehneet (normaali liitos)

SELECT etunimi, sukunimi, kurssi, arvosana
FROM Opiskelija INNER JOIN Tenttii
ON opiskelija.sotu = Tenttii.opiskelija
;
-- tai
SELECT etunimi, sukunimi, kurssi, arvosana
FROM Opiskelija, Tenttii
WHERE opiskelija.sotu = Tenttii.opiskelija
;

KAIKKIEN opiskelijoiden nimet ja tenttisuoritukset

SELECT etunimi, sukunimi, arvosana
FROM Opiskelija LEFT OUTER JOIN Tenttii
ON opiskelija.sotu = Tenttii.opiskelija
;

RIGHT OUTER JOIN

SELECT etunimi, sukunimi, puhelinnumero
FROM Puhelinnumero NATURAL RIGHT OUTER JOIN Opiskelija
;

SELECT etunimi, sukunimi, arvosana
FROM Tenttii RIGHT OUTER JOIN Opiskelija
ON opiskelija.sotu = Tenttii.opiskelija
;

FULL OUTER JOIN

Millä kaikilla kursseilla on opiskelijoita (tavallinen liitos)

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;

Liitoksia sekaisin

Haetaan kaikki opiskelijat, heidän puhelinnumeronsa ja tenttisuorituksensa ja kaikkien kurssien nimet.

SELECT etunimi, sukunimi, puhelinnumero,  kurssi.nimi, arvosana 
FROM Opiskelija NATURAL LEFT JOIN Puhelinnumero
LEFT JOIN Tenttii 
ON Opiskelija.sotu = Tenttii.opiskelija
RIGHT 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 ;

LEIKKAUS (INTERSECT)

Leikkas on sama asia kuin liitos

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

SELECT sotu
FROM Opiskelija
INTERSECT
SELECT Opiskelija
FROM Tenttii ;

Sama kuin yllä mutta ilman INTERSECT-operaatiota:

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 nimet, jotka eivät ole tehneet yhtään tenttiä

SELECT etunimi, sukunimi
FROM Opiskelija
WHERE SOTU NOT IN (
  SELECT Opiskelija
  FROM Tenttii
);

haetaan niiden nimet, jotka ovat käyneet tentissä

SELECT etunimi, sukunimi
FROM Opiskelija
WHERE SOTU IN (
  SELECT Opiskelija
  FROM Tenttii
);
-- tai vanhalla tavalla
-- tässä tavassa tulee ongelmia jos yksilöivä kenttä ei ole mukana lopputuloksessa
-- ja sattuisi olemaan samannimisiä opiskelijoita
SELECT DISTINCT sotu, etunimi, sukunimi
FROM Opiskelija, tenttii
WHERE opiskelija.sotu = tenttii.opiskelija

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

-- UPDATE-lausetta ei voi kohdistaa kuin yhteen tauluun kerrallaan joten
-- alikysely on usein ainut mahdollinen tapa tehdä laajempia rajoituksia
-- päivitettävien tietueiden joukkoon
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, NULL, NULL
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'
);
-- sama tulos saadaan myös:
SELECT *
FROM Tenttii T
WHERE t.kurssi = 'TIE150'
AND arvosana >  (
        SELECT MAX(arvosana)
        FROM Tenttii
        WHERE Opiskelija = '111112-111P '
        AND kurssi = 'TIE150'
);

Sama kuin edellä paitsi nyt kelpaavat kaikki ne suoritukset, jotka ovat parempia kuin mikä tahansa oppilas 111112-111P :n suoritus tietokannat-kurssilta.

SELECT *
FROM Tenttii T
WHERE t.kurssi = 'TIE150'
AND arvosana > ANY (
        SELECT arvosana
        FROM Tenttii
        WHERE Opiskelija = '111112-111P '
        AND kurssi = 'TIE150'
);
-- vastaava tulos saadaan myös:
SELECT *
FROM Tenttii T
WHERE t.kurssi = 'TIE150'
AND arvosana >  (
        SELECT MIN(arvosana)
        FROM Tenttii
        WHERE Opiskelija = '111112-111P '
        AND kurssi = 'TIE150'
);

Haetaan niiden sotut, jotka eivät ole tehneet yhtään tenttiä (EXISTS-versio)

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ä (EXISTS-versio)

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

Alikysely voi olla linkitetty ylempään kyselyyn. Alikysely suoritetaan silloin uudelleen jokaista ylemmässä kyselyssä käsiteltyä tietuetta kohti.

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 ovat tenttineet

-- tietokantaan on ensin lisättävä tenttisuoritus:
--INSERT INTO Tenttii VALUES ('111115-111P', 'TIE160', '2002-02-02', 2);

SELECT Kurssikoodi
FROM Kurssi k
WHERE (
       SELECT COUNT(*) 
       FROM Opiskelija
       ) = (
       SELECT COUNT(DISTINCT opiskelija)
       FROM Tenttii
       WHERE Kurssi = k.kurssikoodi
       )
;

-- tai:

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
));

-- ja suorituksen poistaminen niin palataan alkutilanteeseen
-- DELETE FROM Tenttii 
-- WHERE opiskelija = '111115-111P'
-- AND kurssi = 'TIE160'
-- AND paivamaara = '2002-02-02'
-- AND arvosana = 2;

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 10 < (
   SELECT SUM(laajuus)
   FROM Kurssi, Tenttii
   WHERE Tenttii.Kurssi = Kurssi.kurssikoodi
   AND Tenttii.Opiskelija = O.Sotu
   AND Tenttii.arvosana >= 1
)
GROUP BY Etunimi, Sukunimi