Luento 8
Monimutkaisempia SQL-kyselyjä

SQL-92:en mukaan liitos voidaan tehdä myös seuraavasti:

SELECT Oppilaat.sukunimi, Tentit.arvosana 
FROM Oppilaat INNER JOIN Tentit 
ON Oppilaat.sotu = Tentit.sotu 

INNER-sana voidaan jättää pois.
Jos liitossarakkeet ovat samannimiset voidaan käyttää myös luonnollista liitosta:

SELECT Oppilaat.sukunimi, Tentit.arvosana 
FROM Oppilaat NATURAL JOIN Tentit 

Tai:

SELECT Oppilaat.sukunimi, Tentit.arvosana 
FROM Oppilaat JOIN Tentit 
USING (sotu) 

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

Yhdiste (UNION)

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ä alle 1:en tai
joiden sukunimi on Meikäläinen. Toteutettu yhdisteellä. */ 

SELECT O.sukunimi, O.etunimi, aloitusvuosi 
FROM Oppilaat AS O, Tentit AS T 
WHERE arvosana < 1 
AND O.sotu = T.sotu 
UNION
SELECT O.sukunimi, O.etunimi, aloitusvuosi 
FROM Oppilaat AS O 
WHERE sukunimi = 'Meikäläinen' 
ORDER BY aloitusvuosi

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.

/* ulkoliitos SQL-92:en mukaan. 
Kaikkien opiskelijoiden
tenttisuoritukset, myös niiden tiedot, jotka eivät ole tehneet
yhtään tenttiä */ 

SELECT sukunimi, kurssitunnus, arvosana 
FROM Oppilaat AS O LEFT OUTER JOIN Tentit AS T 
ON O.sotu = T.sotu

Erotus (EXCEPT)

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

SELECT sotu 
FROM Oppilaat 
EXCEPT 
SELECT sotu 
FROM Tentit 

Ei toimi Accessissa.

LEIKKAUS (INTERSECT)

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

SELECT sotu 
FROM Oppilaat 
INTERSECT 
SELECT sotu 
FROM Tentit 

Ei toimi Accessissa.

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 Tentit 
WHERE arvosana = (
	SELECT Max(arvosana) 
	FROM Tentit
)


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

SELECT sukunimi, etunimi, kurssitunnus, arvosana 
FROM Oppilaat AS O, Tentit AS T 
WHERE O.sotu = T.sotu 
UNION 
SELECT sukunimi, etunimi, '', 0 
FROM Oppilaat O 
WHERE O.sotu NOT IN (
	SELECT sotu 
	FROM Tentit
) 
ORDER BY sukunimi, etunimi



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

SELECT sotu 
FROM Oppilaat 
WHERE SOTU NOT IN ( 
	SELECT sotu 
	FROM Tentit 
)

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

SELECT sotu 
FROM Oppilaat 
WHERE SOTU IN ( 
	SELECT sotu 
	FROM Tentit 
)

/* Haetaan kaikki ne tenttisuoritukset kurssilta tietokannat, jotka
ovat parempia kuin mikään oppilas 123456-0000:n suoritus tältä
samalta kurssilta */ 

SELECT *
FROM Tentit T
WHERE t.kurssitunnus = 1
AND arvosana > ALL (
        SELECT arvosana
        FROM Tentit
        WHERE sotu = '121212-111r'
        AND kurssitunnus = 1
)
        
/* Haetaan niiden sotut, jotka eivät ole tehneet yhtään tenttiä
*/ 

SELECT sotu 
FROM Oppilaat O 
WHERE NOT EXISTS ( 
	SELECT * 
	FROM Tentit 
	WHERE sotu = O.sotu 
)

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

SELECT sotu 
FROM Oppilaat O 
WHERE EXISTS ( 
	SELECT * 
	FROM Tentit 
	WHERE sotu = O.sotu 
)


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

UPDATE Tentit 
SET arvosana = arvosana - 0.25 
WHERE kurssitunnus = 'TIE160' 
AND arvosana BETWEEN 1 AND 2.75 
AND sotu IN ( 
	SELECT sotu 
	FROM Oppilaat 
	WHERE aloitusvuosi > 1998 
)


/* Etsi kolme suurinta tenttisuoritusta */

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

/* Etsi kaikki ne kurssit joita kaikki oppilaat ovat tenttineet */

SELECT KurssiID
FROM Kurssit
WHERE NOT EXISTS (
SELECT * 
FROM Oppilaat
WHERE NOT EXISTS (
SELECT *
FROM Tentit
WHERE Tentit.sotu = Oppilaat.sotu
))