4. Lisää SQL-kielestä

Tyypit ja lausekkeet

SQL-kielessä esiintyy tyyppejä ja lausekkeita samaan tapaan kuin ohjelmoinnissa. Olemme jo nähneet monia esimerkkejä SQL-komennoista, mutta nyt on hyvä hetki tutustua syvällisemmin kielen rakenteeseen.

Jokainen tietokantajärjestelmä toteuttaa tyypit ja lausekkeet vähän omalla tavallaan, ja tietokantojen toiminnassa on paljon pieniä eroja. Niinpä aiheeseen liittyvät yksityiskohdat kannattaa tarkastaa käytetyn tietokannan dokumentaatiosta.

Tyypit

Taulun määrittelyssä jokaiselle sarakkeelle annetaan tyyppi:

CREATE TABLE Movies (
  id INTEGER PRIMARY KEY,
  name TEXT,
  release_year INTEGER
);

Tässä sarakkeen name tyyppi on TEXT (merkkijono) ja sarakkeen release_year tyyppi on INTEGER (kokonaisluku). Nämä ovat yleisimmät tyypit, jotka ovat saatavilla näillä nimillä monissa tietokannoissa. Esimerkkejä muista yleisistä tyypeistä ovat TIMESTAMP (ajanhetki), REAL (liukuluku) ja BLOB (raakadata).

TEXT vs. VARCHAR

Perinteikäs tapa tallentaa merkkijono SQL:ssä on käyttää tyyppiä VARCHAR, jossa annetaan suluissa merkkijonon maksimipituus. Esimerkiksi tyyppi VARCHAR(10) tarkoittaa, että merkkijonossa voi olla enintään 10 merkkiä.

Tämä on muistuma vanhan ajan ohjelmoinnista, jossa merkkijono saatettiin esittää kiinteän pituisena merkkitaulukkona. Tyyppi TEXT on kuitenkin mukavampi, koska siinä ei tarvitse keksiä maksimipituutta.

SQLiten tyypit

Erikoinen SQLiten piirre on, että taulun määrittelyssä esiintyvä tyyppi on vain ohje, mitä tyyppiä sarakkeessa tulisi olla. Voimme kuitenkin olla välittämättä ohjeesta ja vaikkapa tallentaa kokonaisluvun kohdalle merkkijonon:

INSERT INTO Movies (name, release_year) VALUES ('Lumikki', 'abc');

Lisäksi tyypin nimenä voi olla mikä tahansa merkkijono, vaikka SQLitessä ei olisi sellaista tyyppiä. Tämän avulla voimme esimerkiksi määritellä sarakkeen, johon on tarkoitus tallentaa ajanhetki:

CREATE TABLE Bookings (
  id INTEGER PRIMARY KEY,
  start_time TIMESTAMP,
  end_time TIMESTAMP,
  description TEXT
);

SQLitessä ei ole tyyppiä TIMESTAMP, vaan ajanhetkiä käsitellään merkkijonoina, mutta tässä kuitenkin sarakkeen tyyppi ilmaisee, mitä siihen on tarkoitus tallentaa.

Lausekkeet

Lauseke on SQL-komennon osa, jolla on tietty arvo. Esimerkiksi kyselyssä

SELECT price FROM Products WHERE name = 'retiisi';

on neljä lauseketta: price, name, 'retiisi' ja name = 'retiisi'. Lausekkeet price ja name saavat arvonsa rivin sarakkeesta, lauseke 'retiisi' on merkkijonovakio ja lauseke name = 'retiisi' on totuusarvoinen.

Voimme rakentaa monimutkaisempia lausekkeita samaan tapaan kuin ohjelmoinnissa. Esimerkiksi kysely

SELECT price * 5 FROM Products;

antaa jokaisen tuotteen hinnan viisinkertaisena ja kysely

SELECT name FROM Products WHERE price % 2 = 0;

hakee tuotteet, joiden hinta on parillinen.

Hyvä tapa testata SQL:n lausekkeiden toimintaa on keskustella tietokannan kanssa tekemällä kyselyitä, jotka eivät hae tietoa mistään taulusta vaan laskevat vain tietyn lausekkeen arvon. Keskustelu voi näyttää vaikkapa seuraavalta:

sqlite> SELECT 2 * (1 + 3);
8
sqlite> SELECT 'tes' || 'ti';
testi
sqlite> SELECT 3 < 5;
1

Ensimmäinen kysely laskee lausekkeen 2 * (1 + 3) arvon. Toinen kysely yhdistää ||-operaattorilla merkkijonot 'tes' ja 'ti' merkkijonoksi 'testi'. Kolmas kysely puolestaan määrittää ehtolausekkeen 3 < 5 arvon. Tästä näkee, että SQLitessä kokonaisluku ilmaisee totuusarvon: 1 on tosi ja 0 on epätosi.

Monet SQL:n lausekkeisiin liittyvät asiat ovat tuttuja ohjelmoinnista:

laskutoimitukset: +, -, *, /, %
vertaileminen: =, <>, <, <=, >, >=
ehtojen yhdistys: AND, OR, NOT

Näiden lisäksi SQL:ssä on kuitenkin myös erikoisempia ominaisuuksia, joiden tuntemisesta on välillä hyötyä. Seuraavassa on joitakin niistä:

BETWEEN

Lauseke x BETWEEN a AND b on tosi, jos x on vähintään a ja enintään b. Esimerkiksi kysely

SELECT * FROM Products WHERE price BETWEEN 4 AND 6;

hakee tuotteet, joiden hinta on vähintään 4 ja korkeintaan 6. Voimme toki kirjoittaa samalla tavalla toimivan kyselyn myös näin:

SELECT * FROM Products WHERE price >= 4 AND price <= 6;

CASE

Rakenne CASE mahdollistaa ehtolausekkeen tekemisen. Siinä voi olla yksi tai useampi WHEN-osa sekä mahdollinen ELSE-osa. Esimerkiksi kysely

SELECT
  name,
  CASE WHEN price > 5 THEN 'kallis' ELSE 'halpa' END
FROM
  Products;

hakee kunkin tuotteen nimen sekä tiedon siitä, onko tuote kallis vai halpa. Tässä tuote on kallis, jos sen hinta on yli 5, ja muuten halpa.

Lauseke x IN (...) on tosi, jos x on jokin annetuista arvoista. Esimerkiksi kysely

SELECT
  SUM(price)
FROM
  Products
WHERE
  name IN ('lanttu', 'nauris', 'selleri');

hakee lantun, nauriin ja sellerin yhteishinnan.

LIKE

Lauseke s LIKE p on tosi, jos merkkijono s vastaa kuvausta p. Kuvauksessa voi käyttää erikoismerkkejä: merkki _ tarkoittaa mitä tahansa yksittäistä merkkiä ja merkki % tarkoittaa mitä tahansa määrää mitä tahansa merkkejä. Esimerkiksi kysely

SELECT * FROM Products WHERE name LIKE '%ri%';

hakee tuotteet, joiden nimen osana esiintyy merkkijono “ri” (kuten nauris ja selleri).

Funktiot

Lausekkeiden osana voi esiintyä funktioita samaan tapaan kuin ohjelmoinnissa. Tässä on esimerkkinä joitakin SQLiten funktioita:

ABS(x): luvun x itseisarvo
LENGTH(s): merkkijonon s pituus
LOWER(s): merkkijono s pienillä kirjaimilla
MAX(x, y): suurempi luvuista x ja y
MIN(x, y): pienempi luvuista x ja y
RANDOM(): satunnainen luku
ROUND(x, d): luku x pyöristettynä d desimaalin tarkkuudelle
SUBSTR(s, a, b): merkkijonon s kohdasta a alkaen b merkkiä
UPPER(s): merkkijono s suurilla kirjaimilla

Seuraava kysely hakee tuotteet, joiden nimessä on kuusi kirjainta (kuten lanttu ja nauris).

SELECT * FROM Products WHERE LENGTH(name) = 6;

Seuraava kysely ryhmittelee tuotteet ensimmäisen kirjaimen mukaan ja ilmoittaa kullakin kirjaimella alkavien tuotteiden määrät.

SELECT
  SUBSTR(name, 1, 1), COUNT(*)
FROM
  Products
GROUP BY
  SUBSTR(name, 1, 1);

Seuraava kysely antaa rivit satunnaisessa järjestyksessä, koska järjestys ei perustu minkään sarakkeen sisältöön vaan satunnaiseen arvoon.

SELECT * FROM Products ORDER BY RANDOM();

ORDER BY ja lausekkeet

Voisi kuvitella, että kyselyssä

SELECT * FROM Products ORDER BY 1;

rivit järjestetään lausekkeen 1 mukaan. Koska lausekkeen arvo on joka rivillä 1, tämä ei tuottaisi mitään erityistä järjestystä. Näin ei kuitenkaan ole, vaan 1 järjestää rivit ensimmäisen sarakkeen mukaan, 2 toisen sarakkeen mukaan, jne. Tämä on siis vaihtoehtoinen tapa ilmaista sarake, johon järjestys perustuu.

Kuitenkin jos ORDER BY -osassa oleva lauseke on jotain muuta kuin yksittäinen luku (kuten RANDOM()), rivit järjestetään kyseisen lausekkeen mukaisesti.

NULL-arvot

NULL on erityinen arvo, joka ilmaisee, että taulun sarakkeessa ei ole tietoa tai jokin kyselyn osa ei tuottanut tietoa. NULL on tietyissä tilanteissa kätevä, mutta voi aiheuttaa myös yllätyksiä.

Oletuksena SQLite-tulkki näyttää NULL-arvon tyhjänä:

sqlite> SELECT NULL;

Kuitenkin NULL-arvon saa näkymään tulkin komennolla .nullvalue:

sqlite> .nullvalue NULL
sqlite> SELECT NULL;
NULL

NULL on selkeästi eri asia kuin luku 0. Jos NULL esiintyy laskun osana, niin koko laskun tulokseksi tulee NULL.

sqlite> SELECT 5 + NULL;
NULL
sqlite> SELECT 2 * NULL + 1;
NULL

Myöskään tavallinen vertailu ei tuota tulosta, jos verrattavana on NULL:

sqlite> SELECT 5 = NULL;
NULL
sqlite> SELECT 5 <> NULL;
NULL

Tämä on yllättävää, koska yleensä lausekkeille a ja b pätee joko a = b tai a <> b. Voimme kuitenkin tutkia erityisen syntaksin IS NULL avulla, onko lausekkeen arvo NULL:

sqlite> SELECT 5 IS NULL;
0
sqlite> SELECT NULL IS NULL;
1

Sarakkeen puuttuva tieto

NULL-arvon yksi käyttötarkoitus on ilmaista, että jossain sarakkeessa ei ole tietoa. Esimerkiksi seuraavassa taulussa Movies Dumbon vuosi puuttuu, joten sen kohdalla on NULL:

id  name       release_year
--  ---------  ------------
 Lumikki    1937        
 Fantasia   1940        
 Pinocchio  1940        
 Dumbo      NULL        
 Bambi      1942        

Kun haemme ensin vuoden 1940 elokuvat ja sitten kaikki elokuvat muilta vuosilta, saamme seuraavat tulokset:

SELECT * FROM Movies WHERE release_year = 1940;

id  name       release_year
--  ---------  ------------
2   Fantasia   1940        
3   Pinocchio  1940        

SELECT * FROM Movies WHERE release_year <> 1940;

id  name     release_year
--  -------  ------------
1   Lumikki  1937        
5   Bambi    1942        

Koska Dumbolla ei ole vuotta, emme saa sitä kummassakaan kyselyssä, mikä on yllättävä ilmiö. Voimme kuitenkin hakea näin elokuvat, joilla ei ole vuotta:

SELECT * FROM Movies WHERE release_year IS NULL;

id  name   release_year
--  -----  ------------
4   Dumbo  NULL        

NULL-arvo koostefunktiossa

Kun koostefunktion sisällä on lauseke (kuten sarakkeen arvo), riviä ei lasketa mukaan, jos lausekkeen arvo on NULL. Tarkastellaan esimerkkinä seuraavaa taulua Employees:

id  name      company  salary
--  --------  -------  ------
 Anna      Google   8000  
 Liisa     Google   7500  
 Kaaleppi  Amazon   NULL  
 Uolevi    Amazon   NULL  
 Maija     Google   9500  

Taulussa Googlen työntekijöillä on ilmoitettu palkka, mutta Amazonin työntekijöillä ei. Koostefunktio COUNT(salary) laskee mukaan vain rivit, joissa palkka on ilmoitettu:

SELECT COUNT(salary) FROM Employees WHERE company = 'Google';

COUNT(salary)
-------------
3

SELECT COUNT(salary) FROM Employees WHERE company = 'Amazon';

COUNT(salary)
-------------
0

Kun sitten laskemme palkkojen summia koostefunktiolla SUM(salary), saamme seuraavat tulokset:

SELECT SUM(salary) FROM Employees WHERE company = 'Google';

SUM(salary)
-----------
25000      

SELECT SUM(salary) FROM Employees WHERE company = 'Amazon';

SUM(salary)
-----------
NULL

Tämä on vähän yllättävää, koska voisi myös odottaa tyhjän summan olevan 0 eikä NULL.

NULL-arvon muuttaminen

Funktio IFNULL(a, b) palauttaa arvon a, jos a ei ole NULL, ja muuten arvon b:

sqlite> SELECT IFNULL(5, 0);
IFNULL(5, 0)
------------
5          
sqlite> SELECT IFNULL(NULL, 0);
IFNULL(NULL, 0)
---------------
0

Yllä oleva tapa on tyypillinen tapa käyttää funktiota: kun toinen parametri on 0, niin funktio muuttaa mahdollisen NULL-arvon nollaksi. Tästä on hyötyä esimerkiksi LEFT JOIN -kyselyissä SUM-funktion kanssa.

Funktio IFNULL ei ole SQL-standardin mukainen funktio, eikä se toimi kaikissa tietokannoissa. Yleisempi standardin mukainen funktio on COALESCE(...), jolle annetaan lista arvoista. Funktio palauttaa listan ensimmäisen arvon, joka ei ole NULL, tai arvon NULL, jos jokainen arvo on NULL. Jos funktiolla on kaksi parametria, se toimii samoin kuin IFNULL.

sqlite> SELECT COALESCE(1, 2, 3);
COALESCE(1, 2, 3)
-----------------
1              
sqlite> SELECT COALESCE(NULL, 2, 3);
COALESCE(NULL, 2, 3)
--------------------
2                 
sqlite> SELECT COALESCE(NULL, NULL, 3);
COALESCE(NULL, NULL, 3)
-----------------------
3                    
sqlite> SELECT COALESCE(NULL, NULL, NULL);
COALESCE(NULL, NULL, NULL)
--------------------------
NULL

Alikyselyt

Alikysely on SQL-komennon osana oleva lauseke, jonka arvo syntyy jonkin kyselyn perusteella. Voimme rakentaa alikyselyjä samaan tapaan kuin varsinaisia kyselyjä ja toteuttaa niiden avulla hakuja, joita olisi vaikea saada aikaan muuten.

Esimerkki

Tarkastellaan esimerkkinä tilannetta, jossa tietokannassa on pelaajien tuloksia taulussa Results. Oletamme, että taulun sisältö on seuraava:

id  name      score
--  --------  -----
 Uolevi    120  
 Maija     80   
 Liisa     120  
 Aapeli    45   
 Kaaleppi  115  

Haluamme nyt selvittää ne pelaajat, jotka ovat saavuttaneet korkeimman tuloksen, eli kyselyn tulisi palauttaa Uolevi ja Liisa. Saamme tämän aikaan alikyselyllä seuraavasti:

SELECT
  name, score
FROM
  Results
WHERE
  score = (SELECT MAX(score) FROM Results);

Kyselyn tuloksena on:

name    score
------  -----
Uolevi  120  
Liisa   120  

Tässä kyselyssä alikysely on SELECT MAX(score) FROM Results, joka antaa suurimman taulussa olevan tuloksen eli tässä tapauksessa arvon 120. Huomaa, että alikysely tulee kirjoittaa sulkujen sisään, jotta se ei sekoitu pääkyselyyn.

Alikyselyn laatiminen

Alikysely voi esiintyä melkein missä tahansa kohtaa kyselyssä, ja se voi tilanteesta riippuen palauttaa yksittäisen arvon, listan arvoista tai kokonaisen taulun.

Alikysely sarakkeessa

Seuraavassa kyselyssä alikyselyn avulla luodaan kolmas sarake, joka näyttää pelaajan tuloksen eron ennätystulokseen:

SELECT
  name, score, (SELECT MAX(score) FROM Results) - score
FROM
  Results;

name      score  (SELECT MAX(score) FROM Results) - score
--------  -----  ----------------------------------------
Uolevi    120    0                                     
Maija     80     40                                    
Liisa     120    0                                     
Aapeli    45     75                                    
Kaaleppi  115    5                                     

Koska tulostaulun sarakkeen tuottava lauseke on monimutkainen, tulostaulua voi selkeyttää nimeämällä sarakkeen uudestaan:

SELECT
  name,
  score,
  (SELECT MAX(score) FROM Results) - score AS difference
FROM
  Results;

name      score  difference
--------  -----  ----------
Uolevi    120    0         
Maija     80     40        
Liisa     120    0         
Aapeli    45     75        
Kaaleppi  115    5         

Alikysely tauluna

Seuraavassa kyselyssä alikysely luo taulun, jossa on kolme parasta tulosta. Näiden tulosten summa (120 + 120 + 115) lasketaan pääkyselyssä.

SELECT
  SUM(score)
FROM
  (SELECT * FROM Results ORDER BY score DESC LIMIT 3);

SUM(score)
----------
355

Tässä LIMIT rajaa tulostaulua niin, että siinä on vain kolme ensimmäistä riviä.

Huomaa, että ilman alikyselyä saisimme väärän tuloksen:

SELECT SUM(score) FROM Results ORDER BY score DESC LIMIT 3;

SUM(score)
----------
480     

Tässä tulostaulussa on vain yksi rivi, jossa on kaikkien tulosten summa (480). Niinpä kyselyn lopussa oleva LIMIT 3 ei vaikuta mitenkään tulokseen.

Alikysely listana

Seuraava kysely hakee pelaajat, joiden tulos kuuluu kolmen parhaimman joukkoon. Alikysely palauttaa listan tuloksista IN-lauseketta varten.

SELECT
  name
FROM
  Results
WHERE
  score IN (SELECT score FROM Results ORDER BY score DESC LIMIT 3);

name
----------
Uolevi    
Liisa     
Kaaleppi  

Riippuva alikysely

Alikysely on mahdollista toteuttaa myös niin, että sen toiminta riippuu pääkyselyssä käsiteltävästä rivistä. Näin on seuraavassa kyselyssä:

SELECT
  name,
  score,
  (SELECT COUNT(*) FROM Results WHERE score > R.score) AS better_count
FROM
  Results R;

Tämä kysely laskee jokaiselle pelaajalle, monenko pelaajan tulos on parempi kuin pelaajan oma tulos. Esimerkiksi Maijalle vastaus on 3, koska Uolevin, Liisan ja Kaalepin tulos on parempi. Kysely antaa seuraavan tuloksen:

name      score  better_count
--------  -----  ------------
Uolevi    120    0           
Maija     80     3           
Liisa     120    0           
Aapeli    45     4           
Kaaleppi  115    2           

Koska taulu Results esiintyy kahdessa roolissa alikyselyssä, pääkyselyn taululle on annettu nimi R. Tämän ansiosta alikyselyssä on selvää, että halutaan laskea rivejä, joiden tulos on parempi kuin pääkyselyssä käsiteltävän rivin tulos.

Tässä on vielä toinen esimerkki riippuvasta alikyselystä:

SELECT
  name
FROM
  Results R
WHERE
  (SELECT COUNT(*) FROM Results WHERE score < R.score) >= 1;

Tämä kysely etsii pelaajat, joilla on parempi tulos kuin jollakin toisella pelaajalla. Tässä alikysely laskee, monellako pelaajalla on huonompi tulos, ja kyselyn ehtona on, että alikyselyn tulos on vähintään yksi. Kyselyn tuloksena on:

name
----------
Uolevi    
Maija     
Liisa     
Kaaleppi  

Tässä tapauksessa kysely palauttaa kaikki pelaajat paitsi Aapelin, jonka tulos on huonoin.

SQL:ssä on myös avainsana EXISTS, joka ilmaisee, palauttaako alikysely vähintään yhden rivin. Tämän avulla äskeinen kysely voidaan kirjoittaa selkeämmin:

SELECT
  name
FROM
  Results R
WHERE
  EXISTS (SELECT * FROM Results WHERE score < R.score);

Milloin käyttää alikyselyä?

Melko usein alikysely on vaihtoehtoinen tapa toteuttaa kysely, jonka voisi tehdä jotenkin muutenkin. Esimerkiksi molemmat seuraavat kyselyt hakevat tuotteiden nimet asiakkaan 1 ostoskorissa:

SELECT
  Products.name
FROM
  Products, Purchases
WHERE
  Products.id = Purchases.product_id AND Purchases.customer_id = 1;

SELECT
  name
FROM
  Products
WHERE
  id IN (SELECT product_id FROM Purchases WHERE customer_id = 1);

Ensimmäinen kysely on tyypillinen kahden taulun kysely, kun taas toinen kysely valikoi tuotteet alikyselyn avulla. Kumpi kysely on parempi?

Ensimmäinen kysely on parempi, koska tämä on tarkoitettu tapa hakea SQL:ssä tietoa tauluista viittausten avulla. Toinen kysely toimii sinänsä, mutta se poikkeaa totutusta eikä tietokantajärjestelmä myöskään pysty ehkä suorittamaan sitä yhtä tehokkaasti.

Alikyselyä kannattaa käyttää vain silloin, kun siihen on todellinen syy. Jos kyselyn voi tehdä helposti usean taulun kyselyllä, tämä on yleensä parempi ratkaisu.

Lisää tekniikoita

Tässä osiossa on lisää näytteitä SQL:n mahdollisuuksista. Näistä tekniikoista on hyötyä joidenkin SQL Trainerin vaikeiden tehtävien ratkaisemisessa.

Kumulatiivinen summa

Hyödyllinen taito SQL:ssä on osata laskea kumulatiivinen summa eli jokaiselle riville summa sarakkeen luvuista kyseiselle riville asti. Tarkastellaan esimerkiksi seuraavaa taulua Items:

id  value
--  -----
1   200  
2   100  
3   400  
4   100  

Voimme laskea kumulatiivisen summan kahden taulun kyselyllä näin:

SELECT
  A.id, SUM(B.value)
FROM
  Items A, Items B
WHERE
  B.id <= A.id
GROUP BY
  A.id;

id  SUM(B.value)
--  ------------
1   200         
2   300         
3   700         
4   800         

Tässä on ideana, että summa lasketaan taulun A riville ja taulusta B haetaan kaikki rivit, joiden id on pienempi tai sama kuin taulun A rivillä. Halutut summat saadaan laskettua SUM-funktiolla ryhmittelyn jälkeen.

Vastaavaa tekniikkaa voi käyttää muissakin tilanteissa, jos haluamme laskea tuloksen, joka riippuu jotenkin kaikista “pienemmistä” riveistä taulussa.

Sisäkkäiset koosteet

Tarkastellaan tilannetta, jossa haluamme selvittää, mikä on suurin määrä elokuvia, jotka ovat ilmestyneet samana vuonna. Esimerkiksi seuraavassa taulussa Movies haluttu tulos on 2, koska vuonna 1940 ilmestyi kaksi elokuvaa.

id  name       release_year
--  ---------  ------------
 Lumikki    1937        
 Fantasia   1940        
 Pinocchio  1940        
 Dumbo      1941        
 Bambi      1942        

Tämä on vähän hankalalta vaikuttava tilanne, koska meidän tulisi tehdä sisäkkäin kyselyt COUNT, joka laskee ilmestymismääriä, ja sitten MAX, joka hakee suurimman arvon. SQL ei salli kuitenkaan kyselyä SELECT MAX(COUNT(release_year)) tai vastaavaa.

Voimme ottaa kuitenkin lähtökohdaksi kyselyn, joka ryhmittelee elokuvat vuoden mukaan ja hakee jokaisesta ryhmästä elokuvien määrän:

SELECT COUNT(*) FROM Movies GROUP BY release_year;

COUNT(*)  
--------
1         
2         
1         
1       

Näistä luvuista pitää vielä saada haettua suurin, mikä onnistuu alikyselyn avulla. Tässä tapauksessa kätevä tapa on käyttää alikyselyä niin, että sen tulos on pääkyselyn FROM-osassa, jolloin alikysely luo taulun, josta pääkysely hakee tietoa:

SELECT MAX(year_count) FROM (
  SELECT COUNT(*) year_count FROM Movies GROUP BY release_year
);

MAX(year_count)
---------------
2       

Entä voisiko tehtävän ratkaista ilman alikyselyä? Kyllä, koska voimme järjestää tulokset suurimmasta pienimpään ja valita tulostaulun ensimmäisen rivin:

SELECT COUNT(*) AS year_count FROM Movies GROUP BY release_year
ORDER BY year_count DESC LIMIT 1;

year_count
----------
2          

Sijaluvut

Tarkastellaan vielä taulua Results, jossa on pelaajia ja heidän tuloksiaan:

id  name      score
--  --------  -----
 Aapeli    45   
 Kaaleppi  115  
 Liisa     120  
 Maija     80   
 Uolevi    120  

Tavoitteena on hakea rivit järjestyksessä tuloksen mukaan suurimmasta pienempään ja ilmoittaa lisäksi kunkin rivin sijaluku. Yksi tapa toteuttaa tämä on tehdä alikysely, joka laskee, monellako rivillä tulos on parempi, jolloin sija on yhtä suurempi kuin alikyselyn tulos:

SELECT
  (SELECT COUNT(*) FROM Results WHERE score > R.score) + 1 AS place,
  name, score
FROM
  Results R
ORDER BY
  score DESC, name;

place  name      score
-----  --------  -----
    Liisa     120  
    Uolevi    120  
    Kaaleppi  115  
    Maija     80   
    Aapeli    45   

Samalla idealla voidaan laskea sijaluvut myös niin, että jokaisella on eri sija ja yhtä suuren tuloksen tapauksessa aakkosjärjestys ratkaisee sijan:

SELECT
  (SELECT COUNT(*) FROM Results WHERE score > R.score OR
    (score = T.score AND name < T.name)) + 1 AS place,
  name, score
FROM
  Results R
ORDER BY
  score DESC, name;

place  name      score
-----  --------  -----
    Liisa     120  
    Uolevi    120  
    Kaaleppi  115  
    Maija     80   
    Aapeli    45   

Vaihtoehtoinen tapa laskea sijalukuja on ikkunafunktio, jos käytetty tietokanta sallii sen. Esimerkiksi SQLiten uusissa versioissa ikkunafunktion RANK avulla voidaan laskea vastaavat sijaluvut kuin äskeisissä esimerkeissä.

SELECT
  RANK() OVER (ORDER BY score DESC) place, name, score
FROM
  Results
ORDER BY
  place, name;

place  name      score
-----  --------  -----
    Liisa     120  
    Uolevi    120  
    Kaaleppi  115  
    Maija     80   
    Aapeli    45   

SELECT
  RANK() OVER (ORDER BY score DESC, name) place, name, score
FROM
  Results
ORDER BY
  place, name;

place  name      score
-----  --------  -----
    Liisa     120  
    Uolevi    120  
    Kaaleppi  115  
    Maija     80   
    Aapeli    45   

Listojen vertailu

Tarkastellaan taulua Lists, johon on tallennettu listojen sisältö. Esimerkiksi seuraavassa taulussa lista 1 sisältää luvut [2, 4, 5], lista 2 sisältää luvut [3, 5] ja lista 3 sisältää luvut [2, 4, 5]:

id  list_id  value
--  -------  -----
 1        2    
 1        4    
 1        5    
 2        3    
 2        5    
 3        2    
 3        4    
 3        5    

Seuraava kysely laskee jokaiselle listaparille, montako yhteistä lukua niillä on:

SELECT
  A.list_id, B.list_id, COUNT(*)
FROM
  Lists A, Lists B
WHERE
  A.value = B.value
GROUP BY
  A.list_id, B.list_id;

list_id  list_id  COUNT(*)
-------  -------  --------
      1        3       
      2        1       
      3        3       
      1        1       
      2        2       
      3        1       
      1        3       
      2        1       
      3        3       

Tästä selviää, että esimerkiksi listoilla 1 ja 2 on yksi yhteinen luku (5) ja listoilla 1 ja 3 on kolme yhteistä lukua (2, 4, 5). Tällaista kyselyä laajentamalla voidaan vaikkapa vertailla, onko kahdella listalla täysin sama sisältö. Näin on silloin, kun listoilla on yhtä monta lukua ja yhteisten lukujen määrä on yhtä suuri kuin yksittäisen listan lukujen määrä.

Tietokantojen perusteet

syksy 2025