Module 2: Core SQL
Lecture 06: Single-Table Queries & Scalar Functions
Conceptual Execution Order, SELECT, WHERE, LIKE pattern matching, Scalar Functions, and CASE Expressions
Lecture Source Slide Deck
Original Slides: Lecture 06 (47 slides) · Amit Kumar Dhar (IIT Bhilai)
Download lecture06.pdf
1. The Logical Execution Order of SQL
Unlike procedural programming languages where execution reads top-to-bottom, an SQL query is written in one order but executed logically in a completely different sequence:
Written Order: Logical Execution Order:
1. SELECT 1. FROM table_name (Identify data source)
2. FROM 2. WHERE condition (Filter individual rows)
3. WHERE 3. SELECT column_list / exprs (Compute expressions & project)
4. ORDER BY 4. ORDER BY sort_criteria (Sort remaining rows)
5. LIMIT / OFFSET 5. LIMIT / OFFSET (Slice row window)Why Column Aliases Fail in WHERE
Because WHERE executes before SELECT, you cannot use an alias created in the SELECT clause within your WHERE filter:
-- WRONG: Raises column does not exist error!
SELECT name, (salary / 12.0) AS monthly_pay
FROM instructor
WHERE monthly_pay > 6000;
-- CORRECT: Repeat expression or use CTE / subquery
SELECT name, (salary / 12.0) AS monthly_pay
FROM instructor
WHERE (salary / 12.0) > 6000;2. Filtering & Comparison Operators
-- Range comparison using BETWEEN
SELECT name, salary
FROM instructor
WHERE salary BETWEEN 70000 AND 90000;
-- Set membership using IN
SELECT title, dept_name
FROM course
WHERE dept_name IN ('Comp. Sci.', 'Physics', 'Finance');
-- Pattern matching with LIKE
-- % matches 0 or more characters; _ matches exactly one character
SELECT course_id, title
FROM course
WHERE title LIKE '%System%' AND title NOT LIKE '%Intro%';3. Useful Scalar Functions
String Functions
UPPER(str)/LOWER(str): Case conversion.LENGTH(str): Returns character count.SUBSTR(str, start, len): Extracts substring (1-indexed).TRIM(str): Strips whitespace.
Numeric & Math Functions
ROUND(val, decimals): Rounds floating point numbers (e.g.,ROUND(salary / 12.0, 2)).ABS(val): Absolute value.%(Modulo): Checks divisibility (e.g.,tot_cred % 10 = 0).
4. Conditional Expressions: CASE
The CASE statement brings conditional branching directly into SQL queries:
SELECT
name,
salary,
CASE
WHEN salary >= 90000 THEN 'High'
WHEN salary >= 70000 THEN 'Medium'
ELSE 'Low'
END AS salary_tier
FROM instructor
ORDER BY salary DESC;5. Topic Practice Questions
SELECT
name,
ROUND(salary / 12.0, 2) AS monthly_pay
FROM instructor
WHERE salary BETWEEN 70000 AND 90000
ORDER BY salary DESC;SELECT id, name, tot_cred
FROM student
ORDER BY tot_cred DESC, name ASC
LIMIT 3;SELECT course_id, title
FROM course
WHERE LOWER(title) LIKE '%system%'
AND LOWER(title) NOT LIKE '%intro%';SELECT id, name, tot_cred
FROM student
WHERE (tot_cred % 10 = 0) AND (tot_cred > 0);