[Udemy] 15 Days of SQL - Day 2: WHERE, BETWEEN, IN, LIKE

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'