A sample database used throughout the notes is described as follows;
customer (
id: int,
first_name: text,
last_name: text,
email:text,
address_id:integer,
created_at:timestamp,
updated_at:timestamp,
is_active:boolean )
product (
id: int,
name: text,
color: text,
given_id:text,
created_at:timestamp,
updated_at:timestamp,
is_active:boolean )
purchase (
id:int,
customer_id:int,
product_id:int,
price:float,
size:int,
created_at:timestamp,
updated_at:timestamp )
Following are single table query examples
SELECT
- Sort rows using ORDER BY clause.
- Select distinct rows using DISTINCT operator.
- Filter rows using WHERE clause.
- Select a subset of rows from a table using LIMIT or FETCH clause.
- Group rows into groups using GROUP BY clause.
- Filter groups using HAVING clause.
- Join with other tables using joins such as INNER JOIN, LEFT JOIN, FULL OUTER JOIN, CROSS JOIN clauses.
- Perform set operations using UNION, INTERSECT, and EXCEPT.
Syntax
SELECT column_list FROM table_name;
SELECT column_list FROM table_name ORDER BY sort [ ASC |DESC ] [ NULLS FIRST | NULLS LAST ];
Example - retrieve columns or all columns
SELECT first_name FROM customer;
SELECT first_name AS name FROM customer;
SELECT first_name, last_name, email FROM customer;
SELECT * FROM customer;
Example - join column results
SELECT first_name || ' ' || last_name FROM customer;
SELECT first_name || ' ' || last_name AS name FROM customer;
Example - expressions
SELECT 4 * 9 ;
SELECT 4 * 9 AS results ;
SELECT first_name, LENGTH(first_name) len FROM customer;
Example - sort column
SELECT first_name FROM customer
ORDER BY first_name ASC;
SELECT first_name, last_name, email FROM customer
ORDER BY first_name ASC, last_name DESC;
SELECT first_name, LENGTH(first_name) len FROM customer
ORDER BY len DESC;
SELECT first_name FROM customer
ORDER BY first_name ASC NULLS LAST;
Syntax
SELECT DISTINCT column1 FROM table_name;
SELECT DISTINCT (column1, column2) AS column_alias, other_column FROM table_name;
Example - unique values sorted by customer
SELECT DISTINCT customer_id FROM purchase
ORDER BY customer_id;
Example - unique multiple columns sorted by customer and product
SELECT DISTINCT customer_id, product_id FROM purchase
ORDER BY customer_id, product_id;
Example - unique values sorted by customer, then keep first row of result set
SELECT DISTINCT ON (customer_id) AS customer_id, product_id FROM purchase
ORDER BY customer_id;
Syntax
SELECT column_list FROM table_name
WHERE condition
ORDER BY sort;
Valid conditions operator includes;
=, >, <, <=, >=, < >, != , AND, OR, IN, BETWEEN, LIKE, IS NULL, NOT
Example - retrieve columns or all columns with condition
SELECT first_name FROM customer
WHERE first_name like 'Albert';
SELECT first_name FROM customer
WHERE id = 12;
SELECT first_name, last_name, email FROM customer
WHERE first_name like 'Albert'
OR first_name like 'Bordy';
SELECT first_name, last_name, email FROM customer
WHERE first_name like 'Alb%';
SELECT first_name, last_name, email FROM customer
WHERE first_name IN ('Albert', 'Bordy', 'Morgan');
SELECT first_name, last_name, email FROM customer
WHERE id BETWEEN 3 AND 10;
Example - retrieve columns or all columns with sub query condition
SELECT customer_id FROM purchase
WHERE CAST (created_at AS DATE) > '2023-01-01'
ORDER BY customer_id;
SELECT customer_id, first_name, last_name FROM customer
WHERE customer_id IN (
SELECT customer_id
FROM purchase
WHERE CAST (created_at AS DATE) > '2023-01-01'
)
ORDER BY customer_id;
Syntax
SELECT column_list FROM table_name
ORDER BY sort
LIMIT row_count OFFSET row_to_skip;
Example - retrieve first 5 purchase
SELECT customer_id, product_id FROM purchase
ORDER BY id
LIMIT 5;
Example - retrieve 5 purchases after the 10th purchase
SELECT customer_id, product_id FROM purchase
ORDER BY id ASC
LIMIT 5 OFFSET 9;
