SQL Interview Questions
Comprehensive SQL interview questions covering queries, joins, indexes, stored procedures, transactions, normalisation, and query optimisation.
DDL (Data Definition Language) defines schema — CREATE, ALTER, DROP. DML (Data Manipulation Language) manipulates data — SELECT, INSERT, UPDATE, DELETE. DCL (Data Control Language) controls permissions — GRANT, REVOKE. TCL (Transaction Control Language) manages transactions — COMMIT, ROLLBACK, SAVEPOINT.
INNER JOIN returns rows where there is a match in both tables. LEFT JOIN returns all rows from the left table and matched rows from the right (NULLs where no match). RIGHT JOIN is the reverse. FULL OUTER JOIN returns all rows from both tables, with NULLs where no match exists.
A primary key uniquely identifies each row in a table and cannot be NULL. A foreign key is a column in one table that references the primary key of another table, enforcing referential integrity between related tables.
Normalisation is the process of organising database tables to reduce data redundancy and improve integrity. Normal forms include 1NF (atomic values), 2NF (no partial dependencies), 3NF (no transitive dependencies), and BCNF.
An index is a database object that speeds up data retrieval by creating a sorted structure on one or more columns. Clustered indexes sort the physical table data; non-clustered indexes create a separate structure. Indexes speed up reads but slow down writes.
WHERE filters rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregate functions like COUNT, SUM, AVG.
A stored procedure is a precompiled set of SQL statements stored in the database that can be executed on demand. Benefits include code reuse, improved performance (cached execution plan), and reduced network traffic.
A stored procedure can perform DML operations, return multiple result sets, and doesn't have to return a value. A function must return a value, cannot perform DML (in most DBs), and can be used in SELECT statements.
A transaction is a sequence of operations executed as a single unit. ACID properties ensure reliability: Atomicity (all or nothing), Consistency (data remains valid), Isolation (concurrent transactions don't interfere), Durability (committed data persists).
A CTE is a temporary named result set defined with the WITH clause, usable within a single query. CTEs improve readability and can be recursive to traverse hierarchical data like organisational charts or category trees.
Window functions perform calculations across a set of rows related to the current row without collapsing them. Common functions include ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), and AVG() OVER() with PARTITION BY and ORDER BY clauses.
UNION combines result sets from two queries and removes duplicate rows. UNION ALL combines result sets and keeps all rows including duplicates, making it faster since it skips the deduplication step.
SQL injection is an attack where malicious SQL code is inserted into input fields to manipulate queries. Prevention: use parameterised queries or prepared statements, ORMs (EF Core), stored procedures with parameters, and input validation.
DELETE removes specific rows (with WHERE), is logged, and can be rolled back. TRUNCATE removes all rows, is minimally logged, faster, and cannot be rolled back in most databases. DROP removes the entire table including its structure.
Optimisation strategies: use execution plans to identify bottlenecks, add indexes on frequently filtered/joined columns, avoid SELECT *, avoid functions on indexed columns in WHERE clauses, use joins instead of subqueries where possible, partition large tables, and update statistics.
A view is a virtual table based on the result of a SELECT query. It simplifies complex queries, enforces security by restricting column access, and provides an abstraction layer. Indexed views can also improve performance.
A clustered index physically sorts the table rows in the index order — there can only be one per table (typically the primary key). Non-clustered indexes create a separate structure with pointers to the actual data rows, and multiple can exist per table.
A trigger is a database object that automatically executes in response to INSERT, UPDATE, or DELETE events on a table. AFTER triggers run after the DML statement; INSTEAD OF triggers replace it. They are used for audit logging, cascading updates, or enforcing complex business rules.
A deadlock occurs when two or more transactions block each other by holding locks the other needs. SQL Server automatically detects and terminates one transaction (the deadlock victim). Prevention: access resources in a consistent order, keep transactions short, and use appropriate isolation levels.
SQL Server supports: READ UNCOMMITTED (dirty reads allowed), READ COMMITTED (default, prevents dirty reads), REPEATABLE READ (prevents non-repeatable reads), SNAPSHOT (row-versioning, non-blocking reads), and SERIALIZABLE (strictest, prevents phantom reads).
Denormalisation intentionally introduces redundancy into a database to improve read performance by reducing joins. It is used in data warehouses and reporting systems where read speed matters more than write efficiency and storage costs.
Use JOINs when you need columns from multiple tables in the result — generally faster. Use subqueries for checking existence (EXISTS), filtering based on aggregates, or when the query is clearer. Correlated subqueries run once per row and can be slow; consider refactoring to JOINs.
CHAR is a fixed-length string type — it always uses the declared length in storage, padding with spaces. VARCHAR is variable-length and only uses the actual string length plus a small overhead. Use CHAR for fixed-size data (like country codes) and VARCHAR for variable-length strings.
A composite key is a primary key made up of two or more columns. Together, the columns uniquely identify a row. Composite keys are common in junction (many-to-many relationship) tables, e.g., StudentID + CourseID.
ROW_NUMBER() assigns a unique sequential integer to each row. RANK() assigns the same rank to ties and skips the next rank (1,1,3). DENSE_RANK() assigns the same rank to ties but does not skip ranks (1,1,2). All are window functions used with OVER(ORDER BY ...).
Want to sharpen your SQL skills? See our database training programmes.