Povezovanje tabel T heta stik na osnovi zunanjega povezovanja

1. Povezovanje tabel Theta stik na osnovi zunanjega povezovanja • Notranje povezovanje (INNER JOIN) • poveže tiste vrstice prve in druge tabele, ki ustrezajo pogoju (pri katerih je vrednost skupnega atributa(ov) enaka) • Zunanje povezovanje omogoča povezovanje • Vseh vrstic prve tabele z ustreznimi vrsticami druge tabele (LEFT OUTER JOIN) • če za nek zapis prve tabele ni ustreznega v drugi tabeli, so vrednosti atributov druge tabele NULL (RIGHT OUTER JOIN) • Vseh vrstic druge tabele z ustreznimi vrsticami prve tabele • če za nek zapis druge tabele ni ustreznega v prvi tabeli, so vrednosti atributov prve tabele NULL • Vseh vrstic prve in druge tabele (FULL OUTER JOIN) • pri zapisih, ki nimajo ustreznega v drugi tabeli so vrednosti atributov le-teh NULL

2. LeftOUTER Povezovanje tabel Theta stik na osnovi zunanjega povezovanja (nad.) • Sintaksa SELECT atributi FROM tabela1 LEFT | RIGHT | FULL [OUTER] JOIN tabela2 ON pogoj; • Primer: izpišite seznam vseh dijakov, in šifre krožkov, ki jih obiskujejo: SELECT Dijak.Priimek, Dijak.Ime, Obiskuje.IDKrozek FROM Dijak LEFT JOIN Obiskuje ON Dijak.IDDijak=Obiskuje.IDDijak; Dijak, ki ne obiskuje nobenega krožka

3. Povezovanje tabel - zunanje povezovanje (2. primer) Student (sifras,imes,priimeks,letnik)Izpit (sifras,predmet,datum,ocena) SELECT s.imes,i.predmet FROM student s LEFT OUTER JOIN izpit i ON s.sifras=i.sifras ali SELECT s.imes,i.predmet FROM izpit i RIGHT OUTER JOIN student s ON i.sifras=s.sifras

4. SQL – vgrajene funkcije(opomba: odvisne od SUPB-ja oz. implementacije SQL-a) 1. agregirane funkcije • MIN, MAX // najmanjša, največja vrednost • COUNT // PREšteje • SUM // SEšteje • AVG // računa povprečje 2. funkcije za delo z nizi znakov • konkatenacija • male/velike črke • podniz niza 3. funkcije za konverzijo • pretvorbe med tipi • izločanje dela datumske vrednosti

5. Uporaba agregiranih funkcij • Z združevanjem • Pred izvedbo agregirane funkcije se vrstice tabele združijo glede na pogoj • Agregirana funkcija se za vsako skupino vrstic izvede posebej SELECT AGR_FUNKCIJA(atribut) [,atrx] FROM tabela GROUP BY atrx; • Samostojna • agregirana funkcija se izvede nad vsemi vrsticami tabele SELECT AGR_FUNKCIJA(atribut) FROM tabela; Agregirane funkcije ignorirajo vrednost NULL!

6. Agregirane funkcije1.1. min, max Izpišite priimek dijaka, ki je prvi po abecedi: SELECT MIN(Priimek) FROM Dijak; Za vsak razred izpišite priimek dijaka, ki je prvi po abecedi: SELECT Razred, MIN(Priimek) FROM Dijak GROUP BY Razred;

7. Agregirane funkcije1.2. count Izpišite koliko dijakov je v tabeli Dijak SELECT COUNT(IDDIjak) FROM Dijak; ali SELECT COUNT(*) FROM Dijak; Izpišite število dijakov po razredih: SELECT Razred, COUNT(IDDijak) FROM Dijak GROUP BY Razred;

8. Agregirane funkcije1.3. sum in 1.4. avg // dela le za numerične podatke Izpišite povprečno ceno knjig SELECT Avg(Book.Price) AS PovpCena FROM Book; Izpišite vrednost knjig po kategorijah (type): SELECT Book.Type, Sum(Book.Price) AS VsotaodPrice FROM Book GROUP BY Book.Type;

9. Pogoji, ki se nanašajo na vrednosti agregiranih funkcij • Stavek SELECT ima 2 tipa pogojev: • Pogoji, ki se nanašajo na vrednosti atributov – zapišemo jih za WHERE • Pogoji, ki se nanašajo na vrednosti agregiranih funkcij – zapišemo jih za HAVING • Razlika med WHERE in HAVING: • WHERE filtrira podatke, ki bodo agregirani in ne stolpec, ki ga dobimo kot rezultat agregiranje funkcije; • HAVING filtrira vrstice po izvedbi agregirane funkcije in se sklicuje ravno na stolpec, ki je rezultat funkcije. Primer: izpišite kategorije knjig, ki imajo nadpovprečno ceno SELECT Book.Type, Avg(Book.Price) AS PovprečjeodPrice FROM Book GROUP BY Book.Type HAVING (((Avg(Book.Price))>(select avg(Book.price) From Book)));

10. Osnovne funkcije za delo z nizi2.1. združevanje nizov (konkatenacija) • SQL92 – operator || • MS Access – operator & • mySQL – funkcija CONCAT Izpišite priimke in imena dijakov, ločene z vejico SQL92 SELECT Dijak.Priimek || ‘, ‘ || Dijak.Ime AS Kandidat FROM Dijak; Access SELECT Dijak.Priimek & ‘, ‘ & Dijak.Ime AS Kandidat FROM Dijak; mySQL SELECT CONCAT(Dijak.Priimek,’, ‘,Dijak.Ime) AS Kandidat FROM Dijak;

11. Osnovne funkcije za delo z nizi2.2. male/velike črke • SQL92 – funkcija LOWER(atr) | UPPER(atr) • MS Access – funkcija LCase(atr) | UCase(atr) • mySQL – dela oboje: LOWER(atr) / LCase(atr) | UPPER(atr) / UCase(atr) Izpišite razrede za malimi črkami in priimke dijakov z velikimi. SQL92 SELECT LOWER(Dijak.Razred) AS Razred, UPPER(Dijak.Priimek) As Priimek FROM Dijak; Access SELECT LCase(Dijak.Razred) AS Razred, UCase(Dijak.Priimek) AS Priimek FROM Dijak;

12. Osnovne funkcije za delo z nizi2.3. podniz niza • SQL92 – funkcija SUBSTR (atr FROM pozicija FOR dolžina) • MS Access – Left(atr,dolžina) | Right(atr,dolžina) | Mid(atr,začetek,dolžina) • mySQL – upošteva standard SQL92 Izpišite začetnico imena, piko in priimek dijaka kot en niz. SQL92 SELECT SUBSTR(Dijak.Ime FROM 1 FOR 1) || ‘. ‘ || Dijak.Priimek As Kandidat FROM Dijak; Access SELECT Left(Dijak.Ime,1) & ‘. ‘ & Dijak.Priimek AS Kandidat FROM Dijak;

13. Funkcije za konverzijo3.1. pretvorbe med tipi • SQL92 – funkcija CAST (atr AS tip) • MS Access – za vsak tip ena funkcija: CBool(atr), CByte(atr) • CCur(atr), CDate(atr),CDbl(atr),CDec(atr),CInt(atr),CLng(atr),CStr(atr), … • mySQL – upošteva standard SQL92 Izpišite priimek in datum rojstva dijaka kot en niz znakov. SQL92 SELECT Dijak.Priimek || ‘ ‘ || CAST(Dijak.Rojen AS CHAR(10)) As Kandidat FROM Dijak; Access SELECT Dijak.Primek & ‘ ‘ & CStr(Dijak.Rojen) AS Kandidat FROM Dijak;

14. Funkcije za konverzijo3.2. izločanje dela datumske vrednosti • SQL92 – funkcija EXTRACT (polje FROM atr); polje = Year | Month | Day | Hour | … • MS Access – DatePart(polje,atribut); polje = "yyyy“ |"m“ | "d“ ali Day(atr), Month(atr), Year(atr) • mySQL – upošteva standard SQL92 Izpišite priimke in imena dijakov, rojenih oktobra. SQL92 SELECT Dijak.Priimek,Dijak.Ime, EXTRACT(MONTH FROM Dijak.Rojen)AS Mesec_R FROM Dijak WHERE EXTRACT(MONTH FROM Dijak.Rojen)=10; Access SELECT Dijak.Priimek, Dijak.Ime, MONTH(Dijak.Rojen) AS Mesec_R FROM Dijak WHERE MONTH(Dijak.Rojen)=10;

15. Naloge – prvi del Bazo sestavljajo tabele Knjiga,JePrebrala in Oseba.Strukture tabel opisujejo relacijske sheme • Knjiga(ISBN:N, Ime_knjige:A40) • JePrebrala(EMSO:A13, ISBN:N, Datum:D) • Oseba(EMSO:A13, Priimek:A20, Ime:A10, Naslov:A30, Kraj:A20) Napišite SQL ukaz, ki: - v tabelo JePrebrala.db doda zapis EMSO=0101988112112, ISBN=2, Datum=5.4.2000; - v tabeli Knjigaimena vseh knjig spremeni v velike črke; - izpiše priimek in imena oseb, ki so prebrale vsaj eno knjigo februarja l. 2000; • - izpiše imena knjig, ki vsebujejo besedo vojna (denimo 'Druga svetovna vojna', 'Vojna in mir', …); • - izbriše podatke o osebah, ki so prebrale knjigo 'Vojna in mir‘; • - izpiše priimek osebe, ki je prva po abecedi; • - za vsako osebo izpiše priimek, ime in koliko knjig je prebrala ta oseba; • - za vsako osebo izpiše priimek, ime in koliko različnih knjig je prebrala ta oseba; • - izpiše priimke oseb, ki niso prebrale knjige 'Vojna in mir‘;

16. Naloge – prvi del (nad.) • - izpiše abecedno urejen seznam vseh knjig. Oblika izpisa: (ISBN) Ime_knjige BAZA_KNJIG (123) NAVIHANČKI (492) VOJNA IN MIR …. • - izpiše imena knjig, ki jih je prebralo vsaj 10 oseb; • - izpiše imena knjig, ki so jih prebrale vse osebe; • - izpiše priimke oseb, ki niso prebrale nobene knjige; • - izpiše koliko oseb je prebralo knjig 'Vojna in mir‘; • - v tabeli Knjiga zmanjša ISBN knjige 'MATEMATIKA 4' za 3; • - izpiše imena knjig, katerih ISBN je manjši od 100; • - v tabelo Oseba.dbdoda zapis EMSO=0101980111111, Priimek=Mlinar, Ime=Miha, Naslov=Vegova 1, Kraj=Ljubljana; • - izpiše imena knjig, ki jih je prebral 'Mlinar Miha‘; • - izbriše vse podatke o osebah iz Ljubljane; • - izpiše imena knjig, ki jih ni prebral 'Mlinar Miha‘; • - izpiše imena knjig, ki jih je prebral le 'Mlinar Miha‘; • - izpiše imena knjig, ki so jih prebrale vse osebe razen 'Mlinar Miha‘; • - izpiše koliko knjig je prebral 'Mlinar Miha' v letih od 1995 do 1998; • - izpiše ime nazadnje prebrane knjige.

17. Naloge – drugi del Bazo sestavljajo tabele Stranka, Hotel, Počitnice, Cenik. Strukture tabel opisujejo relacijske sheme: • Stranka(Sifra_stranke:N, Priimek_stranke:A20, Ime_stranke:A10, Kraj_stranke:A20) • Hotel(Sifra_hotela:N, Ime_hotela:A20, Št_zvezdic:N, Kraj_hotela:A20) • Počitnice(Sifra_stranke:N, Sifra_hotela:N, Datum_od:D, Datum_do:D) • Cenik(Sifra_hotela:N, Oznaka_storitve:A3, Cena:N) Napišite izraz relacijske algebre/SQL, ki vrne: • - priimke in imena strank, ki so bile na počitnicah v Portorožu; • - imena hotelov, ki so bili (vsaj delno) zasedeni aprila 1998; • - imena hotelov, v katerih so vse storitve dražje od 20000; • - priimke in imena strank, ki so bile na počitnicah le v hotelih s petimi zvezdicami; • - imena in priimke strank, ki so bile na počitnicah v hotelu, ki je v istem kraju kakor stranka; • - priimke in imena strank, ki so vedno letovale v Portorožu; • - imena hotelov, v katerih so letovale vse stranke iz Ljubljane;

18. Naloge – drugi del (nad.) • - imena hotelov, v katerih so letovale le stranke iz Ljubljane; • - imena hotelov, ki niso bili zasedeni januarja 1999; • - priimke in imena strank, ki so bile na počitnicah na Bledu in na Rogli ( v obeh krajih); • - priimke in imena strank, ki so bile na počitnicah bodisi na Bledu ali na Rogli ( vsaj v enem kraju). • Napišite SQL ukaz , ki: • - vrne priimke in imena strank, ki so bile na počitnicah v Portorožu; izpis naj bo urejen po priimkih strank.; • - vrne imena hotelov, ki so bili (vsaj delno) zasedeni aprila 1998; • - vrne koliko oseb je letovalo v posameznem hotelu. izpis naj bo urejen po imenih hotelov; • - vrne povprečno ceno storitev hotelov; • - hotelom s petimi zvezdicami poveča cene za 10%; • - vrne koliko strank iz Ljubljane je letovalo v Bernardinu; • - vrne imena in priimke Ljubljančanov, ki so letovali le v Brenardinu; • - izbriše podatke o počitnicah za leto 1980;

19. Naloge – prvi del Podane so relacije: obiskuje, prodaja in najraje. Sh(obiskuje)=Obiskuje(Otrok:A10,Slaščičarna:A20) Sh(prodaja)=Prodaja(Slaščičarna:A20,Sladoled:A20) Sh(najraje)=Najraje(Otrok:A10,Sladoled:A20) Podatki v relacijah so:

20. Vprašanja: Napišite izraz relacijske algebre, ki vrne: • imena otrok, ki zahajajo v slaščičarno Zmajček; • s katerim sladoledom lahko postrežejo v slaščičarni Slon; • s katerim sladoledom se lahko posladka Jana; • imena otrok, ki obiskujejo slaščičarni Zmajček in Slon (obe) • imena otrok, ki zahajajo samo v slaščičarno Zmajček; • v katerih slaščičarnah prodajajo Juretov najljubši sladoled; • imena otrok, ki obiskujejo le slaščičarno Slon; • v katero slaščičarno zahajajo vsi otroci; • v kateri slaščičarni ponujajo vse sladolede; • kateri otrok zahaja v vse slaščičarne; • kateri otrok ne hodi v slaščičarno Slon; • katere sladolede ne strežejo pri Slonu;

21. Naloge – drugi del Podane so relacije: izdelek, dobavitelj, račun, stranka in vrstica_računa. Sh(izdelek)=Izdelek(Sifra_i:N, Ime_i:A20,Cena:N, Sifra_d:N) Sh(dobavitelj)=Dobavitelj(Sifra_d:N, Ime_d:A20, Kraj_d:A20) Sh(račun)=Račun(Stev_r:N, Sifra_s:N, Datum:D) Sh(stranka)=Stranka(Sifra_s:N, Ime_s:A20, Naslov:A30, Kraj_s:A20) Sh(vrstica_računa)=Vrstica_računa(Stev_r:N,Zap_st_vrstice:N, Sifra_i:N, St_kos:N) • Vprašanja Napišite izraz relacijske algebre, ki vrne: • številke računov, ki so bili izstavljeni leta 1998; • imena strank, s katerimi smo poslovali leta 1998; • imena izdelkov, ki jih je naročila stranka 'SSER‘; • imena strank, s katerimi smo poslovali samo leta 1998; • imena strank, ki so vedno naročale vsaj 5 kosov nekega izdelka; • imena izdelkov dobaviteljev iz Ljubljane; • imena strank, ki so naročile izdelke cenejše od 10000; • imena strank, ki so vedno naročale izdelke cenejše od 10000; • imena strank, ki nikoli niso naročile izdelke cenejše od 10000; • imena strank, ko so januarja ali marca l. 1998 kupovale izdelke JUB-a; • imena strank, ko so januarja in marca l. 1998 kupovale izdelke JUB-a; • imena izdelkov, ki niso bili nikoli prodani.