THE COMPLETE SQL & PL/SQL MASTER

GUIDE FOR PLACEMENTS

A deep, theoretical, exhaustive walk-through. Read it like a book.

PART A — SQL DEEP DIVE

CHAPTER 1: THE MENTAL MODEL OF SQL

Before learning a single command, you need to understand how SQL "thinks." Most
candidates fail SQL rounds not because they don't know syntax, but because they have the
wrong mental model. Fix the model and the syntax becomes obvious.

1.1 SQL is Declarative

When you write code in Java, Python, or C++, you tell the computer how to do something
— step by step. SQL is the opposite. You tell the database what result you want, and the
database itself figures out how to compute it.

For example, when you write SELECT name FROM employees WHERE salary > 50000, you are not
telling the database to "open the file, loop through rows, check each one, append to a list."
You are stating a fact: "I want the names of employees whose salary exceeds 50000." The
database's query optimizer then decides:

Should I read the entire table sequentially (full table scan) or use an index?
If joining, should I use nested loops, a hash join, or a sort-merge join?
In what order should I apply filters?
Can I rewrite this query into an equivalent but faster form?

This is why two queries that look completely different can produce identical performance —
the optimizer rewrites them into the same internal plan. And it's why a "clever" looking query
can be slower than a "dumb" one if it confuses the optimizer.

Takeaway for you: Don't micromanage. Write clear, simple SQL. Trust the optimizer. Only
get clever when you've measured and there's a real problem.

1.2 SQL Operates on Sets, Not Rows

This is the single biggest mental shift programmers from other backgrounds need to make.
SQL doesn't loop. When you write UPDATE employees SET salary = salary * 1.1 WHERE dept_id
= 1,the database doesn't iterate through rows like a for loop — it applies the operation to
the entire set of qualifying rows in one logical step.

If you find yourself thinking "for each row, do X" — you're probably writing slow or wrong
SQL. Instead, think: "given this set of rows, produce this transformed set." Joins are set
operations. GROUP BY is a set partition. Window functions are set computations. Embrace
sets.

The only time you legitimately fall back to row-by-row processing is in PL/SQL with cursors,
and even there it's discouraged for performance reasons.

1.3 The Logical Order of Query Execution — THE Most Important Concept

You write a SELECT statement in this order:

SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT

But the database executes it in a completely different order:

1. FROM — identify the source tables

2. ON — apply join conditions
3. JOIN — combine rows from joined tables
4. WHERE — filter individual rows
5. GROUP BY — partition rows into groups
6. HAVING — filter groups
7. SELECT — project (compute) the output columns
8. DISTINCT — eliminate duplicate rows
9. ORDER BY — sort the result
10. LIMIT — truncate to N rows

Understanding this order explains so many "weird" SQL behaviors:

Why can't I use a column alias inside WHERE? Because WHERE (step 4) runs before
SELECT (step 7), so the alias doesn't exist yet. You can, however, use aliases in ORDER BY
because that runs after SELECT.
Why does WHERE COUNT(*) > 5 give an error? Aggregates are computed during GROUP
BY (step 5), but WHERE (step 4) runs before that. Use HAVING instead — it runs after
grouping.
Why are duplicate rows still there when I use DISTINCT with ORDER BY? DISTINCT
happens before ORDER BY, so the ordering doesn't affect duplicate detection.
Why does a query with a LEFT JOIN sometimes drop rows even though I expected
them to stay? Probably because your WHERE clause has a condition on the right-side
table that filters out rows where it's NULL — effectively converting the LEFT JOIN into an
INNER JOIN. Move that condition into the ON clause instead.
Memorize this order. Internalize it. It will resolve 70% of the confusion you ever feel about
SQL.

1.4 NULL — The Three-Valued Logic Trap

SQL doesn't use the regular TRUE/FALSE logic you're used to. It uses three-valued logic:
TRUE, FALSE, and UNKNOWN. Any expression involving NULL produces UNKNOWN, not
TRUE or FALSE.

This has bizarre consequences:

NULL = NULL is not TRUE — it's UNKNOWN. Two unknowns aren't proven equal.
NULL != 5 is also UNKNOWN, not TRUE. You don't know the value, so you can't say it's
different.
WHERE x = NULL will never return rows. You must use WHERE x IS NULL.
WHERE x NOT IN (1, 2, NULL) returns ZERO rows, because x != NULL is UNKNOWN, which
fails the WHERE filter.

WHERE only keeps rows where the condition is TRUE. UNKNOWN and FALSE both get
filtered out. This is the source of countless bugs, including in production. Whenever you
write filters, ask yourself: "what happens if this column is NULL?"

CHAPTER 2: DDL — DATA DEFINITION LANGUAGE

DDL commands define the structure of your database — tables, columns, constraints. They
auto-commit, which means once executed, they cannot be rolled back. Be careful.

2.1 CREATE TABLE — Building the Foundation

A table is the fundamental container of data. Creating one means deciding what columns it
has, what types those columns are, and what rules (constraints) the database should enforce.

Constraints are your single most powerful tool for data integrity. They are rules enforced by
the database engine itself — meaning even if a buggy application tries to insert bad data,
the database will reject it. This protects you from yourself and from every future developer
who touches the system.

CREATE TABLE employees (

emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
salary DECIMAL(10,2) DEFAULT 30000 CHECK (salary > 0),
dept_id INT,
hire_date DATE DEFAULT CURRENT_DATE,
 CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Result after execution:

emp_id (PK) name (NN) email (UQ) salary (≥0, def 30000) dept_id (FK) hire_date
— — — — — —

An empty table with structure defined and constraints active.

Understanding each constraint deeply:

PRIMARY KEY uniquely identifies each row. It implicitly enforces both UNIQUE and
NOT NULL. A table can have only one. Always pick a column whose value never
legitimately changes — an emp_id is good; an email is bad because people change
emails.
FOREIGN KEY ensures referential integrity. If dept_id = 5 exists in employees, then 5
must exist in departments. You can configure what happens when a referenced row is
deleted: ON DELETE CASCADE (also delete dependents), ON DELETE SET NULL (orphan them),
or ON DELETE RESTRICT (block the delete entirely — default).
UNIQUE prevents duplicates but unlike PRIMARY KEY, it permits one NULL value
(because NULL ≠ NULL, so they don't "collide").
NOT NULL is one of the most under-rated constraints. Every nullable column adds
branches to your query logic. Make columns NOT NULL whenever possible.
CHECK lets you enforce arbitrary boolean rules. CHECK (salary > 0) blocks negative
salaries. CHECK (status IN ('active', 'inactive', 'pending')) enforces enumerated
values.
DEFAULT supplies a value when INSERT omits the column. Useful for timestamps
(DEFAULT CURRENT_TIMESTAMP) and status flags.

Insight: Always declare constraints when creating the table. Adding them later requires the
database to scan the entire table to verify existing data complies — locking the table for the
duration on large datasets. Doing it at creation is free.

2.2 ALTER TABLE — Evolving Your Schema

Schemas change. Maybe you forgot a column. Maybe a column needs to be wider. ALTER
TABLE lets you change structure without dropping data.

Starting with this table:

emp_id name salary

1 Riya 50000
2 Amit 60000

Add a new column:

 ALTER TABLE employees ADD email VARCHAR(100);

emp_id name salary email

1 Riya 50000 NULL
2 Amit 60000 NULL

The new column is added with NULL for existing rows. If you add it with a DEFAULT, existing
rows get that default value, but on very large tables this can lock the table briefly while the
database fills in the new column.

Modify column type or size:

ALTER TABLE employees MODIFY name VARCHAR(100); -- Oracle/MySQL

ALTER TABLE employees ALTER COLUMN name VARCHAR(100); -- SQL Server/PostgreSQL

Drop a column:

ALTER TABLE employees DROP COLUMN email;

Rename a column:

ALTER TABLE employees RENAME COLUMN name TO full_name;

Add a constraint after creation:

ALTER TABLE employees ADD CONSTRAINT chk_sal CHECK (salary > 0);

This will fail if existing rows violate the constraint — a useful safety check.

2.3 DROP, TRUNCATE, DELETE — The Most Asked Comparison in

Interviews

These three feel similar but behave very differently. Interviewers love this question because
the difference reveals whether you understand transactions, logging, and DDL vs DML.

DELETE is a DML command. It removes specific rows based on the WHERE clause. Each row
deletion is logged in the transaction log, which means:

You can ROLLBACK if you're inside a transaction.

Triggers fire (BEFORE DELETE, AFTER DELETE).
It's slow on large data because logging every row is expensive.
The table structure, indexes, and constraints remain untouched.
 DELETE FROM employees WHERE dept_id = 5;

TRUNCATE is a DDL command. It removes ALL rows by deallocating the underlying data
pages — essentially "forget where the data lived." Because nothing is logged row-by-row,
it's incredibly fast.

It cannot be rolled back in most databases (it auto-commits).

Triggers do NOT fire.
Identity/auto-increment columns typically reset.
The table structure remains intact — you still have an empty table.

TRUNCATE TABLE employees;

DROP is a DDL command that removes the entire table — data, structure, indexes,
constraints, triggers, everything. The table simply ceases to exist.

DROP TABLE employees;

The interview-ready summary: DELETE is selective and reversible. TRUNCATE wipes

everything fast but irreversibly. DROP removes the table itself.

CHAPTER 3: DML — DATA MANIPULATION LANGUAGE

DML changes the data inside tables. Unlike DDL, changes are NOT permanent until you
COMMIT. This gives you a safety net — make changes, verify, and only then commit.

3.1 INSERT — Adding Rows

Starting with an empty employees table.

Single row, columns specified (best practice):

INSERT INTO employees (emp_id, name, salary, dept_id)

VALUES (1, 'Riya', 50000, 1);

emp_id name salary dept_id

1 Riya 50000 1

Always list columns explicitly. If someone later adds a column or reorders them, your INSERT
continues to work. The "lazy" version INSERT INTO employees VALUES (...) depends on column
order — it breaks the day someone restructures the table.
Multiple rows at once:

INSERT INTO employees (emp_id, name, salary, dept_id) VALUES

(2, 'Amit', 60000, 2),
(3, 'Sara', 75000, 1),
(4, 'Vikram', 45000, 2);

emp_id name salary dept_id

1 Riya 50000 1
2 Amit 60000 2
3 Sara 75000 1
4 Vikram 45000 2

This is dramatically faster than four separate INSERTs because it's one network round-trip
and one transaction.

Insert from another table (powerful for ETL):

INSERT INTO high_earners (emp_id, name, salary)

SELECT emp_id, name, salary FROM employees WHERE salary > 55000;

The SELECT runs first, producing a result set; those rows then get inserted into the target.
This is how you copy or migrate data between tables.

3.2 UPDATE — Changing Existing Data

Starting with:

emp_id name salary dept_id

1 Riya 50000 1
2 Amit 60000 2
3 Sara 75000 1

Give everyone in department 1 a 10% raise:

UPDATE employees
SET salary = salary * 1.10
WHERE dept_id = 1;

emp_id name salary dept_id

1 Riya 55000 1
2 Amit 60000 2
3 Sara 82500 1
Notice that the update is set-based. The expression salary * 1.10 is evaluated per row, but
the database doesn't loop in your sense — it applies the update to the entire qualifying set
in one operation. This is why SQL is fast.

Update multiple columns simultaneously:

UPDATE employees
SET salary = 80000, dept_id = 3
WHERE emp_id = 2;

The catastrophic mistake every developer makes once: Running UPDATE employees SET
salary = 50000; without a WHERE clause sets EVERY row's salary to 50000. Always write your
WHERE clause first. Many developers run a SELECT with the same WHERE first to confirm
exactly which rows will be affected, then convert it to UPDATE.

Execution insight: The database evaluates WHERE first to identify target rows, locks them,
then applies the SET expression. If your SET references the column you're updating (e.g.,
salary = salary * 1.1), the original value is used as the input.

3.3 DELETE — Removing Rows

DELETE FROM employees WHERE emp_id = 2;

The row is gone, but the operation is logged so you can ROLLBACK if you haven't committed
yet. Same warning as UPDATE: a DELETE without WHERE deletes every row in the table.

3.4 MERGE (UPSERT) — Insert-or-Update in One Statement

Real-world data sync scenarios constantly need: "If this record exists, update it; if not, insert
it." Writing this as separate SELECT-then-INSERT-or-UPDATE has race conditions and is
wordy. MERGE handles it atomically.

Target employees:

emp_id name salary

1 Riya 50000
2 Amit 60000

Source new_data:

emp_id name salary

2 Amit 65000
3 Sara 70000
 MERGE INTO employees e
USING new_data n ON (e.emp_id = n.emp_id)
WHEN MATCHED THEN
UPDATE SET [Link] = [Link]
WHEN NOT MATCHED THEN
INSERT (emp_id, name, salary) VALUES (n.emp_id, [Link], [Link]);

Result:

emp_id name salary

1 Riya 50000
2 Amit 65000
3 Sara 70000

The database walks through every row in new_data, checks if it exists in employees by the join
condition, and either updates or inserts. Atomic, fast, single-statement.

CHAPTER 4: DQL — THE SELECT STATEMENT IN DEPTH

SELECT is where you'll spend 80% of your SQL life. Mastering it is mastering SQL.

4.1 Basic Anatomy

SELECT column1, column2, expression AS alias

FROM table
WHERE row_condition
GROUP BY column
HAVING group_condition
ORDER BY column
LIMIT n;

Each clause has a precise role. Remember the logical order from Chapter 1: FROM is
processed first, SELECT later. This means filtering happens before projection, and
aggregation happens before final selection.

4.2 WHERE — Filtering Rows

WHERE is evaluated for every row independently. If the expression evaluates to TRUE, the
row passes through to the next stage. FALSE or UNKNOWN (from NULLs) — filtered out.

Given:

emp_id name salary dept_id

1 Riya 50000 1
emp_id name salary dept_id
2 Amit 60000 2
3 Sara 75000 1
4 Vik NULL 2

Compound conditions and various operators:

SELECT * FROM employees

WHERE salary BETWEEN 50000 AND 70000 -- inclusive on both ends
AND dept_id IN (1, 2)
AND name LIKE 'A%' -- starts with 'A'
AND salary IS NOT NULL;

Result: just Amit's row.

Wildcard rules for LIKE:

% matches any number of characters (including zero)

_ matches exactly one character
LIKE 'A%' — names starting with A
LIKE '%a%' — names containing "a" anywhere
LIKE '_a%' — names where second character is "a"

The classic NULL trap:

SELECT * FROM employees WHERE salary != 50000;

This will NOT return Vik (the row with NULL salary), because NULL != 50000 evaluates to
UNKNOWN, which fails the WHERE filter. To include him: WHERE salary != 50000 OR salary IS
NULL. This catches countless developers in production.

4.3 DISTINCT — Eliminating Duplicates

DISTINCT removes duplicate rows from the SELECT projection. Important: it works across the
entire row of selected columns, not individual columns.

SELECT DISTINCT dept_id FROM employees; -- unique departments

SELECT DISTINCT dept_id, job_title FROM employees; -- unique combinations

Under the hood, DISTINCT typically requires a sort or hash operation to identify duplicates
— it isn't free. If you don't need uniqueness, skip it.

4.4 ORDER BY — Sorting Results

 SELECT name, salary FROM employees
ORDER BY salary DESC, name ASC;

Sorts primarily by salary descending; when two rows have the same salary, secondary sort by
name ascending. You can sort by:

Column names
Column position numbers (ORDER BY 2 sorts by the second SELECT column — fragile,
avoid)
Expressions (ORDER BY salary * 12)
Aliases defined in SELECT (because ORDER BY runs after SELECT)
CASE expressions for custom orderings

NULL ordering: Databases differ in default behavior. Oracle puts NULLs last by default in
ASC. PostgreSQL puts them last. MySQL puts them first. Use NULLS FIRST or NULLS LAST
explicitly when it matters.

4.5 LIMIT, FETCH, ROWNUM — Restricting Row Count

Different databases use different syntax:

-- MySQL, PostgreSQL
SELECT * FROM employees ORDER BY salary DESC LIMIT 5;

-- SQL standard (PostgreSQL, Oracle 12c+, modern systems)

SELECT * FROM employees ORDER BY salary DESC FETCH FIRST 5 ROWS ONLY;

-- SQL Server
SELECT TOP 5 * FROM employees ORDER BY salary DESC;

-- Oracle (older)
SELECT * FROM employees WHERE ROWNUM <= 5 ORDER BY salary DESC;

Pagination with OFFSET — for showing page 3 of 10 rows per page:

SELECT * FROM employees ORDER BY emp_id LIMIT 10 OFFSET 20;

OFFSET 20 skips the first 20 rows. Note that ORDER BY is mandatory for pagination —
without it, "rows 21 to 30" is meaningless because rows have no inherent order.

Performance note: Large OFFSETs are slow. OFFSET 100000 makes the database materialize
100000 rows and throw them away. For deep pagination, use keyset pagination (WHERE id >
last_seen_id) instead.

4.6 CASE Expression — Conditional Logic Inline

CASE turns SELECT into a mini programming language. You can produce computed columns
based on conditions.

SELECT name, salary,

CASE
WHEN salary >= 70000 THEN 'High'
WHEN salary >= 50000 THEN 'Medium'
WHEN salary >= 30000 THEN 'Low'
ELSE 'Entry'
END AS salary_band
FROM employees;

name salary salary_band

Riya 55000 Medium
Amit 60000 Medium
Sara 82500 High

CASE evaluates top-to-bottom and stops at the first matching WHEN. The ELSE is optional
but recommended — without it, unmatched rows return NULL.

You'll use CASE constantly: for pivoting (turning rows into columns), conditional aggregation
(SUM(CASE WHEN status='paid' THEN amount ELSE 0 END)), custom sort orders (ORDER BY CASE
status WHEN 'active' THEN 1 ELSE 2 END), and computed columns.

CHAPTER 5: AGGREGATE FUNCTIONS AND GROUP BY

5.1 Aggregate Functions

Aggregates collapse multiple rows into a single value. The fundamental ones:

Function What it does

COUNT(*) Counts ALL rows including those with NULLs
COUNT(column) Counts rows where that column is NOT NULL
COUNT(DISTINCT column) Counts unique non-NULL values
SUM(column) Total of non-NULL values
AVG(column) Mean of non-NULL values
MIN(column), MAX(column) Smallest/largest non-NULL value

The critical NULL behavior: Every aggregate except COUNT(*) silently ignores NULLs. This is
usually what you want, but sometimes it bites you. If salaries are [100, 200, NULL], then
AVG(salary) = 150 (sum 300 ÷ count 2), not 100 (sum 300 ÷ count 3). If you want NULLs
treated as zero, do AVG(COALESCE(salary, 0)).
COUNT(*)is special — it counts rows, not values. Use it when you want "how many rows total"
regardless of NULL.

5.2 GROUP BY — Partitioning Data

GROUP BY divides rows into groups based on column values, and then aggregates compute
one value per group. Conceptually, you're saying "bucket these rows by X, then summarize
each bucket."

Starting data:

emp_id name dept_id salary

1 Riya 1 50000
2 Amit 2 60000
3 Sara 1 75000
4 Vik 2 45000
5 Neha 1 80000

SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_sal, MAX(salary) AS top_sal

FROM employees
GROUP BY dept_id;

dept_id headcount avg_sal top_sal

1 3 68333.33 80000
2 2 52500.00 60000

THE GOLDEN RULE that trips up beginners: Every column in SELECT must either appear in
GROUP BY or be wrapped in an aggregate function. Why? Because each group becomes one
row in the output, and the database can't decide which individual value to pick for a non-
grouped column. If you GROUP BY dept_id and try to SELECT name, the database asks "which
name? There are three in this group." Some databases (like older MySQL) silently picked an
arbitrary one and let your code run — leading to subtle bugs. Modern databases reject this
query.

You can GROUP BY multiple columns to form finer partitions:

SELECT dept_id, job_title, AVG(salary)

FROM employees
GROUP BY dept_id, job_title;

Now you get one row per (department, job_title) combination.

5.3 HAVING — Filtering Groups

WHERE filters individual rows before grouping. HAVING filters groups after grouping. This
distinction is the #1 most-asked question after the basic SELECT structure.

SELECT dept_id, AVG(salary) AS avg_sal

FROM employees
WHERE hire_date >= '2020-01-01' -- filter rows: only recent hires
GROUP BY dept_id
HAVING AVG(salary) > 55000; -- filter groups: only high-paying depts

The WHERE clause says "only consider employees hired in 2020 or later." The HAVING clause
says "among the resulting departments, only show those whose average exceeds 55000."
Two completely different operations even though both use comparison operators.

Why two clauses? Because the operation happens at different stages. WHERE can't use
aggregates because aggregates don't exist yet. HAVING can use them because grouping has
already occurred.

CHAPTER 6: JOINS — THE MOST CRITICAL TOPIC FOR

INTERVIEWS
If you're shaky on joins, you'll fail almost every SQL interview. Every product company tests
joins.

6.1 The Mental Model

Imagine two tables sitting side-by-side. A JOIN matches rows from one with rows from the
other based on a condition (almost always equality on a key). The different join types differ
only in what they do with rows that don't have a match.

Setup tables we'll reuse throughout:

employees:

emp_id name dept_id

1 Riya 1
2 Amit 2
3 Sara NULL

departments:

dept_id dept_name
1 Engineering
2 Marketing
3 Finance
6.2 INNER JOIN — Only Matches

Returns rows where the join condition is satisfied in BOTH tables. Unmatched rows from
either side are discarded.

SELECT [Link], d.dept_name

FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

name dept_name
Riya Engineering
Amit Marketing

Sara is excluded because her dept_id is NULL (and NULL doesn't match anything). Finance is
excluded because no employee belongs to it. INNER JOIN is the strictest form — only
mutual matches survive.

6.3 LEFT JOIN — Keep Everything from the Left

Returns ALL rows from the left table, paired with matching rows from the right table. Where
there's no match, the right side is filled with NULLs.

SELECT [Link], d.dept_name

FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

name dept_name
Riya Engineering
Amit Marketing
Sara NULL

Sara is kept (because she's on the left), but with NULL for department since no match exists.
Use LEFT JOIN when you want to keep "primary" data and optionally enrich it with secondary
data.

A classic interview pattern: "Find employees who have no department." You LEFT JOIN and
then filter the right side for NULL:

SELECT [Link] FROM employees e

LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

6.4 RIGHT JOIN — Keep Everything from the Right

Mirror image of LEFT JOIN. Keeps all rows from the right table.

SELECT [Link], d.dept_name

FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

name dept_name
Riya Engineering
Amit Marketing
NULL Finance

In practice, RIGHT JOINs are rare — you can always rewrite them as LEFT JOINs by swapping
the tables, which most people find more readable.

6.5 FULL OUTER JOIN — Keep Everything

Combines LEFT and RIGHT: all rows from both sides, matched where possible, NULLed where
not.

SELECT [Link], d.dept_name

FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

name dept_name
Riya Engineering
Amit Marketing
Sara NULL
NULL Finance

Useful for finding mismatches in both directions, like reconciling two systems.

6.6 CROSS JOIN — Cartesian Product

Pairs every row from the left with every row from the right. No condition. If you have 3
employees and 3 departments, you get 9 rows.

SELECT [Link], d.dept_name FROM employees e CROSS JOIN departments d;

Most often this is a bug (forgetting the ON clause), but legitimate uses include generating
combinations, creating test data, and "every customer × every product" reporting matrices.

6.7 SELF JOIN — A Table With Itself

Sometimes a table references itself. The classic example: an employees table where each row
has a manager_id pointing to another emp_id in the same table.

employees:

emp_id name manager_id

1 Riya NULL
2 Amit 1
3 Sara 1
4 Vik 2

To get "employee name → manager name" pairs, you join the table with itself using two
aliases:

SELECT [Link] AS employee, [Link] AS manager

FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

employee manager
Riya NULL
Amit Riya
Sara Riya
Vik Amit

The aliases e and m are crucial — they're how the database distinguishes "the employee row"
from "the manager row," even though they're the same table.

6.8 ON vs WHERE in Joins — A Subtle But Critical Distinction

For INNER JOIN, conditions in ON and WHERE behave identically. But for OUTER JOINs
(LEFT/RIGHT/FULL), they're different.

Conditions in ON are part of the join logic — they decide what counts as a match.
Unmatched rows still pass through with NULLs.

Conditions in WHERE are applied AFTER the join is done. They filter the combined result.

Compare:

-- LEFT JOIN with condition in ON

SELECT [Link], d.dept_name FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id AND d.dept_name = 'Marketing';

Returns all employees; those in Marketing get the dept_name, others get NULL. The
condition filters the matches but not the employees.
 -- LEFT JOIN with condition in WHERE
SELECT [Link], d.dept_name FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Marketing';

Only returns Marketing employees. The WHERE applies after the join, dropping all rows
where dept_name isn't Marketing — effectively converting the LEFT JOIN into an INNER JOIN.

This single distinction has caused more bugs than nearly any other SQL concept.

6.9 How Joins Actually Execute

Internally, databases use one of three join algorithms:

Nested loop join — for each row in the outer table, scan the inner table for matches.
Simple but O(n*m). Efficient when one side is tiny or the inner side has an index on the
join column.
Hash join — build a hash table on the smaller side, then probe with rows from the
larger side. O(n+m). The default for large equality joins.
Sort-merge join — sort both sides by the join key, then walk through them in tandem.
Efficient when both sides are already sorted (e.g., by index).

You don't choose the algorithm directly — the optimizer picks based on table sizes, indexes,
and statistics. But understanding this helps you reason about performance: a join on indexed
columns is fast; a join on unindexed columns of two large tables can be brutal.

CHAPTER 7: SUBQUERIES
A subquery is a query inside another query. They're powerful because they let you compose
results — using the output of one query as input to another.

7.1 Scalar Subquery — Returns One Value

SELECT name, salary

FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

The inner query runs once, produces a single number (the company-wide average). The
outer query compares each row's salary to it. Because the inner result is a scalar, you can use
it wherever a single value is expected — including in SELECT or as a derived column.

7.2 Multi-Row Subquery — Returns a List

When the subquery returns multiple values, use IN, ANY, ALL, or EXISTS:
 SELECT name FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE location = 'Mumbai');

ANYmeans "satisfies the condition with at least one value," and ALL means "satisfies it with
every value":

WHERE salary > ANY (SELECT salary FROM managers) -- > at least one manager
WHERE salary > ALL (SELECT salary FROM managers) -- > every manager

7.3 Correlated Subquery — The Tricky One

A correlated subquery references the outer query's columns. As a result, the inner query re-
runs for every row processed by the outer query. This is conceptually like a nested loop.

SELECT [Link], [Link], e1.dept_id

FROM employees e1
WHERE [Link] > (
SELECT AVG([Link])
FROM employees e2
WHERE e2.dept_id = e1.dept_id
);

For each row e1, the inner subquery computes the average salary for THAT row's department,
then compares. This gives you employees earning above their own department's average —
a common interview ask.

Performance insight: Correlated subqueries can be slow because of the re-execution. The
optimizer often rewrites them as joins internally, but not always. If you can express the same
logic as a join with a GROUP BY (or use a window function), it's usually faster and more
readable.

7.4 Subquery in FROM — Derived Tables

You can use a subquery as a virtual table:

SELECT dept_id, max_sal

FROM (
SELECT dept_id, MAX(salary) AS max_sal
FROM employees
GROUP BY dept_id
) AS dept_max
WHERE max_sal > 60000;
The inner query produces a result set (a derived table), and the outer query treats it like a
real table. Useful for layered logic where you want to compute aggregates and then filter or
join on them.

7.5 EXISTS vs IN — A Crucial Performance Decision

Both check for existence, but they behave differently:

INmaterializes the subquery into a list and checks membership. With a million-row
subquery, that's expensive.

EXISTS short-circuits — the moment it finds one match, it stops scanning. It returns a
boolean per outer row, never building a full list.

-- Equivalent semantics, often different performance

SELECT name FROM employees e
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.emp_id = e.emp_id);

SELECT name FROM employees e

WHERE e.emp_id IN (SELECT emp_id FROM orders);

For large subqueries, prefer EXISTS. There's also the NULL trap: NOT IN with a list containing
NULL returns zero rows (because x != NULL is UNKNOWN). NOT EXISTS handles NULLs
correctly. Default to EXISTS/NOT EXISTS for safety.

CHAPTER 8: SET OPERATORS

Sometimes you have two queries and want to combine, intersect, or subtract their results.
Set operators do this.

Operator Behavior
UNION Combines results, removes duplicates
UNION ALL Combines results, keeps duplicates (faster)
INTERSECT Rows appearing in both queries
EXCEPT / MINUS Rows in the first query but not the second

SELECT name FROM employees_2023

UNION
SELECT name FROM employees_2024;

Rules every result-combining operator follows:

Both queries must have the same number of columns

Corresponding columns must have compatible types
 Column names come from the FIRST query
ORDER BY can only appear at the very end, applied to the combined result

The most important performance tip here: UNION removes duplicates, which requires
sorting or hashing the combined result. If you know your queries can't produce duplicates —
or you don't care — use UNION ALL. It's significantly faster because it skips the dedup step.

CHAPTER 9: BUILT-IN FUNCTIONS

These are the everyday utilities. You don't need to memorize all of them — but recognize the
categories so you can look up the right one.

9.1 String Functions

SELECT UPPER('hello'); -- 'HELLO'

SELECT LOWER('HELLO'); -- 'hello'
SELECT LENGTH('placement'); -- 9
SELECT SUBSTR('placement', 1, 5); -- 'place' (Oracle/MySQL: 1-indexed)
SELECT TRIM(' abc '); -- 'abc'
SELECT REPLACE('abcabc', 'a', 'X'); -- 'XbcXbc'
SELECT CONCAT('Hello', ' ', 'SQL'); -- 'Hello SQL'
SELECT INSTR('placement', 'c'); -- 4 (position of first 'c')
SELECT LPAD('5', 4, '0'); -- '0005'
SELECT RPAD('5', 4, '*'); -- '5***'

Combining them gets you far. For example, capitalizing the first letter:

SELECT UPPER(SUBSTR(name, 1, 1)) || LOWER(SUBSTR(name, 2)) FROM employees;

9.2 Numeric Functions

SELECT ROUND(3.567, 2); -- 3.57 (rounds)

SELECT TRUNC(3.567, 2); -- 3.56 (truncates without rounding)
SELECT CEIL(3.1); -- 4
SELECT FLOOR(3.9); -- 3
SELECT MOD(10, 3); -- 1
SELECT ABS(-7); -- 7
SELECT POWER(2, 10); -- 1024
SELECT SQRT(16); -- 4

9.3 Date Functions

Date handling varies by database — these are the Oracle/standard forms. MySQL and
PostgreSQL have their own equivalents.
 SELECT SYSDATE; -- current date+time
SELECT CURRENT_DATE; -- today
SELECT EXTRACT(YEAR FROM hire_date) FROM employees;
SELECT ADD_MONTHS(SYSDATE, 6); -- 6 months from now
SELECT MONTHS_BETWEEN('2025-12-31', '2025-01-01'); -- ~12
SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY'); -- formatting to string
SELECT TO_DATE('15-08-2025', 'DD-MM-YYYY'); -- parsing from string

9.4 NULL Handling Functions

NULL handling functions are everyday tools because real data is always messy.

COALESCE(a, b, c, ...) returns the first non-NULL argument. Universal, ANSI-standard:

SELECT COALESCE(phone, mobile, 'N/A') FROM contacts;

NVL(a, b) (Oracle) — returns a if not NULL, else b. Like a two-argument COALESCE.

NULLIF(a, b) returns NULL if a = b, otherwise returns a. Useful for avoiding divide-by-zero:

SELECT total_sales / NULLIF(num_orders, 0) FROM stats;

If num_orders is 0, you get NULL instead of an error.

CHAPTER 10: WINDOW FUNCTIONS — THE MODERN SQL

SUPERPOWER
Window functions are the difference between a basic SQL user and a competent one. Every
modern interview tests them. Master them deeply.

10.1 The Big Idea — What Makes Windows Different

Aggregate functions with GROUP BY collapse rows: 10 rows in, 3 groups out. Window
functions compute aggregates across rows BUT keep every original row intact. You get the
per-row detail AND the per-group aggregate, side by side.

The syntax pattern:

function(args) OVER (PARTITION BY col ORDER BY col2 ROWS BETWEEN ...)

PARTITION BY — divides rows into independent groups (like GROUP BY conceptually, but
rows are not collapsed)
 ORDER BY — orders rows within each partition (essential for ranking and running totals)
ROWS BETWEEN — optional, defines a sliding "frame" of rows over which the function
operates

10.2 Ranking Functions

The three rankers — ROW_NUMBER, RANK, DENSE_RANK — are critical for "find the top N" and "Nth
highest" problems.

name dept_id salary

Riya 1 50000
Amit 1 70000
Sara 1 70000
Vik 2 60000

SELECT name, dept_id, salary,

ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rn,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS drk
FROM employees;

name dept_id salary rn rk drk

Amit 1 70000 1 1 1
Sara 1 70000 2 1 1
Riya 1 50000 3 3 2
Vik 2 60000 1 1 1

Understanding the difference:

ROW_NUMBER gives each row a unique number, with arbitrary tie-breaking. Even tied
salaries get different numbers.
RANKgives tied rows the same rank but then SKIPS subsequent ranks. Notice that after
two 1s, the next rank jumps to 3.
DENSE_RANK gives tied rows the same rank but does NOT skip. After two 1s, the next is 2.

Which one for "Nth highest salary"? Use DENSE_RANK — if two people tie for first, you still
want the next person to be "second highest," not "third highest." Use RANK if competition
logic applies ("if two people tie for first, no one is second"). Use ROW_NUMBER when you
specifically need unique ordinals, even for ties.

10.3 LAG and LEAD — Looking Across Rows

LAG gives you a previous row's value; LEAD gives you a future row's. Both within the
partition/order you define.
 SELECT month, revenue,
LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
LEAD(revenue, 1) OVER (ORDER BY month) AS next_month
FROM monthly_sales;

Now you have the current month's revenue alongside last month's and next month's —
without joining the table to itself. This is the modern way to compute period-over-period
changes, churn, retention, and so on.

10.4 Running Totals and Moving Averages

The OVER clause can include a frame specification — telling the database which subset of
rows to compute over for each output row.

SELECT sale_date, amount,

SUM(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total,
AVG(amount) OVER (
ORDER BY sale_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3day
FROM sales;

Frame options:

— from the very first row in the partition

UNBOUNDED PRECEDING
N PRECEDING — N rows before the current row
CURRENT ROW
N FOLLOWING
UNBOUNDED FOLLOWING — to the very last row

For running totals, you almost always want ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
For moving averages over the last N rows, use ROWS BETWEEN (N-1) PRECEDING AND CURRENT ROW.

10.5 When to Use Window Functions

Whenever you find yourself wanting an aggregate but you want to keep each row's detail
too, that's a window function. Whenever you want to compare each row to a previous row,
that's LAG. Whenever you want rankings, that's ROW_NUMBER/RANK/DENSE_RANK. They've
replaced most "self-join + subquery" hackery.

CHAPTER 11: VIEWS

A view is a saved SELECT statement, stored under a name. When you query the view, the
database runs the underlying SELECT. The view itself stores no data — it's just a named
query.

Views serve three main purposes:

1. Abstraction — hide complex joins or business logic behind a simple name. Users query
customer_revenue without knowing it's a 5-table join.
2. Security — grant access to the view, not the underlying tables. Users see only the
columns and rows the view exposes.
3. Stability — provide a stable interface even when the underlying tables change. You can
refactor schemas under the hood without breaking consumers.

CREATE VIEW high_earners AS

SELECT emp_id, name, salary, dept_id
FROM employees
WHERE salary > 70000;

SELECT * FROM high_earners WHERE dept_id = 1;

The query against the view gets internally rewritten as a query against employees with both
conditions combined.

Simple vs complex views: A simple view (one table, no aggregates, no DISTINCT, no

GROUP BY) is updatable — you can INSERT/UPDATE/DELETE through it and the changes flow
to the underlying table. A complex view (joins, aggregates, etc.) is generally read-only
because the database can't unambiguously translate a change back to specific rows.

Materialized views are different: they physically store the result. Faster reads because you're
querying a real table, but the data can be stale. You refresh them periodically. Use them for
expensive aggregations that are queried far more often than the underlying data changes.

CHAPTER 12: TRANSACTIONS AND TCL

12.1 What Is a Transaction?

A transaction is a logical unit of work — one or more SQL statements that must either all
succeed or all fail together. The classic example: transferring money. If you debit account A
and crash before crediting account B, money has vanished. A transaction guarantees this
can't happen — either both updates succeed, or neither does.

BEGIN;
UPDATE accounts SET balance = balance - 1000 WHERE acc_id = 1;
 UPDATE accounts SET balance = balance + 1000 WHERE acc_id = 2;
COMMIT;

COMMIT makes the changes permanent and visible to other sessions. Until you COMMIT,
your changes exist only in your session — others see the old data.

ROLLBACK undoes everything done since the transaction began:

BEGIN;
UPDATE employees SET salary = 0 WHERE dept_id = 1;
-- "Wait, that was wrong!"
ROLLBACK;
-- salaries are back to original

12.2 ACID Properties

Every interviewer asks about ACID. Memorize these.

Atomicity — all or nothing. A transaction either completes fully or is rolled back fully.
Partial commits don't happen.
Consistency — the database moves from one valid state to another. Constraints
(foreign keys, checks) are enforced. Application invariants are preserved.
Isolation — concurrent transactions don't interfere with each other. Each transaction
appears to run alone, even if many are happening simultaneously.
Durability — once a transaction commits, the changes survive crashes, power failures,
anything. They're written to durable storage (disk, with proper logging).

12.3 SAVEPOINT — Partial Rollback Within a Transaction

For long transactions, you can set checkpoints and roll back only to a checkpoint instead of
undoing everything.

BEGIN;
INSERT INTO orders VALUES (1, 'item1');
SAVEPOINT after_first;
INSERT INTO orders VALUES (2, 'item2');
ROLLBACK TO SAVEPOINT after_first; -- only the second insert is undone
INSERT INTO orders VALUES (3, 'item3');
COMMIT;
-- final result: rows for item1 and item3

12.4 Isolation Levels and Concurrency Anomalies

When many transactions run simultaneously, weird things can happen if the database isn't
careful. The four anomalies:
 Dirty read — reading data another transaction has modified but not yet committed. If
that transaction rolls back, you read data that never officially existed.
Non-repeatable read — reading the same row twice in your transaction and getting
different values because another transaction committed an update in between.
Phantom read — running the same query twice and getting different sets of rows
because another transaction inserted or deleted rows that match your filter.

Isolation levels control which anomalies are prevented:

Level Prevents
READ UNCOMMITTED Nothing — dirty reads possible
READ COMMITTED Dirty reads
REPEATABLE READ Dirty + non-repeatable reads
SERIALIZABLE All anomalies — strictest

Higher isolation gives you safer behavior but lower concurrency (more locking, more
blocking, more rollbacks). Most production systems use READ COMMITTED as the default —
it's a good balance.

CHAPTER 13: DCL — GRANT AND REVOKE

Database access control. The database treats users (or roles) as principals and privileges as
the right to do specific things.

GRANT SELECT, INSERT ON employees TO analyst_role;

GRANT ALL ON employees TO admin_user;
REVOKE INSERT ON employees FROM analyst_role;
GRANT SELECT ON employees TO user1 WITH GRANT OPTION;

lets the recipient further grant the privilege to others — useful for
WITH GRANT OPTION
delegating administration but dangerous because revoking it later can cascade.

Privileges are granular: SELECT, INSERT, UPDATE, DELETE on a specific table; EXECUTE on a
procedure; references on a column. Real production systems use ROLES — bundles of
privileges — assigned to users rather than granting privileges per-user.

CHAPTER 14: QUERY OPTIMIZATION

This is what separates juniors from seniors in interviews. You don't need to be a DBA, but
you should articulate these principles.

14.1 Avoid SELECT *

Pulling every column means more I/O from disk, more memory consumption, and more
network bytes. Worse, it prevents "index-only" scans where the database could have
answered your query without touching the table at all. Always list the columns you need.

14.2 Filter Early, Project Late

Apply WHERE filters as early as possible to reduce the number of rows flowing into joins and
aggregations. Modern optimizers do this for you (it's called "predicate pushdown"), but
writing queries in a filter-first style helps in cases where the optimizer struggles.

-- Less efficient idea

SELECT * FROM orders o JOIN customers c ON o.cust_id = c.cust_id
WHERE [Link] = 'IN';

-- Often produces the same plan, but more explicit

SELECT * FROM orders o
JOIN (SELECT * FROM customers WHERE country = 'IN') c
ON o.cust_id = c.cust_id;

14.3 EXISTS Over IN for Large Subqueries

As discussed in Chapter 7, EXISTS short-circuits at the first match. IN materializes the full list.
For large subqueries, EXISTS is typically faster.

14.4 Avoid Functions on Indexed Columns in WHERE

This is the most under-appreciated performance principle.

-- Bad: function wraps the column. Index unusable.

WHERE YEAR(hire_date) = 2024

-- Good: range condition. Index works directly.

WHERE hire_date >= '2024-01-01'
AND hire_date < '2025-01-01'

When you wrap a column in a function, the database has to evaluate the function for every
row before it can compare. An index on hire_date doesn't help because the index stores raw
dates, not YEAR(hire_date) values. Always re-write to put the column bare on one side and
the computed value on the other.

The same applies to type conversions: comparing an INT column to a string forces an
implicit conversion on every row.

14.5 Prefer JOINs to Correlated Subqueries

Correlated subqueries can re-execute per outer row, which is O(n × m) in the worst case. A
JOIN with GROUP BY computes the same result in a single set operation. Modern optimizers
often rewrite correlated subqueries as joins, but writing it as a join directly is clearer and
more reliable.

14.6 UNION ALL Beats UNION When Possible

UNION removes duplicates, which requires an extra sort or hash. If you know the queries
can't produce duplicates — or you don't care — use UNION ALL. The difference can be
enormous on large result sets.

14.7 LIMIT Early in Subqueries

If you only need the top N, push LIMIT down as early as possible. Otherwise the database
might compute a million-row intermediate result and throw most of it away.

14.8 Read the Execution Plan

Every database has an EXPLAIN (or EXPLAIN PLAN) command that shows how a query will
be executed:

EXPLAIN SELECT * FROM employees WHERE dept_id = 5;

Look for: full table scans on large tables (often bad), large row count estimates, expensive
join algorithms on big tables. Optimize the most expensive step first. Use EXPLAIN
religiously — guessing about performance is a fool's errand.

14.9 Batch DML

Inserting 10,000 rows with 10,000 separate INSERT statements means 10,000 round-trips.
Batch into one statement, or use bulk-insert features. This single change can speed up data
loads by 100x or more.

14.10 Pagination — Avoid Large OFFSETs

LIMIT 10 OFFSET 1000000 makes the database fetch a million rows and discard them. For deep
pagination, use keyset pagination instead:

SELECT * FROM events

WHERE id > :last_seen_id
ORDER BY id
LIMIT 20;

The index on id lets the database jump directly to the next page.
PART B — PL/SQL DEEP DIVE

CHAPTER 15: WHAT PL/SQL IS AND WHY IT EXISTS

SQL is a declarative query language. It's brilliant for set operations, but it has no variables, no
IF/ELSE, no loops, no exception handling. For complex business logic that's tightly coupled
to data, this is limiting.

PL/SQL (Procedural Language extension to SQL) is Oracle's solution: a full programming

language that runs inside the database, with SQL as a first-class citizen. You get variables,
control flow, exception handling, modular procedures and functions, and triggers — all
running close to the data, with no network overhead between the application and the
database.

Other databases have their own equivalents (T-SQL in SQL Server, PL/pgSQL in PostgreSQL),
but PL/SQL is the syllabus standard because Oracle dominated enterprise databases for
decades. The concepts transfer.

CHAPTER 16: THE BLOCK STRUCTURE

Every PL/SQL program is organized into blocks. A block has three sections, two of which are
optional:

DECLARE
-- variable, constant, cursor, exception declarations (optional)
BEGIN
-- executable statements (required)
EXCEPTION
-- error handling (optional)
END;
/

The semicolon ends each statement. The forward slash on its own line tells the SQL*Plus or
SQL Developer tool to actually execute the block.

Anonymous block — not stored in the database. You type it, run it, it's gone. Useful for
one-off tasks or testing.

Named block — stored in the database for reuse: procedures, functions, packages, triggers.
These are the workhorses of production PL/SQL.
Blocks can be nested. Inner blocks have access to the outer block's variables (lexical
scoping). Exception handlers in inner blocks can re-raise unhandled exceptions to outer
blocks.

CHAPTER 17: VARIABLES, CONSTANTS, AND TYPE

INHERITANCE

DECLARE
v_name VARCHAR2(50);
v_salary NUMBER(10, 2) := 50000;
c_tax_rate CONSTANT NUMBER := 0.18;
v_emp_sal [Link]%TYPE;
v_emp_row employees%ROWTYPE;
BEGIN
v_name := 'Riya';
DBMS_OUTPUT.PUT_LINE('Name: ' || v_name || ', Salary: ' || v_salary);
END;
/

A few things to notice:

The := operator is PL/SQL's assignment (different from SQL's =, which is equality).

CONSTANT makes the variable immutable — attempting to reassign causes a compile
error.
%TYPE is one of PL/SQL's killer features. [Link]%TYPE says "whatever type the
salary column of employees is, use that here." If the table column changes from
NUMBER(10,2) to NUMBER(12,2) tomorrow, your variable automatically tracks. No code
changes needed. This makes your code resilient to schema evolution.
%ROWTYPE declares a variable that's a record matching the entire table row structure —
like a Java object whose fields mirror the columns. Access fields with v_emp_row.salary,
v_emp_row.name, etc. Use it whenever you SELECT entire rows.

DBMS_OUTPUT.PUT_LINE is the PL/SQL equivalent of print() — it writes to the session's output

buffer.

CHAPTER 18: CONTROL FLOW

PL/SQL has the control structures you'd expect from any programming language.

18.1 IF / ELSIF / ELSE

IF salary > 70000 THEN

band := 'High';
 ELSIF salary > 40000 THEN
band := 'Medium';
ELSE
band := 'Low';
END IF;

Note the spelling: ELSIF, not ELSE IF (which is a common typo from Java/C). Conditions
evaluate top to bottom; the first TRUE branch executes and the rest are skipped.

18.2 CASE Statement vs CASE Expression

PL/SQL has both, like SQL. The expression form returns a value; the statement form executes
a block:

-- Expression form
band := CASE
WHEN salary > 70000 THEN 'High'
WHEN salary > 40000 THEN 'Medium'
ELSE 'Low'
END;

-- Statement form
CASE
WHEN salary > 70000 THEN give_bonus(emp_id);
WHEN salary > 40000 THEN no_change(emp_id);
ELSE adjust_minimum(emp_id);
END CASE;

18.3 Loops

PL/SQL has three loop forms:

-- Basic loop with manual exit

LOOP
counter := counter + 1;
EXIT WHEN counter > 10;
END LOOP;

-- WHILE loop with condition checked at top

WHILE counter < 10 LOOP
counter := counter + 1;
END LOOP;

-- FOR loop with implicit counter — most common

FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

-- Reverse FOR
 FOR i IN REVERSE 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;

The FOR loop's counter variable is implicitly declared and scoped to the loop. After the loop
ends, the variable doesn't exist. The range 1..10 is inclusive on both ends.

EXIT WHEN condition is cleaner than IF condition THEN EXIT; END IF; and idiomatic in PL/SQL.

CHAPTER 19: CURSORS — ROW-BY-ROW PROCESSING

A cursor is a pointer to a result set, letting you process rows one at a time. There are
situations where set-based SQL isn't enough — when you need procedural logic per row,
you need cursors.

19.1 Implicit Cursors

Every SQL statement in PL/SQL automatically uses an implicit cursor. You don't declare it, but
you can query its attributes:

SQL%FOUND — TRUE if the last statement affected at least one row

SQL%NOTFOUND — TRUE if no rows affected
SQL%ROWCOUNT — number of rows affected
SQL%ISOPEN — always FALSE for implicit cursors (they're auto-closed)

BEGIN
UPDATE employees SET salary = salary * 1.1 WHERE dept_id = 1;
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated');
END;

19.2 Explicit Cursors — The Four Steps

For more control — especially when iterating through results — you use explicit cursors. The
lifecycle has four steps: declare, open, fetch, close.

DECLARE
CURSOR c_emp IS
SELECT emp_id, name FROM employees WHERE dept_id = 1;
v_id employees.emp_id%TYPE;
v_name [Link]%TYPE;
BEGIN
OPEN c_emp; -- executes the query
LOOP
FETCH c_emp INTO v_id, v_name; -- gets the next row
EXIT WHEN c_emp%NOTFOUND; -- exit when no more rows
 DBMS_OUTPUT.PUT_LINE(v_id || ' - ' || v_name);
END LOOP;
CLOSE c_emp; -- frees resources
END;

Understanding each step:

— defines the cursor; the query is NOT executed yet.

DECLARE CURSOR c_emp IS ...
OPEN c_emp — now the query runs. The result set is materialized (or set up to be
streamed).
FETCH c_emp INTO ... — copies the current row's values into variables and advances the
pointer to the next row.
c_emp%NOTFOUNDbecomes TRUE once you've fetched past the last row.
CLOSE c_emp — releases the memory and locks. Always close cursors, ideally in an
exception handler too.

19.3 Cursor FOR Loops — The Clean Way

The four-step pattern is verbose. PL/SQL gives you a much cleaner alternative — the cursor
FOR loop — which auto-opens, auto-fetches, and auto-closes:

BEGIN
FOR rec IN (SELECT emp_id, name FROM employees WHERE dept_id = 1) LOOP
DBMS_OUTPUT.PUT_LINE(rec.emp_id || ' - ' || [Link]);
END LOOP;
END;

The variable rec is implicitly declared as a record matching the SELECT's columns. The loop
opens the cursor, fetches each row into rec, and closes when done. Use this 90% of the time
— it's safer (no resource leaks) and shorter.

19.4 Parameterized Cursors

When you want the same cursor query with different filter values:

DECLARE
CURSOR c_emp(p_dept INT) IS
SELECT name FROM employees WHERE dept_id = p_dept;
BEGIN
FOR rec IN c_emp(2) LOOP
DBMS_OUTPUT.PUT_LINE([Link]);
END LOOP;
END;

The cursor declaration takes parameters; you pass values when opening.
CHAPTER 20: EXCEPTION HANDLING
In SQL, an error stops the statement. In PL/SQL, you can catch errors and respond — log
them, retry, or gracefully recover. This is essential for reliable code.

20.1 The Exception Handling Pattern

DECLARE
v_name [Link]%TYPE;
BEGIN
SELECT name INTO v_name FROM employees WHERE emp_id = 999;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No employee with that ID');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('More than one match');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
END;

When an exception fires inside BEGIN, control jumps immediately to EXCEPTION. The
handlers are checked top to bottom; the first match runs. After it runs, the block ends (or, if
nested, control returns to the outer block).

20.2 Predefined Exceptions to Know

Exception When it fires

NO_DATA_FOUND A SELECT INTO returned zero rows
TOO_MANY_ROWS A SELECT INTO returned more than one row
ZERO_DIVIDE Division by zero
DUP_VAL_ON_INDEX Unique constraint violated by INSERT/UPDATE
INVALID_NUMBER Failed numeric conversion
VALUE_ERROR Type or size mismatch
CURSOR_ALREADY_OPEN Tried to open an already-open cursor
INVALID_CURSOR Tried to fetch/close a non-open cursor

WHEN OTHERS is the catch-all — it handles anything not handled by a specific clause. Always
put it last. SQLERRM returns the error message; SQLCODE returns the numeric error code. Logging
these is good practice.

20.3 User-Defined Exceptions

You can define your own exceptions for business rules:

 DECLARE
invalid_salary EXCEPTION;
v_sal NUMBER := -100;
BEGIN
IF v_sal < 0 THEN
RAISE invalid_salary;
END IF;
EXCEPTION
WHEN invalid_salary THEN
DBMS_OUTPUT.PUT_LINE('Salary cannot be negative');
END;

Declare it like a variable but with the type EXCEPTION. RAISE triggers it. The handler in
EXCEPTION processes it.

20.4 RAISE_APPLICATION_ERROR

If you want to throw a meaningful error back to the calling application (not just within your
PL/SQL), use this:

RAISE_APPLICATION_ERROR(-20001, 'Salary must be positive');

Error codes between -20000 and -20999 are reserved for application use. The calling code
sees this as a database error with your custom message.

CHAPTER 21: STORED PROCEDURES

A stored procedure is a named, pre-compiled PL/SQL block stored in the database. Call it
like calling a function in any language — but it runs inside the database, not in your
application.

CREATE OR REPLACE PROCEDURE give_raise (

p_emp_id IN employees.emp_id%TYPE,
p_percent IN NUMBER,
p_new_sal OUT [Link]%TYPE
) AS
BEGIN
UPDATE employees
SET salary = salary * (1 + p_percent / 100)
WHERE emp_id = p_emp_id
RETURNING salary INTO p_new_sal;
END give_raise;
/

Call it:
 DECLARE
v_new_sal [Link]%TYPE;
BEGIN
give_raise(101, 10, v_new_sal);
DBMS_OUTPUT.PUT_LINE('New salary: ' || v_new_sal);
END;

Parameter modes:

IN (default) — input only. The procedure can read it but not change the caller's variable.
OUT — output only. The procedure writes to it; the caller reads the result.
IN OUT — both ways. The procedure can read the input and write a new value back.

Why use procedures?

Code reuse — write once, call from many places.

Centralized business logic — rules live in the database, not scattered across apps.
Security — grant EXECUTE permission on the procedure without granting direct table
access.
Performance — the procedure is compiled once; subsequent calls skip compilation.

CHAPTER 22: FUNCTIONS

A function is like a procedure, but it returns a value and can be used in SQL expressions.

CREATE OR REPLACE FUNCTION get_annual_salary (

p_emp_id IN employees.emp_id%TYPE
) RETURN NUMBER AS
v_sal [Link]%TYPE;
BEGIN
SELECT salary INTO v_sal FROM employees WHERE emp_id = p_emp_id;
RETURN v_sal * 12;
EXCEPTION
WHEN NO_DATA_FOUND THEN RETURN 0;
END;
/

-- Use in SQL
SELECT name, get_annual_salary(emp_id) AS annual FROM employees;

Procedure vs Function — the classic interview question:

A function MUST return a value via a RETURN statement. A procedure doesn't return; it
produces effects through OUT parameters or DML.
Functions can be called inside SELECT statements; procedures cannot.
 Best practice: functions should be "pure" — given the same input, return the same
output, with no side effects. Procedures are where side effects belong.

CHAPTER 23: PACKAGES

A package bundles related procedures, functions, variables, and types into a single named
unit. Packages are how serious PL/SQL code is organized.

A package has two parts:

Specification — the public interface, declaring what's available:

CREATE OR REPLACE PACKAGE emp_pkg AS

PROCEDURE hire(p_name VARCHAR2, p_sal NUMBER);
FUNCTION get_salary(p_id NUMBER) RETURN NUMBER;
END emp_pkg;
/

Body — the actual implementation:

CREATE OR REPLACE PACKAGE BODY emp_pkg AS

PROCEDURE hire(p_name VARCHAR2, p_sal NUMBER) IS
BEGIN
INSERT INTO employees(name, salary) VALUES (p_name, p_sal);
END;

FUNCTION get_salary(p_id NUMBER) RETURN NUMBER IS

v_sal NUMBER;
BEGIN
SELECT salary INTO v_sal FROM employees WHERE emp_id = p_id;
RETURN v_sal;
END;
END emp_pkg;
/

Use it:

BEGIN
emp_pkg.hire('Tara', 60000);
DBMS_OUTPUT.PUT_LINE(emp_pkg.get_salary(101));
END;

Why packages matter:

Modularity — group related functionality logically.

 Encapsulation — anything declared in the body but not in the specification is private.
Callers can't see or invoke it.
Performance — when a package is first referenced in a session, the whole thing loads
into memory. Subsequent calls are fast.
Overloading — multiple procedures/functions with the same name but different
parameter signatures are allowed.
Persistent state — package-level variables persist for the entire session, unlike local
variables.

CHAPTER 24: TRIGGERS

A trigger is code that fires automatically in response to a database event — typically INSERT,
UPDATE, or DELETE on a specific table. The application doesn't call the trigger; the database
itself invokes it whenever the triggering event occurs.

CREATE OR REPLACE TRIGGER trg_salary_audit

AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN ([Link] <> [Link])
BEGIN
INSERT INTO salary_audit(emp_id, old_sal, new_sal, change_date)
VALUES (:OLD.emp_id, :[Link], :[Link], SYSDATE);
END;
/

This trigger runs after any UPDATE that changes the salary column, logging the change to an
audit table. It fires once per row affected.

Trigger anatomy:

BEFORE / AFTER / INSTEAD OF — when does it fire? BEFORE lets you validate or
modify the change before it happens. AFTER fires after the change is committed to
memory (but before transaction commit). INSTEAD OF is for views and replaces the
underlying DML.
INSERT / UPDATE / DELETE — what event triggers it? You can list multiple events.
FOR EACH ROW — fires per row affected (row-level). Without this, it fires once per
statement regardless of row count (statement-level).
WHEN (condition) — optional filter to skip the trigger when the condition is false.
:OLD and :NEW — pseudo-records exposing the row's values. :OLD is the existing value
(available in UPDATE and DELETE); :NEW is the incoming value (available in INSERT and
UPDATE).

Use cases:

Audit logging — track who changed what, when.

 Enforcing business rules too complex for CHECK constraints.
Maintaining derived data — e.g., updating a summary table when source data changes.
Cross-table consistency — propagating changes.

Cautions:

Triggers fire invisibly. Stacking many of them turns the database into a hidden state
machine that's a nightmare to debug.
Triggers run inside the same transaction as the triggering statement. A trigger failure
rolls back the original statement.
Be wary of "mutating table" errors — a row-level trigger can't query the table it's
defined on (because that table is mid-modification).

CHAPTER 25: COLLECTIONS AND BULK OPERATIONS

For performance, you often want to fetch many rows in one shot or apply DML in batches.
PL/SQL collections + BULK COLLECT + FORALL enable this.

25.1 Associative Arrays

The simplest collection — a hash map.

DECLARE
TYPE name_list IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
names name_list;
BEGIN
names(1) := 'Riya';
names(2) := 'Amit';
DBMS_OUTPUT.PUT_LINE(names(1));
END;

25.2 BULK COLLECT — Read Many Rows in One Step

Instead of fetching one row at a time, fetch the entire result set into a collection:

DECLARE
TYPE emp_tab IS TABLE OF employees%ROWTYPE;
emps emp_tab;
BEGIN
SELECT * BULK COLLECT INTO emps FROM employees;
FOR i IN 1..[Link] LOOP
DBMS_OUTPUT.PUT_LINE(emps(i).name);
END LOOP;
END;
This is dramatically faster than row-by-row cursor processing because it minimizes context
switches between PL/SQL and SQL engines.

25.3 FORALL — DML in Batches

Same idea for INSERT/UPDATE/DELETE — apply DML to all elements of a collection in one
shot:

DECLARE
TYPE id_tab IS TABLE OF NUMBER;
ids id_tab := id_tab(101, 102, 103);
BEGIN
FORALL i IN 1..[Link]
UPDATE employees SET salary = salary * 1.1 WHERE emp_id = ids(i);
END;

One round-trip to the SQL engine instead of three. On collections of thousands of items, this
can be a 10-100x speedup over a regular FOR loop with individual UPDATEs.

PART C — TOP 30 INTERVIEW PROBLEMS

These cover every pattern asked in product-company OAs and interviews. Don't just read
them — solve each one yourself first, then check.

Problem 1 — Second Highest Salary

Find the second highest salary in the employees table.

SELECT MAX(salary) AS second_highest

FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Alternative with DENSE_RANK

SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
FROM employees
) WHERE rk = 2;

The first solution is elegant — find the max excluding the actual max. The DENSE_RANK
version generalizes to Nth.

Problem 2 — Nth Highest Salary

 SELECT salary FROM (
SELECT DISTINCT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
FROM employees
) WHERE rk = N;

DENSE_RANK over DISTINCT salary handles ties correctly: tied salaries share a rank, no gaps.

Problem 3 — Find Duplicate Emails

SELECT email, COUNT(*) AS cnt

FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Group by the candidate-duplicate column, then filter groups with more than one row.

Problem 4 — Delete Duplicates, Keep One

DELETE FROM employees

WHERE emp_id NOT IN (
SELECT MIN(emp_id) FROM employees GROUP BY name, salary, dept_id
);

The inner query identifies the "canonical" row per duplicate group (the one with the smallest
id). The outer query deletes everything else.

Problem 5 — Department-wise Highest Salary

SELECT dept_id, name, salary FROM (

SELECT dept_id, name, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
FROM employees
) WHERE rk = 1;

Partition by department, rank within each, keep only rank 1. If two people tie for top in a
department, both are returned.

Problem 6 — Top 3 Salaries Per Department

 SELECT dept_id, name, salary FROM (
SELECT dept_id, name, salary,
DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk
FROM employees
) WHERE rk <= 3;

DENSE_RANK so ties don't push out third-place candidates.

Problem 7 — Employees Earning More Than Their Manager

SELECT [Link] AS employee, [Link] AS manager

FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE [Link] > [Link];

Classic self-join. The same table is aliased twice — once as the employee, once as the
manager.

Problem 8 — Employees With No Manager

SELECT name FROM employees WHERE manager_id IS NULL;

Remember: manager_id = NULL won't work. Use IS NULL.

Problem 9 — Departments With No Employees

SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;

LEFT JOIN keeps all departments. Departments with no employees show NULL on the right
side; filter on that.

Problem 10 — Customers Who Never Ordered

SELECT [Link]
FROM customers c
 LEFT JOIN orders o ON c.cust_id = o.cust_id
WHERE o.order_id IS NULL;

Same anti-join pattern. Memorize it — it's everywhere.

Problem 11 — Running Total

SELECT sale_date, amount,

SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

Default frame for SUM with ORDER BY is "from start of partition through current row" —
perfect for running totals.

Problem 12 — Month-over-Month Growth %

SELECT month, revenue,

LAG(revenue) OVER (ORDER BY month) AS prev,
ROUND(
(revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0
/ LAG(revenue) OVER (ORDER BY month),
2
) AS growth_pct
FROM monthly_revenue;

LAG gives the previous month's revenue inline; from there, compute the percentage change.

Problem 13 — Pivot Rows to Columns

SELECT student_id,
MAX(CASE WHEN subject = 'Math' THEN marks END) AS Math,
MAX(CASE WHEN subject = 'Science' THEN marks END) AS Science,
MAX(CASE WHEN subject = 'English' THEN marks END) AS English
FROM marks
GROUP BY student_id;

The CASE expressions emit a value only for matching subjects; MAX collapses the rows per
student into one. Standard pivot pattern.

Problem 14 — Median Salary

 SELECT AVG(salary) AS median FROM (
SELECT salary,
ROW_NUMBER() OVER (ORDER BY salary) AS rn,
COUNT(*) OVER () AS total
FROM employees
) WHERE rn IN (FLOOR((total + 1) / 2), CEIL((total + 1) / 2));

Median = middle value(s). For odd count, the middle row. For even count, average of the
two middle rows. The FLOOR/CEIL formula picks either one row or two depending on parity.

Problem 15 — Consecutive Login Days (≥3)

Find users who logged in on at least 3 consecutive days.

SELECT DISTINCT user_id FROM (

SELECT user_id, login_date,
login_date - ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS gr
FROM logins
)
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

 

The clever trick: If login dates are consecutive, then login_date - row_number() is constant —
the gap to the row number doesn't change. Group by this gap, and each group is a
consecutive streak. Filter groups of length ≥ 3.

Problem 16 — Customers Who Bought ALL Products

SELECT customer_id
FROM orders
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) = (SELECT COUNT(*) FROM products);

A customer has bought all products if their distinct product count equals the total product
count.

Problem 17 — Products Never Ordered

SELECT p.product_name FROM products p

WHERE NOT EXISTS (
 SELECT 1 FROM orders o WHERE o.product_id = p.product_id
);

NOT EXISTS is the safest anti-join idiom (handles NULLs correctly, unlike NOT IN).

Problem 18 — Find Gaps in Sequential IDs

SELECT id + 1 AS missing_id_start
FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM orders WHERE id = [Link] + 1)
AND id < (SELECT MAX(id) FROM orders);

For each row, check if id+1 exists. If not, that's where a gap starts.

Problem 19 — Exchange Seats (Swap Adjacent Rows)

Classic LeetCode-style problem: swap student names in seats (1,2), (3,4), etc. If the last seat
has odd id, leave it alone.

SELECT
CASE
WHEN id % 2 = 1 AND id = (SELECT MAX(id) FROM seats) THEN id
WHEN id % 2 = 1 THEN id + 1
ELSE id - 1
END AS id,
student
FROM seats
ORDER BY id;

Compute a new id per row using CASE, then sort by it. The names move with the new ids.

Problem 20 — Highest Salary Per Department With Department Name

SELECT d.dept_name, [Link], [Link]

FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE (e.dept_id, [Link]) IN (
SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id
);

Tuple IN — match the (dept, salary) pair against (dept, max_salary) pairs.
Problem 21 — Departments Where Average Salary > Company Average

SELECT dept_id, AVG(salary) AS dept_avg

FROM employees
GROUP BY dept_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

The HAVING clause compares the group's aggregate against a global scalar subquery.

Problem 22 — Cumulative Distinct Counts

How many distinct products had been bought up to and including each date?

SELECT order_date,
COUNT(DISTINCT product_id) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS distinct_products_so_far
FROM orders;

Note: not all databases support COUNT DISTINCT inside a window. For those, use a
subquery-based approach with EXISTS or correlated GROUP BY.

Problem 23 — Find Nth Salary Without Window Functions

SELECT salary FROM employees e1

WHERE N - 1 = (
SELECT COUNT(DISTINCT salary) FROM employees e2 WHERE [Link] > [Link]
);

For each salary, count how many distinct salaries exceed it. The Nth highest is where this
count = N-1. Slow but useful when window functions aren't available.

Problem 24 — Employees Hired in the Last 30 Days

SELECT name, hire_date

FROM employees
WHERE hire_date >= CURRENT_DATE - INTERVAL '30' DAY;

Range condition on hire_date — keeps any index usable, unlike WHERE DATEDIFF(...) <= 30.
Problem 25 — Find the Manager With the Most Direct Reports

SELECT manager_id, COUNT(*) AS report_count

FROM employees
WHERE manager_id IS NOT NULL
GROUP BY manager_id
ORDER BY report_count DESC
FETCH FIRST 1 ROW ONLY;

Count, sort descending, take the top.

Problem 26 — Find the Most Recent Order per Customer

SELECT * FROM (
SELECT o.*,
ROW_NUMBER() OVER (PARTITION BY cust_id ORDER BY order_date DESC) AS rn
FROM orders o
) WHERE rn = 1;

For each customer's orders, rank by date descending, keep rank 1. Standard "latest per
group" pattern.

Problem 27 — Two-Way Friend Pairs (Mutual Friends)

Find pairs (a, b) where a is a friend of b AND b is a friend of a.

SELECT DISTINCT
LEAST(f1.user_id, f1.friend_id) AS u1,
GREATEST(f1.user_id, f1.friend_id) AS u2
FROM friendships f1
JOIN friendships f2
ON f1.user_id = f2.friend_id
AND f1.friend_id = f2.user_id;

Self-join the friendships table to find reciprocal entries. LEAST/GREATEST normalize the pair
so (a,b) and (b,a) don't appear as separate rows.

Problem 28 — Find Customers With Increasing Order Amounts (3 in a

Row)

SELECT DISTINCT cust_id FROM (

SELECT cust_id, amount,
 LAG(amount, 1) OVER (PARTITION BY cust_id ORDER BY order_date) AS prev1,
LAG(amount, 2) OVER (PARTITION BY cust_id ORDER BY order_date) AS prev2
FROM orders
) WHERE amount > prev1 AND prev1 > prev2;

LAG with offsets 1 and 2 lets you compare three consecutive orders in a single row.

Problem 29 — Department With the Highest Bill Across All Employees

Find the department whose total payroll is the largest.

SELECT dept_id, total FROM (

SELECT dept_id, SUM(salary) AS total,
RANK() OVER (ORDER BY SUM(salary) DESC) AS rk
FROM employees
GROUP BY dept_id
) WHERE rk = 1;

Note that you can use aggregates inside the OVER clause when combined with GROUP BY.
Each group's aggregate is ranked.

Problem 30 — Find the Manager Chain (Recursive)

How many levels of management above each employee?

WITH RECURSIVE chain(emp_id, name, manager_id, level) AS (

SELECT emp_id, name, manager_id, 0
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.emp_id, [Link], e.manager_id, [Link] + 1
FROM employees e
JOIN chain c ON e.manager_id = c.emp_id
)
SELECT name, level FROM chain;

Recursive CTE — the anchor selects the CEO (no manager); the recursive part adds direct
reports of already-found rows, incrementing level. Continues until no new rows. This is THE
way to handle hierarchies in SQL.

FINAL NOTES — HOW TO USE THIS GUIDE

Don't try to memorize. Internalize:
 1. The logical order of execution — Chapter 1.3. If only one thing sticks, make it this.
Every confusing SQL behavior derives from it.
2. NULL is UNKNOWN, not equal to itself — Chapter 1.4. Every production bug
eventually traces back here.
3. Set-based thinking — Stop writing SQL as if it were a loop.
4. The five JOIN behaviors and the ON-vs-WHERE distinction — Chapter 6. Most asked
topic.
5. Window functions — Chapter 10. Modern SQL is unthinkable without them.
6. The 30 problems — Solve them on paper, then on a real database. Tweak each
variation. The same patterns recur in every interview.

For PL/SQL, focus on: block structure, cursors (especially the FOR loop form), exception
handling, the procedure/function distinction, packages, and triggers with :OLD/:NEW. Bulk
operations and dynamic SQL are good to know but lower priority.

Practice on a real database. SQL clicks only when you run queries and see results. Use a free
PostgreSQL, MySQL, or Oracle XE locally, or use online sandboxes like sqlfiddle, db-fiddle, or
LeetCode's SQL section.

Best of luck with your placements. You've got this.