SQL · Intermediate
Correlated Subqueries
A correlated subquery references columns from the outer query. It is evaluated once for each row processed by the outer query to perform row-specific logic.
Open snippet →Code library
Intermediate SQL exercises
Read these Intermediate SQL snippets line by line — each one comes with a written breakdown of what the code does and why.
SQL · Intermediate
Common Table Expressions (CTEs)
A CTE is a temporary result set that you can reference within another statement. It improves readability by breaking down complex queries into logical blocks.
Open snippet →SQL · Intermediate
Filtering Aggregated Data with HAVING
The HAVING clause filters groups created by the GROUP BY clause. Unlike WHERE, which filters individual rows, HAVING filters based on aggregate function results.
Open snippet →SQL · Intermediate
Window Functions with ROW_NUMBER
Window functions perform calculations across a set of table rows related to the current row. ROW_NUMBER assigns a unique sequential integer to rows within a specific partition.
Open snippet →SQL · Intermediate
Atomic Transactions with COMMIT
Transactions ensure multiple operations are treated as a single unit. COMMIT saves all pending changes permanently to the database, ensuring data consistency.
Open snippet →SQL · Intermediate
Handling Nulls with COALESCE
COALESCE takes a list of arguments and returns the first non-null value. It is essential for providing fallback values when dealing with optional columns in a database.
Open snippet →SQL · Intermediate
Set Intersection with INTERSECT
The INTERSECT operator returns only the distinct rows that are present in both the first and second query result sets. It is useful for finding common records across different datasets without usin…
Open snippet →SQL · Intermediate
Self-Joins for Hierarchical Relationships
A self-join is a regular join where a table is joined with itself. This is primarily used to query hierarchical data, such as employees and their managers stored in the same table.
Open snippet →SQL · Intermediate
Enforcing Uniqueness with Constraints
A UNIQUE constraint ensures that all values in a column are different across all rows. Unlike a PRIMARY KEY, a table can have multiple UNIQUE constraints, and they allow for data integrity and auto…
Open snippet →SQL · Intermediate
Conditional Logic with CASE
The CASE expression allows you to add conditional logic to your SQL queries. It evaluates conditions and returns a value when the first condition is met (like an if-then-else statement).
Open snippet →SQL · Intermediate
Managing Access Permissions with GRANT and REVOKE
Database security relies on the principle of least privilege. In standard SQL, GRANT bestows specific permissions on database objects to roles or users, while REVOKE removes existing permissions, e…
Open snippet →SQL · Intermediate
Explicit Type Conversion Using the CAST Operator
The ANSI SQL CAST operator explicitly converts a value or column expression from one data type into another (e.g., converting text or floating-point values into fixed numeric precision or dates), e…
Open snippet →SQL · Intermediate
Handling Missing Data Safely with COALESCE
The standard COALESCE scalar function evaluates arguments in sequence and returns the first non-NULL value. It prevents NULL values from propagating through arithmetic operations or output reports,…
Open snippet →SQL · Intermediate
Improving Multi-Column Search Performance with Composite Indexes
Composite (multi-column) indexes organize table data using two or more columns in a left-to-right hierarchy. Placing high-cardinality equality columns first dramatically speeds up filtering operati…
Open snippet →SQL · Intermediate
Standardized Date Arithmetic Using Temporal Datatypes and INTERVAL
ANSI SQL provides standardized temporal types and the INTERVAL keyword to perform clean date and time arithmetic without relying on vendor-specific functions. Combining timestamps with INTERVAL exp…
Open snippet →SQL · Intermediate
Correlated Subqueries for Row-by-Row Comparison
A correlated subquery evaluates once for every row processed by the outer query. By referencing columns from the outer table (e1.department_id), the subquery dynamically computes an aggregate thres…
Open snippet →SQL · Intermediate
Ranking Data with Window Functions Using DENSE_RANK
Window functions compute values across a set of table rows related to the current row without collapsing the output dataset. Unlike RANK(), DENSE_RANK() assigns consecutive integer rankings to orde…
Open snippet →SQL · Intermediate
Filtering Aggregated Results with the HAVING Clause
While the WHERE clause filters individual records before aggregation occurs, the HAVING clause filters summarized groups after the GROUP BY clause has evaluated. This allows logical conditions to b…
Open snippet →SQL · Intermediate
Ensuring Data Integrity with Atomicity Using Explicit Transactions
Transactions bundle multiple SQL data modification statements into a single, indivisible logical unit of work. By committing the transaction explicitly at the end, ANSI SQL guarantees that either a…
Open snippet →SQL · Intermediate
Combining Query Sets with UNION ALL to Avoid Duplicate Elimination Overhead
The UNION ALL operator merges the result sets of two structurally compatible queries while retaining all rows. Unlike UNION, which performs sorting and distinct filtering to eliminate duplicate row…
Open snippet →SQL · Intermediate
Computing Cumulative Totals with Window Framing
Window functions compute aggregates over defined subsets without collapsing dataset rows. Using explicit frame boundaries such as UNBOUNDED PRECEDING AND CURRENT ROW ensures predictable running tot…
Open snippet →SQL · Intermediate
Handling Null Values Safely with COALESCE
The ANSI SQL standard COALESCE function evaluates arguments sequentially from left to right and returns the first non-NULL value. It prevents unexpected NULL results in calculations and reporting.
Open snippet →SQL · Intermediate
Pivoting Data via Conditional Aggregation with CASE
Combining aggregate functions with CASE expressions allows conditional counting and pivoting within a single table scan. This pattern avoids multiple separate joins or subqueries.
Open snippet →SQL · Intermediate
Optimizing Multi-Column Search Efficiency with Composite Indexes
Composite indexes index multiple columns in a specified order. Under ANSI SQL standard query optimization principles, column ordering should follow high selectivity and the leftmost prefix rule.
Open snippet →SQL · Intermediate
Managing Partial Rollbacks using Transaction Savepoints
Savepoints provide granular transaction control by allowing partial rollbacks to specified markers within a transaction without aborting the entire sequence of operations.
Open snippet →SQL · Intermediate
Filtering Rows with Scalar Correlated Subqueries
A scalar correlated subquery executes once for each candidate row processed by the outer query. It computes the average salary for the current employee's specific department dynamically, allowing c…
Open snippet →SQL · Intermediate
Structuring Multi-Step Logic Using Common Table Expressions
Common Table Expressions (CTEs) define temporary named result sets using the WITH clause. CTEs simplify complex database logic by breaking queries into modular, readable steps.
Open snippet →SQL · Intermediate
Enforcing Domain and Referential Integrity with Schema Constraints
Schema constraints enforce business rules directly inside the database engine. CHECK constraints validate value ranges or allowed options, while FOREIGN KEY constraints guarantee referential integr…
Open snippet →SQL · Intermediate
Controlling Transaction Concurrency with SQL Isolation Levels
Setting isolation levels controls how changes made by concurrent transactions are visible to one another. REPEATABLE READ prevents non-repeatable reads during atomic state updates.
Open snippet →SQL · Intermediate
ANSI Standard String Extraction and Position Functions
Standard ANSI SQL provides portable string functions such as SUBSTRING, POSITION, and CHARACTER_LENGTH. Using standard string operations ensures cross-platform SQL compatibility.
Open snippet →