Äänityksen äänenlaatu on ikävän huono :-(
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
;
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.
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
;
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
;
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;
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
Haetaan niiden oppilaiden sotut, jotka eivät ole tehneet yhtään tenttiä:
SELECT sotu
FROM Opiskelija
EXCEPT
SELECT Opiskelija
FROM Tenttii ;
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;
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