Challenges on SELECT
1. Use a SELECT statement to grab the first and last name of
every customer and their email address.
DISTINCT
Sometimes atable contains a column that has duplicate values,
and you may find yourself in a situtaion where you only want to
list the unique/distinct value.
The DISTINCT keyword can be used to return only the distict
values in a column.
SELECT DISTICT column_name FROM table
e.g
SELECT DISTINCT release_year FROM film;
SELECT DISTINCT(release_year) FROM film;
Note: You can use parentheses or not
SELECT DISTINCT return_price FROM film;
Challenges on DISTINCT
2. An Australian visitor isn't familiar with MPAA movie ratings
(e.g PG, PG-13, R etc..)
We want to know the type of ratings we have in our database.
What ratings do we have available?
SOLUtion
SELECT DISTINCT rating FROM film;
PROBLEM 3
3. Count the distinct amount paid by customer
SOLUTION
SELECT COUNT(DISTINCT amount) FROM payment;
or
SELECT COUNT(DISTINCT(amount)) FROM payment;
SELECT and WHERE are the most fundamental SQL states and
you will find yourself using them often!
The WHERE statement allows us to specify condition on column
for the row to be return.
Basic Sntax example:
SELECT colum1, columns2
FROM table
WHERE conditions;
Comparison Operators
Compare a column value to something
is the price greater than $3.50
is the pet's name equal to "bingo"
LIst of Comparison Operators
=
>
<
>=
<=
<> or !=
Logical Operators
Allow us to combine multiple comparison operators
List of Logical Operators are
AND
OR
NOT
EXAMPLE
SELECT name,choice FROM table
WHERE name = 'David'
SELECT name,choice FROM table
WHERE name = 'David' AND choice = 'Red'
SELECT * FROM customer
WHERE firs_name ='Jared'
SELECT * FROM film
WHERE rental_rate > 4 AND replace_cost >= 19.99
AND rating = 'R'
SELECT title FROM film
WHERE rental_rate > 4 AND replace_cost >= 19.99
AND rating = 'R'
SELECT COUNT(title) FROM film
WHERE rental_rate > 4 AND replace_cost >= 19.99
AND rating = 'R'
SELECT COUNT(*) FROM film
WHERE rental_rate > 4 AND replace_cost >= 19.99
AND rating = 'R'
SELECT COUNT(*) FROM film
WHERE rating = 'R' OR rating ='PG-13'
SELECT COUNT(*) FROM film
WHERE rating != 'R' OR rating !='PG-13'
Challenges on WHERE, Comparison Operator
A customer forgot their wallet at our store! We need to track
down their email to inform them.
What is the email for the customer with the name Nancy
Thomas?
SOLUTION
SELECT email FROM customer
WHERE first_name = 'Nancy'
AND last_name = 'Thomas'
Challenges 2
A customer wants to know what the movie 'OUtlaw Hanky' is
about.
Could you give them the description for the movie 'OUtlaw
Hanky'
SOLUTION
SELECT description FROM film
WHERE title = 'Outlaw Hanky'
Challenge 3
A customer is late on their movie return, and we've mailed
them a letter to their address at '259 Ipoh Drive' we should also
call them on the phone to let them know.
Can you get the phone number for the customer who lives at
'259 Ipoh Drive' ?
SOLUTION
SELECT phone FROM address
WHERE address = '259 Ipoh Drive';
ORDER BY
You can use ORDER BY to sort rows based on a column value, in
either ascending or descending order.
Basic syntax
SELECT column_1, column_2
FROM table
ORDER BY column_1 ASC or DESC
Example
SELECT * FROM customer
ORDER BY first_name DESC;
Better way to get querry sorted is
SELECT stotre_id, first_name, last_name
FROM customer
ORDER BY store_id
SELECT stotre_id, first_name, last_name
FROM customer
ORDER BY store_id DESC, first_name ASC
if we run this without outputing the stote_id, it will still work
LIMIT
The LIMIT commmand allows us to limit the number of row
quered.
It's useful for not wanting to return every single row in a table,
but only view the top few rows to get the idea layout of the
table
LIMIT also become useful in combination with ORDER BY
EXAMPLE
what is the most 5 recent payment by customer and which
cuctomer_id make the payment?
SELECT * FROM payment
ORDER BY payment_date DESC
LIMIT 5;
SELECT * FROM payment
WHERE amount <= 5.00
ORDER BY payment_date DESC
LIMIT 5;
Challenge Task
We want to reward our first 10 paying customers.
What are the customer ids of the first 10 cuustomers who make
payment?
SOLUTION
SELECT customer_id FROM payment
ORDER BY payment_date ASC
LIMIT 10;
BETWEEN
The BETWEEN operator can be used to match a value against a
range of values:
value BETWEEN low AND high
The BETWEEN operator can also be used with dtaes. Note that
you need to format dates in the ISO 8601 standard format,
which is YYYY-MM-DD
date BETWEEN '2020-04-23' AND '2020-08-23'
EXAMPLE
SELECT * FROM payment
WHERE amount BETWEEN 8 AND 9:
to know total number of transaction of that range we use
COUNT
SELECT COUNT(*) FROM payment
WHERE amount BETWEEN 8 AND 9:
SELECT COUNT(*) FROM payment
WHERE amount NOT BETWEEN 8 AND 9:
SELECT COUNT(*) FROM payment
WHERE payment_datee BETWEEN '2007-02-01' AND '2007-02-
15':
If we do this nothing get return cause no payment between that
range
SELECT COUNT(*) FROM payment
WHERE payment_datee BETWEEN '2007-02-01' AND '2007-02-
14':
IN
In certain Cases you want to check for multiple possible value
options, for example, if a user name show up IN a list of known
names.
We can use IN Operation to create a condition that check to see
if a value is included in a list of multiple options.
Query Example
SELECT color FROM table
WHERE color IN ('red','blue')
SELECT * FROM payment
WHERE amount IN (0.99,1.98,1.99)
SELECT * FROM customer
WHERE first_name IN ('John', 'Jake', 'Julie')
We've already been able to perform direct comparisons against
strings, such as:
WHERE first_name = 'John'
But what if we want to match against a general pattern in a
string?
All email ending with '@gmail.com'
All name that begin with an 'A'
THe LIKE opeartor allows us to perform pattern matching
against string data with the use of wildcard characters
Percent %
Matches any sequence of characters
Underscore _
Matches any single character
All names that begin with an 'A'
WHERE name LIKE 'A%'
All names that end with an 'a'
WHERE name LIKE '%a'
Using the underscore allow us to replace just a single character
Get all Mission Impossible films
WHERE title LIKE 'Mission Impossible _' or
'Mission Impossible _ _'
Note LIKE is case-sensitive, we can use ILIKE which is not case
sensitive
Example
SELECT * FROM customer
WHERE first_name LIKE 'J%' AND last_name LIKE 'S%'
SELECT * FROM customer
WHERE first_name LIKE 'J%' AND last_name ILIKE 's%'
SELECT * FROM customer
WHERE first_name LIKE '%Ja%'
SELECT * FROM customer
WHERE first_name LIKE '%_her%'
SELECT * FROM customer
WHERE first_name LIKE 'A%'
ORDER BY last_name
SELECT * FROM customer
WHERE first_name LIKE 'A%' AND last_name NOT LIKE 'B%'
ORDER BY last_name
CHallenge
1 How may payment transactions were greater than &5.00?
SELECT COUNT(amount) FROM payment
WHERE amount > 5;
2 How many actors have a first name that start with the letter
P?
SELECT COUNT(*) FROM actor
WHERE first_name LIKE 'P%'
3 How many unique district are our customer from?
SELECT COUNT(DISTINCT(district))
FROM address;
4 Retrieve the list of name for those distinct district from the
previous question.
SELECT DISTINCT(district)
FROM address;
5 Homany films have a rating of R and a replacement cost
between 5 and 15?
SELECT COUNT(*) FROM film
WHERE rating = 'R'
AND replacement_cost BETWEEN 5 AND 15;
6 How many films have the word Truman somewhere in the
title
SELECT COUNT(*) FROM film
WHERE title LIKE '%Truman%'
