Tut4

-- Zadatak 1
SELECT Sum(Nvl(e.commission_pct, 0) * e.salary) "Suma iznosa dodataka na platu", Count(e.commission_pct) "Dodatak prima", Count(*) "Broj uposlenih"
FROM employees e;

-- Zadatak 2
SELECT j.job_title "Naziv posla", d.department_name "Naziv org. jedinice", Count(*) "Broj uposlenih"
FROM employees e, jobs j, departments d
WHERE e.department_id = d.department_id AND j.job_id = e.job_id
GROUP BY j.job_title, d.department_name;

-- Zadatak 3
SELECT Round(Max(e.salary), 6) "Najveca plata", Round(Min(e.salary), 6) "Najmanja plata", Round(Sum(e.salary), 6) "Sumarna plata", Round(Avg(e.salary), 6) "Prosjecna plata"
FROM employees e;

-- Zadatak 4
SELECT j.job_title "Naziv posla", Round(Max(e.salary), 6) "Max plata", Round(Min(e.salary), 6) "Min plata", Round(Avg(e.salary), 6) "Prosjecna plata"
FROM employees e, jobs j
WHERE e.job_id = j.job_id
GROUP BY j.job_title;

-- Zadatak 5
SELECT j.job_title "Naziv posla", Count(e.employee_id) "Broj zaposlenih"
FROM employees e, jobs j
WHERE e.job_id = j.job_id
GROUP BY j.job_title;

-- Zadatak 6
SELECT Count(DISTINCT manager_id) "Broj menadzera"
FROM employees;

-- Zadatak 7
SELECT e.first_name||' '||e.last_name "Naziv zaposlenog", e.salary "Plata", d.department_name "Odjel"
FROM employees e, departments d
WHERE e.department_id = d.department_id AND e.salary < (SELECT Min(a.salary)
                                             FROM employees a, employees b
                                             WHERE a.employee_id = b.manager_id AND a.department_id <> b.department_id);

-- Zadatak 8
SELECT d.department_name "Odjel", l.city "Grad", Count(*) "Broj zaposlenih", Round(Avg(e.salary), 2) "Prosjecna plata"
FROM employees e, departments d, locations l
WHERE e.department_id = d.department_id AND d.location_id = l.location_id
GROUP BY d.department_name, l.city;

-- Zadatak 9
SELECT Sum(Decode(To_Number(To_Char(hire_date, 'yyyy')), 1995, 1, 0)) "1995g",
       Sum(Decode(To_Number(To_Char(hire_date, 'yyyy')), 2002, 1, 0)) "2002g",
       Sum(Decode(To_Number(To_Char(hire_date, 'yyyy')), 1997, 1, 0)) "1997g",
       Sum(Decode(To_Number(To_Char(hire_date, 'yyyy')), 2004, 1, 0)) "2004g",
       Sum(Decode(To_Number(To_Char(hire_date, 'yyyy')), 1995, 1, 2002, 1, 1997, 1, 2004, 1)) "Ukupan broj zaposlenih"
FROM employees;
-- Upit je izmjenjen radi prikazivanja rezultata :)

-- Zadatak 10
SELECT j.job_title "Naziv posla", Decode(e.department_id, 10, Sum(e.salary), 0) "Odjel 10",
                                  Decode(e.department_id, 30, Sum(e.salary), 0) "Odjel 30",
                                  Decode(e.department_id, 50, Sum(e.salary), 0) "Odjel 50",
                                  Decode(e.department_id, 90, Sum(e.salary), 0) "Odjel 90",
                                  Decode(e.department_id, 10, Sum(e.salary), 30, Sum(e.salary), 50, Sum(e.salary), 90, Sum(e.salary), 0) "UKUPNO"
FROM employees e, jobs j
WHERE e.job_id = j.job_id
GROUP BY j.job_title, e.department_id;
/* http://pastebin.com/SAEh3KDZ ?? */