DBMS & SQL›Level 12
PART 3 • SQL MASTERY

Ask Precise Questions. Read Trustworthy Results.

Shape a result through projection, predicates, ordering and limits while reasoning about NULL, duplicates and deterministic output.

Level 12 of 18Core SQL150–190 minutesLevel 11 recommended
BY THE END, YOU CAN
  • Select only needed columns.
  • Build correct compound predicates.
  • Use LIKE, IN and BETWEEN.
  • Filter NULL safely.
  • Sort and paginate deterministically.
  • Classify values with CASE.
01 • SHAPE THE RESULT

SELECT Projects Expressions from a Row Source

SELECT student_id,
       name AS student_name,
       cgpa
FROM students;
PROJECTION

Choose output expressions

Return only columns and calculations the consumer needs.

ALIAS

Name the result clearly

AS student_name labels an output expression; it does not rename stored data.

ROW SOURCE

Identify where rows come from

FROM students supplies candidate rows.

Avoid routine SELECT *

It exposes unnecessary columns, couples code to schema order and increases data transfer.

Expressions are allowedcredits * fee AS total_fee

A result column may be computed without changing stored values.

DISTINCT is result-wideSELECT DISTINCT branch, status

Duplicate combinations of all selected expressions are removed.

02 • READ SQL IN TWO ORDERS

Written Order Is Not Logical Processing Order

WRITTEN ORDER
  1. SELECT
  2. FROM
  3. WHERE
  4. ORDER BY
  5. LIMIT / FETCH
SQL engine reasons from sources to output
SIMPLIFIED LOGICAL ORDER
  1. FROM
  2. WHERE
  3. SELECT
  4. DISTINCT
  5. ORDER BY
  6. LIMIT / FETCH
03 • KEEP ONLY TRUE ROWS

WHERE Builds a Boolean Gate

COMPARISON=   <>   <   <=   >   >=

Compare compatible values. SQL uses =, not ==.

ANDbranch = 'AIML' AND cgpa >= 8

Both conditions must be TRUE.

ORbranch = 'AIML' OR branch = 'CSE'

At least one condition must be TRUE.

NOTNOT status = 'WITHDRAWN'

Negates a predicate but preserves UNKNOWN as UNKNOWN.

WHERE branch = 'AIML'
   OR branch = 'CSE' AND cgpa >= 9
means
WHERE branch = 'AIML'
   OR (branch = 'CSE' AND cgpa >= 9)

AND is normally evaluated before OR. Use parentheses to make the business rule visible rather than relying on memory.

04 • MATCH PATTERNS, SETS AND RANGES

Choose the Predicate That Expresses the Rule Directly

LIKEname LIKE 'A%'

% matches zero or more characters; _ matches exactly one. Case sensitivity is product and collation dependent.

INbranch IN ('AIML', 'CSE')

Expresses membership more clearly than repeated equality joined with OR.

BETWEENcgpa BETWEEN 8 AND 9

Includes both boundaries. It is equivalent to cgpa >= 8 AND cgpa <= 9.

NOT INbranch NOT IN ('ECE', 'IT')

Be careful when the compared value or list contains NULL; UNKNOWN can remove unexpected rows.

'A%' begins with A'%an%' contains an'_i%' second character is i'____' exactly four characters
05 • TEST MISSING VALUES EXPLICITLY

NULL Requires IS NULL or IS NOT NULL

INCORRECTWHERE mentor_id = NULLMatches no rows through TRUE

Ordinary comparison with NULL produces UNKNOWN.

→
CORRECTWHERE mentor_id IS NULLMatches rows with missing mentor_id

IS NULL is the dedicated nullness predicate.

COALESCECOALESCE(cgpa, 0)

Returns the first non-NULL argument. Use a replacement only when its meaning is valid.

NULL-safe orderingORDER BY cgpa NULLS LAST

Useful where supported; default NULL placement and syntax vary by DBMS.

Three-valued gateWHERE keeps TRUE only

FALSE and UNKNOWN are both excluded.

06 • MAKE OUTPUT REPEATABLE

ORDER BY Defines Sequence; Tables Do Not

SELECT student_id, name, cgpa
FROM students
ORDER BY cgpa DESC, student_id ASC
LIMIT 5 OFFSET 10;
Multiple sort keys

student_id breaks ties between equal CGPA values.

Direction

ASC is commonly the default; write DESC when highest values should lead.

Pagination

LIMIT/OFFSET, FETCH/OFFSET and TOP are dialect families. Deep OFFSET pages can be expensive or unstable under changing data.

Deterministic pages

Include a unique final sort key so the same rows do not move arbitrarily between pages.

07 • DERIVE CATEGORIES

CASE Adds Conditional Meaning to a Result

SELECT name, cgpa,
       CASE
         WHEN cgpa IS NULL THEN 'Pending'
         WHEN cgpa >= 9 THEN 'Outstanding'
         WHEN cgpa >= 8 THEN 'Strong'
         ELSE 'Developing'
       END AS performance
FROM students;
First TRUE WHEN wins→Remaining WHEN clauses are skipped→ELSE handles unmatched rows
08 • BUILD AND TRACE A QUERY

Interactive SELECT Laboratory

Choose result columns, combine up to two filters, sort and limit. The generated SQL and every logical-stage row count update together.

1. Select columns
2. Filter rows
3. Sort and limit
GENERATED SQL
RESULT SET

10 rows returned

09 • PREDICT BEFORE READING THE RESULT

Predicate Outcome Laboratory

Select a predicate to see which rows become TRUE, FALSE or UNKNOWN.

10 • CHECK YOUR UNDERSTANDING

Ten Formative Concept Checks

1. SELECT primarily controls:

2. Which clause filters individual source rows?

3. BETWEEN 8 AND 9 includes:

4. Pattern 'A%' means:

5. Correct way to find missing mentor_id?

6. DISTINCT applies to:

7. Without ORDER BY, result row order is:

8. AND and OR should be clarified using:

9. In searched CASE, SQL returns:

10. Stable pagination needs:

Answered correctly: 0 of 10
11 • EXPLAIN & PREPARE

University and Interview Questions

2-MARK QUESTIONS
  1. Define projection.
  2. What does DISTINCT do?
  3. LIKE wildcard meanings?
  4. Why use IS NULL?
  5. What is an alias?
5-MARK / PRACTICAL
  1. Write filters using AND and OR.
  2. Compare IN and repeated OR.
  3. Explain logical query order.
  4. Build deterministic pagination.
INTERVIEW QUESTIONS
  1. Why avoid SELECT *?
  2. Can WHERE use a SELECT alias?
  3. Why can NOT IN and NULL surprise?
  4. How do you sort NULL values?
  5. How does CASE choose a branch?
Show the SELECT answer framework
  1. State the exact result grain.
  2. List only required expressions.
  3. Name the source relation.
  4. Translate the business rule into parenthesized predicates.
  5. Handle NULL explicitly.
  6. Apply duplicate removal only if required.
  7. Define deterministic ordering and a unique tie-breaker.
  8. Verify boundary cases and expected row count.
LEVEL 12 SUMMARY

You Can Now Build Predictable Result Sets

  • SELECT shapes output expressions; FROM supplies rows.
  • WHERE keeps only TRUE predicates.
  • LIKE, IN and BETWEEN state common filters directly.
  • NULL needs explicit three-valued reasoning.
  • ORDER BY is required for guaranteed sequence.
  • CASE returns the first matching branch.
  • Stable pagination requires deterministic ordering.
COURSE CHECKPOINT

Mark this level when you can predict which rows and columns a SELECT returns before running it.

Saved in this browser only.