Click any module to expand. This module is 100% hands-on — every topic is practised by writing queries on a live database. The focus is on STANDARD SQL that works across PostgreSQL, MySQL, and Oracle — not vendor-specific syntax. PL/SQL is covered as database procedural programming in Modules 7–8.
Creating and managing database objects — tables, constraints, indexes, and schema design
What SQL is (declarative, set-based — tell the DB WHAT you want, not HOW). SQL categories: DDL, DML, DCL, TCL. Setting up the practice environment: PostgreSQL or MySQL (not Oracle-specific — standard SQL first). Database vs schema vs table. Using a SQL client (pgAdmin, MySQL Workbench, DBeaver).
CREATE TABLE with column definitions. Data types: INT/BIGINT, VARCHAR(n)/TEXT, DATE/TIMESTAMP, DECIMAL/NUMERIC, BOOLEAN. Choosing the right data type — VARCHAR(255) vs TEXT, INT vs BIGINT, DECIMAL vs FLOAT. Why data type choices matter for storage, indexing, and correctness.
PRIMARY KEY (uniqueness + NOT NULL). FOREIGN KEY (referential integrity — CASCADE, SET NULL, RESTRICT on delete/update). UNIQUE (allows one NULL). CHECK (custom validation: CHECK(age >= 18)). DEFAULT (auto-fill). NOT NULL. Composite primary keys. When to use each constraint.
ALTER TABLE: ADD column, DROP column, MODIFY/ALTER column type, ADD/DROP constraint, RENAME. DROP TABLE (deletes table + data + structure). TRUNCATE TABLE (deletes all data, keeps structure, faster than DELETE, resets auto-increment). DROP vs TRUNCATE vs DELETE — the classic interview question with three correct distinctions.
Why indexes exist: B-tree lookup is O(log n) vs full table scan O(n). CREATE INDEX on columns used in WHERE, JOIN, ORDER BY. Composite index and leftmost prefix rule. When NOT to index: small tables, frequently updated columns, low-cardinality columns. EXPLAIN to see if the index is being used. "When should you create an index?" — asked in every SQL interview.
AUTO_INCREMENT (MySQL) / SERIAL (PostgreSQL) / IDENTITY for auto-generated primary keys. Sequences: CREATE SEQUENCE, NEXTVAL, CURRVAL. UUID vs integer primary keys — when to use which. Reset sequence after bulk delete. Why auto-increment gaps happen and why they're usually fine.
Retrieving data — the foundation of every SQL skill
SELECT columns FROM table WHERE condition. Comparison (=, <>, >, <), BETWEEN, IN (list), LIKE ('%pattern%', '_x'), IS NULL / IS NOT NULL. AND, OR, NOT — precedence matters (use parentheses). SELECT DISTINCT for unique values. LIMIT/OFFSET (MySQL/PostgreSQL) / FETCH FIRST N ROWS (standard SQL).
ORDER BY column ASC/DESC. Multi-column sort: ORDER BY dept ASC, salary DESC. Column aliasing: SELECT salary * 12 AS annual_salary. Table aliasing: FROM employees e. THE execution order every SQL writer must know: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Why you can't use a SELECT alias in WHERE.
NULL is NOT zero, NOT empty string — it's UNKNOWN. IS NULL / IS NOT NULL (never use = NULL). COALESCE(col, default) — return first non-NULL value. NULLIF(a, b) — return NULL if a equals b. How NULLs behave in comparisons (NULL = NULL is NOT true), in COUNT (COUNT(*) counts NULLs, COUNT(col) doesn't), in ORDER BY (NULLs first or last). The most misunderstood SQL concept.
CONCAT (or ||), LENGTH/CHAR_LENGTH, UPPER/LOWER, TRIM/LTRIM/RTRIM, SUBSTRING (position, length), REPLACE, LEFT/RIGHT, REVERSE, POSITION/INSTR (find substring). LIKE patterns with wildcards. String formatting for reports. "Extract the domain from email addresses" — a common SQL interview exercise.
CURRENT_DATE, CURRENT_TIMESTAMP, NOW(). Date arithmetic: adding/subtracting days, months, years. EXTRACT(YEAR/MONTH/DAY FROM date). DATE_TRUNC (PostgreSQL) / DATE_FORMAT (MySQL) for grouping by month/year. DATEDIFF for calculating intervals. Age calculation. "Find all orders placed in the last 30 days" — daily SQL at every company.
Simple CASE: CASE col WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE default END. Searched CASE: CASE WHEN condition THEN result END. Using CASE inside SELECT (conditional columns), WHERE (conditional filtering), ORDER BY (custom sort), and with aggregates (conditional counting: SUM(CASE WHEN status='active' THEN 1 ELSE 0 END)). The Swiss Army knife of SQL.
Combining tables and summarising data — the two most tested SQL skills
Returns only matching rows from both tables. ON clause for join condition. Joining on foreign key = primary key (the most common pattern). Multi-table JOINs: employees JOIN departments JOIN locations. Table aliasing is essential for readability in multi-join queries. "Find all orders with customer names" — INNER JOIN, the most basic and most used.
LEFT JOIN: all rows from left table + matching from right (NULLs where no match). RIGHT JOIN: reverse. FULL OUTER JOIN: all rows from both (NULLs on both sides where no match). "Find customers who have never placed an order" = LEFT JOIN WHERE order_id IS NULL. The Venn diagram mental model for visualising each join type.
CROSS JOIN: Cartesian product — every row from A paired with every row from B. Use cases: generating date ranges, size-colour combinations. SELF JOIN: joining a table to itself — employee-manager hierarchy, finding pairs. "Find employees who earn more than their manager" = self join on manager_id = employee_id. Self joins are a product company favourite.
COUNT(*) counts all rows (including NULLs). COUNT(col) counts non-NULL values. COUNT(DISTINCT col) counts unique values. SUM, AVG for numerical columns. MIN, MAX work on dates and strings too. Aggregates without GROUP BY return a single row for the entire table. Combining aggregates: SELECT COUNT(*), AVG(salary), MAX(salary) FROM employees.
GROUP BY groups rows sharing the same value(s). Every column in SELECT must be either in GROUP BY or inside an aggregate. HAVING filters groups (WHERE filters rows — HAVING filters AFTER grouping). "Find departments with more than 10 employees and average salary above 50000" = GROUP BY dept HAVING COUNT(*) > 10 AND AVG(salary) > 50000. GROUP BY + HAVING is the second most tested SQL pattern after JOINs.
UNION: combine results, remove duplicates. UNION ALL: combine without removing duplicates (faster). INTERSECT: rows in both queries. EXCEPT/MINUS: rows in first but not second. Rules: same number of columns, compatible types. When to use SET operations vs JOINs — "Find customers who are also suppliers" = INTERSECT vs INNER JOIN approaches.
Queries inside queries — solving complex problems with nested SQL
Subquery returns one value: WHERE salary > (SELECT AVG(salary) FROM employees). Subquery in SELECT: SELECT name, salary, (SELECT AVG(salary) FROM employees) AS avg_sal. Subquery in FROM (derived table / inline view): SELECT * FROM (SELECT ...) AS subq. When to use subquery vs JOIN — sometimes a JOIN is cleaner, sometimes a subquery is clearer.
WHERE dept_id IN (SELECT id FROM departments WHERE location='Delhi'). ANY: salary > ANY (SELECT salary FROM...) — greater than at least one. ALL: salary > ALL (SELECT salary FROM...) — greater than every one. NOT IN with NULLs — the classic trap (if subquery returns any NULL, NOT IN returns empty).
Inner query references the outer query — re-executes for EACH row of the outer. "Find employees whose salary is above their department average": WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id). "Find the second highest salary" using correlated subquery. Performance: correlated subqueries can be slow (N executions). When to rewrite as a JOIN or window function.
EXISTS returns TRUE if subquery returns any rows (doesn't care about values). IN checks if value is in result set. EXISTS is often faster for large subquery results (stops at first match). IN is simpler and faster for small lists. EXISTS handles NULLs correctly (IN doesn't). "Find departments that have at least one employee" = WHERE EXISTS (SELECT 1 FROM employees WHERE dept_id = d.id). Interview favourite.
The advanced SQL that product companies and analytics roles test — ranking, running totals, and readable complex queries
Window function = calculation across a "window" of rows WITHOUT collapsing groups (unlike GROUP BY). OVER() — the entire table as one window. OVER(PARTITION BY dept) — separate window per department. OVER(ORDER BY salary) — ordered window. The concept: GROUP BY collapses rows, window functions keep ALL rows while computing aggregates alongside. This distinction is key.
ROW_NUMBER: 1,2,3,4,5 (no ties — arbitrary tiebreak). RANK: 1,2,2,4,5 (ties get same rank, gap after). DENSE_RANK: 1,2,2,3,4 (ties get same rank, no gap). "Find the top 3 highest-paid employees per department" = DENSE_RANK() OVER(PARTITION BY dept ORDER BY salary DESC) with WHERE rnk <= 3. THE most common window function interview question.
LAG(col, offset): access previous row's value. LEAD(col, offset): access next row's value. "Find employees whose salary decreased from previous month" = WHERE salary < LAG(salary) OVER(ORDER BY month). Running totals: SUM(amount) OVER(ORDER BY date). Running average: AVG(amount) OVER(ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). Moving window calculations.
NTILE(n): divide rows into n equal groups (quartiles, deciles, percentiles). FIRST_VALUE / LAST_VALUE: get first/last value in window frame. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (default frame). Frame specification: ROWS vs RANGE. Practical: "Divide customers into 4 spending quartiles" = NTILE(4) OVER(ORDER BY total_spend).
WITH cte_name AS (SELECT ...) SELECT * FROM cte_name. CTEs make complex queries readable — name your intermediate steps. Multiple CTEs: WITH cte1 AS (...), cte2 AS (...) SELECT ... CTEs vs subqueries: CTEs are reusable within the query (reference a CTE multiple times). CTEs vs views: CTEs are query-scoped (don't persist). When CTE makes a query 10x more readable.
WITH RECURSIVE for hierarchical data: employee org chart (manager → reports), category trees (parent → children), bill of materials. Structure: base case UNION ALL recursive step. "Find all subordinates of a manager" = recursive CTE traversing the org chart. Also: generate date sequences, number series. Recursive CTEs replace what would need procedural code in most other approaches.
Modifying data, managing transactions, and making queries fast
INSERT INTO ... VALUES (single row), INSERT INTO ... SELECT (from another table). UPDATE with WHERE (without WHERE = update ALL rows — the most dangerous SQL mistake). DELETE with WHERE. Multi-table UPDATE and DELETE. MERGE / UPSERT (INSERT ... ON CONFLICT in PostgreSQL, INSERT ... ON DUPLICATE KEY UPDATE in MySQL). Batch operations for performance.
BEGIN TRANSACTION → operations → COMMIT (make permanent) or ROLLBACK (undo all). SAVEPOINT: partial rollback point within a transaction. ACID properties through transactions. Auto-commit mode vs explicit transactions. Why transactions matter: bank transfer example (debit + credit must both succeed or both fail). Real-world: every production application uses transactions.
GRANT SELECT, INSERT ON table TO user. GRANT ALL PRIVILEGES. REVOKE permissions. Role-based access control: CREATE ROLE, GRANT role TO user. WITH GRANT OPTION (allow user to grant others). Principle of least privilege — grant only what's needed. Why DCL matters: database security in production environments.
CREATE VIEW: virtual table (query stored, executed on access). Simplify complex queries, restrict access to specific columns, provide backward compatibility. Updatable views (simple SELECT) vs read-only (with JOINs, aggregates). Materialised views: pre-computed results stored on disk — fast reads, needs periodic REFRESH. When to use views vs CTEs vs materialised views.
EXPLAIN SELECT ... (shows how the DB will execute the query). Sequential Scan (slow — full table scan) vs Index Scan (fast). Nested Loop Join vs Hash Join vs Merge Join — which the optimizer chooses and why. Identifying slow queries: high row estimates, sequential scans on large tables, missing indexes. "This query is slow — optimise it" = EXPLAIN + add index + rewrite query. The single most practical advanced SQL skill.
SELECT * in production (fetches unnecessary columns). Missing indexes on JOIN columns. N+1 query problem (application sends 1 query + N follow-ups instead of one JOIN). Implicit type conversion in WHERE (kills index usage). NOT IN with NULLs. Using OFFSET for pagination (use keyset pagination instead). Writing readable SQL: formatting, aliasing, commenting complex queries.
Adding programming logic to SQL — variables, control flow, cursors, and error handling
DECLARE (variables, constants, cursors) → BEGIN (executable statements) → EXCEPTION (error handling) → END. Anonymous blocks vs named blocks (procedures, functions). Variable declaration: v_name VARCHAR2(100) := 'default';. Constants: c_tax CONSTANT NUMBER := 0.18;. %TYPE and %ROWTYPE for matching column/row types. The fundamental structure every PL/SQL program follows.
IF-THEN-ELSIF-ELSE-END IF. CASE (simple and searched). Loops: LOOP...EXIT WHEN...END LOOP (basic), WHILE...LOOP (condition-checked), FOR i IN 1..10 LOOP (counter). FOR rec IN (SELECT ...) LOOP (implicit cursor). CONTINUE and EXIT for loop control. Nested loops with labels.
Implicit cursor: automatic for single-row queries (SELECT INTO). Cursor attributes: SQL%FOUND, SQL%ROWCOUNT, SQL%NOTFOUND. Explicit cursor: DECLARE → OPEN → FETCH → CLOSE for multi-row processing. Cursor FOR loop (simplest pattern — auto-opens, fetches, closes). Parameterised cursors. REF CURSOR for dynamic result sets. When to use cursors vs set-based SQL (prefer set-based when possible).
Predefined exceptions: NO_DATA_FOUND, TOO_MANY_ROWS, DUP_VAL_ON_INDEX, ZERO_DIVIDE, VALUE_ERROR. User-defined exceptions: DECLARE exc EXCEPTION; RAISE exc;. WHEN OTHERS THEN — catch-all (use sparingly). SQLCODE and SQLERRM for error details. RAISE_APPLICATION_ERROR for custom error messages to the caller. Error logging tables. Why exception handling is critical in production database code.
Reusable database programs, event-driven automation, and modular code organisation
CREATE PROCEDURE name (params) AS BEGIN ... END. Parameter modes: IN (input, default), OUT (output), IN OUT (both). Calling procedures: CALL proc_name() or EXEC. Use cases: encapsulate business logic, reduce network traffic, enforce data rules. Stored procedures vs application code — when to put logic in the database and when to keep it in the app.
CREATE FUNCTION name RETURN type AS BEGIN ... RETURN value; END. Functions return a value and can be used inside SELECT statements. Pure functions (no side effects) vs functions with DML. Deterministic functions. "Create a function that calculates tax for a given amount" — standard PL/SQL interview exercise. Functions vs procedures — when to use which.
Triggers fire automatically on INSERT, UPDATE, DELETE events. Row-level (FOR EACH ROW) vs statement-level. :NEW and :OLD pseudo-records (access the new and previous values). Use cases: audit logging (track who changed what), data validation, auto-populating columns, enforcing complex business rules. Trigger cascading and infinite loop risks. When triggers help vs when they create maintenance nightmares.
Packages = module system for PL/SQL. Package specification (public interface) + package body (private implementation). Encapsulation: expose procedures/functions, hide helper logic. Package state: session-level variables. Overloading functions within a package. Standard packages: DBMS_OUTPUT, UTL_FILE, DBMS_SQL. Why packages matter for large database applications — modularity and reuse.
EXECUTE IMMEDIATE for runtime SQL construction: EXECUTE IMMEDIATE 'DROP TABLE ' || v_name. Bind variables with USING clause (prevents SQL injection). Bulk operations: BULK COLLECT for efficient multi-row fetch. FORALL for efficient multi-row DML. Performance: bulk operations can be 10–50x faster than row-by-row processing. "Process 1 million rows efficiently" — BULK COLLECT + FORALL is the answer.
Nested tables, VARRAYs, associative arrays (INDEX BY tables). Declaring, populating, iterating collections. Using collections with BULK COLLECT. Table functions: return a collection as a table (SELECT * FROM TABLE(my_function)). Pipelined table functions for streaming results. Why collections matter: bridging set-based SQL with procedural processing.
From basic SELECT to complex window functions — graded by difficulty, auto-evaluated against expected results on live database.
Second highest salary, top N per group, running totals, employee-manager hierarchy, consecutive dates — the 20 most asked SQL questions.
Design schema for an e-commerce/hospital/library system: tables, constraints, indexes. Then query it with complex SQL.
Given slow queries: use EXPLAIN, add indexes, rewrite with JOINs instead of subqueries, measure improvement. Real performance tuning.
Build a complete database application: procedures, functions, triggers, packages — payroll calculation or inventory management system.
4 queries in 30 minutes — mimicking placement SQL rounds. Speed + accuracy on medium-difficulty JOIN and window function problems.
Trainers solve SQL problems live on a database — showing the thinking process, not just the answer. "I need top 3 per group, so I'll use DENSE_RANK with PARTITION BY..."
Students write queries on our platform against live databases. Auto-evaluated: your query returns the correct result or it doesn't. Instant feedback. 80+ exercises graded by difficulty.
Timed SQL query challenges + MCQs on SQL theory. Auto-evaluated. Topic-wise analytics: "Strong in JOINs, weak in window functions — practise Module 5."
Student dashboards showing: queries solved per topic, accuracy, speed improvement, weak areas. TPOs see batch-level SQL fluency.
E-305, Industrial Area, Sahibzada Ajit Singh Nagar, Punjab 160055, IN
