RELACIJSKO POIZVEDOVANJE SQL-1.del 6.April 2010

Uvod v SQL…

§SQL je transformacijsko usmerjen jezik, ki ga sestavljata dve skupini ukazov:

–Skupina ukazov DDL (Data Definition Language) za opredelitev strukture podatkovne baze in

–Skupina ukazov DML (Data Manipulation Language) za poizvedovanje in ažuriranje podatkov.

§SQL do izdaje SQL:1999 ne vključuje ukazov kontrolnega toka. Kontrolni tok je bil potrebno obvladati s programskim jezikom ali interaktivno z odločitvami uporabnikov.

§Lastnosti SQL:

–Enostaven;

–Nepostopkoven (kaj in ne kako);

–Uporaben v okviru številnih vlog: skrbniki PB, vodstvo, razvijalci informacijskih rešitev, končni uporabniki;

–Obstaja ISO standard za SQL;

–SQL je de-facto in tudi uradno standardni jezik za delo z relacijskimi podatkovnimi bazami.

§ 

Pisanje SQL stavkov…

§SQL stavki so sestavljeni iz rezerviranih in uporabniško definiranih besed.

§Rezervirane besede so natančno določene, napisane morajo biti pravilno, ne smejo se lomiti med vrstice.

§Uporabniško definirane besede označujejo razne podatkovne objekte, kot so npr. relacije, stolpci, pogledi,…

§Večina komponent SQL stavkov je neodvisna od velikosti pisave; izjema so tekstovni podatki.

§Da dosežemo boljšo berljivost, pišemo SQL stavke v več vrsticah in z zamiki:

–Vsak sklop SQL stavka se začne v novi vrstici

–Sklopi so levo poravnani

–Če ima sklop več delov, je vsak v svoji vrstici in poravnan z začetkom sklopa

§Za opis sintakse SQL stavkov bomo uporabljali razširjeno BNF notacijo:

–REZERVIRANE BESEDE z velikimi črkami,

–uporabniško definirane besede z malimi črkami,

–Znak | za izbiro med alternativami,

–{Obvezni elementi} v zavitih oklepajih,

–[Opcijski elementi] v oglatih oklepajih,

–Znak … za opcijske ponovitve (0 ali več).

§ 

§Podatkovne vrednosti predstavljajo konstante v SQL stavkih.

§Vse ne-numerične vrednosti so zapisane v enojnih navednicah

‘Ljubljana’

§Vse numerične vrednosti brez navednic

225.990

Implementacije SQL

Stavki skupine SQL DML

§DML skupina zajema SQL stavke za manipulacijo s podatki

–SELECT à Izbira

–INSERT à Dodajanje

–DELETE à Brisanje

–UPDATE à Spreminjanje

§Sintaksa SELECT stavka:

§FROM                      Določa tabele za poizvedbo

§WHERE                    Filtrira vrstice

§GROUP BY              Združuje vrstice po vrednostih izbranih stolpcev

§HAVING                  Filtrira skupine glede na določene pogoje

§SELECT                   Določa stolpce, ki naj se pojavijo v izhodni relaciji

§ORDER BY             Določa vrstni red vrstic na izhodu

Primeri:

§Za primere bomo uporabljali shemo PB o hotelih

Hotel    (hotelNo, hotelName, address)

Room   (roomNo, hotelNo, type, price)

Booking           (hotelNo, guestNo, dateFrom, dateTo, roomNo, comments)

Guest   (guestNo, guestName, guestAddress)

§Izpiši vse podatke o hotelih:

                        SELECT hotelNo, hotelName, address

                        FROM Hotel

            ali krajše

                        SELECT * FROM Hotel

Uporaba DISTINCT

§Izpiši oznake hotelov in sob, ki so bile kdaj koli rezervirane

                        SELECT DISTINCT hotelNo, roomNo

                        FROM Booking

Izračunana polja

§Izpiši ceno sob za deset dnevni najem

            SELECT RoomNo, type, price*10 AS CenaNajema

            FROM Room

Iskalni kriteriji

§Izpiši oznake hotelov, ki imajo tri-posteljne sobe (type = 3) in ceno manjšo kot 100 EUR

                        SELECT hotelNo

                        FROM Room

                        WHERE type = 3 AND price < 100

Iskanje z uporabo BETWEEN

§Izpiši vse sobe s ceno med 50 in 70 EUR

                        SELECT roomNo

                        FROM Room

                        WHERE price BETWEEN 50 AND 70

Iskanje po članstvu množice

§Izpiši oznake hotelov in številke dve ali tri-posteljnih sob

                        SELECT hotelNo, roomNo

                        FROM Room 

                        WHERE type IN (2,3)

Iskanje z vzorcem

§Izpiši vse goste, ki živijo kjerkoli v Ljubljani (v polju guestAddress je tudi string ‘Ljubljana’)

                        SELECT guestNo, guestName, guestAddress

                        FROM Guest

                        WHERE guestAddress LIKE ‘%Ljubljana%’

Iskanje z NULL vrednostjo v pogoju

§Izpiši vse rezervacije brez podanih komentarjev

                        SELECT *

                        FROM Booking

                        WHERE comments IS NULL

Sortiranje vrstic v izhodni relaciji

§Izpiši vse podatke o sobah, urejene po tipu sobe od največje do najmanjše in znotraj tipa po cenah od najmanjše do največje

                        SELECT *

                        FROM Room

                        ORDER BY type DESC, price ASC

Agregiranje podatkov…

§ISO standard definira pet agregarnih operacij

–COUNT        vrne število vrednosti v določenem stolpcu

–SUM              vrne seštevek vrednosti v določenem stolpcu

–AVG              vrne povprečje vrednosti v določenem stolpcu

–MIN              vrne najmanjšo vrednost v določenem stolpcu

–MAX             vrne največjo vrednost v določenem stolpcu

§Vse operacije delujejo na enem stolpcu in vračajo eno samo vrednost.

§COUNT, MIN in MAX se uporabljajo za numerične in ne-numerične vrednosti, SUM in AVG zahtevata numerične vrednosti.

§Vse operacije razen COUNT(*) najprej odstranijo vrstice z NULL vrednostjo v stolpcu, po katerem agregiramo.

§COUNT(*) prešteje vse vrstice, ne glede na NULL vrednosti ali duplikate.

§Če se želimo znebiti duplikatov, uporabimo DISTINCT pred imenom stolpca.  Nima učinka na MIN/MAX, lahko pa vpliva na SUM/AVG.

§Agregarne operacije lahko uporabimo le v SELECT ali HAVING sklopu

§Če SELECT sklop vsebuje agregarno operacijo, mora obstajati tudi GROUP BY sklop, sicer ni moč dodeliti agregirane vrednosti.

                        SELECT roomNo, AVG(price)

                        FROM Room

Uporaba COUNT in DISTINCT

§V koliko različnih hotelih obstajajo rezervacije za prvi teden v mesecu januarju 2005

            SELECT COUNT (DISTINCT hotelNo) AS numH

            FROM Booking

            WHERE          dateFrom = ‘1.1.2005’ AND
                        dateTo = ‘7.1.2005’

           

Uporaba več agregatov istočasno                   

§Izpiši povprečno, minimalno in maksimalno ceno dvoposteljne sobe

            SELECT AVG(price), MIN(price), MAX(price)

            FROM Room

            WHERE type = 2       

Združevanje podatkov…

§Sklop GROUP BY uporabimo za združevanje podatkov v skupine.

§SELECT in GROUP BY sta tesno povezana

–vsak element v SELECT seznamu, mora imeti samo eno vrednost za vse elemente v skupini,

–SELECT sklop lahko vsebuje le:

§imena stolpcev

§agregarne operacije

§konstante ali

§izraze, ki so sestavljeni iz kombinacije naštetih elementov.

§Vsi stolpci, ki so navedeni v SELECT sklopu, se morajo nahajati tudi v GROUP BY sklopu, razen tistih, ki nastopajo samo v agregarnih operacijah.

§Če uporabljamo WHERE sklop v kombinaciji z GROUP BY, se WHERE upošteva najprej, združevanje pa se izvede na preostalih vrsticah.

§ISO standard jemlje NULL vrednosti kot enake, ko gre za združevanje.

Primer:

§Izpiši število enoposteljnih, dvoposteljnih in troposteljnih sob v vsakem hotelu

            SELECT hotelNo, type, COUNT(roomNo)

            FROM Room

            GROUP BY hotelNo, type

Omejitev skupin

§HAVING sklop je namenjen uporabi v kombinaciji z GROUP BY kot omejitev skupin, ki se lahko pojavijo v rezultatu.

§Deluje podobno kot WHERE

–WHERE filtrira posamezne vrstice

–HAVING filtrira skupine.

§Stolpci, ki so navedeni v HAVING sklopu, morajo biti tudi v SELECT sklopu ali v agregatih.

Uporaba sklopa HAVING

§Izpiši število enoposteljnih, dvoposteljnih in troposteljnih sob v vsakem hotelu ter njihovo povprečno ceno. Upoštevaj samo tiste primere, ko je število sob večje od 1.

            SELECT hotelNo, type, COUNT(roomNo), AVG(price)

            FROM Room

            GROUP BY hotelNo, type

            HAVING COUNT(roomNo) > 1

Gnezdenje poizvedb…

§Nekateri SQL stavki imajo lahko vgnezdene SELECT stavke.

§Vgnezdeni SELECT stavki se lahko uporabijo v WHERE ali HAVING sklopih drugega SELECT stavka (subselect).

§Lahko se pojavijo tudi v INSERT, UPDATE in DELETE stavkih.

Primer vgnezdenega SELECT stavka:

§Izpiši nazive hotelov, ki imajo vsaj 10 troposteljnih sob

           
SELECT hotelName

            FROM Hotel

            WHERE hotelNo IN

                (       SELECT hotelNo

                        FROM Room

                        GROUP BY hotelNo

                        HAVING COUNT(hotelNo) > 9)

Pravila gnezdenja SELECT stavkov…

§Vgnezdeni SELECT stavki ne smejo uporabljati ORDER BY sklopa.

§SELECT sklop vgnezdenega SELECT stavka lahko zajema samo en stolpec, razen v primeru uporabe ukaza EXISTS.

§Imena stolpcev v vgnezdenem SELECT stavku se privzeto nanašajo na tabele iz vgnezdenega ali zunanjega SELECT stavka (uporaba alias-ov)

§Ko je vgnezden SELECT stavek operand v primerjavi, se mora nahajati na desni strani enačbe.

§Vgnezdeni SELECT stavek ne more biti operand v izrazu.

Vaja

§Napišite SQL poizvedbo, ki vrne imena hotelov, ki imajo nadpovprečno ceno svojih sob.

Uporaba ANY in ALL

§V vgnezedenih SELECT stavkih, ki vračajo en sam stolpec, lahko uporabljamo operatorja ANY in ALL.

§Z uporabo ALL bo pogoj izpolnjen samo, če bo veljal za vse vrednosti, ki ji vrača poizvedba.

§Z uporabo ANY, bo pogoj izpolnjen, če bo veljal za vsaj eno od vrednosti, ki ji poizvedba vrača.

§Če je rezultat poizvedbe prazen, bo ALL vrnil true, ANY pa false.

§Namesto ANY lahko uporabljamo tudi SOME.

Primer uporabe ANY:

§Izpiši številke sob ter pripadajočih hotelov, katerih cena je večja kot cena vsaj ene sobe v hotelu ‘Hilton New York’ (hotelNo = 5).

            SELECT R.hotelNo, R.roomNo

            FROM room AS R

            WHERE R.hotelNo <>5 AND

               R.price > ANY ( SELECT price

                                      FROM Room

                                      WHERE hotelNo = 5 );

Primer uporaba ALL:

§Izpiši številke sob ter pripadajočih hotelov, katerih cena je večja kot cena vseh sob v hotelu ‘Hilton New York’ (hotelNo = ‘HIL’).

            SELECT R.hotelNo, R.roomNo

            FROM Room AS R

            WHERE R.hotelNo <>5 AND

               R.price > ALL ( SELECT Price

                                      FROM Room

                                      WHERE hotelNo = 5 );