SQL Basic Statements

SELECT

SELECT column_name FROM table_name;

You can select multiple columns:

SELECT c1,c2,c3 FROM table_1;

You can use * to select all the columns (only do this if you need all the columns):

SELECT * FROM table_1

SELECT DISTINCT

Returns only distinct values on a column

SELECT DISTINCT column_name FROM table_name;

SELECT COUNT()

The COUNT function returns the number of input rows that match a specific condition of a query.

SELECT COUNT (column_name) FROM table_name;

SELECT COUNT (DISTINCT column_name) FROM table_name;

SELECT and WHERE

The where statement allows to specify conditions on columns for the rows to be returned.

SELECT col_1,col_2 FROM table WHERE conditions;

SELECT name,choice FROM table WHERE name = 'David' AND choice='Red';

ORDER BY

Sort rows based on a column value, in either ascending or descending order.

SELECT col_1,col_2 FROM table ORDER BY column_1 ASC/DESC

You can also ORDER BY multiple columns when one column has duplicate entries.

SELECT company,sales FROM table ORDER BY company,sales

LIMIT

Can be used to limit the number of rows returned for a query.

SELECT company,sales FROM table ORDER BY company,sales LIMIT 3;

BETWEEN

Can be used to match a value against a range of values.

Same as writing value >= low AND value <= HIGH

Can be used with date (formatted in ISO 8601 'YYYY-MM-DD') too!

SELECT * FROM payment WHERE amount BETWEEN 8 AND 9;

SELECT * FROM payment WHERE amount NOT BETWEEN 8 AND 9;

Note that the the dates assume 0:00 hour so the following select will not return payment happening on day 15 at 3PM.

SELECT * FROM payment WHERE payment_date BETWEEN '2007-02-01' AND '2007-02-15';

IN

The IN operator can be used to create a condition that checks if a value is included in a list of multiple options.

SELECT * FROM table WHERE value IN (option_1, option_2);

SELECT color FROM table WHERE color IN ('red', 'blue', 'brown');

Can be combined with other operators such as NOT

SELECT color FROM table WHERE color NOT IN ('red', 'blue', 'brown');

LIKE and ILIKE

LIKE (case-sensitive) and ILIKE (case-insensitive) can be used to match against a specific pattern using wildcards:

• % matches any sequence of characters ('A%' matches all characters that start with A)
• _ matches any single character ('Version#_ _' the last two underscores can be filled by any character)

Note that PostgreSQL also supports full regex capabilities.

SELECT * FROM customer
WHERE first_name LIKE 'A%' AND last_name NOT LIKE ('B%')
ORDER BY last_name;

AS

The AS statement allows us to create an "alias" for a column or result.

• Note that the AS operator is executed at the end of a query, we cannot use the ALIAS inside a WHERE operator.

SELECT column AS new_name
FROM table

SELECT SUM(column) AS new_name
FROM table

SELECT customer_id , SUM(amount) AS total_spent
FROM payment
GROUP BY customer_id;