OBP TUT 4

--1
SELECT SUM(Nvl(commission_pct, 0)) "Suma dodataka na platu", COUNT(commission_pct) "Broj dodataka na platu",
COUNT(*) "Broj uposlenika"
FROM employees;

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

--3
SELECT Round(MAX(salary), 6) "Maksimalna plata", Round(MIN(salary), 6) "Minimalna plata",
Round(SUM(salary), 6) "Sumarna plata", Round(AVG(salary), 6) "Prosjecna plata"
FROM employees;

--4
SELECT j.job_title, Round(MAX(e.salary), 6) "Maksimalna plata", Round(MIN(e.salary), 6) "Minimalna plata",
Round(SUM(e.salary), 6) "Sumarna 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;

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

--6.1
SELECT COUNT(e.employee_id) "Broj menadzera"
FROM employees e, departments d
WHERE e.employee_id = d.manager_id;

--6.2
SELECT COUNT(e.employee_id) "Broj menadzera"
FROM employees e, jobs j
WHERE e.job_id=j.job_id AND LOWER(j.job_title) LIKE '%manager%';

--7
SELECT e.first_name || ' ' || e.last_name "naziv", e.salary "plata"
FROM employees e, jobs j
WHERE e.job_id=j.job_id AND LOWER(j.job_title) LIKE '%manager%'
AND e.salary < (SELECT MIN(e1.salary)
                FROM employees e1, jobs j1
                WHERE e1.job_id=j1.job_id AND LOWER(j1.job_title) LIKE '%manager%'
                AND e.department_id<>e1.department_id);

--8
SELECT e.first_name || ' ' || e.last_name "Ime i prezime", e.salary, COUNT(e.emp)