Structure of a Query

Selection	Columns
Filtering	Rows
Sorting	Order of rows

SELECT output columns
FROM table
WHERE condition
ORDER BY column names

Selection, Filtering, Sorting

select lastname, salary
from employee
where ouid=14
order by salary;

select lastname, salary
from employee
where ouid=14
order by salary desc;

select *
from employee
order by eid;

Output of complete table, i.e. all rows and all columns

Calculated Columns

select
  lastname,
  extract(year from hiredate) as hireyear
from employee
where ouid=14
order by lastname;

select
  lastname,
  extract(year from current_date) - 
    extract(year from hiredate) 
    as years_of_employment
from employee
where ouid=14
order by lastname;

Compoud Condidition

select
  lastname, salary
from employee
where
  salary < 35000
  and hiredate 
    between '2010-01-01' and '2023-12-31'
order by lastname;

Null Values in Conditions

select
  lastname, salary, bonus
from employee
where
  bonus=null
order by eid;

Falsch: Vergleich (=) mit "unbekannt" liefert "weiß ich nicht"

select
  lastname, salary, bonus
from employee
where
  bonus is null
order by eid;

select
  lastname, salary, bonus
from employee
where
  not bonus is null
order by eid;

Calculations with Null Values

select
  lastname, salary, bonus,
  salary + bonus as total
from employee
where
  ouid=17
order by eid;

select
  lastname, salary, bonus,
  salary+
    case
      when bonus is null then 0
      else bonus
    end as total
from employee
where
  ouid=17
order by eid;

Detail Reduction in Output

select
  lastname, ouid,
  case
   when salary>100000 then 'high'
   when salary>35000 then 'medium'
   else 'low'
  end as salary_band
from employee
order by salary;

Limit Number of Lines in Output

select lastname, salary
from employee
order by salary
limit 2;

Set Operations

Example Tables

Table t01

Table t02

Union

select id, tag1, tag2 from t01
union
select id, tag1, tag2 from t02
order by id, tag1, tag2;

Union All

select id, tag1, tag2 from t01
union all
select id, tag1, tag2 from t02
order by id, tag1, tag2;

Intersect

select id, tag1, tag2 from t01
intersect
select id, tag1, tag2 from t02
order by id, tag1, tag2;

Except

select id, tag1, tag2 from t01
except
select id, tag1, tag2 from t02
order by id, tag1, tag2;