SELECT Queries and Filtering Data

# CHAPTER 7

SELECT Queries and Filtering Data #

1. Introduction #

2. Learning Objectives #

• Retrieve all rows and columns using SELECT *.

• Retrieve specific columns to optimize performance.

• Use the WHERE clause to filter rows based on conditions.

• Utilize basic comparison operators (=, >, <).

• Remove duplicate results using SELECT DISTINCT.

3. The Basics: SELECT * #

-- "Select ALL columns FROM the employees table"
SELECT * FROM employees;

*Output: A massive grid displaying every piece of data in the table.*

4. Selecting Specific Columns #

-- We only want to see names and emails. Ignore the salary and ID.
SELECT first_name, email FROM employees;

*Output: A clean, 2-column table. This executes thousands of times faster than SELECT * on a large database.*

5. Filtering Data with the WHERE Clause #

-- Find the single employee with ID number 4
SELECT * FROM employees 
WHERE emp_id = 4;

-- Find everyone who works in the IT department
SELECT first_name, email FROM employees 
WHERE department = 'IT';

6. Comparison Operators #

• = (Equal to)

• > (Greater than)

• < (Less than)

• >= (Greater than or equal to)

• <= (Less than or equal to)

• != or <> (Not equal to)

-- Find everyone making a six-figure salary
SELECT first_name, salary FROM employees 
WHERE salary >= 100000;

-- Find everyone who does NOT work in the Exec department
SELECT first_name, department FROM employees 
WHERE department != 'Exec';

7. Handling Duplicates: SELECT DISTINCT #

-- This returns every department name exactly ONE time, no matter how many employees are in it.
SELECT DISTINCT department FROM employees;

-- Output:
-- IT
-- Sales
-- HR
-- Exec

8. Mini Project: The Analyst's Dashboard #

-- The perfect query for the boss:
SELECT first_name, email, salary 
FROM employees 
WHERE salary < 60000;

-- Output:
-- Mike | mike@company.com | 45000.00
-- John | john@company.com | 55000.00

9. Common Mistakes #

• Case Sensitivity in Data: In standard MySQL configurations, comparing text in a WHERE clause (e.g., WHERE department = 'sales') is *case-insensitive*. It will match 'Sales', 'SALES', and 'sales'. However, table names and column names ARE case-sensitive on Linux servers! Always use exact lowercase casing for columns.

• Using = with NULL: If an employee's hire date is blank (NULL), you CANNOT find them by typing WHERE hiredate = NULL. Standard math operators do not work on "nothingness". (We will learn how to find NULLs in Chapter 9).

• Never use SELECT * in Production Code: If your PHP or Python application only needs the user's email, explicitly write SELECT email. If you use SELECT *, and a database admin adds a 5-Megabyte profilepicture column to the table later, your app will suddenly crash because it's downloading 5MB of data for every user unnecessarily!

11. Exercises #

1. 1. Write a query to select ONLY the firstname and department columns for employees who make exactly $85,000.

• Q: Describe the architectural difference in how MySQL processes a SELECT * query versus a SELECT firstname, last_name query.

• Q: Explain a scenario where the SELECT DISTINCT command is absolutely necessary to generate an accurate business report.

14. FAQs #

15. Summary #

16. Next Chapter Recommendation #