Advanced SQL 2

What is a subquery in SQL?

A query nested inside another query, often used in WHERE or SELECT clauses.

What is a correlated subquery?

A subquery that references a column from the outer query.

What is a common table expression (CTE)?

A temporary named result set defined using WITH keyword used within a single query.

What is the syntax of a CTE?

WITH cte_name AS (SELECT ...) SELECT * FROM cte_name;

What is a recursive CTE?

A CTE that references itself to perform recursive operations, like traversing hierarchies.

What is a window function?

A function that performs calculations across a set of rows related to the current row.

Give an example of a window function.

ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC)

What is the difference between RANK() and DENSE_RANK()?

RANK() skips ranks on ties; DENSE_RANK() does not.

What does NTILE(n) do?

Divides the result set into n buckets or groups as evenly as possible.

What is LEAD() in SQL?

A window function that accesses the next row’s value in a result set.

What is LAG() in SQL?

A window function that accesses the previous row’s value.

What is PARTITION BY in window functions?

It divides the result set into groups before applying the window function.

What is a self-join?

A join where a table is joined with itself using table aliases.

What is a pivot table in SQL?

A transformation of rows into columns for reporting or summarization.

What is the SQL keyword to create a pivot?

In some databases like SQL Server, use PIVOT; others use CASE WHEN + aggregation.

What is dynamic SQL?

SQL statements constructed and executed at runtime, often using variables.

What are stored procedures?

Precompiled blocks of SQL code stored in the database for reuse.

What is the difference between stored procedures and functions?

Functions return values and can be used in queries; procedures may not return a value and are invoked independently.

What are triggers in SQL?

Special procedures that execute automatically in response to certain events on a table (INSERT, UPDATE, DELETE).

What is an indexed view?

A view with a clustered index, allowing it to store data physically and improve performance.

What is a materialized view?

A view whose result is stored on disk and periodically refreshed.

What is a lateral join?

A join where the right-side subquery can reference columns from the left side (supported using LATERAL or CROSS APPLY).

What is CROSS APPLY in SQL Server?

It allows invoking a table-valued function or subquery for each row from the left side.

What is OUTER APPLY?

Like CROSS APPLY, but it returns NULLs when no matching rows exist.

What is JSON support in SQL?

The ability to parse, query, and manipulate JSON data stored in table columns.

What function is used to extract JSON values in PostgreSQL?

->, ->>, and jsonb_extract_path()

What function is used to extract JSON values in MySQL?

JSON_EXTRACT() or shorthand ->

What is full-text search in SQL?

A specialized search feature that supports complex text search operations like stemming and relevance.

What is a ranking function?

A window function that assigns a rank to rows within a partition.

What does GROUPING SETS do?

Allows multiple groupings in a single query for advanced summarization.

What is a rollup in SQL?

A grouping operation that adds subtotals and a grand total.

What is a cube in SQL?

A multidimensional grouping operation to compute all possible subtotals.

What is the difference between UNION and UNION ALL?

UNION removes duplicates; UNION ALL includes all rows.

What is a MERGE statement?

A command that performs INSERT, UPDATE, or DELETE operations based on matching conditions.

What is an upsert in SQL?

A pattern to insert a new row or update an existing one if it exists.

What is ON CONFLICT in PostgreSQL?

A clause used to handle upserts with INSERT INTO ... ON CONFLICT DO UPDATE.

What is INSERT IGNORE in MySQL?

An insert that skips rows causing duplicate key errors instead of failing.

What is a temporary table?

A table that exists only during the session or transaction.

What is a derived table?

A subquery in the FROM clause treated as a temporary table.

What is a scalar subquery?

A subquery that returns exactly one row and one column.

What is a set operator?

An operator like UNION, INTERSECT, or EXCEPT used to combine query results.

What is a parameterized query?

A query that uses placeholders (? or :param) to safely insert variable values.

What is a cursor in SQL?

A database object used to iterate over query result sets row by row.

What are user-defined functions (UDFs)?

Functions written by users that can be reused in SQL statements.

What are analytic functions?

Another name for window functions used to compute values over a set of rows.

What does WITH TIES do in a query with TOP or FETCH?

Returns all tied rows with the same rank or value as the last included row.

What is a recursive query?

A query that calls itself, usually via recursive CTEs to navigate hierarchies.

What is the difference between DELETE and TRUNCATE?

DELETE removes rows one by one and logs them; TRUNCATE is faster and removes all rows without logging each one.

What is a transaction in SQL?

A sequence of operations executed as a single logical unit of work.

What is the purpose of SAVEPOINT?

To create a restore point within a transaction that can be rolled back to.

What is a deadlock in SQL?

A situation where two or more transactions block each other permanently.

How can you detect and resolve deadlocks?

By using database logs, deadlock detection tools, and avoiding circular locks.

What is an execution plan?

A breakdown of how the database executes a query, including indexes, joins, and access methods.

What is query optimization?

The process of improving query performance by rewriting SQL or modifying indexes/hints.

What is a bind variable?

A placeholder in SQL that is replaced with an actual value at execution time.

What is the difference between implicit and explicit joins?

Implicit joins use commas in FROM; explicit joins use JOIN keyword and ON clause.

What is normalization in SQL databases?

The process of organizing data to reduce redundancy and improve integrity.