BAZE PODATAKA
SQL Upiti – Izvanredni studenti
BAZE PODATAKA izvanredni studenti SQL upiti
Listopad 2010.
1
BAZE PODATAKA
1.
SQL Upiti – Izvanredni studenti
Dohvat podataka – SQL naredba SELECT Često korišteni oblik: SELECT
FROM WHERE ORDER BY Primjeri: a. Dohvat imena i prezimena svih studenata: SELECT ime, prezime FROM student b. Dohvat svih atributa svih studenata: SELECT * FROM student (znak * označava sve atribute u tablici) c. Dohvat imena i prezimena studenata koji su rođeni u Splitu: SELECT ime, prezime FROM student WHERE pbrRod = 21000 d. Dohvat naziva predmeta koji imaju dva sata predavanja: SELECT naziv_pred FROM predmet WHERE sati_pred = 2
2.
Relacijski i logički operatori < > <= >= = <>
manje veće manje ili jednako veće ili jednako jednako različito
AND logičko i OR logičko ili NOT logičko ne Primjeri: a. Dohvat predmeta koji imaju dva ili više sata laboratorijskih vježbi: SELECT naziv_pred FROM predmet WHERE sati_lab >= 2 b. Dohvat predmeta koji imaju dva sata predavanja i dva sata auditornih vježbi: SELECT naziv_pred FROM predmet WHERE sati_pred = 2 AND sati_av = 2 c. Dohvat studenata koji su rođeni u Dubrovniku ili Splitu: SELECT * FROM student WHERE pbrRod = 20000 OR pbrRod = 21000
Listopad 2010.
2
BAZE PODATAKA
SQL Upiti – Izvanredni studenti
d. Dohvat studenata koji nisu rođeni u Dubrovniku ili Splitu: SELECT * FROM student WHERE pbrRod <> 20000 AND pbrRod <> 21000 ili SELECT * FROM student WHERE NOT (pbrRod = 20000 OR pbrRod = 21000) 3.
Rad za znakovnim nizovima Operator sličnosti (LIKE) omogućava bilo koju kombinaciju slijedećih zamjenskih (wildcard) znakova i kombinaciju sa slovima:
* - zamjenjuje bilo koju kombinaciju znakova
[znakovi] - zamjenjuje jedan od znakova koji se nalaze unutar zagrada
[^znakovi] - zamjenjuje jedan od znakova koji se NE nalaze unutar zagrada (u Access-u se koristi umjesto ovoga NOT LIKE [znakovi])
[a1-a2] - zamjenjuje bilo koji znak iz intervala a1-a2
? - zamjenjuje bilo koji znak
\ - ukida specijalno značenje znaka ispred kojeg se pojavi (koristi se za ukidanje specijalnog značenja za znakove * [ ] ? \)
Primjeri: a. Dohvat predmeta koji u nazivu imaju riječ program: SELECT * FROM predmet WHERE naziv_pred LIKE '*program*' b. Dohvat studenata kojima prezime počinje sa slovom između I i P: SELECT ime, prezime FROM student WHERE prezime LIKE '[I-P]*' c. Dohvat studenata kojima prezime završava sa 'ić': SELECT ime, prezime FROM student WHERE prezime LIKE '*ić' d. Dohvat naziva svih mjesta koja u nazivu nemaju slova p, c i g: SELECT naziv FROM mjesto WHERE naziv NOT LIKE '*[pcg]*' 4.
Rad za datumima i vremenom Funkcije za rad sa datumima:
NOW() – trenutno vrijeme dobiveno od operacijskog sustava. Evaluira se kao vrijeme 5.11.2002. 10:30:01 ako je današnji datum 5. studeni 2002. i vrijeme je 10:30:01. DATE() – trenutni datum dobiven od operacijskog sustava. Evaluira se kao '5.11.2002' ako je današnji datum 5. studeni 2002.
Listopad 2010.
3
BAZE PODATAKA
SQL Upiti – Izvanredni studenti
DAY() – vraća dan od zadanog datuma MONTH() – vraća mjesec od zadanog datuma YEAR() – vraća godinu od zadanog datuma DATESERIAL(, , ) – evaluira datum iz 3 INT vrijednosti (godina, mjesec, dan) Npr. DATESERIAL(2002, 3, 21) je datumska vrijednost 21.03.2002. WEEKDAY() – redni broj dana u tjednu za zadani datum (1-nedjelja) Npr. WEEKDAY(DATE()) vraća 2 ako je danas ponedjeljak
Primjeri: a. Dohvat studenata koji su rođeni na datum 11.05.1981: SELECT ime, prezime FROM student WHERE datRod = #1981-05-11# b. Dohvat studenata koji su rođeni 1980. godine: SELECT ime, prezime FROM student WHERE YEAR(datRod) = 1980 c. Dohvat studenata kojima je danas rođendan: SELECT ime, prezime FROM student WHERE DAY(datRod) = DAY(DATE()) AND MONTH(datRod) = MONTH(DATE()) d. Upit koji će dohvatiti ime, prezime i starost studenata u danima: SELECT ime, prezime, DATE() - datRod AS starost FROM student (kada se koriste funkcije možemo pomoću ključne riječi AS stupci dati proizvoljno ime, inače će MS Access dati svoje generičko) e. Dohvat ispita održanih u razdoblju od 01.01.2000. do 05.02.2001.: SELECT * FROM ispit WHERE dat_ispit >= #2000-01-01# AND dat_ispit <= #2001-02-05# ili SELECT * FROM ispit WHERE dat_ispit BETWEEN #2000-01-01# AND #2001-02-05# 5.
Rad sa NULL vrijednostima NULL je specijalna vrijednost koja govori da atribut u retku nije popunjen. NULL treba razlikovati od praznog teksta i nule kod brojeva. Atributi kojima je postavljeno ograničenje NOT NULL (Required u dizajneru tablice) ne mogu imati vrijednost NULL. Da li je atribut NULL ne može se provjeriti pomoću znaka jednakosti već je potrebno koristiti ključnu riječ IS. Primjeri: a. Dohvat studenata kojima se ne zna poštanski broj: SELECT ime, prezime FROM student WHERE pbrRod IS NULL
Listopad 2010.
4
BAZE PODATAKA
SQL Upiti – Izvanredni studenti
b. Dohvat studenata kojima se zna poštanski broj: SELECT ime, prezime FROM student WHERE pbrRod IS NOT NULL
6.
Sortiranje rezultata upita Na kraju svakog upita može se staviti ORDER BY dio koji sortira rezultate upita po zadanom atributu. Primjeri: a. Dohvat svih studenata poredanih po prezimenu: SELECT ime, prezime FROM student ORDER BY prezime b. Dohvat svih studenata poredanih po prezimenu obrnutim redoslijedom (Ž-A): SELECT ime, prezime FROM student ORDER BY prezime DESC c. Dohvat svih studenata poredanih prvo po prezimenu pa onda po imenu: SELECT ime, prezime FROM student ORDER BY prezime, ime d. Dohvat svih studenata poredanih prvo po prezimenu pa onda po imenu ali obrnutim redoslijedom (Ž-A): SELECT ime, prezime FROM student ORDER BY prezime DESC, ime DESC e. Dohvat svih studenata poredanih od najmlađeg do najstarijeg: SELECT ime, prezime FROM student ORDER BY datRod DESC
7.
Dodaci SELECT naredbi SELECT naredba se može proširiti dodatnim ključnim riječima: DISTINCT - bez ponavljanja rezultata TOP - uzima samo n prvih redaka INTO – sprema rezultat u novu tablicu Primjeri: a. Dohvat imena koja se pojavljuju u tablici student sortiranih uzlazno: SELECT DISTINCT ime FROM student ORDER BY ime b. Dohvat pet najstarijih studenata: SELECT TOP 5 * FROM student ORDER BY datRod c. Dohvat pet najmlađih studenata: SELECT TOP 5 * FROM student ORDER BY datRod DESC d. Kreiranje tablice sa studentima iz Splita: SELECT * INTO splicani FROM student WHERE pbrRod = 21000
Listopad 2010.
5
BAZE PODATAKA
SQL Upiti – Izvanredni studenti
8. Spajanje tablica: Najčešće korišteni oblik spajanja tablica: SELECT FROM ON INNER JOIN – odabiru se samo oni zapisi iz obe tablice koji imaju jednake vrijednosti odabranih atributa. student stud_ID 1234 1422 1765 1283
ime_prez pbr_rod Pero Perić 31000 Ive Marić 22000 Mia Koch 10000 Ana Sršen 21000
mjesto pbr 21000 31000 23000 51000
naziv Split Osijek Zadar Rijeka
rezultat stud_ID ime_prez pbr_rod naziv 1234 Pero Perić 31000 Osijek 1283 Ana Sršen 21000 Split
SELECT student.*, naziv FROM student INNER JOIN mjesto ON student.pbr_rod = mjesto.pbr ili SELECT student.*, naziv FROM student, mjesto WHERE student.pbr_rod = mjesto.pbr LEFT JOIN – odabiru se svi zapisi iz lijeve tablice te oni zapisi iz desne tablice koji imaju odgovarajuće vrijednosti odabranih atributa (ukoliko ne postoje, u rezultatu će vrijednosti atributa iz desne tablice biti NULL). student stud_ID ime_prez pbr_rod 1234 Pero Perić 31000 1422 Ive Marić 22000 1765 Mia Koch 10000 1283 Ana Sršen 21000
mjesto pbr 21000 31000 23000 51000
naziv Split Osijek Zadar Rijeka
rezultat stud_ID 1234 1286 1422 1756
ime_prez pbr_rod Pero Perić 31000 Ana Sršen 21000 Ive Marić 22000 Mia Koch 10000
naziv Osijek Split NULL NULL
SELECT student.*, naziv FROM student LEFT JOIN mjesto ON student.pbr_rod = mjesto.pbr RIGHT JOIN – odabiru se svi zapisi iz desne tablice te oni zapisi iz lijeve tablice koji imaju odgovarajuće vrijednosti odabranih atributa (ukoliko ne postoje, u rezultatu će vrijednosti atributa iz lijeve tablice biti NULL). student stud_ID ime_prez pbr_rod 1234 Pero Perić 31000 1422 Ive Marić 22000 1765 Mia Koch 10000 1283 Ana Sršen 21000
mjesto pbr 21000 31000 23000 51000
naziv Split Osijek Zadar Rijeka
rezultat stud_ID ime_prez pbr_rod 1283 Ana Sršen 21000 1234 Pero Perić 31000 NULL NULL 23000 NULL NULL 51000
naziv Split Osijek Zadar Rijeka
SELECT student.*, naziv FROM student Listopad 2010.
6
BAZE PODATAKA
SQL Upiti – Izvanredni studenti
RIGHT JOIN mjesto ON student.pbr_rod = mjesto.pbr Primjeri: a. Dohvat imena, prezimena i naziva mjesta u kojem je student rođen (ako je mjesto rođenja nepoznato preskočiti zapis): SELECT ime, prezime, naziv FROM student INNER JOIN mjesto ON student.pbrrod = mjesto.pbr ili SELECT ime, prezime, naziv FROM student, mjesto WHERE student.pbrrod = mjesto.pbr b. Dohvat imena, prezimena svih studenata, te naziva mjesta u kojem su rođeni (dohvatiti i studente kojima je mjesto rođenja nepoznato), poredanih po prezimenu i imenu: SELECT ime, prezime, naziv FROM student LEFT JOIN mjesto ON student.pbrrod = mjesto.pbr ORDER BY prezime, ime c. Dohvat datuma održavanja ispita i naziva predmeta poredanih od zadnjeg prema prvom: SELECT dat_ispit, naziv_pred FROM ispit LEFT JOIN predmet ON ispit.pred_ID = predmet.pred_ID ORDER BY dat_ispit DESC d. Dohvat imena, prezimena, naziva predmeta i datuma polaganja ispita za sve studente koji ispit položili ocjenom izvrstan 5 (rezultati trebaju biti poredani po prezimenu i imenu studenta): SELECT student.ime, student.prezime, predmet.naziv_pred, ispit.dat_ispit FROM (ispit LEFT JOIN predmet ON ispit.pred_ID = predmet.pred_ID) LEFT JOIN student ON ispit.stud_ID = student.stud_ID WHERE ocjena = 5 ORDER BY prezime, ime Napomena: Ako se spajaju više od dvije tablice, u Access-u je potrebno staviti zagrade tako da se FROM dio upita smatra kao prva tablica, a posljednji LEFT/RIGHT/INNER JOIN kao druga e. Dohvat imena i prezimena studenata, te naziva predmeta koje su položili: SELECT student.ime, student.prezime, predmet.naziv_pred FROM (ispit INNER JOIN student ON ispit.stud_ID = student.stud_ID) INNER JOIN predmet ON ispit.pred_ID = predmet.pred_ID WHERE ocjena > 1 9. Agregatne funkcije: MIN – vraća najmanju vrijednost zadanog atributa MAX – vraća najveću vrijednost zadanog atributa COUNT – vraća ukupan broj zapisa SUM – izračunava zbroj vrijednosti zadanog atributa
Listopad 2010.
7
BAZE PODATAKA
SQL Upiti – Izvanredni studenti
AVG – izračunava prosječnu vrijednost zadanog atributa Primjeri: a. Dohvat najnovijeg ispitnog roka: SELECT MAX(dat_ispit) FROM ispit b. Prebrojavanje predmeta koji imaju dva sata predavanja i jedan sat labosa: SELECT COUNT(*) FROM predmet WHERE sati_pred = 2 AND sati_lab = 1 c. Prebrojavanje prezimena koja završavju na 'ić': SELECT COUNT(prezime) FROM student WHERE prezime LIKE '*ić' d. Izračun ukupnog broja predavanja, labosa i auditornih vježbi na svim predmetima: SELECT SUM(sati_pred) AS ukupno_pred, SUM(sati_lab) AS ukupno_lab, SUM(sati_av) AS ukupno_av FROM predmet e. Izračun prosječne ocjene na osnovu svih položenih ispita u bazi: SELECT AVG(ocjena) AS prosjek FROM ispit WHERE ocjena > 1 10. Podupiti: Kao dio SQL upita moguće je iskoristiti druge SQL upite koji se upisuju unutar zagrada. Primjeri: a. Dohvat imena i prezimena studenata koji su pali neki ispit: SELECT ime, prezime FROM student WHERE stud_ID IN (SELECT stud_ID FROM ispit WHERE ocjena = 1) b. Dohvat mjesta u kojima nitko od studenata nije rođen: SELECT * FROM mjesto WHERE pbr NOT IN (SELECT pbrRod FROM student WHERE pbrRod IS NOT NULL) Napomena: unutar rezultata podupita ne smiju se nalaziti redci sa NULL vrijednostima, te je stoga dodan uvjet pbrRod IS NOT NULL c. Dohvat predmeta koji imaju broj sati predavanja iznad prosjeka: SELECT naziv_pred FROM predmet WHERE sati_pred > (SELECT AVG(sati_pred) FROM predmet) d. Dohvat mjesta iz kojih dolazi 2 ili više studenata: SELECT * FROM mjesto WHERE (SELECT COUNT(*) FROM student WHERE pbrRod = mjesto.pbr) >= 2 e. Dohvat svih mjesta sa brojem studenata koji dolaze iz njih: SELECT pbr, naziv, (SELECT COUNT(*) FROM student WHERE pbrRod = pbr) AS broj FROM mjesto
Listopad 2010.
8
BAZE PODATAKA
SQL Upiti – Izvanredni studenti
11. Grupiranje: Grupiranje je vrlo korisno u kombinaciji s agregatnim funkcijama. Svi atributi koji se nalaze u SELECT listi, a koji nisu unutar agregatnih funkcija, moraju biti navedeni u GROUP BY listi. U GROUP BY listi dozvoljeno je koristiti i one atribute koji se ne nalaze u SELECT listi. U HAVING dijelu naredbe smiju se koristiti samo izrazi koji se nalaze u GROUP BY listi, te agregatne funkcije. Primjeri: a. Izračun prosjeka ocjena studenata rođenih iste godine (u rezultatu upita trebaju biti dva stupca, godina i prosjek): SELECT YEAR(datRod) AS godina, AVG(ocjena) AS prosjek FROM student, ispit WHERE student.stud_ID=ispit.stud_ID GROUP BY YEAR(datRod) b. Dohvat imena i prezimena studenata sa njihovim prosjekom ocjena (poredati rezultate po prosjeku od većeg prema manjem): SELECT ime, prezime, AVG(ocjena) AS prosjek FROM ispit LEFT JOIN student ON student.stud_ID = ispit.stud_ID WHERE ocjena > 1 GROUP BY ispit.stud_ID, ime, prezime ORDER BY AVG(ocjena) DESC c. Izračun prosjeka ocjena studenata rođenih iste godine (u rezultatu upita trebaju biti dva stupca, godina i prosjek) samo za generacije od 1980. godine: SELECT YEAR(datRod) AS godina, AVG(ocjena) AS prosjek FROM student, ispit WHERE student.stud_ID=ispit.stud_ID GROUP BY YEAR(datRod) HAVING YEAR(datRod) >= 1980 12. Kreiranje nove tablice (naredba CREATE TABLE) Sintaksa: CREATE TABLE ime_tablice ( [NOT NULL], [NOT NULL], [NOT NULL], … [ograničenje1], [ograničenje2], [ograničenje3], …
Listopad 2010.
9
BAZE PODATAKA
SQL Upiti – Izvanredni studenti
) Najčešća ograničenja su postavljanje primarnog i stranog ključa. Ograničenje primarnog ključa: CONSTRAINT PRIMARY KEY() Ograničenje stranog ključa CONSTRAINT FOREIGN KEY() REFERENCES () Primjeri: a. Kreiranje tablice županija sa atributima zup_ID i naziv (zup_ID je primarni ključ): CREATE TABLE zupanija ( zup_ID int NOT NULL, naziv varchar(30) NOT NULL, CONSTRAINT pk_zup_ID PRIMARY KEY(zup_ID) ) b. Kreiranje tablice stipendisti sa atributima stud_ID, ime, prezime, prosjek. Atribut stud_ID treba biti strani ključ koji referencira na tablicu student: CREATE TABLE stipendisti ( stud_ID int, ime varchar(30) NOT NULL, prezime varchar(30) NOT NULL, prosjek float, CONSTRAINT fk_stud_id FOREIGN KEY(stud_ID) REFERENCES student(stud_ID) ) 13. Dodavanje novog zapisa u tablicu (naredba INSERT INTO): Sintaksa: INSERT INTO () VALUES () Atributi kojima je dopušteno sadržavati NULL vrijednost i koji nisu dio relacije koja čuva integritet podataka, nije obvezno navesti u popisu atributa. Tip i redoslijed vrijednosti mora odgovarati tip u redoslijedu navedenih atributa. Pored jednostavnog dodavanja zapisa, u kombinaciji sa podupitom (SELECT naredbom) moguće je dodati više zapisa odjednom. U tom slučaju tip i redoslijed atributa u SELECT-u mora odgovarati tipu i redoslijedu atributa u INSERT dijelu naredbe Naredba INSERT u kombinaciji sa SELECT:
Listopad 2010.
10
BAZE PODATAKA
SQL Upiti – Izvanredni studenti
INSERT INTO () SELECT FROM … Primjeri: f. Unos nove županije: INSERT INTO zupanija(zup_ID, naziv) VALUES (21, 'Splitsko-Dalmatinska') g. Unos novog studenta: INSERT INTO student(stud_id, ime, prezime, pbrRod) VALUES (60001, 'Marko', 'Marković', 21000) h. Unos studenata stipendista (prosjek iznad 4.0) automatski, koristeći podupit za izračun i izdvajanje studenata sa odgovarajućim prosjekom: INSERT INTO stipendisti(stud_ID, ime, prezime, prosjek) SELECT ispit.stud_ID, ime, prezime, AVG(ocjena) AS prosjek FROM ispit LEFT JOIN student ON ispit.stud_ID = student.stud_ID WHERE ocjena > 1 GROUP BY ispit.stud_ID, ime, prezime HAVING AVG(ocjena) >= 4 14. Mijenjanje postojećih zapisa tablici (naredba UPDATE): Sintaksa: UPDATE SET = WHERE Atributa kojima se mijenja vrijednost može biti više (odvajaju se zarezom). Primjeri: a. Postaviti prosjek svim stipendistima na vrijednost 0: UPDATE stipendisti SET prosjek = 0 b. Umanjiti broj sati auditornih vježbi za jedan svim predmetima koji imaju 2 ili više sati laboratorijskih vježbi: UPDATE predmet SET sati_av = sati_av - 1 WHERE sati_lab >= 2 c. Studentima kojima se ne zna poštanski broj rođenja postaviti jmbg na NULL: UPDATE student SET jmbg = NULL WHERE pbrRod IS NULL 15. Brisanje zapisa iz tablice (naredba DELETE): Sintaksa: DELETE FROM WHERE Primjeri: a. Brisanje svih zapisa iz tablice 'zupanija': DELETE FROM zupanija
Listopad 2010.
11
BAZE PODATAKA
SQL Upiti – Izvanredni studenti
b. Brisanje svih studenata kojima nije poznat jmbg: DELETE FROM student WHERE jmbg IS NULL c. Brisanje onih studenata iz tablice 'stipendisti' koji su na bilo kojem ispitu dobili ocjenu manju od vrlodobar 4: DELETE FROM stipendisti WHERE stud_ID IN (SELECT stud_ID FROM ispit WHERE ocjena < 4) 16. Mijenjanje strukture tablice (naredba ALTER TABLE): Naredba ALTER TABLE može se koristiti za dodavanje novih atributa u tablicu, brisanje i izmjenu postojećih atributa, ali i za dodavanje, izmjenu i brisanje ograničenja (ključeva). Sintaksa: ALTER TABLE ADD [NOT NULL] ALTER TABLE DROP COLUMN ALTER TABLE ALTER COLUMN [NOT NULL] ALTER TABLE ADD CONSTRAINT ALTER TABLE DROP CONSTRAINT Primjeri: a. Dodavanje atributa pbrStan u tablicu student: ALTER TABLE student ADD pbrStan int b. Brisanje atributa pbrStan iz tablice student: ALTER TABLE student DROP COLUMN pbrStan c. Brisanje ograničenja primarnog ključa iz tablice zupanija: ALTER TABLE zupanija DROP CONSTRAINT pk_zup_ID d. Promjena tipa atributa zup_id u tablici zupanija u varchar(5) (ne smije biti NULL): ALTER TABLE zupanija ALTER COLUMN zup_id varchar(5) NOT NULL e. Dodavanje ograničenja primarnog ključa (atribut zup_ID) u tablici zupanija: ALTER TABLE zupanija ADD CONSTRAINT pk_zup_ID PRIMARY KEY(zup_ID) 17. Brisanje tablica (naredba DROP): Sintaksa: DROP Primjer: a. Brisanje tablice zupanija: DROP zupanija
Listopad 2010.
12
