PART 3 • SQL MASTERY

Think in Relations. Express the Result in SQL.

Build a precise SQL mental model before writing large queries: statement purpose, clause structure, data types, logical execution and NULL behavior.

Level 09 of 18Foundation SQL120–150 minutesNo database required

BY THE END, YOU CAN

Classify common SQL statements.
Read SELECT clause by clause.
Trace logical query processing.
Choose suitable data-type families.
Predict TRUE, FALSE and UNKNOWN.
Avoid common portability mistakes.

01 • BUILD THE RIGHT MENTAL MODEL

SQL Describes What Result Is Needed

The DBMS chooses a physical execution strategy that preserves the query’s meaning.

IMPERATIVE THINKING

Specify every operation

Open a file, loop through rows, test each row, keep selected fields and sort the collection.

versus

DECLARATIVE SQL

Describe the required relation

SELECT name, cgpa
FROM students
WHERE branch = 'AIML'
ORDER BY cgpa DESC;

SQL textyour request→Parsersyntax and names→Optimizerchooses a plan→Executorprocesses data→Result relationrows and columns

02 • CLASSIFY STATEMENT PURPOSE

SQL Statements Define, Manipulate, Control and Transact

DDL

Data Definition

CREATE · ALTER · DROP · TRUNCATE

Defines or changes database objects.

DML

Data Manipulation

INSERT · UPDATE · DELETE · MERGE

Adds, changes or removes rows.

DQL

Data Query

SELECT

Retrieves a derived result relation.

DCL

Data Control

GRANT · REVOKE

Controls privileges and authorization.

TCL

Transaction Control

COMMIT · ROLLBACK · SAVEPOINT

Controls transaction boundaries.

Terminology note: These are common teaching labels, but exact classifications vary. Some references place SELECT within DML rather than using a separate DQL group.

03 • READ A QUERY AS A STRUCTURE

Every Clause Has One Responsibility

SELECT branch, COUNT(*) AS student_count
FROM students
WHERE cgpa >= 7.5
GROUP BY branch
HAVING COUNT(*) >= 2
ORDER BY student_count DESC;

SELECT chooses result expressions and aliases.

FROM establishes source relations.

WHERE filters individual rows.

GROUP BY forms groups with equal key values.

HAVING filters completed groups.

ORDER BY sorts the final result.

WHERERows before groupingWHERE cgpa >= 7.5

HAVINGGroups after groupingHAVING COUNT(*) >= 2

04 • SEPARATE WRITTEN FROM LOGICAL ORDER

SQL Is Not Logically Processed Top to Bottom

WRITTEN ORDER

SELECT
FROM / JOIN
WHERE
GROUP BY
HAVING
ORDER BY
LIMIT / FETCH

CONCEPTUAL LOGICAL ORDER

FROM / JOIN
WHERE
GROUP BY
HAVING
SELECT
DISTINCT
ORDER BY
LIMIT / FETCH

Why a SELECT alias may not work in WHERE

SELECT cgpa * 10 AS score
FROM students
WHERE score > 80;  -- often invalid

WHERE is logically evaluated before SELECT creates score. Many systems allow the alias in ORDER BY because ORDER BY is logically later.

05 • WRITE PRECISE TOKENS

Keywords, Identifiers, Literals, Expressions and Predicates

KEYWORDSELECT

Part of SQL grammar; conventionally uppercase.

IDENTIFIERstudent_name

Names a database object.

STRING LITERAL'AIML'

Text values use single quotes.

NUMERIC LITERAL8.50

Numeric values normally need no quotes.

EXPRESSIONcredits * fee

Calculates a value.

PREDICATEcgpa >= 8

Produces TRUE, FALSE or UNKNOWN.

'single quotes'String and date literals

"double quotes"Delimited SQL identifiers

`backticks` / [brackets]Vendor-specific identifier quoting

06 • MATCH THE DOMAIN

Data Types Protect Meaning, Range and Operations

EXACT NUMERIC

INTEGER, DECIMAL(p,s)

Use DECIMAL/NUMERIC for exact fixed-scale values such as money.

APPROXIMATE NUMERIC

REAL, FLOAT, DOUBLE

Useful for measurements; floating point may not represent decimal fractions exactly.

CHARACTER

CHAR(n), VARCHAR(n), TEXT

CHAR is fixed length; VARCHAR is variable length. TEXT details vary.

DATE & TIME

DATE, TIME, TIMESTAMP

Enable temporal validation, comparison and arithmetic.

BOOLEAN

Represents logical values where supported.

SPECIALIZED

BINARY, BLOB, JSON, UUID

Names and behavior are dialect-dependent.

Phone numberCharacter type

An identifier, not a quantity.

PriceDECIMAL

Exact decimal arithmetic.

Date of birthDATE

Valid temporal operations.

CGPAConstrained numeric

Choose precision and enforce range.

07 • REASON WITH MISSING INFORMATION

NULL Produces Three-Valued Logic

NULL means missing, unknown or not applicable; it is not zero, empty text or the word “NULL”.

WRONGmentor_id = NULL

Comparison produces UNKNOWN.

RIGHTmentor_id IS NULL

Explicitly tests nullness.

p	NOT p
TRUE	FALSE
FALSE	TRUE
UNKNOWN	UNKNOWN

p	q	p AND q	p OR q
TRUE	UNKNOWN	UNKNOWN	TRUE
FALSE	UNKNOWN	FALSE	UNKNOWN
UNKNOWN	UNKNOWN	UNKNOWN	UNKNOWN

WHERE keeps onlyTRUE

FALSEUNKNOWN

Both FALSE and UNKNOWN rows are filtered out.

08 • TRACE A QUERY

Logical Query Processing Laboratory

Select a scenario and move through its conceptual stages.

Query scenario

09 • PREDICT BEFORE EXECUTING

NULL Predicate Predictor

Evaluate every row as TRUE, FALSE or UNKNOWN and see what WHERE retains.

10 • WRITE SQL THAT TRAVELS

Standard SQL Is the Base; Dialects Add Differences

Task	Portable direction	Common variations
Limit rows	`FETCH FIRST n ROWS ONLY`	`LIMIT n`, `TOP n`
Generated identity	`GENERATED ... AS IDENTITY`	`AUTO_INCREMENT`, `SERIAL`
Concatenation	`a \|\| b`	`CONCAT(a,b)`, `a + b`
Current date	`CURRENT_DATE`	Vendor functions also exist
Identifiers	`"Order"`	Backticks or brackets

Start standard

Prefer standard constructs when clear.

Know the DBMS

Verify types, functions and syntax.

Avoid reserved names

Names such as order and group need care.

Test NULL behavior

Do not apply ordinary two-valued assumptions.

11 • CHECK YOUR UNDERSTANDING

Ten Formative Concept Checks

1. SQL is primarily:

2. Which belongs to DDL?

3. Which clause establishes source relations logically first?

4. WHERE filters:

5. Best common type for exact money?

6. NULL represents:

7. Correct nullness test?

8. TRUE AND UNKNOWN evaluates to:

9. WHERE retains rows evaluating to:

10. Which syntax is notably dialect-dependent?

Answered correctly: 0 of 10

12 • EXPLAIN & PREPARE

University and Interview Questions

2-MARK QUESTIONS

What is SQL?
List SQL statement families.
WHERE versus HAVING?
Define NULL.
What is three-valued logic?

5-MARK / PROBLEMS

Explain logical SELECT processing.
Classify SQL statements.
Compare exact and approximate numerics.
Construct 3VL truth tables.

INTERVIEW QUESTIONS

Why can an alias fail in WHERE?
Why is = NULL incorrect?
CHAR versus VARCHAR?
Why avoid FLOAT for money?
How do dialects differ?

Show the SQL reasoning answer format

State the required result.
Identify source relations.
Apply row predicates.
Form and filter groups.
Choose result expressions.
Eliminate duplicates only if required.
Sort and limit conceptually last.
Account for NULL and dialect behavior.

LEVEL 9 SUMMARY

You Can Now Read SQL Before You Write It

SQL declaratively expresses a result relation.
Statement families describe purpose.
Written order differs from logical processing.
Types must match meaning and operations.
NULL introduces UNKNOWN.
WHERE keeps only TRUE.
Portable SQL begins with standards.

COURSE CHECKPOINT

Mark this level when you can classify statements, trace SELECT processing and predict NULL conditions without executing a query.

Saved in this browser only.