Tietokantakyselyt - Luento 8

• Luentotaltiointi
• Autonumber
• Yleistä SQL:n käytöstä kyselyissä ja tiedon lisäämisessä
• Kyselyt
  • Yksinkertaisia kyselyjä yhteen tauluun
  • Ehtorajoitettuja kyselyjä yhteen tauluun
  • Useamman kentän mukaan ehtorajoitettuja kyselyjä
  • Kyselyn tietojen järjestäminen
  • Kyselyjen tekeminen useampaan tauluun
  • Kyselyn tietojen ryhmittäminen
• Lisätietoa
  • Alikyselyt
  • Näkymät

Tällä luennollä käydään läpi tietojen lisääminen tietokannan tauluihin (relaatioihin) SQL:n avulla sekä erilaisten kyselyjen tekemistä tietokannan tiedoista.

Luennolla käytetään kyselyissä käytetään esimerkkinä suutari-tietokantaa.

• Esimerkin tietokannan luontilauseet
  • Access
  • Base
• Esimerkkinä käytettyjen tietojen lisääminen SQL:nä
• Tietokantojen tallennustiedostot, joissa on testidataa
  • suutari.mdb (Access)
  • suutari.odb (OO Base)

Autonumber

Keksittyjen perusavainten syöttäminen käsin voi olla turhaa ja rasittavaa. Monesti kannattaa tällaisissa tapauksissa antaa tietokannanhallintajärjestelmän huoleksi perusavaimen keksiminen

• Microsoft Access tuntee autonumber-tietotyypin, joka ei ole minkään standardinmukainen, mutta helpottaa huomattavasti tietojen syöttämistä.
• SQL-lauseissa autonumber-tyyppiä vastaa COUNTER. OpenOfficen tietokannanhallintajärjestelmä vaatii lisämääreen GENERATED BY DEFAULT AS IDENTITY tietotyypin perään.
• Muissa tietokannanhallintajärjestelmissä käytetään esim. lisämääreitä tai tietotyyppejä AUTO_INCREMENT (MySQL) tai SERIAL (PostgreSQL). Joskus automaattinen numerointi on toteutettavissa epästandardilla CREATE SEQUENCE -laajennoksella (PostgreSQL, Oracle).

Yleistä SQL:n käytöstä kyselyissä ja tiedon lisäämisessä

Useimmista tietokantahallintajärjestelmistä tai -sovelluksista löytyy graafinen käyttöliittymä taulujen luomiseen, tietojen lisäämiseen sekä erilaisten kyselyjen tekemiseen. Miksi SQL:ää sitten kannattaa käyttää?

• Jokainen tietokannanhallintajärjestelmä tukee SQL:ää kohtuullisen hyvin. (Pieniä eroja esim. Access)
• Tietohallintajärjestelmien ja sovelluksien omat kyselytyökalut voivat olla hyvinkin rajoittuneita.

Kyselyt

Tietokannan tauluihin voidaan tehdään monipuolisia kyselyjä SELECT-komennon avulla. Seuraavissa esimerkeissä käydään läpi kyselyjen tekemistä aina yksinkertaisista yhteen tauluun kohdistuvasta kyselystä hieman monimutkaisempiin ja moneen tauluun kohdistuvaan kyselyyn. Seuraavassa SELECT-komennon (yksinkertaistettu) yleinen muoto. Pakollisia osia ovat ainoastaan SELECT ja FROM, kuten esimerkeistä käy ilmi.

SELECT Kentta1, Kentta2
FROM Taulu1, Taulu2
WHERE Ehto;

Yksinkertaisia kyselyjä yhteen tauluun

Seuraavalla kyselyllä haetaan Henkilo-taulun kaikki taulun tiedot eli kaikkien tietueiden (rivien) kaikki kentät (sarakkeet). Tähtimerkki (*) korvaa kaikkien kenttien luettelemisen. Kyselyssä tarvitaan harvemmin kaikkia kenttiä, joten myöhemmät esimerkit ovat tavallisesti käyttökelpoisempia.

SELECT *
FROM Henkilo;

Seuraavalla kyselyllä haetaan Henkilo-taulun kaikkien rivien (tietueiden) Etunimi-kentät:

SELECT Etunimi
FROM Henkilo;

Seuraavalla kyselyllä haetaan kaikkien asiakkaiden etu- ja sukunimet Henkilo-taulusta.

SELECT Etunimi, Sukunimi
FROM Henkilo;

Seuraavalla kyselyllä haetaan kaikki sukunimet, mutta samaa sukunimeä esitetään vastauksessa vain kerran:

SELECT DISTINCT Sukunimi
FROM Henkilo;

Seuraavalla kyselyllä haetaan Tyo-taulusta kaikkien töiden kuvaukset, kestot ja hinnat.

SELECT Tehtavankuvaus, Kesto, Hinta
FROM Tyo;

Edellisen kyselyn tekeminen palauttaa näkyville taulukon, jossa oli näkyvillä sarakeotsikkoina Tehtavankuvaus, Kesto ja Hinta. Otsikoiden alle tuli varsinainen taulujen tieto. Haluttaessa voimme myös muuttaa tuloksessa näytettäviä sarakeotsikoita AS-sidesanan avulla. Seuraavassa esimerkissä muutamme Tehtavankuvaus-otsikon paikalle Kuvaus-otsikon ja Hinta-otsikon paikalle Hinta-arvio-otsikon. Lisäksi esimerkissä on käytetty kommenttia. Kommenttirivi täytyy aloittaa tuplaviivalla (--).

-- Muutetaan hieman tuloksen otsikkotietoja
SELECT Tehtavankuvaus AS Kuvaus, Kesto, Hinta AS ArvioituHinta
FROM Tyo;

Peruskyselyillä voidaan kätevästi rajoittaa näytettäviä tietosarakkeita (kenttiä), mutta usein on tarve tehdä jonkin ehdon mukaan rajoitettuja kyselyjä tauluun.

Ehtorajoitettuja kyselyjä yhteen tauluun

Ehdon avulla voidaan kyselyssä rajoittaa tulokseen tulevia tulosrivejä (tietueita). Ehtorajoitetuissa kyselyissä pitää muistaa muutama asia.

• Pienillä ja isoilla kirjaimille on merkitystä!
• Tekstiarvot täytyy antaa heittomerkkien sisään (esim. 'tanne').
• Numeroarvot voidaan antaa ilman heittomerkkejä (esim. 2000).
• Kyselyä rajoittavan ehdon ei tarvitse kohdistua lopputulokseen tulevaan kenttään.
• Käytettävissä on vertailuoperaattorit
  • yhtäsuuruus (=)
  • pienempi kuin (<)
  • suurempi kuin (>)
  • pienempi tai yhtä suuri kuin (<=)
  • suurempi tai yhtä suuri kuin (>=)
  • erisuuruus (<>)
• NOT-operaattorilla ehdosta voidaan tehdä päinvastainen.
• LIKE-operaattorilla voidaan tehdä hakua osittain täsmäävillä arvoilla.
  • Prosenttimerkki (%) vastaa nollaa tai useampaa merkkiä. Accesissa käytetään asteriksia (*).
  • Alaviiva (_) tarkoittaa mitä tahansa yksittäistä merkkiä. Accesissa käytetään kysymysmerkkiä (?).
• NULL-arvoja voidaan etsiä IS NULL ja IS NOT NULL-määreillä. NULL-arvoja kannattaa kuitenkin vältellä viimeiseen asti.

Seuraavassa esimerkissä rajoitetaan haettavien tietueiden (rivien) määrää WHERE-ehdolla. Näkyville halutaan vain ne työt, joiden kesto on tasan 1 (tunti). Ehdossa määritellään sarakkeen nimi (kesto) ja siihen liittyvä ehto (= 5).

SELECT Tehtavankuvaus, Kesto
FROM Tyo
WHERE kesto = 1;

Seuraavassa esimerkissä hakuehtona mainittua kenttää (Kesto) ei esiinny haettavissa kentissä (Tehtavankuvaus), mutta haku toimii siitäkin huolimatta. Ominaisuus on kätevä, koska aina ei haluta lopputulokseen rajoittavia kenttiä. Esimerkissä halutaan pelkästään ne työn kuvaukset, joiden kesto on 1 (tunti).

SELECT Tehtavankuvaus
FROM Tyo
WHERE Kesto = 1;

Seuraavassa esimerkissä haetaan jäsenten työt, joiden hinta on yli 19 (euroa). Tuloksena tulee vain kolme työtä kuudesta.

SELECT Tehtavankuvaus
FROM Tyo
WHERE Hinta > 19;

Seuraavassa esimerkissä haetaan jäsenten työt, joiden hinta on suurempaa tai yhtäsuurta kuin 19 (euroa). Tuloksena saadaan neljän työn tiedot, koska edellisestä esimerkistä poiketen yhden mukaan tulleen työn hinta on 19.

SELECT Tehtavankuvaus, Kesto, Hinta
FROM Tyo
WHERE hinta >= 19;

Seuraavassa esimerkissä haetaan niiden asiakkaiden nimiä Henkilo-taulusta, joiden sukunimi on Meikäläinen. Huomaa, että kirjaiten koolla on merkitystä, joten meikäläinen on eri asia kuin Meikäläinen. Vastaukseen tuli kaksi asiakasta.

SELECT Etunimi, Sukunimi
FROM Henkilo
WHERE Sukunimi = 'Meikäläinen';

Seuraavassa esimerkissä haetaan niiden asiakkaiden etu- ja sukunimi Henkilo-taulusta, joiden sukunimi ei ole Meikäläinen. Vastaukseen tuli kolme asiakasta.

SELECT Etunimi, Sukunimi
FROM Henkilo
WHERE Sukunimi <> 'Meikäläinen';

Seuraava esimerkki antaa saman vastauksen kuin edellinen esimerkki. Nyt kysely on toteutettu NOT-operaattorilla.

SELECT Etunimi, Sukunimi
FROM Henkilo
WHERE NOT Sukunimi = 'Meikäläinen';

Seuraavassa esimerkissä käytetään LIKE-operaattoria rajoittamaan hakua. Esimerkissä halutaan ainoastaan sellaisten asiakkaiden nimet, joiden sukunimi päättyy tavuun nen. Vastaukseen tule asiakkaita 4 kpl.

SELECT Etunimi, Sukunimi
FROM Henkilo
WHERE Sukunimi LIKE '%nen';

Seuraavassa esimerkissä halutaan näkyville sellaisten töiden tiedot, joiden tehtäväkuvauksessa esiintyy sana kopio. (esim. 35 kopiota). Vastaukseen tulee kahden työn tiedot.

SELECT Tehtavankuvaus, Kesto, Hinta
FROM Tyo
WHERE Tehtavankuvaus LIKE '%kopio%';

Useamman kentän mukaan ehtorajoitettuja kyselyjä

Taulun tietoja voidaan rajoittaa useammankin ehdon mukaan. Ehtojen liittäminen toisiinsa tapahtuu AND- ja OR-operaattoreiden mukaan.

• AND-operaattorilla yhdistetyt ehdot rajaavat taulun tietoja yhdessä. Tällöin ensimmäisen ehdon rajoittamia rivejä rajoitetaan lisää toisella ehdolla. AND-operaattorilla yhdistettyjen ehtojen täytyy toteutua samalla rivillä.
• OR-operaattorilla yhdistettyihin ehtoihin rajaavat taulun tietoja erikseen. Tällöin tuloksena tulee ensimmäisen ehdon täyttävät rivit, joihin lisätään toisen ehdon täyttämät rivit. Ehdot voivat toteutua samoilla riveillä, mutta rivejä ei näytetä kahta kertaa. OR-operaattorilla yhdistettyjen ehtojen EI tarvitse toteutua samalla rivillä.

Seuraavassa esimerkissä haetaan töitä, joista saa paljon rahaa ja ne kestävät suhteellisen vähän aikaa. Ehtoa rajoitetaan ensin keston mukaan (kesto < 3) ja jäljelle jääviä rajoitetaan hinnan mukaan (hinta > 30). Ensimmäisen rajoituksen mukaan töitä on viisi kappaletta ja näistä viidestä toisen ehdon toteuttaa kaksi.

SELECT Tehtavankuvaus, Kesto, Hinta
FROM Tyo
WHERE Kesto < 3 AND hinta > 30;

Seuraavassa esimerkissä haetaan sellaisia töitä, joiden kesto on tasan 1 tai 2,5 (tuntia). Tulokseen tulee yhteensä 4 työtä.

SELECT Tehtavankuvaus, Kesto, Hinta
FROM Tyo
WHERE Kesto = 1 OR Kesto = 2.5;

Edellinen esimerkki voidaan myös ilmaista IN-määreen avulla.

SELECT Tehtavankuvaus, Kesto, Hinta
FROM Tyo
WHERE Kesto IN (1, 2.5);

Seuraavassa esimerkissä rajoitetaan hinnan ja keston mukaan. Nyt hinnan on oltava yli 15 (euroa) ja keston 1h tai 2,5h. Vastauksia tulee 3 kappaletta.

SELECT Tehtavankuvaus, Kesto, Hinta
FROM Tyo
WHERE Hinta > 15 AND (kesto = 1 OR kesto = 2.5);

Seuraavassa esimerkissä haetaan niiden töiden tiedot, joiden hinta on välillä 30-60 euroa. Vastaukseksi tulee kaksi työtä.

SELECT Tehtavankuvaus, Kesto, Hinta
FROM Tyo
WHERE Hinta >=30 AND Hinta <= 60;

Edellinen esimerkki voidaan ilmaista myös BETWEEN-määreen avulla seuraavasti.

SELECT Tehtavankuvaus, Kesto, Hinta
FROM Tyo
WHERE Hinta BETWEEN 30 AND 60;

Kyselyn tietojen järjestäminen

Kyselyn lopputulokseen tulevia tietoja voidaan myös haluttaessa järjestää. Järjestäminen onnnistuu lisäämällä ORDER BY määrityksen kyselyn loppuun. Jos

• Oletuksena tiedot järjestetään pienimmästä suurimpaan eli nousevaan järjestykseen.
• ASC-määritys järjestää tiedot pienimmästä suurimpaan.
• DESC-määritys järjestää tiedot suurimmasta pienimpään eli laskevaan järjestykseen.

SELECT Kentta1, Kentta2
FROM Taulu1, Taulu2
WHERE Ehto
ORDER BY Kentta1 ASC;

Seuraavassa kyselyssä haetaan kaikkien asiakkaiden nimi järjestettynä laskevaan järjestykseen (DESC). Tällöin Ville Valtonen on ensimmäisenä.

SELECT Etunimi, Sukunimi
FROM Henkilo
ORDER BY Sukunimi DESC;

Matin ja Maijan järjestys on kuitenkin vielä epäselvä. Määrätään, että järjestetään vielä toissijaisesti etunimen mukaan laskevaan järjestykseen, jolloin Maija on vastauksessa viimeisenä.

SELECT Etunimi, Sukunimi
FROM Henkilo
ORDER BY Sukunimi DESC, Etunimi DESC;

Seuraavassa kyselyssä haetaan työt ja järjestään ne hinnan mukaan pienimmästä suurempaan.

SELECT Tehtavankuvaus, Kesto, Hinta
FROM Tyo
ORDER BY Hinta ASC;

Kyselyjen tekeminen useampaan tauluun

Usein on tarpeellista tehdä kyselyjä useampaan tauluun. Esimerkiksi suutari haluaa varmasti tietää, mitkä tavarat liittyvät mihinkäkin työhön ja kuka työn ostanut asiakas. Valmistuneesta työstä pitää soittaa henkilölle, joten pitäisi saada selville henkilöön liittyvät puhelinnumerot. Koska tiedot ovat useammassa taulussa, niin taulut joudutaan jollakin tavalla "yhdistämään" kyselyssä toisiinsa. Taulujen yhdistäminen tapahtuu WHERE-lausetta käyttäen. Tauluissa on toisensa yhdistäviä kenttiä, jotka näkee havainnollisesti luennon alussa olevasta taulu(relaatio)kuvasta.

Seuraavan esimerkin haku hakee kaikki tiedot sekä Henkilo, -taulusta. Tauluja ei ole kyselyssä yhdistetty mitenkään toisiinsa, joten vastauksia tulee 30 kappaletta (5 henkilöä * 6 puhelinnumeroa). Vastaus ei ole sellainen mitä haluttiin...

Liittäminen tekemättä!

SELECT Henkilo.Etunimi, Henkilo.Sukunimi, Puhelinnumero.Suuntanumero, Puhelinnumero.Loppuosa
FROM Henkilo, Puhelinnumero;

Seuraavassa kyselyssä on liitetään taulut toisiinsa. Henkilo-taulun AsiakasID esiintyy viitekenttänä puhelinnumerotaulussa nimellä Henkilo_AsiakasID. Piste edellä oleva osa kertoo mistä taulusta kenttä otetaan. Tämä on välttämätöntä vain, jos eri tauluissa on samannimisiä kenttiä.

SELECT Henkilo.Etunimi, Henkilo.Sukunimi, Puhelinnumero.Suuntanumero, Puhelinnumero.Loppuosa
FROM Henkilo, Puhelinnumero;
WHERE Henkilo.AsiakasID = Puhelinnumero.Henkilo_AsiakasID

Entäpä jos halutaan selvittää mitä tavaroita eri asiakkaat ovat tuoneet suutarille? Nyt tarvitaan tauluja Henkilo, Tyo, Tavara ja Tyyppi. Tässä välissä kannattaa vilkaista relaatiokaaviota. Jokaisen kahta taulua yhdistävien viitekenttien välinen yhteys on määritettävä WHERE-ehtoon. Jotta kaikki ehdot olisivat voimassa on käytettävä AND-operaattoria. Ehtoja tarvitaan mukaan otettavien taulujen määrä vähennettynä yhdellä!

SELECT Henkilo.Sukunimi, Henkilo.Etunimi, Tyo.Tehtavankuvaus, Tavara.Lapunnro, Tyyppi.Tyyppinimi
FROM Henkilo, Tyo, Tavara, Tyyppi
WHERE Tyo.Henkilo_AsiakasID = Henkilo.AsiakasID
AND Tavara.Tyo_TyoID = Tyo.TyoID
AND Tavara.Tyyppi_TyyppiID = Tyyppi.TyyppiID
ORDER BY Henkilo.Sukunimi ASC;

Seuraava esimerkki toimii täsmälleen samoin kuin edellinenkin. Nyt taulujen nimistä on tehty käyttökelpoiset lyhenteet AS-sidesanan avulla (esim. Henkilo AS P), jolloin koko taulun nimeä ei tarvitse toistaa enää uudelleen. Pelkkä lyhenne riittää yksilöimään taulun.

SELECT P.Sukunimi, P.Etunimi, W.Tehtavankuvaus, O.Lapunnro, T.Tyyppinimi
FROM Henkilo AS P, Tyo AS W, Tavara AS O, Tyyppi AS T
WHERE W.Henkilo_AsiakasID = P.AsiakasID
AND O.Tyo_TyoID = W.TyoID
AND O.Tyyppi_TyyppiID = T.TyyppiID
ORDER BY P.Sukunimi ASC;

Jos vain työt, jotka liittyvät Esko Saarelaan, niin tämä onnistuu lisäämällä uuden ehdon WHERE-lauseeseen

SELECT P.Sukunimi, P.Etunimi, W.Tehtavankuvaus, O.Lapunnro, T.Tyyppinimi
FROM Henkilo AS P, Tyo AS W, Tavara AS O, Tyyppi AS T
WHERE W.Henkilo_AsiakasID = P.AsiakasID
AND O.Tyo_TyoID = W.TyoID
AND O.Tyyppi_TyyppiID = T.TyyppiID
AND P.Etunimi = 'Esko' AND P.Sukunimi = 'Saarela'

Jos haluttaisiin selvittää koska eri asiakkaiden työt on aloitettu, niin tällöin kyselyssä tarvittavat taulut ovat Henkilo, Tyo, Tehdaan ja Tyovaihe.

SELECT P.Sukunimi, P.Etunimi, W.Tehtavankuvaus, T.Pvm
FROM Henkilo AS P, Tyo AS W, Tehdaan AS T, Tyovaihe AS V
WHERE W.Henkilo_AsiakasID = P.AsiakasID
AND T.Tyo_TyoID = W.TyoID
AND T.Tyovaihe_TyovaiheID = V.TyovaiheID
AND V.Kuvaus = 'Työn aloitus';

Jos tiedetään, että työn aloitus on TyovaiheID:llä kaksi, niin kyselyksi kelpaa myös:

SELECT P.Sukunimi, P.Etunimi, W.Tehtavankuvaus, T.Pvm
FROM Henkilo AS P, Tyo AS W, Tehdaan AS T, Tyovaihe AS V
WHERE W.Henkilo_AsiakasID = P.AsiakasID
AND T.Tyo_TyoID = W.TyoID
AND T.Tyovaihe_TyovaiheID = 2;

Mitä työvaiheita on jo tehty Esko Saarelan töissä?

SELECT W.TyoID, T.Pvm, V.Kuvaus
FROM Henkilo AS P, Tyo AS W, Tehdaan AS T, Tyovaihe AS V
WHERE W.Henkilo_AsiakasID = P.AsiakasID
AND T.Tyo_TyoID = W.TyoID
AND T.Tyovaihe_TyovaiheID = V.TyovaiheID
AND P.Etunimi = 'Esko' AND P.Sukunimi = 'Saarela'

Mitä kenkätöitä on saatu tehtyä loppuun tämän kuun aikana?

SELECT W.TyoID, W.Tehtavankuvaus, T.Pvm
FROM Tyo AS W, Tehdaan AS T, Tyovaihe AS V, Tavara, Tyyppi
WHERE T.Tyo_TyoID = W.TyoID
AND T.Tyovaihe_TyovaiheID = V.TyovaiheID
AND Tavara.Tyo_TyoID = W.TyoID
AND Tavara.Tyyppi_TyyppiID = Tyyppi.TyyppiID
AND Tyyppi.Tyyppinimi = 'Kengät'
AND V.Kuvaus = 'Työn lopetus' AND '2006-11-1' <= T.Pvm AND T.Pvm <= '2006-11-30';

Access-ohjelmaa varten viimeinen rivi on muutettava muotoon:

AND V.TyovaiheID = 3 AND #2006-11-1# <= T.Pvm AND T.Pvm <= #2006-11-30#;

Kyselyn tietojen ryhmittäminen

Kyselyn tietojen ryhmittäminen tulee tarpeelliseksi esimerkiksi erilaisten koostefunktioiden käytön yhteydessä. Tällöin ryhmittelyä tarvitaan kertomaan ryhmä, jonka suhteen koostefunktiota käytetään.

• GROUP BY-määrettä ei pidä käyttää ellei käytä koostefunktioita.
• Kaikki tavalliset kentät (ei koostefunktioissa käytettävät) pitää luetella GROUP BY-määreessä.
• Kyselyn rakentaminen kannattaa aloittaa ilman koostefunktiota. Jos tulokseen tulee toistuvia tyyppejä, nimiä tms. niin nämä ovat järkeviä kenttiä ryhmittelyä varten.
• COUNT-koostefunktio sopii määrien laskemiseen, mutta muita koostefunktioita varten tarvitaan numeerinen kenttä.

SELECT Kentta1, SUM(Kentta2)
FROM Taulu1, Taulu2
WHERE Ehto
GROUP BY Kentta1
HAVING  Lisaehto
ORDER BY Kentta1 ASC;

Lasketaan kaikista töistä saadut tuotot:

SELECT SUM(Tyo.Hinta)
FROM Tyo

Seuraavassa kyselyssä lasketaan henkilöiden suutarille tuomat tuotot. Aloitetaan ensin selvittämällä kaikki työt, näiden hinnat ja kuka ne on teetättänyt:

SELECT Henkilo.Sukunimi, Henkilo.Etunimi, Tyo.Hinta FROM Henkilo, Tyo WHERE Henkilo.AsiakasID = Tyo.Henkilo_AsiakasID ;

Seuraavassa esimerkissä käytetään SUM-koostefunktiota (hinnan suhteen). Ryhmittelyä täytyy käyttää kyselyssä, koska sillä määritellään minkä suhteen summa lasketaan (henkilön etunimi ja sukunimi).

SELECT Henkilo.Sukunimi, Henkilo.Etunimi, SUM(Tyo.Hinta) AS HenkilonTuomatTuotot
FROM Henkilo, Tyo
WHERE Henkilo.AsiakasID = Tyo.Henkilo_AsiakasID
GROUP BY Henkilo.Sukunimi, Henkilo.Etunimi;

Lasketaan henkilöiden suutarilla tekemien töiden määrä:

SELECT Henkilo.Etunimi, Henkilo.Sukunimi, COUNT(*) AS Määrä
FROM Tyo, Henkilo
WHERE Tyo.Henkilo_AsiakasID = Henkilo.AsiakasID
GROUP BY Henkilo.Etunimi, Henkilo.Sukunimi
ORDER BY Henkilo.Sukunimi ASC;

Työhön liittyvien tavaroiden määrä:

SELECT Tehtavankuvaus, COUNT(TyoID) AS TavaroidenMaara
FROM Tyo, Tavara
WHERE Tavara.Tyo_TyoID = Tyo.TyoID
GROUP BY Tehtavankuvaus
;

HAVING-lauseella voidaan rajoittaa rivien määrä koostefunktion tuloksen suhteen:

SELECT Henkilo.Sukunimi, Henkilo.Etunimi, SUM(Tyo.Hinta) AS HenkilonTuomatTuotot
FROM Henkilo, Tyo
WHERE Henkilo.AsiakasID = Tyo.Henkilo_AsiakasID
GROUP BY Henkilo.Sukunimi, Henkilo.Etunimi
HAVING SUM(Tyo.Hinta)> 50;

1. WHERE-ehdolla rajoitetaan rivien määrää.
2. GROUP BY-määreellä ryhmitellään rivit tietyn ehdon perusteella. Tämä mahdollistaa koostefunktioiden käytön ryhmän tietojen laskemiseen.
3. HAVING-ehdolla voidaan edelleen rajata ryhmien näyttämistä (kohdistuu siis ryhmässä oleviin riveihin). HAVING-ehto liittyy aina koostefunktioon, ei siis tavalliseen kenttään!

Järjestelyehtoja voi olla useampiakin. Tällöin ne erotellaan pilkuilla GROUP BY-ehdossa. Vähintään SELECT-lauseessa olevat kentät, poislukien koostefunktiokentät, täytyy merkitä GROUP BY:hin. GROUP BY:ssa voi olla toki muitakin kenttiä mitä ei ole SELECT-lauseessa.

Lisätietoa

Nämä asiat eivät kuulu kurssin varsinaiseen aihepiiriin, mutta ovat nice-to-know-tietoa:

Alikyselyt

Jos halutaan rajoittaa tulosjoukkoa toisen kyselyn perusteella, niin tämä onnistuu alikyselyillä. Esim. halutaan kaikki työn, jotka on aloitettu, mutta joita ei ole vielä lopetettu:

SELECT P.Sukunimi, P.Etunimi, W.Tehtavankuvaus, T.Pvm, V.Kuvaus
FROM Henkilo AS P, Tyo AS W, Tehdaan AS T, Tyovaihe AS V
WHERE W.Henkilo_AsiakasID = P.AsiakasID
AND T.Tyo_TyoID = W.TyoID
AND T.Tyovaihe_TyovaiheID = V.TyovaiheID
AND V.Kuvaus = 'Työn aloitus'
AND W.TyoID NOT IN (
  SELECT TyoID
  FROM Tyo, Tehdaan, Tyovaihe
  WHERE Tyo.TyoID = Tehdaan.Tyo_TyoID
  AND Tehdaan.Tyovaihe_TyovaiheID = Tyovaihe.TyovaiheID
  AND Tyovaihe.Kuvaus = 'Työn lopetus'
)
;

Ensin käsitellään sisempi kysely, josta tuloksena on yksi sarake, jossa on kaikki TyoID:t, mitkä liittyvät työnvaiheeseen "työn lopetus". Sen jälkeen tehdään kysely, jossa haetaan työt, jotka liittyvät vaiheeseen "työn aloitus". Lopuksi lisätään uusi rajoitus niin, että otetaan vain ne työt, joiden TyoID ei ole sisemmän kyselyn tulosjoukossa.

Toinen vaihtoehto (EXISTS):

SELECT P.Sukunimi, P.Etunimi, W.Tehtavankuvaus, T.Pvm, V.Kuvaus
FROM Henkilo AS P, Tyo AS W, Tehdaan AS T, Tyovaihe AS V
WHERE W.Henkilo_AsiakasID = P.AsiakasID
AND T.Tyo_TyoID = W.TyoID
AND T.Tyovaihe_TyovaiheID = V.TyovaiheID
AND V.Kuvaus = 'Työn aloitus'
AND NOT EXISTS (
  SELECT *
  FROM Tehdaan, Tyovaihe
  WHERE Tehdaan.Tyo_TyoID = W.TyoID
  AND Tehdaan.Tyovaihe_TyovaiheID = Tyovaihe.TyovaiheID
  AND Tyovaihe.Kuvaus = 'Työn lopetus'
);

Ilman EXISTS-rajoitusta saadaan kaikki työt, jotka liittyvät vaiheeseen työn aloitus. Halutaan rajata nämä vielä siten, että otetaan vain ne, joilla ei ole työn lopetus. Tehdään alikysely, joka kytketään ulomman kyselyn tiettyyn ominaisuuteen. Tässä tapauksessa se on W.TyoID. Sisempi kysely suoritetaan jokaista ulomman kyselyn tulosriviä kohden. Jos saadaan jokin vastaus eli tietyn rivin TyoID:lla liittyy työvaiheeseen Työn lopetus, niin tällöin riviä ei hyväksytä lopulliseen tulokseen (NOT EXISTS). EXIST-alikyselyissä vastauksen muodolla ei ole väliä, ratkaisevaa on vain saadaanko vastaus vai ei.

Näkymät

Luentoesimerkissä haluttiin laskea, mikä on tuottavin tai kannattavin tavaratyyppi. Emme suoraan voi käyttää tauluja Tyo, Tavara ja Tyyppi ja laskea keskiarvoa hinnalle tyypin mukaan ryhmiteltynä, koska jos johonkin työhön liittyy useita tavaroita, niin näille tulee sama hinta kuin mitä koko työllä on! Ensin on siis laskettava mikä on kunkin työn tavarakohtainen hinta:

SELECT TyoID, Hinta, COUNT(*) AS TavaroidenMaara, Hinta / TavaroidenMaara AS TavaranKeskihinta
FROM Tyo, Tavara
WHERE Tavara.Tyo_TyoID = Tyo.TyoID
GROUP BY TyoID, Hinta;

tai toinen vaihtoehto:

SELECT TyoID, MAX(hinta) / COUNT(lapunnro) as TavaranKeskihinta
FROM Tyo, Tavara
WHERE Tyo.TyoID = Tavara.Tyo_TyoID
GROUP BY TyoID;

Tämä onnistuu, koska MAX(hinta) on aina sama jokaista työtä kohden

Tämän jälkeen luodaan näkymä kyselyn pohjalta. Näkymä käyttäytyy kuin uusi taulu, mutta saa tietueensa muiden taulujen pohjalta.

CREATE VIEW Keskihinnat AS
SELECT TyoID, Hinta, COUNT(*) AS TavaroidenMaara, Hinta / TavaroidenMaara AS TavaranKeskihinta
FROM Tyo, Tavara, Tyyppi
WHERE Tavara.Tyo_TyoID = Tyo.TyoID AND Tavara.Tyyppi_TyyppiID = Tyyppi.TyyppiID
GROUP BY TyoID, Hinta
;

Nyt saamme tavaran (keski-)hinnan ja tyypin samaan kyselyyn:

SELECT Tyyppinimi, TavaranKeskihinta
FROM Tavara, Tyyppi, Keskihinnat
WHERE Tavara.Tyo_tyoid = Keskihinnat.TyoID
AND Tavara.Tyyppi_TyyppiID = Tyyppi.TyyppiID
;

Tyypin keskihinta voidaan laskea koostefunktiolla AVG ja ryhmittelemällä tyypinnimen mukaan:

SELECT Tyyppinimi, AVG(TavaranKeskihinta) AS TyypinKeskihinta
FROM Tavara, Tyyppi, Keskihinnat
WHERE Tavara.Tyo_tyoid = Keskihinnat.TyoID
AND Tavara.Tyyppi_TyyppiID = Tyyppi.TyyppiID
GROUP BY Tyyppinimi;