Modularization of Queries

Common Table Expression (CTE)

with
  asal_ou as (
    select ouid, round(avg(salary)) as avgsal_ou
    from employee
    group by ouid
  )
select
  e.ouid,
  lastname,
  salary,
  avgsal_ou,
  salary-avgsal_ou as diff
from employee e
     join asal_ou on e.ouid=asal_ou.ouid
order by e.ouid, diff desc;

CTE with one Row and one Column

with
  asal as (select round(avg(salary)) as avgsal from employee)
select
  lastname,
  salary,
  avgsal,
  salary-avgsal as diff
from employee cross join asal
order by diff desc;

Subquery in Where Part

Usage as Value - one Row and one Column

select lastname
from employee
where salary >= (
  select round(avg(salary ))
  from employee
)
order by lastname;

Usage as List - many Rows and one Column

select ouid, lastname
from employee
where ouid in (
  select ouid
  from orgunit
  where head=109
);

Exists

Employees with largest Salary

select eid, lastname, salary
from employee e
where not exists (
  select *
  from employee e2
  where e2.salary > e.salary
);

Orgunits with largest average salary

with
  asal as (
    select ouid, avg(salary) as avgsal_ou
    from employee
    group by ouid
 )
select ouid, avgsal_ou
from asal asal_ou1
where not exists (
  select *
  from asal asal_ou2
  where asal_ou2.avgsal_ou > asal_ou1.avgsal_ou
);

Recursive Queries

xwith 
  recursive super as (
    select ouid, name, 1 as level, superunit
    from orgunit
    where superunit is null
    union all
    select sub.ouid, sub.name, super.level + 1 as level, sub.superunit
    from orgunit sub
         join super on super.ouid=sub.superunit
  )
select * from super;

Lateral Join

with
  asal as (select round(avg(salary)) as avgsal from employee)
select lastname, salary, avgsal, diff
from asal, 
     lateral (select lastname, salary, salary-avgsal as diff from employee)
order by diff desc;