Fundamentals

SELECT column_1, column_2 FROM table;

• To select all columns from the table (not recommended):

SELECT * FROM table;

Select distinct (unique) values in a column:

SELECT DISTINCT column FROM table;
SELECT DISTINCT(column) FROM table;

Count function

SELECT COUNT(column_1) FROM table;
SELECT COUNT(column_2) FROM table;
SELECT COUNT(*) FROM table;

returns number of rows in the table. All the calls above return the same value.

Combining distinct and count:

SELECT COUNT(DISTINCT column_1) FROM table;

Conditional selection

SELECT column_1, column_2 FROM table WHERE condition;

• comparison operators: =, >, <, >=, <=, and <> or !=.

• logical operators: AND, OR, NOT.

SELECT name, choice FROM table WHERE name = 'David';
SELECT name, choice FROM table WHERE name = 'David' AND rate > 4;

Ordering (ORDER BY)

SELECT col1, col2 FROM table ORDER BY col1 ASC
SELECT col1, col2 FROM table ORDER BY col1 ASC, col2 DESC

• Ascending is the default. Order by comes at the end of the query, including after WHERE statements.

Limiting number of rows return in a query

• LIMIT is the last command to be executed

SELECT * FROM table ORDER BY payment_date DESC LIMIT 5;

• The command below is similar to .head() in a pandas dataframe:

SELECT * FROM table LIMIT 5;

BETWEEN

• value BETWEEN low AND HIGH

• the same as value >= low AND value <= high

• can combine with NOT operator: value NOT BETWEEN low AND high

• the same as value < low OR value > high

• Can be used with dates (formatted as '2022-12-31' or '2022-12-31 23:39:11')

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

IN

Check if a value is included in a list of options.

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

LIKE and ILIKE

• The LIKE operator allows us to perform pattern matching against string data with the use of wildcard characters:
  • %: matches any sequence of characters
  • _ matches any single character

SELECT * FROM table WHERE first_name LIKE 'J%' AND last_name NOT LIKE 'S%';

• The ILIKE operator is just like the LIKE operator, but LIKE is case sensitive and ILIKE is case insensitive.

Aggregate functions

• AVG() -> average
• COUNT() -> number of values
• MAX() -> maximum value
• MIN() -> minimum value
• SUM() -> sum of values

SELECT MIN(replacement_cost) FROM film;
SELECT ROUND(AVG(replacement_cost),2) FROM film;