SQL Select examples part_02

-- COUNT
SELECT COUNT(*) FROM radnik;

SELECT COUNT(DISTINCT sef) broj_sefova
FROM radnik;

-- MIN MAX
SELECT MIN(plt) minimalna_plata, MAX(plt) maximalna_plata
FROM radnik;

-- SUM
SELECT COUNT(*) broj_radnika, SUM(plt) ukupna_mesecna_plata
FROM radnik;

-- AVG
SELECT COUNT(*) broj_radnika, avg(plt) prosecna_plata,
SUM(plt)*12 godisanja_plata
FROM radnik;

-- ROUND
SELECT round(avg(plt), 1) "Proseèna plata, 1 dec"
FROM radnik;

-- GROUP BY
SELECT spr, COUNT(mbr), SUM(brc)
FROM radproj
GROUP BY spr;

-- HAVING
SELECT mbr
FROM radproj
GROUP BY mbr
HAVING COUNT(spr)>2;

SELECT mbr, COUNT(spr)
FROM radproj
GROUP BY mbr
HAVING COUNT(spr)>2;

-- ZADATAK 1
SELECT ime, SUM(plt), COUNT(mbr)
FROM radnik
WHERE god < '1-1-1975'
GROUP BY ime
HAVING SUM(plt)>25000;

-- UGNJEZDENI UPITI
SELECT mbr, ime, prz, plt
FROM radnik
WHERE(plt > (SELECT avg(plt) FROM radnik))
ORDER BY plt ASC;

SELECT ime, prz
FROM radnik
WHERE mbr IN(SELECT spr FROM radproj WHERE spr=30);

SELECT mbr, ime, prz
FROM radnik
WHERE mbr IN(SELECT spr FROM radproj WHERE spr=10) AND
mbr NOT IN(SELECT spr FROM radproj WHERE spr=30);

SELECT ime, prz, god
FROM radnik
WHERE god<=ALL(SELECT god FROM radnik);

SELECT ime, prz, god
FROM radnik
WHERE god = (SELECT MIN(god) FROM radnik);

-- SPAJANJE TABELA
SELECT radnik.mbr, ime, prz, plt, brc
FROM radnik, radproj
WHERE spr=10 AND radnik.mbr = radproj.mbr;

SELECT DISTINCT mbr, ime, prz, plt
FROM radnik, projekat
WHERE mbr = ruk;

SELECT mbr, ime, prz, plt
FROM radnik, projekat
WHERE spr = 10 AND ruk != mbr;

SELECT mbr, ime, prz, plt
FROM radnik
WHERE mbr != (SELECT ruk FROM projekat WHERE spr=10);

SELECT nap
FROM projekat
WHERE spr IN (SELECT spr FROM radproj
WHERE mbr IN (SELECT mbr FROM radproj
WHERE spr = 60));

SELECT ime, prz, COUNT(spr)
FROM radnik r, projekat p
WHERE r.mbr = p.ruk
GROUP BY r.mbr, ime, prz;

-- PITANJE ZA COUNT(*) na šta se odnosi
SELECT r.mbr, r.ime, r.prz, COUNT(*), SUM(rp.brc)
FROM radnik r, radproj rp
WHERE r.mbr = rp.mbr
GROUP BY r.mbr, r.ime, r.prz;

SELECT ime, prz, COUNT(DISTINCT rp.spr)
FROM radnik r, radproj rp
WHERE r.mbr = rp.mbr
AND r.mbr IN (SELECT ruk FROM projekat)
GROUP BY ime, prz;

-- SAME AS ABOVE
SELECT prz, ime, COUNT(DISTINCT rp.spr) bp FROM
radnik r, radproj rp, projekat p
WHERE r.mbr=rp.mbr AND r.mbr = ruk
GROUP BY prz, ime;

SELECT nap
FROM projekat p, radproj rp
WHERE p.spr = rp.spr
GROUP BY p.spr, nap
HAVING SUM(brc)>15;

SELECT p.spr, nap
FROM projekat p, radproj rp
WHERE p.spr = rp.spr
GROUP BY p.spr, nap
HAVING COUNT(rp.mbr)>2;

SELECT p.spr, p.nap
FROM projekat p, radproj rp
WHERE p.spr = rp.spr
GROUP BY p.spr, p.nap
HAVING avg(rp.brc) > (SELECT avg(brc) FROM radproj);

SELECT p.spr, p.nap
FROM projekat p, radproj rp
WHERE p.spr = rp.spr
GROUP BY p.spr, p.nap
HAVING avg(brc)>=ALL(SELECT avg(brc) FROM radproj GROUP BY spr);

-- VIŠESTRUKA UPOTREBA ISTE TABELE
SELECT r1.ime, r1.prz, r1.plt, p.nap FROM
radnik r1, radnik r2, projekat p, radproj rp
WHERE r1.mbr=rp.mbr AND rp.spr=p.spr AND
p.ruk=r2.mbr AND r1.plt+1000<r2.plt;

SELECT r.mbr, r.ime, r.prz, r.plt
FROM radnik r, radnik r1
WHERE r.plt > r1.plt AND r1.mbr = 40;

-- EXISTS
SELECT ime, prz, god
FROM radnik r
WHERE NOT EXISTS
(SELECT mbr FROM radnik r1
WHERE r1.god<r.god);

SELECT mbr, ime, prz
FROM radnik r
WHERE NOT EXISTS
(SELECT * FROM radproj rp
WHERE r.mbr=rp.mbr AND rp.spr=10);

SELECT mbr, ime, prz
FROM radnik r
WHERE NOT EXISTS
(SELECT * FROM radproj rp WHERE r.mbr=rp.mbr);

SELECT mbr, ime, prz
FROM radnik r
WHERE mbr NOT IN
(SELECT rp.mbr FROM radproj rp
WHERE r.mbr=rp.mbr);

SELECT mbr, ime, prz
FROM radnik r
WHERE NOT EXISTS
(SELECT * FROM projekat WHERE mbr=ruk);

SELECT mbr, ime, prz
FROM radnik r
WHERE mbr NOT IN
(SELECT ruk FROM projekat WHERE mbr=ruk);

SELECT DISTINCT mbr, ime, prz, god
FROM radnik r, projekat p
WHERE r.mbr=p.ruk AND NOT EXISTS
(SELECT mbr FROM radnik r1, projekat p1
WHERE r1.mbr=p1.ruk AND r1.god<r.god);

-- UNION
SELECT mbr, ime, prz FROM radnik
WHERE mbr IN
(SELECT mbr FROM radproj WHERE spr=20)
UNION
SELECT mbr, ime, prz FROM radnik
WHERE plt>(SELECT avg(plt) FROM radnik);

-- UNION ALL
SELECT mbr, ime, prz FROM radnik
WHERE mbr IN
(SELECT mbr FROM radproj WHERE spr=20)
UNION ALL
SELECT mbr, ime, prz FROM radnik
WHERE plt>(SELECT avg(plt) FROM radnik);

-- INTERSECT
SELECT mbr, ime, prz FROM radnik
WHERE prz LIKE 'M%' OR prz LIKE 'R%'
INTERSECT
SELECT mbr, ime, prz FROM radnik
WHERE prz LIKE 'M%' OR prz LIKE 'P%'

-- MINUS
SELECT mbr, ime, prz FROM radnik
WHERE prz LIKE 'M%' OR prz LIKE 'R%'
MINUS
SELECT mbr, ime, prz FROM radnik
WHERE prz LIKE 'M%' OR prz LIKE 'P%'

-- NATURAL
SELECT ime, prz
FROM radnik NATURAL JOIN radproj
WHERE spr=30;

-- INNER
SELECT ime, prz
FROM radnik r INNER JOIN radproj rp
ON r.mbr=rp.mbr
WHERE spr=30;

-- LEFT OUTER
SELECT r.mbr,ime, prz, spr
FROM radnik r LEFT OUTER JOIN radproj rp
ON r.mbr=rp.mbr;

-- RIGHT OUTER
SELECT nvl(rp.mbr, 0) "Mbr radnika", nap
FROM radproj rp RIGHT OUTER JOIN projekat p
ON rp.spr=p.spr;

SELECT nvl(rp.mbr, 0) "Mbr radnika", nap
FROM radproj rp, projekat p
WHERE rp.spr(+)=p.spr;

-- FULL OUTER
SELECT nvl(rp.mbr, 0) "Mbr radnika", nap
FROM radproj rp FULL OUTER JOIN projekat p
ON rp.spr=p.spr;