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

/* Print all jobs (each job once). */
\echo  1. Print all jobs (each job once).

select distinct e.Job
from emp e
;

-- To run (all) queries in this file, use 'psql -f queries_emp.sql'

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

/* Print names and jobs of employees with salary at least 2000. */
\echo 2. Print names and jobs of employees with salary at least 2000.
select e.Ename,e.Job
from emp as e
where e.Sal >= 2000
;

/* Print names and jobs of employees who work in department 30. */
\echo 3. Print names and jobs of employees who work in department 30.
select e.Ename, e.Job
from emp as e
where e.Deptno = 30
;

/* Print the number of department in which the president works. */
\echo 4. Print the number of department in which the president works.
select e.Deptno
from emp as e
where e.Job = 'PRESIDENT'
;

/* Print names of employees who were hired between 1 September 1981 and 31 October 1981. */
\echo 5. Print names of employees who were hired between 1 September 1981 and 31 October 1981.
select e.Ename
from emp as e
where e.Hiredate >= '1981-09-01' and e.Hiredate <= '1981-10-31'
;

/* Print names and salaries of managers, sort the output in the descending order of salaries. */
\echo 6. Print names and salaries of managers, sort the output in the descending order of salaries.
select e.Ename, e.Sal
from emp as e
where e.Job = 'MANAGER'
order by e.Sal DESC
;

/* Print names, brutto incomes, national insurance contributions, income taxes and netto incomes of employees (subtract 13.4% for national insurance and 19% for income tax). */
\echo 7. Print names, brutto incomes, national insurance contributions, income taxes and netto incomes of employees (subtract 13.4% for national insurance and 19% for income tax).

select e.Ename, e.Sal, e.Sal*0.134 as ins, e.Sal*0.19 as tax, e.Sal-e.Sal*0.134-e.Sal*0.19 as netto
from emp as e
;

/* Print names and the number of working years (since hired) of all employees. */
\echo 8. Print names and the number of working years (since hired) of all employees.
select e.Ename, date_part('year', age(e.Hiredate))
from emp as e
;

/* Print names of all employees with the first letters of their department names. */
\echo 9. Print names of all employees with the first letters of their department names.
select e.Ename, substring(d.Dname from 1 for 1)
from emp as e, dept as d
where e.deptno = d.deptno
;

/* Print name and "total salary" (total salary = salary + comm) of each employee. (Warning: the column comm may contain NULL values.) */
\echo 10. Print name and "total salary" (total salary = salary + comm) of each employee. (Warning: the column comm may contain NULL values.)
select e.Ename,
        case WHEN e.Comm is null THEN e.Sal
                                 ELSE e.Sal + e.Comm
        END
from emp as e
;


/* Print jobs of employees who work in Chicago. */
\echo 11. Print jobs of employees who work in Chicago.
select distinct e.Job
from emp e, dept d
where e.Deptno = d.Deptno and d.Location = 'Chicago'
;

/* Print tuples [Name, City, Coworker] which stand for all employees, their working places and names of their co-workers (employees who work in the same department). */
\echo 12. Print tuples [Name, City, Coworker] which stand for all employees, their working places and names of their co-workers (employees who work in the same department).
select e1.Ename, d.Location, e2.Ename
from emp e1, dept d, emp e2
where e1.Deptno = d.Deptno and e2.Deptno = d.Deptno and e1.Empno <> e2.Empno
;

/* Print names, department names and salaries of all employees whose salaries are greater than the lowest salary in department 20. */
\echo 13. Print names, department names and salaries of all employees whose salaries are greater than the lowest salary in department 20.
create temporary table minsal as (
select e1.Sal as min20
from emp e1
where e1.deptno = 20 and not exists
    (select *
    from emp e2
    where e2.Deptno = 20 and e2.Sal < e1.Sal)
);

select e.Ename, d.Dname, e.Sal
from emp e, dept d, minsal m
where e.Deptno = d.Deptno and e.Sal > m.min20
;

/* Print names of employees together with names of their managers. */
\echo 14. Print names of employees together with names of their managers.
select e1.Ename, e2.Ename
from emp e1, emp e2
where e1.Empno = e2.Mgr
;


/* Which departments contain all job positions? */
\echo 15. Which departments contain all job positions?
create temporary table missed as(
select *
from emp e1, emp e2
where not exists(
  select *
  from emp e
  where e.Job = e1.Job and e.Deptno = e2.Deptno)
);

select d.Deptno
from dept d
where not exists(
  select *
  from missed m
  where m.Deptno = d.Deptno
);

/* Which departments are empty (have no employees)? */
\echo 16. Which departments are empty (have no employees)?
select d.Deptno
from dept d
where not exists(
  select *
  from emp e
  where e.Deptno = d.Deptno
);

/* Which employees manage only clerks? */
\echo 17. Which employees manage only clerks?
select e1.Ename
from emp e1
where not exists(
  select *
  from emp e2
  where e1.Mgr = e2.Empno and e2.Job <> 'CLERK'
);

/* Which departments employ no salesmen? */
\echo 18. Which departments employ no salesmen?
select d.Dname
from dept d
where not exists(
  select *
  from emp e
  where d.Deptno = e.Deptno and e.Job = 'SALESMAN'
)
;

/* 19. Find names of all employees who are subsidiaries of Blake - both direct and indirect subsidiaries. (This query is a tricky one, skip it unless you understand what precisely you are doing.) */
\echo 19. Find names of all employees who are subsidiaries of Blake - both direct and indirect subsidiaries. (This query is a tricky one, skip it unless you understand what precisely you are doing.)
with recursive subsidiaries as(
  select e2.Empno as Num, e1.Ename as Ename
  from emp e1, emp e2
  where e1.Mgr = e2.Empno and e2.Ename = 'BLAKE'
  union
  select s.Num, e1.Ename
  from emp e1, subsidiaries s
  where e1.Mgr = s.Num
)
select s.Ename
from subsidiaries s;