Ryhmittely, liitokset ja skalaarifunktiot - Luento 7

• tietokannat0702.mp3 3.4M
• tietokannat0703.mp3 3.8M
• tietokannat0704.mp3 3.8M
• tietokannat0705.mp3 3.1M
• tietokannat0706.mp3 3.1M
• tietokannat0707.mp3 2.3M

• Luonti
• Sisältö
• Kyselyt

Koostefunktiot (AVG, COUNT, MIN, MAX ja SUM) kohdistuvat aina arvojoukkoon mutta palauttavat tuloksena yhden arvon.

Koostefunktioita ei voi käyttää WHERE-lauseessa.

SQL-kyselyn suoritusjärjestys

Ryhmittely (GROUP BY)

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

SELECT Opiskelija, AVG(arvosana) AS Keskiarvo
FROM tenttii
GROUP BY Opiskelija
;

Kaikki hakutulokseen tulevat kentät joita ei käsitellä yhteenvetofunktioilla pitää luetella GROUP BY -osassa.

-- Kaikkien Tietokannat-kurssin tenttisuoritusten keskiarvo
-- ryhmiteltynä opiskelijan mukaan

SELECT Opiskelija, AVG(arvosana) AS Keskiarvo
FROM tenttii
WHERE Kurssi = 'TIE150'
GROUP BY Opiskelija
;

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

-- Ryhmille voidaan asettaa ehtoja HAVING-lauseessa. Myös koostefunktiot ovat sallittuja.
-- Keskiarvo niiden opiskelijoiden Tietokannat-kurssin
-- tenttiisuorituksista jotka ovat yrittäneet vähintään 2 kertaa

SELECT Opiskelija, AVG(arvosana) AS Keskiarvo
FROM tenttii
WHERE Kurssi = 'TIE150'
GROUP BY Opiskelija
HAVING COUNT(arvosana) >= 2
;

Järjestäminen (ORDER BY)

-- Keskiarvo niiden opiskelijoiden Tietokannat-kurssin
-- tenttisuorituksista, jotka ovat yrittäneet vähintään 2 kertaa.
-- Haun tulos järjestetään keskiarvon mukaan siten, että pienimmän
-- keskiarvon saanut tulee ensimmäiseksi (nouseva järjestys).

SELECT Opiskelija, AVG(arvosana) as Keskiarvo
FROM tenttii
WHERE Kurssi = 'TIE150'
GROUP BY Opiskelija
HAVING COUNT(arvosana) >= 2
ORDER BY Keskiarvo ASC
;

-- Keskiarvo, paras arvosana ja keskiarvon ja parhaan arvosanan
-- erotus niiden opiskelijoiden Tietokannat-kurssin  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 Opiskelija, MAX(arvosana) as Paras, AVG(arvosana) as Keskiarvo, MAX(arvosana) - AVG(arvosana) as Erotus
FROM tenttii
WHERE Kurssi = 'TIE150'
GROUP BY Opiskelija
HAVING COUNT(arvosana) >= 2
ORDER BY Keskiarvo DESC
;

KYSELYT USEAAN TAULUUN

Useampaan kuin yhteen tauluun kohdistuvat kyselyt vaativat aina WHERE-lauseen käyttöä. WHERE-lauseessa täytyy kertoa minkä kenttien perusteella kyselyssä olevat taulut liittyvät toisiinsa.

Jos kysely kohdistuu N:ään tauluun niin WHERE-osassa pitää olla ainakin N-1 tauluja yhdistävää ehtoa.

-- Haetaan oppilaiden sukunimet ja kyseisten oppilaiden kaikkien
-- tenttien arvosanat


SELECT Opiskelija.sukunimi, tenttii.arvosana
FROM Opiskelija, tenttii
WHERE Opiskelija.sotu = tenttii.opiskelija
;


-- Haetaan oppilaiden sukunimet, kurssien nimet ja
-- tenttiarvosanat
SELECT O.sukunimi, K.nimi, T.arvosana
FROM Opiskelija AS O, tenttii AS T ,kurssi AS K
WHERE O.sotu = T.opiskelija
AND K.Kurssikoodi = T.Kurssi
;
-- Haetaan kaikki oppilaan tiedot


SELECT O.sukunimi, Pnro.Postitoimipaikka, L.nimi, K.nimi, T.arvosana
FROM Opiskelija AS O, tenttii AS T , kurssi AS K, postinumero AS Pnro, Laitos AS L
WHERE O.sotu = T.Opiskelija
AND K.Kurssikoodi = T.Kurssi
AND O.postinumero = Pnro.postinumero
AND L.LaitosID = O.Laitos
;
-- haetaan kaikki oppilaan tiedot ja näytetään keskiarvot
-- tenteistä
SELECT O.sukunimi, Pnro.Postitoimipaikka, L.nimi, K.nimi, T.arvosana, AVG(T.arvosana)
FROM Opiskelija AS O, tenttii AS T , kurssi AS K, postinumero AS Pnro, Laitos AS L
WHERE O.sotu = T.Opiskelija
AND K.Kurssikoodi = T.Kurssi
AND O.postinumero = Pnro.postinumero
AND L.LaitosID = O.Laitos
GROUP BY O.sukunimi, Pnro.Postitoimipaikka, L.nimi, K.nimi, T.arvosana
;


-- Ryhmitellään kurssien tenttitulosten arvosanojen keskiarvot
-- postinumeroittain ja kursseittain

SELECT Postinumero, K.nimi, AVG(T.arvosana)
FROM Opiskelija AS O, tenttii AS T ,kurssi AS K,  Laitos AS L
WHERE O.sotu = T.Opiskelija
AND K.Kurssikoodi = T.Kurssi
AND O.laitos = L.LaitosID
GROUP BY Postinumero, K.nimi
;

-- Tutkitaan ketkä kaikki asuvat samassa kaupungissa
SELECT O.Etunimi, O.sukunimi, OP.Etunimi, OP.sukunimi, K. Postinumero
FROM Opiskelija AS O, Opiskelija AS OP, postinumero AS K
WHERE O.postinumero = K.postinumero
AND OP.postinumero = K.postinumero
AND OP.postinumero = O.postinumero
AND OP.sukunimi <> O.sukunimi
;


-- ryhmitellään sotun loppuosan perusteella arvosanat. 
-- skalaarifunktioiden avulla ryhmitteleminen ei välttämättä toimi kaikkialla
SELECT SUBSTRING(Opiskelija FROM 8), AVG(arvosana) AS Keskiarvo
FROM tenttii
WHERE Kurssi = 'TIE150'
GROUP BY SUBSTRING(Opiskelija FROM 8)
;

Skalaarifunktiot

Skalaarifunktiot kohdistuvat aina yksittäiseen arvoon ja palauttavat yhden arvon. Skalaarifunktioita voidaan käyttää WHERE-lauseessa.

SQL99-standardi määrittelee seuraavat skalaarifunktiot:

CURRENT_DATE

Palauttaa suoritushetkellä olevan päivämäärän.

SELECT CURRENT_DATE;

SELECT *
FROM Tenttii
WHERE Paivamaara < CURRENT_DATE;

INSERT INTO Tenttii
VALUES ('111111-111P', 'TIE150', CURRENT_DATE, 2)

CURRENT_TIME

Palauttaa suoritushetkellä olevan ajan.

SELECT CURRENT_TIME;

CURRENT_TIMESTAMP

Palauttaa suoritushetkellä olevan aikaleiman (päivämäärä + aika).

SELECT CURRENT_TIMESTAMP;

CURRENT_USER

Palauttaa aktiivisen käyttäjän tunnuksen.

SELECT CURRENT_USER;

SESSION_USER

Palauttaa aktiivisen Authorization ID:n jos muu kuin aktiivinen käyttäjä.

SELECT SESSION_USER;

SYSTEM_USER

Palauttaa aktiivisen käyttäjän tunnuksen asiakaskoneen käyttöjärjestelmässä.

SELECT SYSTEM_USER;

BIT_LENGTH(expression)

Palauttaa bittien lukumäärän.

SELECT etunimi, BIT_LENGTH(etunimi)
FROM Opiskelija;

CHAR_LENGTH(expression)

Palauttaa merkkien lukumäärän.

SELECT etunimi, CHAR_LENGTH(etunimi)
FROM Opiskelija;

EXTRACT(datetime_expression datepart FROM expression)

Palauttaa vuoden, kuukauden, päivän, tunnin, minuutin, sekunnin, aikavyöhyketunnin tai aikavyöhykeminuutin.

SELECT EXTRACT(YEAR FROM Paivamaara), EXTRACT(MONTH FROM Paivamaara),
EXTRACT(DAY FROM Paivamaara)
FROM Tenttii;

SELECT EXTRACT(HOUR FROM CURRENT_TIME), EXTRACT(MINUTE FROM CURRENT_TIME),
EXTRACT(SECOND FROM CURRENT_TIME);

-- Mitkä kurssit kukakin opiskelija on suorittanut aloitusvuonnaan
SELECT sukunimi, kurssi
FROM Opiskelija, Tenttii
WHERE Opiskelija.sotu = Tenttii.opiskelija
AND Opiskelija.aloitusvuosi = EXTRACT(YEAR FROM Paivamaara);

OCTET_LENGTH(expression)

Palauttaa oktettien lukumäärän. Sama kuin bittien lukumäärä jaettuna kahdeksalla.

SELECT etunimi, OCTET_LENGTH(etunimi)
FROM Opiskelija;

POSITION(starting_string IN search_string)

Palauttaa merkkijonon paikan toisessa merkkijonossa.

SELECT Sahkopostiosoite, POSITION('@' IN Sahkopostiosoite)
FROM Opiskelija;

-- Kenellä on sähköpostitunnuksen login osa alle 8 merkkiä pitkä.
SELECT Sahkopostiosoite
FROM Opiskelija
WHERE POSITION('@' IN Sahkopostiosoite) < 9;

||

Yhdistää kaksi merkkijonoa, lukua tai muuttujaa yhdeksi merkkijonoksi.

SELECT Etunimi || ' ' || Sukunimi
FROM Opiskelija;

CONVERT

Muuntaa merkkijonon toiseen muotoon saman merkistön sisällä.

LOWER

Muuntaa merkit kapitaaleista pieniksi.

SELECT LOWER(etunimi), LOWER(sukunimi)
FROM Opiskelija;

SELECT *
FROM Opiskelija
WHERE LOWER(sukunimi) = 'lahtonen';

SUBSTRING

Palauttaa osan merkkijonosta.

SELECT SUBSTRING(Sahkopostiosoite FROM 1 FOR 8)
FROM Opiskelija;

SELECT SUBSTRING(Sahkopostiosoite FROM 1 FOR (POSITION('@' IN Sahkopostiosoite) - 1))
FROM Opiskelija;

TRANSLATE

Muuntaa merkkijonon merkistöstä toiseen merkistöön.

TRIM

Poistaa merkkijonon alusta, lopusta tai molemmista halutut merkit.

SELECT sotu, TRIM(LEADING '1' FROM sotu)
FROM Opiskelija;

SELECT sotu, TRIM(TRAILING 'P' FROM sotu)
FROM Opiskelija;

SELECT sotu, TRIM(BOTH '1' FROM ( TRIM(TRAILING 'P' FROM sotu) ) )
FROM Opiskelija;

UPPER

Muuntaa merkkijonon kapitaaleiksi (isoiksi kirjaimiksi).

SELECT UPPER(etunimi), UPPER(sukunimi)
FROM Opiskelija;

SELECT *
FROM Opiskelija
WHERE UPPER(sukunimi) = 'lahtonen';

ABS

Palauttaa luvun itseisarvon

MOD

Palauttaa modulon eli jakojäännöksen

Epästandardeja mutta hyvin usein käytettyjä skalaarifunktioita:

ROUND

Pyöristää luvun

SELECT kurssi, AVG(arvosana), ROUND(AVG(arvosana), 1), TRUNC(AVG(arvosana),1)
FROM Tenttii
GROUP BY kurssi

TRUNC

Pyöristää luvun alaspäin

SELECT kurssi, AVG(arvosana), ROUND(AVG(arvosana), 1), TRUNC(AVG(arvosana),1)
FROM Tenttii
GROUP BY kurssi

Lisätietoja eri tietokannanhallintajärjestelmien tukemista funktioista voi lukea SQL in a Nutshell -kirjasta.