Vežba 6

GRUOP, HAVING, ORDER BY, AGREGATNE FUNKCIJE
1. Prikazati ime i broj odeljenja zaposlenih koji rade u odeljenju 10 ili 20, a kvalifikacija im je VKV.
SELECT ime,Id_odeljenja FROM radnik WHERE (Id_odeljenja=10 OR Id_odeljenja=20) AND Kvalif=N'VKV'
2. Prikazati brojeve odeljenja i srednju platu u svakom od njih. Iz proračuna isključiti analitičare i upravnike. Rezultate urediti po prosečnim primanjima u rastućem redosledu.
SELECT Id_odeljenja, AVG(plata) AS 'Prosecna plata' FROM radnik WHERE posao NOT IN(N'analitičar', N'upravnik') GROUP BY Id_odeljenja ORDER BY AVG(plata) ASC
3.Prikazati brojeve odeljenja i srednju platu u svakom od njih, samo za odeljenja u kojima je srednja plata veća od 2 000.
SELECT Id_odeljenja, round(AVG(plata),2) AS 'Prosecna plata' FROM radnik GROUP BY Id_odeljenja HAVING AVG(plata)>2000
4.  Za svako odeljenje prikazati broj radnika i ukupna primanja. Ne prikazivati ukupna primanja neraspoređenih radnika.
 SELECT Id_odeljenja, COUNT (*) AS [Broj radnika], SUM(plata+ISNULL(premija,0)) AS [Ukupna primanja] FROM RADNIK WHERE Id_odeljenja is NOT NULL GROUP BY Id_odeljenja
5. Ko su najbolje plaćeni radnici u celom preduzeću i u kom broju odeljenja rade?
SELECT ime, Id_odeljenja FROM RADNIK where plata in (select max(plata) from radnik)
INNER JOIN
6. Prikazati imena projekata i broj radnika na njima za sve projekte na kojima radi više od 3 radnika. SELECT Ime_proj, COUNT(*) AS [BROJ RADNIKA] FROM PROJEKAT, UCESCE WHERE PROJEKAT.Id_projekta=UCESCE.Id_projekta GROUP BY Ime_proj HAVING COUNT(*)>3
7. Prikazati idbr, ime, ime projekta i ukupna primanja radnika koji ne učestvuju na projektu izvoz i čija su ukupna primanja manja od 1500. Rezultate urediti po ukupnim primanjima u opadajućem redosledu, pa po imenu projekta u rastućem redosledu.
 SELECT RADNIK.Id_radnika, ime, Ime_proj, plata+ ISNULL(premija,0) AS [UKUPNA PRIMANJA] FROM PROJEKAT, UCESCE, RADNIK WHERE PROJEKAT.Id_projekta=UCESCE.Id_projekta AND RADNIK.Id_radnika = UCESCE.ID_radnika AND Ime_proj <> N'izvoz' and plata + ISNULL(premija,0)

LEFT, RIGHT, FULL JOIN
8. Prikazati nazive odeljenja, ime i posao svakog radnika koji u njima rade, uključujući i radnike koji nisu raspoređeni ni u jednom odeljenju.
a) Upotrebom desnog spajanja - RIGHT JOIN   b) Upotrebom LEFT JOIN
a. SELECT ime_od, ime, posao FROM Odeljenje O RIGHT JOIN Radnik R ON R.Id_odeljenja = O.Id_odeljenja
b. SELECT ime_od, ime, posao FROM Radnik R LEFT JOIN Odeljenje O ON R.Id_odeljenja = O.Id_odeljenja
9. Prikazati imena i poslove radnika, kao i broj i imena projekata na kojima rade samo za radnike koji ne rade ni na jednom projektu.
SELECT RADNIK.Ime, RADNIK.Posao, PROJEKAT.Id_projekta, PROJEKAT.Ime_proj FROM PROJEKAT LEFT OUTER JOIN UCESCE ON PROJEKAT.Id_projekta = UCESCE.Id_projekta RIGHT OUTER JOIN RADNIK ON UCESCE.Id_radnika = RADNIK.Id_radnika WHERE PROJEKAT.Id_projekta IS NULL
10. Prikazati nazive odeljenja, ime i posao svakog radnika koji u njima rade uključujući i radnike koji nisu raspoređeni ni u jednom odeljenju, kao i odeljenja u kojima ne radi ni jedan radnik.
SELECT Ime_od,Ime, Posao FROM RADNIK FULL OUTER JOIN ODELJENJE ON RADNIK.Id_odeljenja = ODELJENJE.Id_odeljenja
11. Prikazati imena radnika i imena projekata na kojima rade uključujući i projekte na kojima ne radi ni jedan radnik, kao i radnike koji ne rade ni na jednom projektu.
SELECT Ime, Ime_proj FROM RADNIK FULL JOIN UCESCE ON RADNIK.Id_radnika = UCESCE.Id_radnika FULL JOIN PROJEKAT ON UCESCE.Id_projekta = PROJEKAT.Id_projekta
12. Prikazati imena radnika i imena njihovih neposrednih rukovodilaca.
SELECT R.Ime AS [Ime radnika], R1.Ime AS [Ime rukovodioca] FROM RADNIK R INNER JOIN RADNIK R1 ON R.Rukovodilac = R1.Id_radnika
INSERT
13. Dodati podatke o novom odeljenju čije je ime Računovodstvo, a broj odeljenja 60. INSERT INTO ODELJENJE VALUES (60, N'Racunovodstvo', NULL, NULL);
14. Kreirati praznu tabelu Penzionisani sa svim atributima kao tabela Radnik.
select * into Penzionisani from radnik where ime='nesto'
U već postojeću tabelu PENZIONISANI, sa svim istim atributima kao i tabela RADNIK, prebaciti sve podatke o zaposlenim pre 20.10.1971.
INSERT INTO PENZIONISANI SELECT * FROM RADNIK WHERE Dat_zap < '1971-10-20'
UPDATE
15. Zaposlenima u odeljenju smeštenom na Novom Beogradu povećati platu 30%.
 UPDATE RADNIK SET plata = plata*1.3 WHERE Id_odeljenja = (SELECT Id_odeljenja FROM ODELJENJE WHERE mesto = 'Novi Beograd')
16. Svim konsultantima smanjiti platu za 25% i ukinuti premiju. UPDATE RADNIK SET Plata = Plata*0.75, Premija = NULL WHERE Id_radnika IN (SELECT Id_radnika FROM UCESCE WHERE Funkcija = 'konsultant');
DELETE
17. Zaposlene čiji je rukovodilac Pavle izbaciti sa svih projekata.
DELETE FROM UCESCE WHERE Id_radnika IN (SELECT Id_radnika FROM RADNIK WHERE Rukovodilac = (SELECT Id_radnika FROM RADNIK WHERE Ime = 'Pavle'))
CASE
18.  Radnicima čija je kvalifikacija KV povećati platu 10%, VKV 5% a VSS smanjiti platu 5%.
 update radnik set plata= case kvalif when 'KV' THEN plata*1.1 when 'VKV' THEN plata*1.05 when 'VSS' THEN plata*0.95 end
 19.  Radnicima koji nemaju premiju dodeliti premiju 150. Radnicima čija je premija manja od 1000 povećati premiju 15% a ostalim radnicima ne menjati premiju.
 update radnik set premija= case when premija is null THEN 150 when premija between 0 and 1000 THEN premija*1.15 else premija end
20. Prikazati ime i posao radnika cija je plata izmedju 1000 i 1500
a) SELECT  ime, posao
FROM   RADNIK  inner JOIN ODELJENJE  ON RADNIK.Id_odeljenja = ODELJENJE.Id_odeljenja where plata between 1000 and 1500
b)SELECT  ime, posao
FROM   RADNIK r inner JOIN ODELJENJE o ON r.Id_odeljenja = o.Id_odeljenja where plata between 1000 and 1500
21. Prikazati imena projekata, sredstva i imena radnika cije ime pocinje slovom a
SELECT PROJEKAT.Ime_proj, PROJEKAT.Sredstva, RADNIK.Ime
FROM PROJEKAT INNER JOIN UCESCE ON PROJEKAT.Id_projekta = UCESCE.Id_projekta INNER JOIN RADNIK ON UCESCE.Id_radnika = RADNIK.Id_radnika
WHERE ime like N'a%'