Data Filtering in SQL

SQL Query Warmups

Let’s review what we worked on yesterday

• Important SQL Syntax
  • SELECT
  • FROM
  • COUNT
  • ORDER BY
• Basic Syntax Pattern

SELECT first_name, last_name, email FROM employees ORDER BY last_name DESC;

• Multi Line Syntax Pattern

SELECT
title, release_year
FROM film;

WildCard Operator

SELECT
COUNT(*)
FROM customers;

DISTINCT, LIMIT, AS

These are other methods that you can use to optimize your queries

DISTINCT

Your Tool for Finding Uniqueness

• Purpose: Eliminates duplicate rows from your query results.
• Example:

SELECT DISTINCT product_category FROM products;

• This returns a list of all unique product categories present in your ‘products’ table.
• Important: DISTINCT operates on the combination of all selected columns, not just individual ones.

Challenge

• A marketing team member has asked you about the different prices that need to be paid.
• Write a SQL query to get the different prices and order them from high to low

LIMIT

Control Your Output Size

• Purpose: Restricts the number of rows returned by a query.
• Example:

SELECT FROM customers LIMIT 10;

• Retrieves only the first 10 customer records.
• Helpful for: Testing queries, displaying results in pages, or getting a quick sample.

Challenge

• Create a single SQL query to retrieve 5 actors first and last names in descending order (starting with Z)

AS

The Nickname Giver

• Purpose: Provides aliases (nicknames) for columns or calculated values in your query results. This makes - your output more readable and easier to work with.
• Example:

SELECT column_name AS alias_name FROM table_name;
SELECT AVG(price) AS average_price FROM products;

• Benefits:
  • Clarity: Improves column names (e.g., changing ‘avg_price’ to ‘Average Price’).
  • Conciseness: Creates shorter names for long expressions.
  • Necessity: Required when your calculation doesn’t have a default name, like with aggregate functions.

Challenge

• Write a query that counts the number of customers with the last name smith. use AS to label the column as number_of_smiths or something to that effect
• important: note the casing, this might cause issues with your query, try using the string manipulator function in sql to convert it to lower case

Aggregator Functions

• COUNT(): Counts the number of rows.
• SUM(): Calculates the total of a numeric column.
• AVG(): Computes the average value of a numeric column.
• MIN(): Finds the minimum value in a column.
• MAX(): Finds the maximum value in a column.

GROUP BY

• Use with GROUP BY: Aggregate functions often work in conjunction with the GROUP BY clause to calculate summary statistics for different categories within your data.

Example: Calculating Average Order Value per Customer

SELECT
customer_id, AVG(order_total) AS average_order_value
FROM orders
GROUP BY customer_id;

Filtering with WHERE

Your Data Filtering Workhorse

Purpose: The WHERE clause lets you specify conditions that rows must meet to be included in the query results. Syntax:

SELECT * FROM table_name WHERE condition(s);

Common Filtering Patterns

Comparison Operators

• = (Equal to)
• != or <> (Not equal to)
• (Greater than)
• < (Less than)
• = (Greater than or equal to)
• <= (Less than or equal to)
• Example:

SELECT * FROM customers WHERE country = 'Canada';

• Example:

SELECT * FROM products WHERE price < 20;

Challenges

• filter a list of movies that are rated “PG”
• filter a list of movies that cost at most 2.99 to rent
• count all of the rentals that have not been returned yet (return_date is null)
• get a list of all the payment_ids with an amount less than or equal to $2. Include the payment_id and the amount

Logical Operators (AND, OR, NOT)

• AND: Combines multiple conditions; both conditions must be true.
• OR: At least one condition must be true.
• NOT: Negates a condition.
• Example:

SELECT * FROM orders WHERE order_date >= '2023-03-01' AND order_status = 'Shipped';

• Example:

SELECT * FROM customers WHERE city = 'New York' OR city = 'Los Angeles';

Challenges

• filter a list of all the payments where the amount is less than 2 OR greater than 10
  • ordered by customer first (ascending)
  • then as a second order, order by amount

LIKE: Pattern Matching

• % (Wildcard): Matches zero or more characters.
• _ (Underscore): Matches a single character.
• Example:

SELECT * FROM products WHERE product_name LIKE 'Smart%';
SELECT * FROM customers WHERE email LIKE '%@gmail.com';

Challenge

• How many customers are there with a first name that is 3 letters long?
• How many have a 3 letter long first name and either an X or a Y as the last letter of the last name?

IN: Checking Membership in a List

• Example:

SELECT * FROM orders WHERE customer_id IN (123, 456, 789);

BETWEEN: Specifying a Range

• Example:

SELECT * FROM employees WHERE salary BETWEEN 50000 AND 80000;

Challenge

• how many payments have been made on january 26 and 27 2020 with an amount between 1.99 and 3.99