SQL
configuration
Character Set
vendors
PostgreSQL
Character types
varchar == text and recommended
character(n) is slowest and requires extra space
DDL
create table
alter table
drop table
DQL
select
from
where
table
permanent (create table)
temporal (subquery result)
virtual (create view)
group by
having
! only columns appeared in GROUP BY may be selected or columns selected in aggregate functions
! it is not possible to mix aggregate functions with just columns without GROUP BY
! column cannot be equal to null, it can be null
! col_val != val DOES NOT return null values
aggregate functions
count()
sum()
avg()
between
! inclusive
and
or
not
=,<>,!=,<,>,=>,<=
in
join
cross join
select * from employee join department
inner join
select * from employee e [inner] join department d on e.dept_id = d.dept_id
using
select t1.t2_id, t2.t2_id from t1 join t2 using (t2_id)
join order?
database optimizer choose the best order, BUT it is possible to provide a hint and set DRIVING TABLE (the first table)
like
like '%bird%'
! Join result is a Cartesian product
compound queries
union [all]
except [all]
intersect [all]
database
isolation
levels
phenomena
read uncommited
read commited
repeatable read
serializable
dirty reads (uncommited dependency)
non-repeatable reads
phantom reads