Filtering Data with WHERE Clause

# CHAPTER 8

Filtering Data with WHERE Clause #

1. Introduction #

2. Learning Objectives #

• Understand the syntax and placement of the WHERE clause.

• Filter data using exact text and numeric matches.

• Utilize mathematical comparison operators (>, <, >=, <=, !=).

• Prevent massive data dumps by targeting specific rows.

3. The WHERE Clause Syntax #

-- Retrieve ALL columns from the users table, 
-- BUT ONLY return rows where the first_name is exactly 'Alice'
SELECT * FROM users 
WHERE first_name = 'Alice';

*(Remember: Just like in INSERT statements, text/string values MUST be wrapped in single quotes!)*

4. Numeric Filtering #

-- Find the specific user with ID number 42
SELECT * FROM users 
WHERE id = 42;

5. Mathematical Comparison Operators #

• = (Equal to)

• > (Greater than)

• < (Less than)

• >= (Greater than or equal to)

• <= (Less than or equal to)

• != or <> (Not equal to)

-- Find all products that cost MORE than 50 dollars
SELECT product_name, price FROM products 
WHERE price > 50;

-- Find all employees who do NOT work in the 'HR' department
SELECT * FROM employees 
WHERE department != 'HR';

6. Filtering by Dates #

-- Find all orders placed AFTER January 1st, 2023
SELECT * FROM orders 
WHERE order_date > '2023-01-01';

7. Mini Project: E-Commerce Inventory Slicing #

-- 1. Which specific products are currently out of stock?
SELECT product_name FROM products 
WHERE in_stock = FALSE;

-- 2. Find premium items (Items priced at $500 or more)
SELECT product_name, price FROM products 
WHERE price >= 500;

-- 3. Find the exact receipt for Order #9928
SELECT * FROM receipts 
WHERE order_id = 9928;

8. Common Mistakes #

• Putting WHERE before FROM: SELECT * WHERE age > 18 FROM users; will cause a fatal syntax error. SQL is processed linearly. You must first declare the table (FROM), and *then* filter it (WHERE).

• Forgetting Quotes on Text: WHERE city = London will crash because SQL thinks London is a column name. It must be WHERE city = 'London'.

• Using == for Equality: Programmers coming from JavaScript or Python often type WHERE id == 5. In standard SQL, equality is evaluated using a single equals sign =.

9. Best Practices #

• Primary Key Filtering: If you are building a "User Profile" page on a website, you should always filter the query using the user's unique id (e.g., WHERE id = 5), rather than their name (WHERE name = 'John'). There might be 50 "Johns" in the database, but only one ID #5!

10. Exercises #

1. 1. What mathematical operator is used in SQL to denote "Not Equal To"?

1. 2. Write the condition to find items where the quantity is less than or equal to 10.

11. SQL Challenges #

SELECT title, author FROM books 
WHERE published_year < 2000;

12. MCQ Quiz with Answers #

• Q: Explain why querying SELECT * FROM users WHERE lastname = 'Smith' might return 50 rows, while querying SELECT * FROM users WHERE id = 105 will mathematically guarantee a maximum of 1 row.

• Q: A developer writes SELECT name FROM employees WHERE salary > 100,000; and the database throws a syntax error. What is wrong with the integer formatting in the WHERE clause? (Answer: No commas allowed in SQL integers).

14. FAQs #

15. Summary #

16. Next Chapter Recommendation #