05 - SQL Queries: SELECT, WHERE, ORDER BY

🎯 Learning Objectives

• Write SELECT-FROM-WHERE queries to retrieve data
• Use DISTINCT, ORDER BY, and column aliases
• Apply string pattern matching with LIKE
• Combine conditions with AND, OR, and NOT
• Write queries with Cartesian products and join conditions

📋 Prerequisites

• 04 - SQL DDL — Understanding of tables and columns
• 03 - Relational Model — Relations, tuples, attributes

📖 Core Content

5.1 Intuition: SQL is Declarative

python
Run Inline
results = []
for row in file:
    if row['dept_name'] == 'Comp. Sci.' and row['salary'] > 70000:
        results.append(row['name'])

sql
SELECT name FROM instructor
WHERE dept_name = 'Comp. Sci.' AND salary > 70000;

Why This Matters: Declarative queries are easier to write, maintain, and optimize. The same SQL works on any database (PostgreSQL, MySQL, Oracle) — the system handles the implementation details.

5.2 The Basic SELECT Statement

sql
SELECT column1, column2, ...
FROM table_name
WHERE condition;

Simple Queries

sql
-- All columns, all rows
SELECT * FROM instructor;
-- Specific columns
SELECT name, salary FROM instructor;
-- All Comp. Sci. instructors
SELECT name FROM instructor
WHERE dept_name = 'Comp. Sci.';

5.3 DISTINCT: Removing Duplicates

sql
-- WITH duplicates (default)
SELECT dept_name FROM instructor;
-- Output: Comp. Sci., Finance, Music, Physics, History, Comp. Sci., ...
-- WITHOUT duplicates
SELECT DISTINCT dept_name FROM instructor;
-- Output: Comp. Sci., Finance, Music, Physics, History

When to use: Use DISTINCT only when you need unique values. It adds a sorting/grouping step that costs performance on large datasets.

5.4 WHERE Clause: Filtering Rows

Combining Conditions with AND, OR, NOT

sql
-- AND: Both conditions must be true
SELECT name FROM instructor
WHERE dept_name = 'Comp. Sci.' AND salary > 70000;
-- OR: Either condition can be true
SELECT name FROM instructor
WHERE dept_name = 'Finance' OR dept_name = 'Music';
-- NOT: Negate a condition
SELECT name FROM instructor
WHERE NOT dept_name = 'Music';

sql
-- Correct: OR evaluated first
SELECT name FROM instructor I, department D
WHERE I.dept_name = D.dept_name
  AND (I.dept_name = 'Finance' OR D.building IN ('Watson', 'Taylor'));

5.5 ORDER BY: Sorting Results

sql
-- Single column ascending (default)
SELECT name, salary FROM instructor ORDER BY salary;
-- Single column descending
SELECT name, salary FROM instructor ORDER BY salary DESC;
-- Multiple columns: sort by dept_name, then by salary descending within each dept
SELECT name, dept_name, salary
FROM instructor
ORDER BY dept_name ASC, salary DESC;

sql
SELECT name, dept_name, salary FROM instructor ORDER BY 3 DESC;
-- Sorts by the 3rd column (salary)

5.6 Column Aliases (AS)

sql
SELECT name AS instructor_name, salary AS annual_salary
FROM instructor;
-- Also works for table aliases (very common!)
SELECT I.name, D.building
FROM instructor AS I, department AS D
WHERE I.dept_name = D.dept_name;

5.7 String Operations: LIKE and Patterns

• % matches any sequence of characters (including empty)
• _ matches exactly one character

sql
-- Names starting with 'S'
SELECT name FROM instructor WHERE name LIKE 'S%';
-- Names with 'son' anywhere
SELECT name FROM instructor WHERE name LIKE '%son%';
-- Course IDs with exactly 3 letters before hyphen
SELECT title FROM course WHERE course_id LIKE '___-%';

5.8 Cartesian Product (Cross Join)

sql
SELECT * FROM student, department;

sql
-- Correct: join condition in WHERE
SELECT name, budget
FROM student, department
WHERE student.dept_name = department.dept_name AND budget < 100000;

5.9 Worked Examples

Example 1: Basic filtering

sql
SELECT name FROM instructor WHERE dept_name = 'Finance';

Example 2: Multiple conditions with ORDER BY

sql
SELECT name, salary
FROM instructor
WHERE dept_name = 'Comp. Sci.' AND salary >= 70000
ORDER BY salary DESC;

Example 3: String matching with JOIN

sql
SELECT DISTINCT I.name
FROM instructor I, department D
WHERE I.dept_name = D.dept_name
  AND I.name LIKE '%in%'
  AND D.building = 'Taylor';

1. FROM: Cartesian product of instructor(I) × department(D)
2. WHERE: Keep rows where dept_name matches, name contains 'in', building is 'Taylor'
3. SELECT: Return unique names

📐 Key Formulas / Concepts

⚠️ Common Pitfalls

Pitfall 1: Forgetting the WHERE Clause in Multi-Table Queries

sql
SELECT name, budget FROM student, department;

sql
SELECT name, budget
FROM student, department
WHERE student.dept_name = department.dept_name;

Pitfall 2: Using = with NULL

Pitfall 3: Confusing Execution Order

sql
SELECT salary * 12 AS annual_salary FROM instructor WHERE annual_salary > 100000;

sql
SELECT salary * 12 AS annual_salary FROM instructor WHERE salary * 12 > 100000;

📝 Practice Questions

Q1. Write a query to find the names of all instructors whose salary is between 60000 and 80000.

Answer

sql
SELECT name FROM instructor
WHERE salary BETWEEN 60000 AND 80000;
-- BETWEEN is inclusive: >= 60000 AND <= 80000

Q2. List all course titles whose course_id has three letters followed by a hyphen.

Answer

sql
SELECT title FROM course
WHERE course_id LIKE '___-%';
-- Three underscores match exactly 3 characters, % matches the rest

Q3. Find the names of all instructors in the History department, sorted by name alphabetically.

Answer

sql
SELECT name FROM instructor
WHERE dept_name = 'History'
ORDER BY name ASC;

Q4. What is the difference between using DISTINCT and omitting it?

Answer

• Without DISTINCT: All rows matching the query are returned, including duplicates
• With DISTINCT: Duplicate rows are eliminated, only unique combinations returned

Example: SELECT dept_name FROM instructor returns 12 rows (some duplicate depts). SELECT DISTINCT dept_name FROM instructor returns 7 unique departments.

Q5. Write a query using AS to display instructor names and their annual salary (salary * 12).

Answer

sql
SELECT name, salary * 12 AS annual_salary
FROM instructor;

Q6. Find all instructors who work in a department whose building is either 'Watson' or 'Taylor'.

Answer

sql
SELECT DISTINCT I.name
FROM instructor I, department D
WHERE I.dept_name = D.dept_name
  AND D.building IN ('Watson', 'Taylor');

Q7. Why does SELECT name FROM instructor WHERE salary = NULL return no rows?

Answer

NULL is not a value — it's the absence of a value. Comparison with NULL always returns "unknown" (neither true nor false). The WHERE clause only keeps rows where the condition is true.

To check for NULL, use:

sql
SELECT name FROM instructor WHERE salary IS NULL;
SELECT name FROM instructor WHERE salary IS NOT NULL;

Q8. Explain the execution order of a SELECT-FROM-WHERE-ORDER BY query.

Answer

The logical execution order is:

1. FROM: Products of all tables (Cartesian product if multiple)
2. WHERE: Filters rows based on condition
3. SELECT: Picks the columns to display (or computes expressions)
4. ORDER BY: Sorts the result

Note: This is the logical order. The actual execution may differ (the optimizer chooses the most efficient path).

🔗 Cross-References

• Next Topic: 06 - SQL Aggregation & Grouping
• Previous Topic: 04 - SQL DDL
• Related: BSMS2001 (BDM) — Business querying
• Related: BSCS2003 (MAD 1) — Using SQL in web applications
• Textbook: Silberschatz, Korth, Sudarshan — Chapter 3 (SQL) Join Discord Previous04 - SQL DDLNext06 - SQL Aggregation