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. Column Aliases (AS Keyword) #

SELECT first_name AS "First Name", salary AS "Annual Income" 
FROM employees;

9. Common Mistakes #

• Case Sensitivity in Data: In PostgreSQL, text comparisons inside a WHERE clause are strictly case-sensitive! WHERE department = 'sales' will NOT match a row that contains 'Sales'. (We will learn how to do case-insensitive searches in Chapter 8 using ILIKE).

• 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.

SELECT first_name, salary AS "Needs_Raise" 
FROM employees 
WHERE salary < 50000;

13. MCQ Quiz with Answers #

14. Interview Questions #

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

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

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #