Evo primer jednog problema koji se moze javiti i u praksi.
Imamo skolu i nastavnike. Nastavnici predaju neke predmete. Jedan nastavnik moze predavati vise predmeta. Data je tabela NastavniciPredmeti u kojoj pise koji nastavnik moze da predaje koji predmet. Izgleda ovako (u MS SQL):
IF OBject_ID('tempdb..#NastavnikPredmet') IS NOT NULL DROP TABLE #NastavnikPredmet
;
CREATE TABLE #NastavnikPredmet
(Nastavnik varchar(10) NOT NULL
, Predmet varchar(15) NOT NULL
, PRIMARY KEY (Nastavnik, predmet)
)
;
Od ovakve tabele, mogu se zahtevati odgovori na ovakva i slicna pitanja:
Koji nastavnici predaju biologiju i hemiju?
Koji nastavnici predaju samo biologiju i hemniju, i ni jedan drugi predmet?
Koji nastavnici predaju sve predmete koji su zastupljeni u skoli?
Unesimo nekoliko redova za testiranje:
INSERT INTO #NastavnikPredmet VALUES ('Aca','Matematika')
INSERT INTO #NastavnikPredmet VALUES ('Aca','Biologija')
INSERT INTO #NastavnikPredmet VALUES ('Aca','Hemija')
INSERT INTO #NastavnikPredmet VALUES ('Aca','Fizika')
INSERT INTO #NastavnikPredmet VALUES ('Aca','Istorija')
INSERT INTO #NastavnikPredmet VALUES ('Aca','Geografija')
INSERT INTO #NastavnikPredmet VALUES ('Marta','Biologija')
INSERT INTO #NastavnikPredmet VALUES ('Maca','Matematika')
INSERT INTO #NastavnikPredmet VALUES ('Maca','Biologija')
INSERT INTO #NastavnikPredmet VALUES ('Maca','Hemija')
INSERT INTO #NastavnikPredmet VALUES ('Maca','Fizika')
INSERT INTO #NastavnikPredmet VALUES ('Peca','Istorija')
INSERT INTO #NastavnikPredmet VALUES ('Peca','Geografija')
INSERT INTO #NastavnikPredmet VALUES ('Peca','Matematika')
INSERT INTO #NastavnikPredmet VALUES ('Ceca','Biologija')
INSERT INTO #NastavnikPredmet VALUES ('Ceca','Geografija')
INSERT INTO #NastavnikPredmet VALUES ('Laza','Matematika')
INSERT INTO #NastavnikPredmet VALUES ('Laza','Biologija')
INSERT INTO #NastavnikPredmet VALUES ('Laza','Hemija')
INSERT INTO #NastavnikPredmet VALUES ('Laza','Fizika')
INSERT INTO #NastavnikPredmet VALUES ('Laza','Istorija')
INSERT INTO #NastavnikPredmet VALUES ('Laza','Geografija')
INSERT INTO #NastavnikPredmet VALUES ('Goca','Biologija')
INSERT INTO #NastavnikPredmet VALUES ('Goca','Hemija')
INSERT INTO #NastavnikPredmet VALUES ('Juca','Hemija')
INSERT INTO #NastavnikPredmet VALUES ('Juca','Fizika')
INSERT INTO #NastavnikPredmet VALUES ('Juca','Biologija')
INSERT INTO #NastavnikPredmet VALUES ('Pera','Istorija')
INSERT INTO #NastavnikPredmet VALUES ('Pera','Geografija')
Pokusajmo da odgovorimo na postavljena pitanja.
1) Koji nastavnici predaju biologiju i hemiju?
Mozemo da pocnemo ovako:
SELECT
Nastavnik, Predmet
FROM #NastavnikPredmet
WHERE Predmet In ('Biologija','Hemija');
Dobicu sve nastavnike koji predaju biologiju, ili hemiju, ili oba predmeta:
Nastavnik Predmet
---------- ---------------
Aca Biologija
Aca Hemija
Ceca Biologija
Goca Biologija
Goca Hemija
Juca Biologija
Juca Hemija
Laza Biologija
Laza Hemija
Maca Biologija
Maca Hemija
Marta Biologija
(12 row(s) affected)
Posto me interesuju samo oni koji predaju oba predmeta, trebalo bi nekako da eliminisem one koji predaju samo jedan predmet? Moze ovako:
SELECT
Nastavnik, COUNT(Predmet)
FROM #NastavnikPredmet
WHERE Predmet In ('Biologija','Hemija')
GROUP BY Nastavnik
HAVING COUNT(Predmet)=2;
sto daje rezultat:
Nastavnik
---------- -----------
Aca 2
Goca 2
Juca 2
Laza 2
Maca 2
(5 row(s) affected)
Interesantno je da COUNT() u SELECT delu nije ni potreban. Isto tako, COUNT(Predmet) se moze zameniti sa COUNT(*), tako da konacan kveri moze da izgleda ovako:
SELECT
Nastavnik
FROM #NastavnikPredmet
WHERE Predmet IN ('Biologija','Hemija')
GROUP BY Nastavnik
HAVING COUNT(*)=2
2) Koji nastavnici predaju samo biologiju i hemiju, i ni jedan drugi predmet?
Iz pitanja vidimo da se traze nastavnici koji predaju tacno dva predmeta, i ta dva predmeta su bas Biologija i Hemija. Mogu da podelim posao u dva dela:
- (1), inadjem sve nastavnike koji predaju tacno dva predmeta
- (2) nadjem nastavnike koji predaju biologiju i hemiju
Nastavnici koji predaju tacno biologiju i hemiju i nijedan drugi predmet su oni koji se nalaze u rezultujucem skupu za korake (1) i (2).Idemo redom.
Tacno dva predmeta predaju sldeci nastavnici:
SELECT
Nastavnik
FROM #NastavnikPredmet
GROUP BY Nastavnik
HAVING COUNT(Predmet) = 2;
Nastavnik
----------
Ceca
Goca
Pera
(3 row(s) affected)
Na pocetku price smo pokazali kako se nalaze nastavnici koji predaju biologiju i hemiju:
SELECT
Nastavnik
FROM #NastavnikPredmet
WHERE Predmet In ('Biologija','Hemija')
GROUP BY Nastavnik
HAVING COUNT(*)=2
Nastavnik
----------
Aca
Goca
Juca
Laza
Maca
(5 row(s) affected)
Nastavnici koji se nalaze na obe liste su oni koji predaju samo biologiju i hemiju i nista vise. U nasem slucaju to je Goca. Posto zelimo rezultat u jednom kveriju, moze ovako:
SELECT
Nastavnik
FROM #NastavnikPredmet
WHERE Nastavnik IN (
SELECT
Nastavnik
FROM #NastavnikPredmet
WHERE Predmet In ('Biologija','Hemija')
GROUP BY Nastavnik
HAVING COUNT(Premet)=2
)
GROUP BY Nastavnik
HAVING COUNT(Predmet) = 2;
i rezultat je naravno
Nastavnik
----------
Goca
(1 row(s) affected)
3) Koji nastavnici predaju sve predmete koji su zastupljeni u skoli?
Do sada smo uocili da se puno toga moze resiti brojanjem i poredkjenjem izbrojanih vrednosti. Svaki nastavnik predaje N predmeta. Ako prebrojimo predmete koji se predaju u skoli, dobijemo broj K. Oni nastavnici koji predaju tacno K predmeta predaju sve predmete koji su zastupljeni u skoli.
Evo koliko predmeta predaje koji nastavnik:
SELECT
Nastavnik, COUNT(*) AS BrojPredmeta
FROM #NastavnikPredmet
GROUP BY Nastavnik;
Nastavnik BrojPredmeta
---------- ------------
Aca 6
Ceca 2
Goca 2
Juca 3
Laza 6
Maca 4
Marta 1
Peca 3
Pera 2
(9 row(s) affected)
Ukupan broj predmeta koji se predaje u skoli:
SELECT COUNT(*)
FROM (SELECt DISTINCT predmet
FROM #NastavnikPredmet
) AS X
-----------
6
(1 row(s) affected)
Ako pogledamo listu nastavnika i koliko predmeta ko predaje, vidimo da Aca i Laza predaju po 6 predmeta. Posto s eu skoli predaje ukupno 6 predmeta, onda mora biti da Aca i Laza predaju ama bas svaki od tih sest predmeta. Sada to sastavimo zajedno i dobijemo ovakav kveri:
SELECT
Nastavnik, COUNT(*) AS BrojPredmeta
FROM #NastavnikPredmet
GROUP BY Nastavnik
HAVING COUNT(*) = (SELECT COUNT(*)
FROM (SELECt DISTINCT predmet
FROM #NastavnikPredmet
) AS X
)
Nastavnik BrojPredmeta
---------- ------------
Aca 6
Laza 6
(2 row(s) affected)
Ovo je bio prikaz interesantnih stvari koje s emogu postici ako se obicnim brojanjem