1/23
select concat stringSELECT first_name || ' ' || last_name FROM customer;column alias“expression AS column_lias” or shorter “expression column_lias“without from a functionSELECT NOW();column alias with spacescolumn_name AS "column alias"order by
SELECT
first_name,
last_name
FROM
customer
ORDER BY
first_name ASC;default order direction
ASC
multiple sort
SELECT
first_name,
last_name
FROM
customer
ORDER BY
first_name ASC,
last_name DESC;LENGTH
SELECT
first_name,
LENGTH(first_name) len
FROM
customer
ORDER BY
len DESC;Order with null
SELECT
num
FROM
sort_demo
ORDER BY
num DESC NULLS LAST;SELECT
num
FROM
sort_demo
ORDER BY
num DESC NULLS FIRST;Distinct one column
SELECT
DISTINCT column1
FROM
table_name;Distinct multile columns
SELECT
DISTINCT bcolor, fcolor
FROM
colors
ORDER BY
bcolor,
fcolor;select IN
SELECT
first_name,
last_name
FROM
customer
WHERE
first_name IN ('Ann', 'Anne', 'Annie');LIKE + length and sort
SELECT
first_name,
LENGTH(first_name) name_length
FROM
customer
WHERE
first_name LIKE 'A%'
AND LENGTH(first_name) BETWEEN 3
AND 5
ORDER BY
name_length;null with and / or
true AND null = null
false AND null = false
false OR null = null
true OR null = trueLIMIT and OFFSET
SELECT
film_id,
title,
release_year
FROM
film
ORDER BY
film_id
LIMIT 4 OFFSET 3;FETCH OFFSEt
SELECT
film_id,
title
FROM
film
ORDER BY
title
OFFSET 5 ROWS
FETCH FIRST 5 ROW ONLY; cast operatorSELECT
payment_id,
amount,
payment_date
FROM
payment
WHERE
payment_date::date IN ('2007-02-15', '2007-02-16');NOT IN
SELECT
film_id,
title
FROM
film
WHERE
film_id NOT IN (1, 2, 3) BETWEEN / NOT BETWEEN
SELECT
payment_id,
amount,
payment_date
FROM
payment
WHERE
payment_date BETWEEN '2007-02-15' AND '2007-02-20'
AND amount > 10
ORDER BY
payment_date;LIKE - ILIKE
SELECT
first_name,
last_name
FROM
customer
WHERE
first_name LIKE '_BAR%';USING ESCAPE
SELECT * FROM t
WHERE message LIKE '%10';SELECT
a,
fruit_a,
b,
fruit_b
FROM
basket_a
INNER JOIN basket_b
ON fruit_a = fruit_b;
(LEFT, RIGHT, FULL) / (IS NULL)Table alias
SELECT
c.customer_id,
c.first_name,
p.amount,
p.payment_date
FROM
customer c
INNER JOIN payment p ON p.customer_id = c.customer_id
ORDER BY
p.payment_date DESC;JOIN with USING
SELECT
select_list
FROM
table1 t1
INNER JOIN table2 t2 USING(column_name);