1. WHERE
- Used to filter the data in the output
- always after FROM
SELECT
*
FROM payment
WHERE amount = 10.99; -- end of query
SELECT first_name, last_name
FROM customer
WHERE first_name = 'ADAM'
* Challenge
1. How many payment were made by the customer with customer_id = 100?
2. What is the last name of our customer with first name 'ERICA'?
SELECT
COUNT(*)
FROM payment
WHERE customer_id = 100;
SELECT
last_name
FROM customer
WHERE first_name = 'ERICA'
FILTERING with WHERE
- WHERE amount >= 10.99 : Greater than 10.99
- WHERE amount != 10.99 : not 10.99
- WHERE amount <> 10.99 : not 10.99 (same as !=)
- WHERE first_name is null - show only null values
- WHERE first_name is not null - except null values
* Challenge
1. How many rentals have not been returned yet?
2. List of all the payment_ids with an amount less than or equal to $2. Include payment_id and the amount
SELECT
COUNT(*)
FROM rental
WHERE return_date is null;
SELECT
payment_id, amount
FROM payment
WHERE amount <= 2
FILTERING with AND/ OR
- Used to connect two conditions
- 'AND' is always processed first
SELECT
*
FROM payment
WHERE amount = 10.99
OR (amount 9.99
AND customer_id = 426)
-- condition 1: amount = 10.99
-- condition 2: amount = 9.99 and customer_id = 426
SELECT *
FROM payment
WHERE
(customer_id = 322 OR customer_id = 346 OR customer_id = 354)
AND (amount < 2 OR amount > 10)
ORDER BY customer_id ASC, amount DESC
-- condition 1: customer_id should be 322, 346, or 354
-- condition 2: amount should be less than 2 or more than 10
2. BETWEEN/ NOT BETWEEN
- Both values are included
- When selecting date - BETWEEN '2023-11-01 0:00' AND '2023-11-16 0:00'
* Challenge
1. How many payments have been made on January 26th and 27th 2020 with an amount between 1.99 and 3.99?
SELECT
COUNT (*)
FROM payment
WHERE
(payment_date BETWEEN '2020-01-26' AND '2020-01-27 23:59')
AND (amount BETWEEN 1.99 AND 3.99);
3. IN/ NOT IN
- Filtering with multiple values
SELECT * FROM customer
WHERE cusomer_id IN (123, 212, 323, 243, 353, 432);
SELECT * FROM customer
WHERE first_name NOT IN ('LYDIA', 'MATTHEW')
* Challenge
Get a list of concerned payments
- customer_id: 12, 25, 67, 93, 124, 234
- amoungs: 4.99, 7.99, 9.99
- date: January 2020
SELECT
*
FROM payment
WHERE
customer_id IN (12, 25, 67, 93, 124, 234)
AND amount IN (4.99, 7.99, 9.99)
AND payment_date BETWEEN '2020-01-01' AND '2020-01-31 23:59';
-- AND payment_date BETWEEN '2020-01-01' AND '2020-02-01'
4. LIKE
- Used to filter by matching against a pattern
- Use '_' to any single character
- Use '%' to any sequence of characters
- Case-sensitive!
SELECT *
FROM actor
WHERE first_name LIKE 'A%' # first_name starts with 'A' will come out.
- 'A%' : starts with A
- '%A%' : includes A
- '_A%' : starts with any characters but the second character has to be A
* Challenge
1. Find out how many movies there are that contain the 'Documentary' in the description
SELECT
COUNT(*)
FROM film
WHERE description LIKE '%Documentary%'
2. How many customers are there with a first name that is 3 letters long and either an 'X' or a 'Y' as the last letter in the last name?
SELECT *
FROM customer
WHERE first_name LIKE '___'
AND last_name LIKE '%X' OR '%Y'
-- Wrong syntax
SELECT *
FROM customer
WHERE first_name LIKE '___'
AND (last_name LIKE '%X'
OR last_name LIKE '%Y')
-- list conditions respectively
TIPS
- Comment using '--' for single sentence.
- Comment using '/*' and '*/' for multiple sentences.
SELECT *
FROM payment
/*WHERE amount = 10.99
AND customer_id = 426*/
- Using 'AS' to describe the output more specifically by changing coulmns' names
SELECT
payment_id AS invoice_no
FROM payment
Day 2 Challenge
1) How many movies are there that contain 'Saga' in the description and where the title starts either with 'A' or ends with 'R'? Use the alias 'no_of_movies'.
SELECT COUNT (*) AS no_of_movies
FROM film
WHERE description LIKE '%Saga%'
AND (title LIKE 'A%' OR title LIKE '%R')
2) Create a list of all customers where the first name contains 'ER' and has an 'A' as the second letter. Order the results by the last name descendingly.
SELECT *
FROM customer
WHERE first_name LIKE '%ER%'
AND first_name LIKE '_A%'
ORDER BY last_name DESC
3) How many payments are there where the amount is either 0 or is between 3.99 and 7.99 and in the same time has happened on 2020-05-01.
SELECT COUNT (*)
FROM payment
WHERE (amount = 0
OR amount BETWEEN 3.99 AND 7.99)
AND payment_date BETWEEN '2020-05-01' AND '2020-05-02'