Transcript Dias nummer 1 - This is not the page you are looking for

8. SQL-99 -kyselykieli: kaavan määrittely,
perusrajoitukset ja kyselyt
•
Standardoidun SQL:n käyttöönotto oli tärkeä syy relaatiotietokantojen
yleistymiselle.
•
SQL (=Structured Query Language) toimii sekä datan määrittely- (DDL)
että varsinaisena kyselykielenä (DML).
•
SQL on verrattain yksinkertainen korkean tason kieli, jolla kuvataan
operaatiot, joita tietokannalle halutaan suorittaa.
•
SQL on luonteeltaan deklaratiivinen; s. o. kyselyjä rakennettaessa on
tärkeää ainoastaan halutun tavoitteen esittäminen. Tavoitteen
saavuttamiseksi suoritettavien operaatioiden suoritusjärjestyksellä ei ole
sellaista painoarvoa kuin relaatioalgebrassa.
•
Kuitenkin relaatioalgebran ymmärtäminen helpottaa melkoisesti
virheettömien SQL-kyselyjen tuottamista.
•
SQL-99 -standardi jakautuu ns. ytimeen, joka kuuluu jokaiseen SQLympäristöön sekä lisäominaisuuksia sisältäviin täydennyspaketteihin
( mm. tiedonlouhintaa, tietovarastointia, multimediaa yms. varten ).
8.1 Datan määrittely, rajoitukset ja tietokannan
kaavan muutokset SQL-99:ssä
•
Relaatioalgebran termejä relaatio, tupla ja attribuutti vastaavat SQL:ssä
taulu, rivi ja sarake.
8.1.1 Kaavan ja hakemiston käsitteet SQL-99:ssä
•
Tietokannan kaava ( = schema ) sisältää tietokannan nimen sekä
tiedon sen omistajasta.
•
Lisäksi kaava pitää sisällään kaikki tietokantaan kuuluvat taulut,
rajoitukset, näkymät, määrittelyjoukot ja muut rajoitukset ( esimerkiksi
käyttöoikeudet ).
•
Uusi tietokannan kaava perustetaan SQL:ssä lauseella CREATE SCHEMA.
•
Esimerkki: Perustetaan esimerkeissä käyttämämme yrityksen tietokanta, ja
annetaan sen omistajan tunnukseksi JSMITH:
•
CREATE SCHEMA COMPANY AUTHORIZATION JSMITH
•
Usea tietokannan kaava voidaan koota yhden hakemiston alle.
•
Hakemisto pitää sisällään ns. informaatiokaavan ( = information schema ),
joka sisältää koostetietoa kaikista hakemistoon kuuluvista tietokannan
kaavoista, kaavojen mukaisten tietokantojen käyttöoikeuksista, yhteisistä
määrityksistä hakemiston sisällä jne.
8.1.2 Taulun perustaminen SQL-kielellä
•
Uusi taulu perustetaan komennolla CREATE TABLE, jota seuraavat taulun
nimi, attribuutit, niihin liittyvät arvoalueet ja rajoitukset.
•
Taulua perustettaessa voidaan tarpeen mukaan mainita myös tietokannan
nimi, johon se liittyy, ellei se ole ilmeistä ympäristössä, jossa taulua ollaan
perustamassa. Esimerkki:
• CREATE TABLE COMPANY.EMPLOYEE ( eksplisiittinen tietokannan
nimeäminen taulua perustettaessa )
• CREATE TABLE EMPLOYEE ( käsiteltäessä paraikaa tietokantaa
COMPANY: implisiittinen nimeäminen )
•
Taulun nimeä seuraa sulkumerkkien sisällä oleva lista taulun attribuuteista.
Jokaisesta attribuutista on lueteltu arvojoukko sekä mahdolliset määräykset
sen arvoista.
•
Tarkastellaan kirjan esimerkkiä 8.1.
8.1.3 Attribuuttien datatyypit ja määrittelyjoukot SQL:ssä
•
Tarjolla olevat tietotyypit ovat numeerinen, merkki- / merkkijonotyyppi,
bittijono, päivämäärä ja kellonaika.
•
Numeeriseen tietotyyppiin sisältyvät eri mittaiset kokonaisluvut (lyhyet,
normaalit ja pitkät) sekä reaaliluvut (normaalit ja kaksinkertaisen
tarkkuuden).
•
Desimaalilukuja varten voidaan määrätä kokonais- ja desimaaliosan
pituudet (esimerkiksi DECIMAL( i, j ) ).
•
Merkkijonot voivat olla kiinteän ( CHAR( n ) ) tai vaihtelevan mittaisia
( VARCHAR( n ) ). Vaihtelevan mittaisilla merkkijonoilla n osoittaa
merkkijonon maksimipituuden.
•
Päivämäärä esitetään muodossa VVVV-KK-PP.
•
Aika esitetään joko sellaisenaan TT:MM:SS tai tarkennettuna
aikavyöhykkeellä, esimerkiksi +03:00.
•
Aikakriittisissä järjestelmissä voidaan hyödyntää myös sekunnin osia,
jolloin voidaan käyttää ns. aikaleimatyyppiä ( = TIMESTAMP ).
•
Lisäksi on käytettävissä tyyppi INTERVAL, jota voidaan käyttää
päiväyksen, kellonajan tai aikaleiman arvon kasvattamiseen tai
pienentämiseen yhdellä yksiköllä.
•
Usein tarvittavat merkkijonotyypit voidaan nimetä omiksi tyypeikseen
käyttämällä hyväksi lausetta CREATE DOMAIN.
•
Esimerkki: CREATE DOMAIN SSN_TYPE AS CHAR(9) luo tyypin nimeltä
SSN_TYPE, joka tarkoittaa kiinteästi yhdeksän mittaista merkkijonoa.
8.2 Perusrajoitusten määrittely SQL:ssä
•
Seuraavassa on tarkoituksena tarkastella ns. perusrajoitusten esittelyä
SQL-kielellä. Perusrajoituksiin luetaan kuuluviksi avain- ja viiteeheysrajoitukset, attribuuttien arvoalueiden määrittely, NULL-arvojen
laillisuus sekä rajoitukset yksittäisille tuplille relaation sisällä.
8.2.1 Attribuuteille asetettavat rajoitukset ja oletusarvot
•
Attribuuteille voidaan asettaa taulun perustamisen yhteydessä
rajoituksia, jotka koskevat sen saamia arvoja.
o Puuttuvien arvojen torjuminen ( NOT NULL ).
 Välttämätöntä, mikäli kyseessä on pääavainattribuutti, mutta voidaan
asettaa tarpeen mukaan myös muille attribuuteille.
o Oletusarvon asettaminen, jos syötetään tyhjä arvo ( DEFAULT ).
 Voidaan asettaa attribuutille oletusarvo, mikäli se jätetään
syöttöhetkellä tyhjäksi. Oletusarvon oletusarvona on NULL, mikäli
attribuutin NULL-arvojen esiintymistä ei ole estetty.
o Voidaan lisäksi asettaa rajoitettu perustyypin arvoalue attribuutin
arvoalueeksi CHECK-lauseella
Esimerkki: Vaaditaan osastonumeron olevan olemassa, ja sen arvon
pitää olla jotain väliltä 1-20:
DNUMBER NOT NULL CHECK ( DNUMBER > 0 AND DNUMBER < 20 )
o CHECK-lausetta olisi voitu yhtäläisesti käyttää CREATE DOMAIN -lauseen
yhteydessä vaikkapa seuraavasti:
o CREATE DOMAIN D_NUM AS INTEGER CHECK
( DNUM > 0 AND DNUM < 21 )
8.2.2 Avainten ja vierasavainten eheysrajoitukset
•
Avainattribuutit luetellaan taulun attribuuttilistan viimeisen attribuutin
jälkeen uudelleen. Seuraavat avaimiin liittyvät rajoitukset voidaan asettaa
attribuuteille:
o Attribuutin asettaminen pääavaimeksi ( PRIMARY KEY ).
o Attribuutin asettaminen vaihtoehtoisavaimeksi ( UNIQUE ).
o Attribuutin määritteleminen vierasavaimeksi ( FOREIGN KEY ).
•
Esimerkki: perustetaan taulu TOIMIPISTEET, jossa on kaksi kenttää osastonumero ja sijainti, jotka kumpikin kuuluvat pääavaimeen:
CREATE TABLE TOIMIPISTEET
( OSASTONUMERO INT NOT NULL,
SIJAINTI VARCHAR( 15 ) NOT NULL,
PRIMARY KEY ( OSASTONUMERO, SIJAINTI )
FOREIGN KEY ( OSASTONUMERO ) REFERENCES OSASTO ( OS_NUM ) )
•
Tarkastellaan kirjan esimerkkiä 8.2.
•
Jos pääavain on taulun yksinkertainen attribuutti, se voidaan mainita jo
taulun attribuutteja esiteltäessä kuten seuraavassa:
DNUMBER INT PRIMARY KEY
•
Attribuuteille voidaan määritellä toimenpiteet, mitä tehdään silloin, kun
siihen viittaava tupla poistetaan tai viittauksen kohteena oleva arvo
muuttuu. Toimenpiteet voivat olla erilaiset eri operaatioissa. Oletuksena
on, että operaatio peruutetaan, mikäli avain- tai viite-eheyttä yritetään
rikkoa.
o SET NULL: asetus puuttuvaksi.
o CASCADE: toimenpiteen vyörytys kaikkialle, missä muutoksen
kohteena olevaan tuplaan viitataan.
o SET DEFAULT: asetetaan oletusarvo, mikäli viittauksen kohteena
oleva tupla poistetaan.
•
Vyörytysoption ( CASCADE ) soveltaminen on tyypillistä tauluille, jotka
edustavat M:N-liittymää.
Esimerkki: Kun projekti lakkautetaan, ei siihen enää voida tehdä
myöskään mitään työsuorituksia.
8.2.3 Rajoitusten nimeäminen
•
Rajoitukset voidaan nimetä käyttämällä varattua sanaa CONSTRAINT ja
kirjoittamalla tämän perään rajoitukselle tunnus ja sen merkitys. Tällöin
voitaisiin myöhemmin viitata rajoitukseen annetun tunnuksen avulla
muutettaessa taulun määrittelyä ALTER TABLE –komennolla ( tästä
tarkemmin kappaleessa 8.3.2 ).
Esimerkki: Taulua EMPLOYEE perustettaessa määritellään:
CONSTRAINT EMPPK PRIMARY KEY ( SSN )
Nyt tunnus EMPPK viittaa rajoitukseen, jonka mukaan SSN on taulun
EMPLOYEE pääavain.
•
Myös edellisessä kappaleessa esitettyjä toimenpiteitä viite-eheyden
säilyttämiseksi voidaan sisällyttää nimettyihin rajoituksiin
•
Tarkastellaan uudelleen kirjan esimerkkiä 8.2.
8.2.4 Rajoitusten asettaminen tuplille CHECK-lauseen avulla
•
Tuplille voidaan asettaa muitakin rajoituksia kuin avain- ja viite-eheyteen
liittyviä käyttämällä hyväksi CHECK-lausetta.
•
CHECK-lauseen avulla voidaan testata muun muassa, onko tuplan tietyn
attribuutin arvo hyväksyttävä suhteessa jonkin toisen attribuutin arvoon.
•
Esimerkki: Oletetaan, että tauluun OSASTO lisättäisiin osaston
perustamispäivää kuvaava attribuutti DEPT_CREATE_DATE.
Nyt voitaisiin varmistua osaston johtajan aloituspäivämäärän
laillisuutta seuraavalla rajoituksella:
CHECK ( DEPT_CREATE_DATE < MGRSTARTDATE )
Tällä tavoin estettäisiin ilmeisen virheellisen päivämäärän
asettaminen johtajan aloituspäiväksi.
•
CHECK-lausetta voidaan käyttää myös luonteeltaan tässä luvussa
esitettäviä yleisempien rajoitusten esittämiseksi. Tästä tarkemmin
kappaleessa 9.1.
8.3 Tietokannan kaavan muutoslauseet SQL:ssä
•
Seuraavassa esitellään komennot, joiden avulla pystytään tekemään
päivityksiä tietokannan jo olemassa olevaan kaavaan. Tämä voi tapahtua
lisäämällä tai poistamalla tauluja, attribuutteja, rajoituksia tai muita kaavan
rakenneosia.
8.3.1. Kaavan ja taulun poistaminen
•
Komennolla DROP SCHEMA voidaan poistaa koko tietokanta.
•
Vastaavasti komennolla DROP TABLE voidaan poistaa yksittäinen taulu.
•
Kumpaankin operaatioon voidaan kohdistaa rajoitus CASCADE tai
RESTRICT.
•
DROP SCHEMAn yhteydessä CASCADE tuhoaa tietokannan kaiken
sisällön. RESTRICT puolestaan estää tuhoamisen, jos tietokannan taulut
eivät ole tyhjiä.
•
DROP TABLEn yhteydessä annettu CASCADE-optio tuhoaa annetun
taulun kaikkine tietoineen ja siihen liittyvine viittauksineen. RESTRICToptio estää taulun poistamisen, mikäli se ei ole tyhjä, tai siihen viitataan
vielä toisaalla.
8.3.2 Komento ALTER
•
ALTER TABLE -komennolla voi muuttaa aikaisemmin tehtyjä määrittelyjä
taululle. Tällä komennolla voidaan lisätä / poistaa attribuutteja, asettaa /
kumota rajoituksia ja oletusarvoja jne. Komento on muotoa
ALTER TABLE taulu MUUTOS KOHDE <OPTIOT>;
•
Mahdolliset muutokset:
o Attribuutin lisääminen tauluun: ADD
Esimerkki: ALTER TABLE COMPANY.EMPLOYEE
ADD JOB VARCHAR( 12 );
* lisää tietokannan COMPANY tauluun EMPLOYEE kentän JOB, joka
on maksimissaan 12-merkkinen vaihtelevan mittainen
merkkijonoattribuutti.
o Attribuutin poistaminen taulusta: DROP;
Esimerkki: ALTER TABLE COMPANY.EMPLOYEE
DROP ADDRESS CASCADE;
* poistaa taulusta EMPLOYEE sarakkeen ADDRESS sekä kaikki siihen
kohdistuvat viittaukset muista tietokannan tauluista. Optio
RESTRICT olisi estänyt operaation, mikäli viittauksia kenttään olisi
ollut toisaalla.
o
Attribuutin ominaisuuksien muuttaminen: ALTER
Esimerkki: ALTER TABLE COMPANY.DEPARTMENT
ALTER MGRSSN DROP DEFAULT;
* poistaa taulun DEPARTMENT sarakkeelta MGRSSN sille annetun
oletusarvon.
Esimerkki: ALTER TABLE COMPANY.DEPARTMENT
ALTER MGRSSN SET DEFAULT "333445555";
* asettaa osaston johtajalle oletusarvoksi Franklin Wongin
henkilötunnuksen.
Esimerkki: ALTER TABLE COMPANY.EMPLOYEE
DROP CONSTRAINT EMPSUPERFK CASCADE;
* lakkauttaa rajoituksen EMPSUPERFK voimassaolon taulusta
EMPLOYEE.
8.4 SQL:n peruskyselyt
•
SELECT-lauseessa ilmoitetaan, mitkä kentät halutaan mukaan
tulostauluun.
•
Kannattaa huomioida, että SQL:n SELECT lause ei vastaa relaatioalgebran
valintaoperaattorin  merkitystä, vaan muistuttaa pikemminkin
projektiota!
•
Lisäksi, SQL sallii duplikaattien esiintymisen tulostauluissa toisin kuin
relaatioalgebra. Siten SQL:llä tuotetut taulut ovat käsitteellisesti joukkojen
sijaan monijoukkoja (= multiset ).
•
Duplikaatit saadaan poistettua valitsimella DISTINCT.
8.4.1 SELECT-FROM-WHERE -lause
•
SELECT-lauseen rakenne on muotoa
SELECT <attribuuttilista>
FROM <taululista>
WHERE <ehdot>;
missä <attribuuttilista> sisältää ne attribuutit, joiden halutaan näkyvän
kyselyn vastauksessa, <taululista> sisältää ne taulut, joita kyselyn
toteuttamiseksi tarvitaan riippumatta siitä, esiintyykö taulujen kenttiä
vastauksessa vai ei, ja <ehdot> kuvaavat kriteerit, jotka täyttävät tuplat
hyväksytään vastaukseen ( valinta- ja liitosehdot ).
•
Attribuutti- ja taululistan jäsenet erotetaan toisistaan pilkuin, ehdot
puolestaan loogisin operaattorein.
•
Attribuuttilistan attribuuttien järjestys määrää tulostaulun attribuuttien
järjestyksen.
•
Esimerkkejä: Esimerkkien numerointi QX viittaa kirjaan
1) Haetaan syntymäaika ja osoite kaikilta työntekijöiltä, joiden nimi on
'John B. Smith'. Kaikki tarvittavat tiedot löytyvät yhdestä taulusta.
Q0: SELECT BDATE, ADDRESS
FROM EMPLOYEE
WHERE FNAME='John' AND MINIT='B' AND LNAME='Smith';
2) Haetaan etu- ja sukunimi sekä osoite kaikilta työntekijöiltä, jotka
työskentelevät tutkimusosastolla. Vaikka kaikki tuloskentät ovat
taulussa EMPLOYEE, tarvitaan avuksi taulua DEPARTMENT, jotta
päästäisiin käsiksi tarkalleen tutkimusosaston työntekijöihin.
Tehdään liitos osaston ja työntekijän välille käyttämällä
liitosattribuuttina osastonumeroa.
Q1: SELECT FNAME, LNAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='Research' AND DNUMBER=DNO;
3)
Etsitään jokaisen Staffordissa toimivan projektin projektinumero,
projektia kontrolloivan osaston numero, sekä osaston johtajan sukunimi,
osoite ja syntymäaika. Nyt tarvitaan avuksi liitos sekä projektin
ja osaston että osaston ja työntekijän välille.
Q2: SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN AND
PLOCATION='Stafford';
8.4.2 Saman niminen sarake useassa kyselyyn osallistuvassa taulussa,
uudelleennimeäminen ( aliasointi ) ja tuplamuuttujat
•
Mikäli saman niminen attribuutti esiintyy useassa kyselyyn osallistuvassa
taulussa, pitää ne erotella toisistaan joko esittelemällä attribuutin nimen
yhteydessä myös viittauksen kohteena olevan taulun nimi tai käyttämällä
hyväksi uudelleennimeämistä.
•
Esimerkki: Kuvitellaan, että taulun EMPLOYEE sukunimeä ja taulun
DEPARTMENT osaston nimeä edustaisikin tunnus NAME
tunnuksien LNAME ja DNAME sijaan, ja attribuutti DNO olisikin
nimetty DNUMBERiksi.
Tällöin kysely 2 eli Q1 ei olisikaan enää tulkittavissa yksikäsitteisesti ilman
taulujen esittelyä, sillä ei tiedettäisi, tarkoitetaanko tunnuksilla NAME ja
DNUMBER taulun EMPLOYEE vai DEPARTMENT kenttiä.
•
Seuraava versio kyselystä 2 ( eli kysely 4 ) ( = Q1A ) korjaa tilanteen
kuvittelemiemme kaltaisilla attribuuttien nimillä:
Q1A: SELECT FNAME, EMPLOYEE.NAME, ADDRESS
FROM EMPLOYEE, DEPARTMENT
WHERE DEPARTMENT.NAME = 'Research' AND
DEPARTMENT.DNUMBER = EMPLOYEE.DNUMBER;
•
Kenttien nimien yksikäsitteisyys häviää myös silloin, kun tehdään liitos
taulun itsensä kanssa. Tällöin voidaan ottaa käyttöön ns. tuplamuuttuja
( = tuple variable ), jonka avulla saadaan taulun nimet erilaisiksi liitoksen
eri rooleissa. Uudelleennimeämisessä eli aliasoinnissa voidaan haluttaessa
käyttää apuna varattua sanaa AS, mutta sen voi myös jättää pois.
•
Seuraavassa esimerkissä 5) halutaan listata kaikkien työntekijöiden etu- ja
sukunimi sekä heidän välittömän esimiehensä etu- ja sukunimi.
Q8: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.SUPERSSN = S.SSN;
•
Haluttaessa voidaan taulujen uudelleennimeämisen yhteydessä nimetä
myös taulun sarakkeet uudelleen FROM-lauseessa.
Esimerkki: Nimetään EMPLOYEE-taululle alias E, jonka kentät on nimetty
uudelleen seuraavasti:
SELECT ...
FROM EMPLOYEE AS E(FN, MI, LN, SSN, BD, ADDR, SEX, SAL,
SSSN, DNO)
WHERE ...
•
Taulujen uudelleennimeäminen voidaan tulkita kopioiden ottamiseksi
alkuperäisestä taulusta. Esimerkiksi FROM-lauseessa annetut EMPLOYEE E
ja EMPLOYEE S tekevät sekä E:stä että S:stä taulun EMPLOYEE edustajia.
•
Lyhyiden aliasnimien käyttäminen lyhentää kätevästi SQL-komentoja.
6) Toteutetaan uudelleen kysely 4) käyttämällä hyväksi aliasnimiä
työntekijä- ja osastotauluille:
Q1B: SELECT E.FNAME, E.NAME, E.ADDRESS
FROM EMPLOYEE E, DEPARTMENT D
WHERE D.NAME = 'Research' AND
D.DNUMBER = E.DNUMBER
•
Edellisessä kyselyssä E ja D ovat tuplamuuttujan asemassa. Mikäli WHERElauseen jokaista taulua kohti perustetaan tuplamuuttuja, kysely muistuttaa
vastaavaa tupla-relaatiokalkyylin kyselyä.
Q1: { e.FNAME, e.LNAME, e.ADDRESS | EMPLOYEE ( e ) AND
(  d ) | ( DEPARTMENT ( d ) AND d.NAME = 'Research' AND
d.DNUMBER = e.DNO ) }
8.4.3 Puuttuva WHERE-lause ja tähtisymbolin käyttö
•
Mikäli kyselyssä ei haluta asettaa käsiteltävien taulujen tuplille mitään
valinta- eikä liitosehtoa, WHERE-lausetta ei kirjoiteta.
•
Jos kysely, josta WHERE-lause puuttuu, kohdistuu vain yhteen tauluun,
tulostetaan SELECT-lauseessa mainitut attribuutit taulun kaikista
tuplista.
•
Jos tällainen kysely kohdistuu useaan tauluun, muodostetaan taulujen
välinen karteesinen tulo projisoituna SELECT-lauseen mukaisille
attribuuteille.
7) Listataan jokaisen työntekijän sosiaaliturvatunnus.
Q9: SELECT SSN
FROM EMPLOYEE;
8) Listataan kaikki sosiaaliturvatunnusten ja osastojen nimien yhdistelmät,
myös mahdolliset duplikaatit. Tulokseksi saadaan taulujen EMPLOYEE ja
DEPARTMENT karteesinen tulo, joka sisältää SELECT-osassa luetellut
attribuutit ( sinänsä varsin hyödytön kysely ).
Q10: SELECT SSN, DNAME
FROM EMPLOYEE, DEPARTMENT;
•
Mikäli jostain taulusta halutaan listata kaikki attribuutit, voidaan tämän
pyynnön esittämiseksi käyttää symbolia * sen sijaan, että kaikki
attribuutit jouduttaisiin luettelemaan.
9) Listataan kaikki tiedot työntekijöistä, jotka ovat töissä osastolla 5.
Q1C: SELECT *
FROM EMPLOYEE
WHERE DNO = 5;
10) Listataan tutkimusosaston työntekijöistä kaikki työntekijää koskevat
tiedot ja niiden perään kaikki osaston tiedot.
Q1D: SELECT *
FROM EMPLOYEE, DEPARTMENT
WHERE DNAME = 'Research' AND DNO = DNUMBER;
11) Listataan työntekijöiden ja osastojen kaikki attribuutit sisältävä
karteesinen tulo.
Q10A: SELECT *
FROM EMPLOYEE, DEPARTMENT;
8.4.4 Taulujen käsittely joukkoina SQL:ssä
•
Toisin kuin relaatioalgebra, SQL tulkitsee taulun tuplat oletuksen mukaisesti
joukon sijasta monijoukoksi, s. o. duplikaatteja saa esiintyä. Näin toimitaan,
sillä
o Duplikaattien poistaminen vaatii tuplien lajittelun, mikä hidastuttaa
vasteaikoja.
o Duplikaattien tulostaminen saattaa olla käyttäjälle kaikesta huolimatta
hyödyllistä.
o Aggregaattifunktioita sovellettaessa duplikaatit joudutaan yleensä
kuitenkin huomioimaan.
•
Duplikaattien listaaminen voidaan estää antamalla SELECT-lauseeseen
optio DISTINCT.
•
Duplikaatit hyväksyvä optio ALL on puolestaan oletuksena, joten se
voidaan jättää pois haluttaessa duplikaatit näkyviin.
12) Listataan kaikkien työntekijöiden palkka näkyviin riippumatta siitä,
vaikka samaa palkkaa esiintyisi usealla työntekijällä
---> tuloksena yhtä monta tuplaa kuin työntekijöitä:
Q11: SELECT ALL SALARY
FROM EMPLOYEE;
13) Jos kutakin eri suuruista palkkaa halutaan näkyville vain kuitenkin
kertaalleen, pitää toimia seuraavasti
---> tuloksena yhtä monta tuplaa kuin erilaisia palkkoja esiintyy:
Q11A: SELECT DISTINCT SALARY
FROM EMPLOYEE;
•
SQL:ssä on valmiina lauseet myös joukkojen unionin ( UNION ), erotuksen
( EXCEPT ) ja leikkauksen ( INTERSECT ) esittämistä varten. Tosin kaikki
järjestelmät eivät tue erotusoperaatiota. Operaatioihin osallistuvat
osakyselyt kirjoitetaan sulkeiden sisään.
14) Muodostetaan unionia hyväksi käyttäen kysely, jossa halutaan
selvittää projektit, joissa Smith-niminen henkilö toimii joko projektia
kontrolloivan osaston johtajana tai itse projektin työntekijänä.
Alkuosassa tutkitaan Smith-nimisten toimiminen projekteja
kontrolloivien osastojen johtajina, loppuosassa heidän
työskentelemisensä eri projekteissa.
Q4: ( SELECT DISTINCT PNUMBER
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM = DNUMBER AND MGRSSN = SSN AND
LNAME = 'Smith' )
UNION
( SELECT DISTINCT PNUMBER
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBER = PNO AND ESSN = SSN AND
LNAME = 'Smith' );
•
SQL-99:ssä on käytettävissä vastaavat lauseet myös monijoukkojen
käsittelyyn ( UNION ALL, EXCEPT ALL, INTERSECT ALL ).
8.4.5 Osajonovertailut ja aritmeettiset operaatiot
•
Merkkijono-, päivämäärä- ja aikatyyppiä edustaviin kenttiin voidaan
kohdistaa osajonovertailuja, eli tutkia, löytyykö jotain tiettyä mallia
( =merkkijonoa ) noudattava osuus tarkasteltavana olevasta kentästä.
•
Tätä tarkoitusta varten on käytettävissä operaattori LIKE. Etsittävä malli
kirjoitetaan yksinkertaisten lainausmerkkien sisään.
•
Mielivaltaisen mittaista ( myös tyhjää ) merkkijonoa kuvaa symboli %, ja
yksittäistä mielivaltaista merkkiä ( ei-puuttuvaa ) edustaa alaviiva ( _ ).
15) Etsitään kaikki työntekijät, joiden osoitteessa esiintyy joissain kohtaa
merkkijono 'Houston, TX'.
Q12: SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE ADDRESS LIKE '%Houston, TX%';
16) Etsitään kaikki työntekijät, jotka ovat syntyneet 1950-luvulla
( oletetaan varovasti, ettei vähintään sata vuotta aikaisemmin
syntyneitä enää ole yrityksen palveluksessa ... ):
Q12A: SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE BDATE LIKE '__5_______';
Päiväys on muotoa VVVV-KK-PP, joten kolmannen merkin pitää olla
numero 5, ja loput eli merkit 1-2 ja 4-10 saavat olla mitä tahansa.
•
Mikäli kenttä voi sisältää symboleita % ja _, ja juuri tällainen merkki esiintyy
etsittävässä mallissa, pitää niiden eteen kirjoittaa tunnistemerkki 
vaikkapa kenoviiva (\)  ja esitellä käytetty tunnistemerkki mallin jälkeen
option ESCAPE perään lainausmerkkien sisään.
Esimerkki: Haetaan mallia 'AB_CD%EF'. Tällöin hakulause olisi muotoa
... LIKE 'AB\_CD\%EF' ESCAPE '\';
•
Samasta syystä merkkijonoon kuuluvat lainausmerkit pitää kyselyiden
yhteydessä kahdentaa, jottei tulkittaisi merkkijonon esittelyä päättyneeksi:
Esimerkki: Mallin 's-Gravenhage ( = Haag ) etsintä tapahtuisi lauseella
... LIKE '''s-Gravenhage'
•
Numeerisille kentille voidaan soveltaa aritmeettisia peruslaskutoimituksia
eli yhteen- (+), vähennys (-), kerto- (*) ja jakolaskua (/).
Desimaalierottimena toimii yleensä piste (.).
17) Listataan, miltä projektissa 'ProductX' toimivien työntekijöiden
palkat näyttäisivät 10%:n korotuksen jälkeen.
Q13: SELECT FNAME, LNAME, 1.1*SALARY
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE SSN = ESSN AND PNO = PNUMBER AND
PNAME = 'ProductX';
•
Kannattaa huomioida, ettei edellinen kysely suinkaan päivitä 'ProductX'projektin työntekijöiden palkkoja tauluun EMPLOYEE, vaan tuottaa pelkän
tulostaulun, jossa palkkoja on korotettu.
•
Merkkijonotyyppisille kentille voidaan soveltaa yhdistämis- eli katenaatiooperaatiota (||).
•
Operaattorit (+) ja (-) tarkoittavat päivämäärä-, aika- ja
aikaleimakentille yhdellä lisäämistä ( increment ) tai vähentämistä
( decrement ).
•
Operaattoria BETWEEN käytetään muodossa X BETWEEN Y AND Z, ja se
on synonyymi kaksoisepäyhtälölle Y  X  Z.
18) Listataan kaikki tiedot osaston 5 työntekijöistä, joiden palkka on
vähintään 30000 ja korkeintaan 40000.
Q14: SELECT *
FROM EMPLOYEE
WHERE ( SALARY BETWEEN 30000 AND 40000 ) AND DNO = 5;
8.4.6 Kyselyn tulostaulun tuplien lajittelu
• Tulostaulun tuplat voidaan lajitella valittujen kenttien mukaisesti nousevaan
tai laskevaan suuruusjärjestykseen käyttämällä ORDER BY -lausetta.
•
ORDER BY -lauseen oletuksena on ei-vähenevä suuruusjärjestys (ASC). Eikasvava suuruusjärjestys saadaan aikaan optiolla DESC.
19) Listataan kaikkien työntekijöiden työskentelytiedot eri projekteissa
aakkosjärjestyksessä ensin työntekijän osaston ja sitten suku- ja
etunimen mukaan.
Q15: SELECT DNAME, LNAME, FNAME, PNAME
FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT
WHERE DNUMBER = DNO AND SSN = ESSN AND PNO = PNUMBER
ORDER BY DNAME, LNAME, FNAME;
•
Jos haluttaisiin listata osastojen nimet käännetyssä aakkosjärjestyksessä
mutta etu- ja sukunimet normaalissa aakkosjärjestyksessä, ORDER BY
-lause näyttäisi seuraavalta ( ASC-optiot voidaan jättää pois ):
ORDER BY DNAME DESC, LNAME ASC, FNAME ASC;
8.5 Aiempia monimutkaisempia SQL-kyselyjä
•
Tähän mennessä tehdyt kyselyt on voitu toteuttaa suoraviivaisesti, sillä
liitosehdot ovat olleet suoraan ilmaistavissa attribuuttien arvojen avulla.
•
Kuitenkin saattaa toisinaan olla tarpeen tehdä kysely, joka on liian
monimutkainen toteutettavaksi yksivaiheisena.
8.5.1 Vertailut NULL-arvojen kanssa ja kolmiarvologiikka
•
Kuten jo kappaleessa 5.1.2 todettiin, arvolla NULL on kolme erilaista
tulkintaa
1. Arvo on varmasti olemassa, mutta jostain syystä sitä ei ole tallennettu (
esimerkiksi syntymäajan puuttuminen työntekijältä ).
2. Arvoa ei ole saatavilla ( esimerkiksi salainen puhelinnumero )
3. Arvoa ei ole määriteltävissä ( esimerkiksi tiedot suoritetuista
loppututkinnoista opiskelijalla, jolla ei ole aikaisempia tutkintoja )
•
Puuttuvaa arvoa voidaan testata operaattoreilla IS NULL tai IS NOT NULL.
Ensimmäinen palauttaa totuusarvon tosi silloin, kun tarkasteltava kenttä on
puuttuva ja jälkimmäinen silloin, kun kenttään on syötetty jokin todellinen
arvo.
•
SQL käsittelee liitoksissa eri paikkoihin tallennettuja NULL-arvoja erillisinä,
joten tästä syystä arvo NULL liitoskentissä ei toteuta normaalin ( eli muun
kuin ulkoisen ) liitoksen liitosehtoa. Tästä syystä ei yleensä arvoa NULL
vastaan tapahtuvaa vertailua myöskään esitetä matemaattisten
operaattoreiden = ja  avulla.
•
NULL-arvojen käsittelyyn sovelletaan SQL:ssä ns. kolmiarvologiikkaa ( tosi /
epätosi / tuntematon ) perinteisen kaksiarvologiikan ( tosi / epätosi )
asemesta.
•
Seuraavassa ovat esiteltyinä kolmiarvologiikan totuusarvotaulut loogisille
operaattoreille JA, TAI ja EI:
___JA
TOSI
EPÄTOSI
TUNTEMATON
TOSI
TOSI
EPÄTOSI
TUNTEMATON
EPÄTOSI
EPÄTOSI
EPÄTOSI
EPÄTOSI
EPÄTOSI
TUNTEMATON
TUNTEMATON
TUNTEMATON
TUNTEMATON
TAI
TOSI
EPÄTOSI
TOSI
TOSI
TOSI
EPÄTOSI
TOSI
EPÄTOSI
TUNTEMATON
TOSI
TUNTEMATON
TOSI
TUNTEMATON
TUNTEMATON
____EI___________________
TOSI
EPÄTOSI
TUNTEMATON
EPÄTOSI
TOSI
TUNTEMATON
• Tarkastellaan seuraavaksi esimerkkiä, jossa vertailu kohdistuu attribuutin
NULL-arvoon.
20) Etsitään työntekijät, joilla ei ole esimiestä:
Q18: SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE SUPERSSN IS NULL;
8.5.2 Sisäkkäiset kyselyt, tuplat ja joukko- / monijoukkovertailut
•
Sisäkkäistä kyselyä voidaan käyttää silloin, kun tuloksen määrääminen
perustuu jonkin toisen kyselyn tulokseen.
•
Myös silloin, kun yksitasoinen kysely on mahdollinen, voidaan käyttää
sisäkkäisiä kyselyitä avuksi.
•
Sisempi kysely esitellään WHERE-lauseessa joko aritmeettisen, operaattorin
IN tai funktioiden EXISTS ja NOT EXISTS jälkeen
( IN = joukkoon kuulumisrelaatio ).
•
Sisempi kysely ( = alikysely ) kirjoitetaan sulkeiden sisään.
21) Toteutetaan kysely 14 (= Q4A ) uudelleen sisäkkäisenä kyselynä
ilman unionioperaatiota.
Q4A: SELECT DISTINCT PNUMBER
FROM PROJECT
WHERE PNUMBER IN ( SELECT PNUMBER
FROM PROJECT, DEPARTMENT, EMPLOYEE
WHERE DNUM=DNUMBER AND MGRSSN=SSN
AND LNAME = 'Smith' )
OR
PNUMBER IN ( SELECT PNO
FROM WORKS_ON, EMPLOYEE
WHERE ESSN = SSN AND LNAME = 'Smith' );
Ylempi alikysely tuottaa edellä luettelon projekteista, joita kontrolloivia
osastoja Smith-niminen henkilö johtaa, ja alempi alikysely luettelon
projekteista, joissa Smith-niminen henkilö toimii työntekijänä. Ulompi
kysely vertaa PROJECT-taulun projektinumeroiden kuulumista
jommankumman alikyselyn tulokseen, joten sen FROM-lauseessa ei
tarvita muita tauluja kuin PROJECT. Ulompi kysely poistaa duplikaatit
alikyselyjen tuloksista.
22) Selvitetään, ketkä henkilöt tekevät henkilön kanssa, jonka
henkilötunnus on '123456789', ainakin yhdessä projektissa saman
määrän työtunteja viikossa. Duplikaatit poistetaan vastauksesta.
SELECT DISTINCT ESSN
FROM WORKS_ON
WHERE ( PNO, HOURS ) IN ( SELECT PNO HOURS
FROM WORKS_ON
WHERE ESSN = '123456789' );
•
Yllä olevassa kyselyssä verrataan tuplan osien vastaavuutta keskenään.
Kulloinkin valittuna olevan taulun WORKS_ON osatuplan ( attribuuttiparin
Projektinumero, Työtunnit ) pitää löytyä alikyselyn tulostaulusta. Mikäli
tällainen löytyy, kyseisen tuplan henkilötunnus päätyy ulomman kyselyn
tulostauluun.
•
IN-operaattorin asemesta voidaan käyttää myös matemaattisia
vertailuoperaattoreita täydennettynä optioilla ANY ( = SOME ) tai ALL.
Tällöin voidaan tutkia, täyttääkö jokin alikyselyn tuloksen tuplista
vertailuoperaattorin mukaisen ehdon.
23) Listataan työntekijät, joiden palkka on korkeampi kuin jokaisella
osaston 5 työntekijöistä.
SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE SALARY > ALL ( SELECT SALARY
FROM EMPLOYEE
WHERE DNO = 5 );
•
Mikäli sisemmässä kyselyssä esiintyy attribuutin nimi ilman pistenotaatiota,
ja saman niminen attribuutti esiintyy myös ulommassa kyselyssä jossain
toisessa taulussa, sisemmän kyselyn määrittely on voimakkaampi ( vrt.
ohjelmointikielten muuttujien näkyvyysalueet ).
24) Listataan työntekijät, joilla on saman niminen ja samaa sukupuolta
oleva perheenjäsen kuin hän itse on.
Q16: SELECT E.FNAME, E.LNAME
FROM EMPLOYEE AS E
WHERE E.SSN IN ( SELECT ESSN
FROM DEPENDENT
WHERE E.FNAME = DEPENDENT_NAME AND
E.SEX = SEX );
Kyselyn viimeisen rivin kenttä SEX viittaa nyt tauluun DEPENDENT,
koska sisemmässä kyselyssä käytetään kyseistä taulua. Jos
sisemmässä kyselyssä tarvittaisiin myös taulua EMPLOYEE,
pitäisi taulut EMPLOYEE ja DEPENDENT nimetä uudelleen
nimikonfliktin välttämiseksi kentässä SEX.
8.5.3 Korreloivat sisäkkäiset kyselyt
•
Edellisen esimerkin kyselyä kutsutaan ns. korreloivaksi sisäkkäiseksi
kyselyksi, sillä ulomman kyselyn taulun attribuutin arvoa verrataan
sisemmässä kyselyssä esiintyvän taulun attribuutin arvoon. Tulkinnallisesti
tämä tarkoittaa sitä, että kutakin ulomman kyselyn tuplaa kohti generoidaan
sisempi kysely ja tutkitaan liitosehdon toteutumista.
•
Mikäli kaksitasoinen kysely on muotoa SELECT-FROM-WHERE, jossa
käytetään operaattoria IN tai =, se voidaan esittää aina yksitasoisena
kyselynä.
25) Kirjoitetaan kysely 24 toisin yksitasoisena.
Q16A: SELECT E.FNAME, E.LNAME
FROM EMPLOYEE AS E, DEPENDENT AS D
WHERE E.SSN = D.SSN AND E.SEX = D.SEX AND
E.FNAME = D.DEPENDENT_NAME;
•
Harjoitustehtävä: Yritä tehdä kyselylle 21 samoin kuin edellä kyselylle 24
( vihje: 'monista' taulua WORKS_ON ).
•
Operaation CONTAINS avulla voidaan suorittaa relaatioiden
jakolaskuoperaatio. Operaatio tosin puuttuu monista
relaatiotietokantajärjestelmistä.
26) Listaa niiden henkilöiden nimet, jotka työskentelevät kaikissa osaston
5 kontrolloimissa projekteissa.
Q3: SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE ( ( SELECT PNO
FROM WORKS_ON
WHERE SSN = ESSN )
CONTAINS
( SELECT PNUMBER
FROM PROJECT
WHERE DNUM = 5 ) );
Alikyselyn ensimmäinen osa etsii kaikki projektit, joissa työntekijä on
töissä, ja toinen osa valitsee kaikki ne projektit, joita osasto 5 kontrolloi.
Jos kaikki loppuosan projektinumerot sisältyvät alkuosaan, henkilö tulee
valituksi tulostauluun.
8.5.4 EXISTS- ja UNIQUE-funktiot SQL:ssä
•
EXISTS-funktio testaa, kuuluuko alikyselyn tulokseen yhtään tuplaa ( jos
kuuluu, on samantekevää, montako ) vai ei.
27) Tehdään kysely 23 ( = Q16 ) käyttämällä hyväksi EXISTS-funktiota.
Q16B: SELECT E.FNAME, E.LNAME
FROM EMPLOYEE AS E
WHERE EXISTS ( SELECT *
FROM DEPENDENT
WHERE E.SSN = ESSN AND E.SEX = SEX AND
E.FNAME = DEPENDENT_NAME );
Mikäli alikyselyn vastaukseksi saadaan yksikin tupla tarkasteltavaa
työntekijää kohti ( eli työntekijällä on ainakin yksi saman niminen
ja samaa sukupuolta oleva perheenjäsen ), kyseinen työntekijä
tulee valituksi.
•
Vastaavasti voidaan käyttää funktiota NOT EXISTS, joka palauttaa
totuusarvon tosi silloin, kun alikyselyn tulos on tyhjä relaatio.
28) Listataan työntekijät, joilla ei ole perheenjäseniä.
Q6: SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
FROM DEPENDENT
WHERE SSN = ESSN );
Jos alikyselyn tulos on tyhjä, henkilöllä ei ole perheenjäseniä, jolloin
työntekijä tulee valituksi.
•
Tarkastellaan vielä paria lisäesimerkkiä.
29) Listataan osastonjohtajat, joilla on ainakin yksi perheenjäsen ( kaksi
korreloivaa sisäkkäistä kyselyä ).
Q7: SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE EXISTS ( SELECT *
FROM DEPENDENT
WHERE SSN = ESSN )
AND
EXISTS ( SELECT *
FROM DEPARTMENT
WHERE SSN = MGRSSN );
30) Toteutetaan kysely 25 ilman CONTAINS-funktiota ( jakolasku R:S on
sama asia kuin erotus S—R =  ).
Q3A: SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE NOT EXISTS ( ( SELECT PNUMBER
FROM PROJECT
WHERE DNUM = 5 )
EXCEPT
( SELECT PNO
FROM WORKS_ON
WHERE SSN = ESSN ) );
Alikyselyn alkuosa muodostaa listan projekteista, joita osasto 5
kontrolloi. Loppuosa puolestaan etsii ne projektit, joissa kulloinkin
tarkasteltava henkilö työskentelee. Jos henkilö työskentelee kaikissa
osaston 5 projekteissa, on erotus tyhjä, jolloin NOT EXISTS
palauttaa arvon tosi, ja henkilö tulee valituksi koko kyselyn
vastaukseen.
31) Koska EXCEPT-operaatio puuttuu monesta järjestelmästä,
kierretään seuraavassa sekin!
Q3B: SELECT LNAME, FNAME
FROM EMPLOYEE
WHERE NOT EXISTS ( SELECT *
FROM WORKS_ON B
WHERE ( B.PNO IN ( SELECT PNUMBER
FROM PROJECT
WHERE DNUM = 5 ) )
AND
NOT EXISTS ( SELECT *
FROM WORKS_ON C
WHERE C.ESSN = SSN
AND C.PNO = B.PNO ) );
Toisen tason kysely listaa kaikki ne projektit, joita osasto 5 kontrolloi.
Kolmannen tason kysely tutkii, osallistuuko sama yksittäinen
työntekijä kuhunkin osaston 5 kontrolloimaan projektiin. Jos löytyy
yksikin sellainen osaston 5 projekti, jossa tarkastelun kohteena oleva
työntekijä ei toimi, sisempi NOT EXISTS palauttaa arvon tosi. Tällöin
toisen tason kyselyn AND-operaatio tuottaa tuloksen tosi, jolloin
ulompi NOT EXISTS palauttaa arvon epätosi. Henkilö tulee valituksi
koko kyselyn vastaukseen ainoastaan silloin, kun ei ole yhtään
sellaista osaston 5 projektia, jossa hän ei työskentele.
•
Mikäli halutaan testata, tuottaako alikysely vastaukseksi duplikaatteja,
voidaan käyttää funktiota UNIQUE ( Q ).
8.5.5 Eksplisiittiset joukot ja attribuuttien uudelleen nimeäminen SQL:ssä
•
SQL:ssä on mahdollista testata attribuutin arvon kuulumista eksplisiittisesti
esiteltyyn vakiojoukkoon. Joukkoon kuulumista kuvaa operaattori IN.
Vakiojoukko esitellään sulkeiden sisällä, ja joukon alkiot erotetaan toisistaan
pilkuilla.
32) Listataan henkilötunnukset kaikilta niiltä työntekijöiltä, jotka toimivat
ainakin jossain projekteista 1, 2 ja 3:
Q17: SELECT DISTINCT ESSN
FROM WORKS_ON
WHERE PNO IN (1, 2, 3);
•
SELECT-lauseessa voidaan valituiksi tulevat attribuutit vaihtaa tarvittaessa
toisen nimisiksi.
33) Listataan jokaisen työntekijän ja hänen lähimmän esimiehensä
sukunimi:
Q8A: SELECT E.LNAME AS EMPLOYEE_LNAME,
S.LNAME AS SUPERVISOR_NAME
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.SUPERSSN = S.SSN
8.5.6 Liitostaulut SQL:ssä
•
SQL-99:ssä on mahdollista esitellä liitos jo FROM-lauseessa, mikä
selkeyttää jonkin verran kyselyjä, sillä nyt voidaan liitos- ja valintaehdot
erotella toisistaan. Liitoksen esittelyssä käytetään merkintää
taulu1 ( INNER ) JOIN taulu2 ON liitosehto.
•
34) Etsitään jälleen tutkimusosastolla työskentelevien nimi- ja osoitetiedot:
Q1A: SELECT FNAME, LNAME, ADDRESS
FROM ( EMPLOYEE JOIN DEPARTMENT ON DNO = DNUMBER )
WHERE DNAME = 'Research';
•
FROM-lauseessa esiteltävä liitos voi olla tyypiltään myös luonnollinen
( NATURAL JOIN ) tai ulkoinen liitos ( LEFT OUTER JOIN, RIGHT
OUTER JOIN, FULL ( OUTER ) JOIN ). Samassa yhteydessä voidaan
nimetä liitokseen osallistuville tauluille ja niiden attribuuteille aliaksia. Näin
on meneteltävä, jos tehdään luonnollinen liitos, eikä liitoksen tauluilla ole
yhtään keskenään saman nimistä attribuuttia.
35) Tehdään kysely 34 uudelleen käyttämällä luonnollista liitosta:
Q1B: SELECT FNAME, LNAME, ADDRESS
FROM ( EMPLOYEE NATURAL JOIN ( DEPARTMENT AS
DEPT ( DNAME, DNO, MSSN, MSDATE ) ) )
WHERE DNAME = 'Research';
36) Listataan jokainen työntekijä ja hänen esimiehensä, vaikka esimiestä
ei työntekijällä olisikaan ( tarvitaan avuksi vasen ulkoinen liitos ):
Q8B: SELECT E.LNAME AS EMPLOYEE_NAME,
S.LNAME AS SUPERVISOR_NAME
FROM ( EMPLOYEE AS E LEFT OUTER JOIN EMPLOYEE AS S ON
E.SUPERSSN = S.SSN );
•
Myös usean liitoksen esitteleminen samalla onnistuu haluttaessa
FROM-lauseessa.
37) Listataan jokaisen Staffordin projektin projektinumero, projektia
kontrolloivan osaston numero sekä kyseisen osaston johtajan
sukunimi, osoite ja syntymäaika:
Q2A: SELECT PNUMBER, DNUM, LNAME, ADDRESS, BDATE
FROM ( ( PROJECT JOIN DEPARTMENT ON DNUM = DNUMBER ) JOIN
EMPLOYEE ON MGRSSN = SSN )
WHERE PLOCATION = 'Stafford';
8.5.7 Koostefunktiot SQL:ssä
•
SQL:ssä on käytettävissä aggregaattifunktiot COUNT, SUM, MAX, MIN ja
AVG. Näiden merkitykset vastaavat kappaleessa 6.4.1 esitettyjä.
•
Aggregaattifunktioita voidaan käyttää SELECT- ja HAVING-lauseissa
( jälkimmäinen esitellään myöhemmin ).
•
Funktioita MIN ja MAX voidaan käyttää myös ei-numeerisille
attribuuteille, jos niiden arvojoukko on täydellisesti järjestetty.
38) Listataan kaikkien työntekijöiden vuosipalkkojen kokonaissumma,
maksimi- ja minimipalkka sekä palkkojen keskiarvo:
Q19: SELECT SUM ( SALARY ), MAX ( SALARY ), MIN ( SALARY ),
AVG ( SALARY )
FROM EMPLOYEE;
39) Listataan kyselyn 38 mukaiset koostetiedot ainoastaan tutkimusosaston
työntekijöiltä:
Q20: SELECT SUM ( SALARY ), MAX ( SALARY ), MIN ( SALARY ),
AVG ( SALARY )
FROM ( EMPLOYEE JOIN DEPARTMENT ON DNO = DNUMBER )
WHERE DNAME = 'Research';
40) Listataan työntekijöiden määrä koko yrityksessä:
Q21: SELECT COUNT (*)
FROM EMPLOYEE;
Kyselyssä merkintä (*) funktion COUNT jälkeen merkitsee rivien eli
tuplien määrää taulussa EMPLOYEE.
41) Listataan työntekijöiden määrä tutkimusosastolla:
Q22: SELECT COUNT (*)
FROM EMPLOYEE, DEPARTMENT
WHERE DNO = DNUMBER AND DNAME = 'Research';
42) Listataan erisuuruisten palkkojen lukumäärä koko yrityksessä:
Q23: SELECT COUNT ( DISTINCT SALARY )
FROM EMPLOYEE;
Mikäli sana DISTINCT jätettäisiin pois, saataisiin sama vastaus kuin
kyselyyn 40, sillä COUNT ei ilman erillistä komentoa poista
duplikaatteja. Kannattaa kuitenkin huomioida, että
COUNT ( SALARY ) ei kuitenkaan huomioisi tuplia, joissa palkkatieto
olisi puuttuva!
•
Aggregaattifunktioita tarvitaan pelkkien koostetietojen tuottamisen lisäksi
myös kyselyissä, joiden valintaehdossa esiintyy lukumäärään perustuvia
ehtoja.
43) Listataan työntekijät, joilla on vähintään 2 perheenjäsentä:
Q5: SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE ( SELECT COUNT (*)
FROM DEPENDENT
WHERE SSN = ESSN ) >= 2;
•
Esimerkki 43 on luonteeltaan korreloiva 2-tasoinen kysely, jossa kutakin
työntekijää kohti lasketaan häneen liittyvien perheenjäsentuplien määrä
taulussa DEPENDENT.
8.5.7 Ryhmittely: GROUP BY- ja HAVING-lauseet
•
Toisinaan on tarpeen ryhmitellä tuplat jonkin tietyn kentän arvojen
mukaisesti ja laskea ryhmäkohtaisia tilastotietoja. Tätä tarkoitusta varten
SQL:ssä on käytettävissä lause GROUP_BY.
•
Kannattaa huomioida, että ryhmittelyattribuutin pitää esiintyä myös
kyselyn SELECT-lauseessa.
44) Listataan työntekijöiden lukumäärä ja keskimääräinen palkka
osastokohtaisesti:
Q24: SELECT DNO, COUNT (*), AVG ( SALARY )
FROM EMPLOYEE
GROUP_BY DNO;
Kyselyn 44 tulostaulu on nähtävissä kirjan kuvassa 8.6(a).
45) Listataan jokaisesta projektista projektinumero, projektin nimi ja
projektissa työskentelevien henkilöiden lukumäärä:
Q25: SELECT PNUMBER, PNAME, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER = PNO
GROUP BY PNUMBER, PNAME;
•
Jos ryhmittelyattribuutin arvo on NULL, perustetaan sitä varten oma rivi
tulostauluun ( vrt. edellä kysely 44, jos sallittaisiin tiedon työntekijän
osastosta olevan puuttuva ).
•
Mikäli halutaan rajoittaa listaukseen otettavia ryhmiä, käytetään apuna
HAVING-lausetta.
46) Listataan numero, nimi ja työntekijöiden lukumäärä jokaisesta
sellaisesta projektista, jossa on vähintään 3 työntekijää:
Q26: SELECT PNUMBER, PNAME, COUNT (*)
FROM PROJECT, WORKS_ON
WHERE PNUMBER = PNO
GROUP BY PNUMBER, PNAME
HAVING COUNT (*) > 2;
Kyselyn tulostaulu ilman projektinumeroa löytyy kirjan kuvasta 8.6(b).
•
Kannattaa huomioida, että WHERE-lauseen valintaehto valitsee yksittäisiä
tuplia, mutta HAVING-lause valitsee tuplista koostuvia ryhmiä.
47) Listataan jokaisesta projektista sen numero, nimi ja osaston 5
työntekijöiden lukumäärä projektissa:
Q27: SELECT PNUMBER, PNAME, COUNT (*)
FROM PROJECT, WORKS_ON, EMPLOYEE
WHERE PNUMBER = PNO AND SSN = ESSN AND DNO = 5
GROUP BY PNUMBER, PNAME;
•
Kun SELECT- ja HAVING-lauseissa käytettyihin aggregaattifunktioihin
liittyvät erilaiset ehdot, pitää kyselyn rakentamisessa olla varovainen, jottei
tuoteta virheellistä kyselyä.
Esimerkki: Oletetaan, että haluttaisiin listata osastokohtaisesti niiden
työntekijöiden lukumäärä, joiden palkka ylittää 40000, mutta kuitenkin
vain sellaisilta osastoilta, joissa on vähintään 6 työntekijää.
48) Kirjoitetaan alustavasti seuraava kyselyehdokas tehtävän
ratkaisemiseksi:
SELECT DNAME, COUNT (*)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER = DNO AND SALARY > 40000
GROUP BY DNAME
HAVING COUNT (*) > 5;
Tämä kyselyversio tuottaa kuitenkin virheellisen vastauksen, sillä se
valitsee ainoastaan sellaiset osastot, joilla on yli 40000 ansaitsevia
työntekijöitä vähintään 6. Kyselyssä sovelletaan WHERE-ehtoa liian
aikaisin eli ennen HAVING-lausetta, mikä ei ole tässä tapauksessa
haluttu ratkaisu.
49) Kysely 48 joudutaan siten uusimaan kelvolliseksi seuraavaan muotoon:
SELECT DNAME, COUNT (*)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER = DNO AND SALARY > 40000 AND DNO IN
( SELECT DNO
FROM EMPLOYEE
GROUP BY DNO
HAVING COUNT > 5 )
GROUP BY DNUMBER;
Tämä kysely valitsee ne osastot, joita kohti taulussa EMPLOYEE on yli
5 työntekijää, ja jokaista tällaista osastoa kohti lasketaan niiden
työntekijöiden määrä, joiden palkka ylittää 40000:n rajan.
8.5.9 Yhteenveto SQL-kyselyistä
•
SQL-kielen kyselyiden yleinen muoto on esitelty seuraavassa. Hakasulkeiden
sisälle merkityt osuudet voivat olla puuttuvia.
SELECT < attribuutti - ja funktiolista >
FROM < taululista >
[ WHERE < ehto > ]
[ GROUP BY < ryhmittelyattribuutit > ]
[ HAVING < ryhmittelyehto >
[ ORDER BY < attribuuttilista >];
•
SELECT-lauseessa luetellaan tulostauluun haluttavat attribuutit ja
koostefunktioita soveltamalla tuotetut kentät.
•
FROM-lauseessa esitellään taulut, joita tarvitaan kyselyn toteuttamiseksi.
Ainoastaan alikyselyissä tarvittavia tauluja ei kuitenkaan esitellä.
•
WHERE-lause sisältää mahdolliset valinta- ja lisäksi liitosehdot, ellei
liitostauluja esitelty jo edellä FROM-lauseessa .
•
GROUP BY -lauseessa määritellään mahdolliset ryhmittelyattribuutit
•
HAVING-lauseessa esitellään tarvittaessa tuplien muodostamat ryhmät,
jotka valitaan tarkasteluun.
•
ORDER BY -lauseella voidaan tarvittaessa käyttää tulostaulun
lajittelemiseksi halutulla tavalla.
•
Kysely suoritetaan käsitteellisesti soveltamalla ensin FROM-lausetta, jotta
tunnistettaisiin ensinnä taulut, joita ( uloimmassa ) kyselyssä tarvitaan sekä
muodostettaisiin siinä esitellyt liitostaulut.
•
Tämän jälkeen suoritetaan järjestyksessä WHERE-, GROUP BY- ja
HAVING-lauseet.
•
Käsitteellisesti viimeisenä suoritetaan tulostaulun tuplien lajittelu
ORDER BY -lauseen mukaisesti.
•
Ellei kyselyssä esiinny mitään lauseista GROUP BY, HAVING ja
ORDER BY, voidaan kysely tulkita suoritettavan käsitteellisesti seuraavasti:
 Jokaista FROM-lauseessa esiteltyjen taulujen tuplien kombinaatiota kohti
evaluoidaan WHERE-lause. Mikäli WHERE-lause palauttaa valittuna
olevalle tuplien kombinaatiolle arvon 'Tosi', kyseisestä tuplien
kombinaatiosta listataan ne attribuutit, jotka esiintyvät SELECTlauseessa.
•
Todellisuudessa tällainen prosessointitapa on kuitenkin tehoton. Kyselyiden
optimointia käsitellään tarkemmin vasta Tietokantojen jatkokurssilla.
8.6 Tuplan lisääminen, muuttaminen ja tuhoaminen
SQL:llä
•
SQL-kielen kolme komentoa tietokannan sisällön päivittämiseksi ovat
INSERT (lisäys), UPDATE (muutos) ja DELETE (tuhoaminen).
8.6.1 Komento INSERT
•
Täyden uuden tuplan lisäämiseksi pitää kaikille sen attribuuteille asettaa
arvot siinä järjestyksessä, kuin attribuutit on kirjoitettu komennon CREATE
TABLE yhteydessä.
•
Täydellisen tietueen ( kaikille attribuuteille syötetään arvo ) lisäyskomento
on muotoa:
INSERT INTO taulu VALUES ( Attr1, Attr2, ..., AttrN );
50) Lisätään uusi tupla tauluun EMPLOYEE kaikkine arvoineen:
U1: INSERT INTO EMPLOYEE
VALUES ( 'Richard', 'K', 'Marini', '653298653', '1962-12-30',
'98 Oak Forest, Katy, TX', 'M', 37000, '987654321', 4 );
•
Mikäli osa attribuuteista halutaan jättää syöttämättä, pitää taulun jälkeen
selvittää, mihin kenttiin syötettävät arvot sijoitetaan.
•
Muille kentille annetaan taulun määrittelyn yhteydessä annettu oletusarvo
tai  ellei tällaista ole  kentän tieto asetetaan puuttuvaksi arvolla NULL.
51) Lisätään uusi tupla tauluun EMPLOYEE, kun vain osa attribuuteista
syötetään eksplisiittisesti:
U1A: INSERT INTO EMPLOYEE ( FNAME, LNAME, DNO, SSN )
VALUES ( 'Richard', 'Marini', 4, '653298653' );
•
Kerralla voidaan lisätä useita tuplia erottelemalla sulkeiden sisällä olevat
arvolistat toisistaan pilkuilla. Tietueen lisääminen estetään, mikäli
tietokannan eheyssääntöjä rikotaan.
52) Seuraava lisäysoperaatio estetään, mikäli työntekijän osastonumeron
pitää olla määriteltynä:
U2: INSERT INTO EMPLOYEE ( FNAME, LNAME, SSN, DNO )
VALUES ( 'Robert', 'Hatcher', '980760540', 2 );
53) Seuraava lisäysyritys tyrehtyisi SSN-kentän puuttumiseen EMPLOYEEtuplasta, sillä arvoa NULL ei sille saada asettaa oletusarvoksi
( kyseessä taulun pääavain ):
U2A: INSERT INTO EMPLOYEE ( FNAME, LNAME, DNO )
VALUES ( 'Robert', 'Hatcher', 5 );
•
Tauluun syöttö voi tapahtua myös kyselyyn saadun vastauksen perusteella.
54) Perustetaan taulu DEPTS_INFO, joka sisältää tiedot osaston nimestä,
sen työntekijämäärästä sekä palkkakustannuksista, ...
U3A: CREATE TABLE DEPTS_INFO
( DEPT_NAME VARCHAR( 15 ),
NO_OF_EMPS INTEGER,
TOTAL_SAL INTEGER );
55) ... ja syötetään sinne tiedot, jotka seuraava kysely tuottaa:
U3B: INSERT INTO DEPTS_INFO ( DEPT_NAME, NO_OF_EMPS,
TOTAL_SAL )
SELECT DNAME, COUNT (*), SUM ( SALARY )
FROM ( DEPARTMENT JOIN EMPLOYEE ON DNUMBER = DNO )
GROUP BY DNAME;
8.6.2 Komento DELETE
•
Komennolla DELETE tuhotaan tuplia tietokannasta.
•
Yhdellä DELETE-komennolla voidaan poistaa joko yksi tai useampi tupla
kerrallaan. Komento on muotoa
DELETE FROM taulu WHERE ehto;
•
Mikäli tuhoamiskomennolle ei ole asetettu ehtoa, kaikki taulun tuplat
hävitetään, mutta itse taulu kuitenkin säilytetään.
56) Poistetaan kaikki Brown-nimiset työntekijät:
U4A: DELETE FROM EMPLOYEE
WHERE LNAME = 'Brown';
57) Poistetaan työntekijä, jonka hetu on '123456789':
U4B: DELETE FROM EMPLOYEE
WHERE SSN = '123456789';
58) Poistetaan tutkimusosaston työntekijät:
U4C: DELETE FROM EMPLOYEE
WHERE DNO IN ( SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME = 'Research' );
59) Poistetaan kaikki (!) työntekijät:
U4D: DELETE
FROM EMPLOYEE;
8.6.3 Komento UPDATE
•
Tietueiden sisällön muuttaminen tapahtuu UPDATE-komennolla. Komento
on muotoa
UPDATE taulu SET muutokset WHERE ehto;
60) Muutetaan projektin 10 tietoja:
U5: UPDATE PROJECT
SET PLOCATION='Bellaire', DNUM=5
WHERE PNUMBER = 10;
61) Korotetaan tutkimusosaston työntekijöiden palkkoja 10 prosenttia.
U6: UPDATE EMPLOYEE
SET SALARY=SALARY*1.1
WHERE DNO IN ( SELECT DNUMBER
FROM DEPARTMENT
WHERE DNAME = 'Research' );