/* ------------------------------------------------------------------------ */
/* Example -----------------------------------------------------------------*/
/* ------------------------------------------------------------------------ */

-- Database emp:
-- emp(Empno, Ename, Job, Mgr, Hiredate, Sal, Comm, Deptno)
-- %dept(Deptno, Dname, Location)
-- %salgrade(Grade, Losal, Hisal)

/* 1. Find the number of employees who work in Dallas. */
\echo Find the number of employees who work in Dallas.

select count(distinct e.Empno)
from emp e, dept d
where e.Deptno = d.Deptno and d.Location = 'DALLAS'
;

/* ------------------------------------------------------------------------ */
/* Write and test the following queries ----------------------------------- */
/* ------------------------------------------------------------------------ */

\echo Find average salary of employees who work in Dallas.
-- 2. Find average salary of employees who work in Dallas.

select avg(e.Sal)
from emp e, dept d
where e.Deptno = d.Deptno and d.Location = 'Dallas'
;

\echo For each department (including departments with no employees), find the sum of salaries of employees who work in that department.
-- 3. For each department (including departments with no employees), find the sum of salaries of employees who work in that department.

select d.Dname, sum(e.Sal)
from emp e join dept d on d.Deptno = e.Deptno
group by e.Deptno, d.Dname

union

select d.Dname, 0
from dept d
where not exists(
select *
from emp e2, dept d2
where e2.Deptno = d2.Deptno and d2.Deptno = d.Deptno
);

\echo Find departments (deptno) with more than 3 employees.
-- 4. Find departments (deptno) with more than 3 employees.

select e.Deptno
from emp e
group by e.Deptno
having count(e.Ename)>3
;

\echo For each department, find the number of analysts who work in that department (the result consists of tuples [D, N]).
-- 5. For each department, find the number of analysts who work in that department (the result consists of tuples [D, N]).

select e.Deptno, count(e.Ename)
from emp e right join dept d on e.Deptno = d.Deptno
where e.Job = 'ANALYST'
group by e.Deptno

\echo Find the job position(s) with the maximal standard deviation of salaries.
-- 6. Find the job position(s) with the maximal standard deviation of salaries.

-- \ Ak ma viac povolani rovnaku odchylku, vrati len jedno z nich!
select e.Job, stddev(e.Sal)
from emp e
group by e.Job
having stddev(e.Sal) is not null
order by stddev(e.Sal) desc
limit 1
;

\echo Find tuples [Deptno, Job, Sum, Average] which for each [Deptno, Job] state the sum of salaries and average salary of employees who work in department Deptno and do job Job.
-- 7. Find tuples [Deptno, Job, Sum, Average] which for each [Deptno, Job] state the sum of salaries and average salary of employees who work in department Deptno and do job Job.

select e.Deptno, e.Job, sum(e.Sal), avg(e.Sal)
from emp e
group by e.Deptno, e.Job
;

\echo For each department find average salary grade of employees in that department
-- 8. For each department find average salary grade of employees in that department

select e.Deptno, avg(s.Grade)
from emp e join salgrade s on e.Sal >= s.Losal and e.Sal <= s.Hisal
group by e.Deptno
;

\echo For each salary grade find number of employees with that grade
-- 9. For each salary grade find number of employees with that grade and an average value of comm

select s.grade, count(e.Ename)
from emp e join salgrade s on e.Sal >= s.Losal and e.Sal <= Hisal
group by s.grade
;

\echo Find tuples [Y,N], where N is number of employees hired in the year Y (the result contains only years when an employee was hired)
-- 10. Find tuples [Y,N], where N is number of employees hired in the year Y (the result contains only years when an employee was hired)

select date_part('year',e.Hiredate), count(e.Ename)
from emp e
group by date_part('year',e.Hiredate)
;

\echo For each year since 1990 find number of employees hired in that year (the result must contain also years when no employee was hired)
-- 11. For each year since 1990 find number of employees hired in that year (the result must contain also years when no employee was hired) - hint: use postgres function generate_series();

select g, count(e.Ename)
from emp e right join generate_series(1990, cast(date_part('year',current_date) as integer)) as g on date_part('year',e.Hiredate)= g
group by g
order by g
;

\echo For each employee, find the number of subsidiaries (direct and indirect) of that employee. Include employees with no subsidiaries.
-- 12. For each employee, find the number of subsidiaries (direct and indirect) of that employee. Include employees with no subsidiaries.

select e.Ename, count
from emp e

with recursive subsidiaries as(
  select e1.Empno as XNo, e1.Ename as XName, e2.Empno as YNo, e2.Ename as YName
  from emp e1, emp e2
  where e1.Mgr = e2.Empno
  union
  select e1.Empno as XNo, e1.Ename as XName, e2.Empno as YNo, e2.Ename as YName
  from emp e1, subsidiaries s
  where e1.Mgr = s.XNo
)
select s.YName, count(s.XNo) as sub_count
from subsidiaries s
group by Yno, YName