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 →Read these Intermediate SQL snippets line by line — each one comes with a written breakdown of what the code does and why.
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 →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 →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 →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 →Transactions ensure multiple operations are treated as a single unit. COMMIT saves all pending changes permanently to the database, ensuring data consistency.
Open snippet →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 →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 →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 →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 →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 →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 →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 →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 →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 →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 →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 →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 →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 →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 →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 →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 →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 →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 →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 →Savepoints provide granular transaction control by allowing partial rollbacks to specified markers within a transaction without aborting the entire sequence of operations.
Open snippet →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 →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 →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 →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 →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 →