SQL-kyselyt - luento 6

Tällä luennolla käydään läpi monimutkaisempia SQL-kyselyjä.

Luentotaltiointi

Ongelmia videon katselussa?

Videotietokannan rakenne

Useamman kentän mukaan ehtorajoitettuja kyselyjä

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

Seuraavassa esimerkissä haetaan Elokuva-taulusta nimi- ja julkaisuvuositietoja. Ehtoa rajoitetaan ensin julkaisuvuoden mukaan (julkaisuvuosi > 2000) ja jäljelle jääviä rajoitetaan arvion mukaan (arvio=10). Ensimmäisen rajoituksen mukaan elokuvia on seitsemän kappaletta ja toisen toisen rajoituksen mukaisia näistä seitsemästä on vain kolme. Tuloksena näytetään kolmen elokuvan tiedot.

SELECT nimi, julkaisuvuosi
FROM Elokuva
WHERE julkaisuvuosi > 2000 AND arvio = 10;

Seuraavassa esimerkissä haetaan sellaisia elokuvia, joiden julkaisuvuosi on joko 2002 tai 2001. Tulokseen tulee yhteensä 7 elokuvaa.

SELECT nimi, julkaisuvuosi
FROM Elokuva
WHERE julkaisuvuosi = 2002 OR julkaisuvuosi = 2001;

Seuraavassa esimerkissä rajoitetaan elokuvia julkaisuvuoden ja arvion mukaan. Mukaan tulokseen tulee neljä elokuvaa, jotka kaikki ovat julkaistu vuoden 2000 jälkeen (>2000) ja joista osan arvio on 10 ja osan 8. Ehtojen liittämisjärjestyksen ilmoittamiseen kannattaa käyttää sulkeita, kuten esimerkissä.

SELECT nimi, julkaisuvuosi
FROM Elokuva
WHERE julkaisuvuosi > 2000 AND (arvio = 10 OR arvio = 8);

Seuraavassa esimerkissä haetaan niiden elokuvien tiedot, joiden arvio on suurempi tai yhtäsuuri kuin 7 ja pienempi tai yhtäsuuri kuin 9 (eli 7-9). Vastaukseksi tulee neljä elokuvaa.

SELECT nimi, julkaisuvuosi, arvio, vuokrahinta
FROM Elokuva
WHERE arvio >=7 AND arvio <= 9;

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

SELECT nimi, julkaisuvuosi, arvio, vuokrahinta
FROM Elokuva
WHERE arvio BETWEEN 7 AND 9;

Seuraavassa esimerkissä haetaan niiden elokuvien tiedot, joiden arvio on 7 tai 9. Vastaukseen tulee kaksi elokuvaa.

SELECT nimi, julkaisuvuosi, arvio, vuokrahinta
FROM Elokuva
WHERE arvio = 7 OR arvio = 9;

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

SELECT nimi, julkaisuvuosi, arvio, vuokrahinta
FROM Elokuva
WHERE arvio IN (7 ,9);

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

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

Seuraavassa kyselyssä haetaan kaikkien jäsenten nimi järjestettynä laskevaan järjestykseen (DESC). Tällöin Ville Vidiootti on ensimmäisenä ja Leila Leffafani viimeisenä.

SELECT nimi
FROM jasen
ORDER BY nimi DESC;

Seuraavassa kyselyssä haetaan elokuvan nimi ja julkaisuvuosi Elokuva-taulusta ja ne järjestettään julkaisuvuoden mukaan vanhimmasta uusimpaan.

SELECT nimi, julkaisuvuosi
FROM Elokuva
ORDER BY julkaisuvuosi ASC;

Edellisestä kyselystä poiketen seuraavassa järjestetään elokuvat ensin julkaisuvuoden mukaan ja sen jälkeen vielä nimen mukaan.

SELECT nimi, julkaisuvuosi
FROM Elokuva
ORDER BY julkaisuvuosi ASC, nimi ASC;

Kyselyjen tekeminen useampaan tauluun

Usein on tarpeellista tehdä kyselyjä useampaan tauluun. Esimerkiksi videovuokrausrekisterin yhteydessä halutaan selvittää mitä elokuvia kukin on vuokrannut. 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ä Elokuva että Jasen-taulusta. Tauluja ei ole kyselyssä yhdistetty mitenkään toisiinsa, joten vastauksia tulee 1155 kappaletta (15 vuokrausta * 11 elokuvaa * 7 henkiloa). Vastaus ei ole sellainen mitä haluttiin

Liittäminen tekemättä!

-- Tämä on esimerkki väärin tehdystä kyselystä. Ei siis toimi!
-- Liitos on tekemättä
SELECT Jasen.nimi, Elokuva.nimi
FROM Elokuva, Jasen, Vuokraus;

Seuraavassa kyselyssä liitetään taulut toisiinsa. Elokuvataulun elokuvaID esiintyy luonnollisesti myös vuokraustaulussa. Vastaavasti Jasen-taulun jasenID esiintyy myös Vuokraus-taulussa. Ehtojen pitää olla yhtä aikaa voimassa, joten ne on yhdistetty AND-operaattorilla.

Selventävä esimerkki.

-- Toimiva versio. Nyt on liitos tehtynä
SELECT Jasen.nimi, Elokuva.nimi
FROM Elokuva, Jasen, Vuokraus
WHERE Elokuva.elokuvaID = Vuokraus.elokuvaID
AND Jasen.jasenID = Vuokraus.JasenID
ORDER BY Jasen.nimi ASC;

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

SELECT j.nimi, e.nimi
FROM Elokuva AS e, Jasen AS j, Vuokraus AS v
WHERE e.elokuvaID = v.elokuvaID
AND j.jasenID = v.jasenID
ORDER BY j.nimi ASC;

Koostefunktiot

Koostefunktioilla voidaan laskea yksinkertaisia yhteenvetoja taulujen tiedoista

SELECT MAX(vuokrahinta)
FROM Elokuva

SELECT MIN(vuokrahinta)
FROM Elokuva

SELECT SUM(vuokrahinta), AVG(vuokrahinta)
FROM Elokuva

SELECT COUNT(*)
FROM Elokuva

SELECT COUNT(arvio)
FROM Elokuva

Kyselyn tietojen ryhmittely

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

Ryhmittelyä selventävät seuraavat esimerkit: esimerkki 1 ja esimerkki 2

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

Seuraavassa kyselyssä lasketaan vuokratuista elokuvista saatu tuotto. Ryhmittelyä täytyy käyttää kyselyssä, koska sillä määritellään minkä suhteen summa laskentaa. Esimerkissä käytetään SUM-koostefunktiota.

SELECT e.nimi, SUM(e.vuokrahinta)
FROM Elokuva AS e, Vuokraus AS v
WHERE e.elokuvaID = v.elokuvaID
GROUP BY e.nimi
ORDER BY e.nimi ASC;

Seuraavalla kyselyllä lasketaan vuokratuista videoista saatu tuotto pienen lisäehdon kanssa. HAVING-lauseella halutaan rajoittaa näytettäväksi ainoastaan ne videot, jotka on vuokrattu enemmän kuin yhden kerran.

  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).
SELECT e.nimi, SUM(e.vuokrahinta)
FROM Elokuva AS e, Vuokraus AS v
WHERE e.elokuvaID = v.elokuvaID
GROUP BY e.nimi
HAVING COUNT(e.nimi)> 1
ORDER BY e.nimi ASC;

Seuraavalla kyselyllä lasketaan videoista saatuja tuloja pienen lisäehdon avulla. Kyselyn tuloksena halutaan ainoastaan ne videot, jotka ovat tuottaneet viisi (euroa) tai enemmän.

SELECT e.nimi, SUM(e.vuokrahinta)
FROM Elokuva AS e, Vuokraus AS v
WHERE e.elokuvaID = v.elokuvaID
GROUP BY e.nimi
HAVING SUM(e.vuokrahinta)>= 5
ORDER BY e.nimi ASC;

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. Ryhmittely tapahtuu siinä järjestyksessä missä kentät on merkitty GROUP BY -ehtoon.

Käyttäjien kommentit

Kommentoi tätä sivua Lisää uusi kommentti
Kurssimateriaalien käyttäminen kaupallisiin tarkoituksiin tai opetusmateriaalina ilman lupaa on ehdottomasti kielletty!
http://appro.mit.jyu.fi/tiedonhallinta/luennot/luento6/
© Antti Ekonoja (antti.j.ekonoja@jyu.fi) <http://users.jyu.fi/~anjoekon/>
Tommi Lahtonen (tommi.j.lahtonen@jyu.fi) <http://hazor.iki.fi/>
Jukka Mäntylä (jmantyla@iki.fi) <http://www.iki.fi/jmantyla/>
2011-01-31 13:56:00
Informaatioteknologia - Jyväskylän yliopiston informaatioteknologian tiedekunta