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

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

/* 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.
-- Find average salary of employees who work in Dallas.

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

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

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

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

-- \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.
-- 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.

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

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

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

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

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