-- Liitos SQL-92:en mukaan:

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

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ä.

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

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

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

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;

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