Luento 7
Monimutkaisempia SQL-kyselyjä

Ryhmittely (GROUP BY)

Kyselyn tulokset voidaan ryhmitellä halutun kentän tai kenttien mukaan GROUP BY-määreellä.

SELECT sotu, AVG(arvosana) 
FROM tentit 
GROUP BY sotu

/* Kaikkien Tietokannat-kurssin tenttisuoritusten keskiarvo
ryhmiteltynä sotun mukaan /* 
SELECT sotu, AVG(arvosana) 
FROM tentit 
WHERE KurssiID = 1 
GROUP BY sotu

Ryhmille voidaan asettaa ehtoja HAVING-lauseessa. Myös koostefunktiot ovat sallittuja.

/* Keskiarvo niiden opiskelijoiden Tietokannat-kurssin
tenttisuorituksista jotka ovat yrittäneet vähintään 2 kertaa */ 
SELECT sotu, AVG(arvosana) 
FROM tentit 
WHERE KurssiID = 1 
GROUP BY sotu 
HAVING COUNT(arvosana) >= 2

/* Keskiarvo niiden opiskelijoiden Tietokannat-kurssin (ID = 2)
tenttisuorituksista, jotka ovat yrittäneet vähintään 2 kertaa.
Haun tulos järjestetään keskiarvon mukaan siten, että suurimman
keskiarvon saanut tulee ensimmäiseksi (laskeva järjestys). */ 
SELECT sotu, AVG(arvosana) as Keskiarvo 
FROM tentit 
WHERE KurssiID = 1 
GROUP BY sotu 
HAVING COUNT(arvosana) >= 2 
ORDER BY Keskiarvo DESC 

/* Keskiarvo, paras arvosana ja keskiarvon ja parhaan arvosanan
erotus niiden opiskelijoiden Tietokannat-kurssin (ID = 1) tenttisuorituksista,
jotka ovat yrittäneet vähintään 2 kertaa. Haun tulos
järjestetään keskiarvon mukaan siten, että suurimman keskiarvon
saanut tulee ensimmäiseksi (laskeva järjestys). */ 
SELECT sotu, MAX(arvosana) as Paras, AVG(arvosana) as Keskiarvo,
MAX(arvosana) - AVG(arvosana) as Erotus 
FROM tentit 
WHERE KurssiID = 1 
GROUP BY sotu 
HAVING COUNT(arvosana) >= 2 
ORDER BY Keskiarvo DESC 

KYSELYT USEAAN TAULUUN

/* Haetaan oppilaiden sukunimet ja kyseisten oppilaiden kaikkien
tenttien arvosanat */ 

SELECT Oppilaat.sukunimi, Tentit.arvosana 
FROM Oppilaat, Tentit 
WHERE Oppilaat.sotu = Tentit.sotu 


/* Haetaan oppilaiden sukunimet, kurssien nimet ja
tenttiarvosanat */ 

SELECT O.sukunimi, K.kurssinnimi, T.arvosana 
FROM Oppilaat AS O, Tentit AS T ,Kurssit AS K 
WHERE O.sotu = T.sotu 
AND K.KurssiID = T.KurssiID 

/* Haetaan kaikki oppilaan tiedot */ 

SELECT O.sukunimi, Pnro.paikkakunta, OS.osastonimi, V.vkurssinimi,
K.kurssinnimi, T.arvosana 
FROM Oppilaat AS O, Tentit AS T ,Kurssit AS K, postinumerot AS Pnro,
Osastot AS OS, Vkurssit AS V 
WHERE O.sotu = T.sotu 
AND K.KurssiID = T.KurssiID 
AND O.postinumero = Pnro.postinumero 
AND O.osastonro = OS.osastonro 
AND O.vkurssinro = V.vkurssinro 

/* haetaan kaikki oppilaan tiedot ja näytetään keskiarvot
tenteistä ryhmiteltynä sukunimen, kaupungin, osastonnimen,
vkurssinnimen ja kurssinnimen mukaan */ 

SELECT O.sukunimi, Pnro.paikkakunta, OS.osastonimi, V.vkurssinimi,
K.kurssinnimi, AVG(T.arvosana) 
FROM Oppilaat AS O, Tentit AS T ,Kurssit AS K, postinumerot AS Pnro,
Osastot AS OS, Vkurssit AS V 
WHERE O.sotu = T.sotu 
AND K.KurssiID = T.KurssiID 
AND O.postinumero = Pnro.postinumero 
AND O.osastonro = OS.osastonro 
AND O.vkurssinro = V.vkurssinro 
GROUP BY O.sukunimi, Pnro.paikkakunta, OS.osastonimi,
V.vkurssinimi, K.kurssinnimi


/* Ryhmitellään kurssien tenttitulosten arvosanojen keskiarvot
kaupungeittain */ 

SELECT Pnro.paikkakunta, K.kurssinnimi, AVG(T.arvosana) 
FROM Oppilaat AS O, Tentit AS T ,Kurssit AS K, postinumerot AS Pnro,
Osastot AS OS, Vkurssit AS V 
WHERE O.sotu = T.sotu 
AND K.KurssiID = T.KurssiID 
AND O.postinumero = Pnro.postinumero 
AND O.osastonro = OS.osastonro 
AND O.vkurssinro = V.vkurssinro 
GROUP BY Pnro.paikkakunta, K.kurssinnimi

/* Tutkitaan ketkä kaikki asuvat samassa kaupungissa */ 
SELECT O.sukunimi, OP.sukunimi, K.paikkakunta 
FROM Oppilaat AS O, Oppilaat AS OP, postinumerot AS K 
WHERE O.postinumero = K.postinumero 
AND OP.postinumero = K.postinumero 
AND OP.postinumero = O.postinumero 
AND OP.sukunimi <> O.sukunimi

TIETOJEN LISÄÄMINEN (INSERT)

Uusien tietueiden lisääminen tehdään INSERT-komennolla:

INSERT INTO Tentit 
VALUES ('000000-0000', 1, '15.3.1999', 2.5)

Kaikkien kenttien tietoja ei ole pakko antaa. Tällöin on lueteltava niiden sarakkeiden nimet, joihin tietoja syötetään.

INSERT INTO Tentit (sotu, KurssiID, pvm, arvosana) 
VALUES ('000000-0000', 1, '15.3.1999', 2.5) 

Vain sellaiset kentät voidaan jättää puuttumaan, jotka sallivat NULL-arvoja tai joihin on määritelty oletusarvo.
Rivejä voidaan lisätä myös useampia kerrallaan, jos haetaan lisättävät rivit alikyselyllä.

INSERT INTO Tietokannat 
SELECT * 
FROM Tentit 
WHERE KurssiID = 1

TIETOJEN MUUTTAMINEN (UPDATE)

Tietoja muutetaan UPDATE-komennolla

UPDATE Tentit 
SET pvm = '1.5.1999', 
arvosana = 3 
WHERE sotu = '000000-0000' 
AND KurssiID = 'TIE110' 
AND pvm = '1.2.1999' 

Haluttaessa päivittää vain yksi tietty tietue on muistettava määritellä kyseisen tietueen avain tarkasti!

TIETOJEN POISTAMINEN (DELETE)

Poistaminen tapahtuu DELETE-komennolla

DELETE FROM Oppilaat 
WHERE sotu = '666666-6666' 

Koko taulun tyhjentäminen: DELETE FROM Taulu
Joissakin ohjelmistoissa myös komento: TRUNCATE Taulu Nopea. Ei voida peruuttaa koska ei talleta muutoksia lokitiedostoon.

Accessin merkkijonofunktiot

Compare two strings.	StrComp
Convert strings.	StrConv
Convert to lowercase or uppercase.	Format, LCase, UCase
Create string of repeating character.	Space, String
Find length of a string.	Len
Format a string.	Format
Justify a string.	LSet, RSet
Manipulate strings.	InStr, Left, LTrim, Mid, Right, RTrim, Trim
Set string comparison rules.	Option Compare
Work with ASCII and ANSI values.	Asc, Chr