/* ------------------------------------------------------------------------ */
/* 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.Loc = '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 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.