From customer limit 5; customer id

SELECT * FROM customer WHERE store_id=2;
(Note: Going forward, output will only be included when there’s something about it to discuss.)
You can combine conditions together with AND and OR:
SELECT * FROM customer WHERE store_id=2 AND customer_id=400;
Note that string (text) values in SQL are surrounded with single quotes:
SELECT * FROM staff WHERE first_name='Jon';
WHERE operators include:

Operator	Description
=	Equal
>	Greater than
<	Less than
>=	Greater than or equal
<=	Less than or equal
<> or !=	Not equal
AND	Logical operator AND
OR	Logical operator OR
NOT	To negate boolean values

SELECT_*_FROM_staff_where'>SELECT * FROM staff where first_name != 'Jon';
SELECT * FROM staff where first_name <> 'Jon';

Exercises
Select rows from the film table where the film_id is less than 4.
Select rows from the film table where the rating is PG.
Select rows from the film table where the rating is PG or G.
BETWEEN
BETWEEN is shorthand for # <= x <= #. The endpoints are inclusive:
SELECT * FROM film WHERE film_id BETWEEN 1 AND 5;
IN
IN lets you specify a lot of values that you would otherwise join together with an OR statement:
SELECT * FROM film WHERE film_id IN (3,5,7,9);
Exercise
Select rows from the actor table where the first name is Angela, Angelina, or Audrey using IN.
Break for exercises: part1_exercises.md - sections Describe Commands and Select.
IS NULL
Missing data in SQL is NULL. NULL values occur where there is no data entered for a specific row and column. You can test for NULL with IS NULL:
SELECT * FROM address WHERE address2 IS NULL;
NULL is different than an empty string (‘’):
SELECT * FROM address WHERE address2 = '';
There is also the opposite: IS NOT NULL.
You cannot use =NULL. The following returns no rows, even though there are 4 rows with a NULL address2:
SELECT * FROM address WHERE address2 = NULL;
NULL values are omitted from the results of comparison tests.
ORDER BY
We can determine the order that our results are shown in:
SELECT film_id, title FROM film ORDER BY film_id;
By default, sorting is done in ascending (ASC) order. To get the reverse order of sorting, use DESC (descending):
SELECT film_id, title FROM film ORDER BY film_id DESC;
This is often useful to combine with LIMIT:
SELECT film_id, title FROM film ORDER BY film_id DESC LIMIT 5;
And with OFFSET to view results in smaller chunks:
SELECT film_id, title FROM film ORDER BY film_id DESC LIMIT 5 OFFSET 5;
You can order by multiple columns:
SELECT customer_id, rental_id FROM rental
ORDER BY customer_id, rental_id;
Exercise
Order the rows in city by country_id and then by city.
DISTINCT
DISTINCT removes duplicate rows from the result. It comes before the list of column names, and applies to the combination of all columns.
SELECT DISTINCT customer_id, staff_id FROM payment;
SELECT DISTINCT amount FROM payment ORDER BY amount;
Break for exercises: part1_exercises.md - sections for Like, Distinct, and Order by exercises
Functions and Arithmetic
There are many common, built-in functions in PostgreSQL. See PostgreSQL Documentation for a full list or list of mathematical functions.
For example, we can get the minimum or maximum of a column from results:
SELECT min(amount) FROM payment;
SELECT max(amount) FROM payment;
These functions apply to the result set, not the full table:
SELECT max(amount) FROM payment WHERE amount < 4;
You can also do arithmetic:
SELECT rental_duration, rental_duration + 1
FROM film LIMIT 10;
Functions need to be used the part of the query where you specify the values (or other values) that you are selecting, not the where clause. The following doesn’t work:
SELECT customer_id FROM payment WHERE amount = max(amount);
dvdrental=# SELECT customer_id FROM payment WHERE amount = max(amount);
ERROR: aggregate functions are not allowed in WHERE
LINE 1: SELECT customer_id FROM payment WHERE amount = max(amount);
^
To achieve this, you need to use a subquery, which we’ll learn about in the next part.
NOTE: the set of provided functions is not standard across different implementations of SQL, although there are some common core functions.
If we wanted to count the number of distinct values in a column, we can use the count function in combination with distinct:
SELECT count(distinct postal_code) from address;
Count without distinct will count the number of rows that aren’t null.