SQL Interview Questions

SQL stands for Structured Query Language, and it's the standard language for interacting with relational databases. You use it to create and modify database structures, insert and update data, and query records from tables. Almost every relational database system, including PostgreSQL, MySQL, Oracle, and SQL Server, uses SQL as its primary interface. It's been around since the 1970s and remains one of the most essential skills in data engineering and backend development.

SQL databases are relational, meaning data is stored in structured tables with predefined schemas, and relationships between tables are enforced through keys. NoSQL databases cover a broad category including document stores like MongoDB, key-value stores like Redis, and columnar databases like Cassandra, and they typically have flexible or schema-less designs. SQL databases excel at complex queries, joins, and transactional consistency (ACID compliance), while NoSQL databases are often chosen for horizontal scalability, high write throughput, or unstructured data. The choice really depends on your data model and access patterns, not which one is universally better.

Failover clustering is a high-availability solution where multiple servers (nodes) share access to the same database storage, and if the active node fails, another node automatically takes over with minimal downtime. SQL Server Failover Cluster Instances (FCI) present a single virtual server name to clients, so applications reconnect transparently after a failover. The shared storage (usually a SAN or Storage Spaces Direct) holds the actual data files, and only one node actively owns the SQL Server instance at any given time. It protects against hardware and OS-level failures but not against data corruption, which is why it's often combined with Always On Availability Groups.

A cursor is a database object that lets you process a result set one row at a time, rather than operating on the whole set at once. You declare it with a SELECT statement, then open it, fetch rows in a loop, process each row, and finally close and deallocate it. Cursors are useful when you need to perform row-by-row operations that genuinely cannot be expressed as a set-based query, like complex procedural logic that depends on the previous row. The downside is that cursors are significantly slower than set-based operations and should be avoided or replaced with set-based alternatives whenever possible.

DECLARE emp_cursor CURSOR FOR
    SELECT emp_id, salary FROM employees;

OPEN emp_cursor;
FETCH NEXT FROM emp_cursor INTO @emp_id, @salary;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- process each row here
    FETCH NEXT FROM emp_cursor INTO @emp_id, @salary;
END;

CLOSE emp_cursor;
DEALLOCATE emp_cursor;

The cleanest approach uses a subquery to exclude the maximum salary and then picks the max of what remains. Another common approach uses DENSE_RANK() which handles ties correctly, which is usually what interviewers actually want.

-- Subquery approach
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Window function approach (handles ties)
SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 2;

You use the SUBSTRING (or LEFT) function to extract the first 5 characters from a string. Both work across most major databases, but LEFT is more concise when you always want characters from the start.

SELECT SUBSTRING(column_name, 1, 5) FROM table_name;

-- Or equivalently
SELECT LEFT(column_name, 5) FROM table_name;

A primary key is a column or combination of columns that uniquely identifies each row in a table. It enforces two constraints automatically: uniqueness and NOT NULL, so no two rows can share the same primary key value and no row can have a null one. Every table should have a primary key because it gives you a reliable way to reference individual records, and it's the foundation for setting up foreign key relationships with other tables.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  total DECIMAL(10,2)
);

A foreign key is a column in one table that references the primary key of another table, creating a link between the two. It enforces referential integrity, meaning you can't insert a value in the foreign key column that doesn't exist in the referenced table, and depending on the setup, the database can restrict or cascade deletes and updates. For example, an orders table might have a customer_id column that references the id column in a customers table, ensuring every order belongs to a real customer.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE
);

Normalization is the process of organizing a database to reduce data redundancy and improve data integrity by applying a series of rules called normal forms. The most common ones are 1NF (no repeating groups, atomic values), 2NF (no partial dependencies on a composite key), and 3NF (no transitive dependencies, meaning non-key columns shouldn't depend on other non-key columns). For example, storing a customer's city and zip code in an orders table violates 3NF because city depends on zip code, not on the order itself. The fix is to move that data into a separate addresses or locations table.

-- Violates 3NF: city depends on zip_code, not order_id
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  zip_code VARCHAR(10),
  city VARCHAR(100)  -- transitive dependency
);

-- Better: separate the location data
CREATE TABLE zip_codes (
  zip_code VARCHAR(10) PRIMARY KEY,
  city VARCHAR(100)
);

Denormalization is the deliberate process of introducing redundancy into a database by merging tables or duplicating data, with the goal of improving read performance. Normalized schemas require many joins, which can become expensive at scale, so denormalization trades write complexity and storage for faster query execution. A common example is storing a precomputed order total directly on the orders table instead of summing line items every time. You'd typically reach for denormalization in read-heavy systems, data warehouses, or reporting layers where query speed matters more than strict data consistency.

A stored procedure is a precompiled collection of SQL statements saved in the database that can be executed as a single unit. You call it by name, optionally passing parameters, and it runs the enclosed logic on the server side. Stored procedures reduce network traffic since you send one call instead of multiple queries, and the database caches the execution plan for better performance.

CREATE PROCEDURE GetEmployeesByDept
  @DeptId INT
AS
BEGIN
  SELECT name, salary
  FROM employees
  WHERE dept_id = @DeptId
  ORDER BY salary DESC;
END;

Stored procedures improve performance because the execution plan is compiled and cached on first run, so subsequent calls skip the parsing and optimization steps. They reduce network traffic by sending a single procedure call instead of multiple SQL statements. They also improve security by allowing users to execute the procedure without direct access to the underlying tables, and they help prevent SQL injection when used with parameterized inputs.

A join in SQL Server is a way to combine rows from two or more tables based on a related column between them. It lets you query data spread across multiple tables as if it were a single result set. SQL Server supports several types: INNER JOIN, LEFT/RIGHT OUTER JOIN, FULL OUTER JOIN, and CROSS JOIN, each controlling which rows are included when there is or is not a match.

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

An INNER JOIN returns only the rows where there is a matching value in both tables based on the join condition. If a row in the left table has no corresponding row in the right table, it is completely excluded from the result, and the same goes for the right table. It is the most commonly used join type and the default when you just write JOIN without specifying a type.

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
-- Only employees with a valid department are returned

DELETE removes rows one at a time, logs each deletion in the transaction log, can use a WHERE clause to target specific rows, and fires any DELETE triggers on the table. TRUNCATE removes all rows at once with minimal logging (it deallocates data pages), cannot use a WHERE clause, does not fire triggers, and resets identity counters. DELETE is slower but more flexible; TRUNCATE is faster but removes everything. Both can be rolled back inside an explicit transaction in SQL Server and PostgreSQL.

ALTER TABLE is the DDL command used to modify an existing table's structure in SQL Server. You can add columns, drop columns, change data types, add or drop constraints, and rename columns. The syntax varies slightly depending on what you're doing, for example adding a column uses ADD, while modifying an existing column's type uses ALTER COLUMN.

-- Add a new column
ALTER TABLE employees ADD department_id INT NULL;

-- Change a column's data type
ALTER TABLE employees ALTER COLUMN salary DECIMAL(12, 2);

-- Drop a column
ALTER TABLE employees DROP COLUMN old_column;

-- Add a constraint
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);

An index is a database object that speeds up data retrieval by creating a separate data structure that points to rows in a table, similar to an index at the back of a book. Without an index, SQL Server has to scan every row in a table to find matches, which gets very slow on large tables. Indexes store a sorted copy of one or more columns along with pointers to the actual rows, so the engine can jump directly to relevant data. The trade-off is that indexes consume disk space and slow down INSERT, UPDATE, and DELETE operations because the index must be maintained alongside the table.

CREATE INDEX idx_employee_lastname ON employees (last_name);

-- With multiple columns (composite index)
CREATE INDEX idx_emp_dept ON employees (department_id, last_name);

A clustered index determines the physical sort order of the data in the table itself, so a table can only have one clustered index. The leaf nodes of a clustered index are the actual data pages. A non-clustered index is a separate structure that stores a sorted copy of the indexed columns along with a pointer back to the actual row, and a table can have up to 999 non-clustered indexes in SQL Server. Lookups using a non-clustered index may require an extra step called a key lookup to fetch columns not included in the index, while a clustered index read goes straight to the data. Primary keys are clustered by default in SQL Server, but you can change that.

SQL Server supports five transaction isolation levels that control how transactions interact with each other. READ UNCOMMITTED allows dirty reads (seeing uncommitted changes from other transactions). READ COMMITTED (the default) only reads committed data. REPEATABLE READ prevents other transactions from modifying rows you have read. SERIALIZABLE is the strictest level, preventing phantom reads by locking entire ranges. SNAPSHOT uses row versioning to give each transaction a consistent view of the data without blocking other transactions.

ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity means all operations in a transaction succeed or all are rolled back, there is no partial completion. Consistency ensures the database moves from one valid state to another, respecting all constraints and rules. Isolation means concurrent transactions do not see each other's intermediate state, controlled by isolation levels like READ COMMITTED or SERIALIZABLE. Durability guarantees that once a transaction is committed, the changes survive even a system crash, ensured by the transaction log. In SQL Server, a transaction is a unit of work wrapped in BEGIN TRANSACTION and ended with COMMIT or ROLLBACK.

BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 500 WHERE account_id = 1;
    UPDATE accounts SET balance = balance + 500 WHERE account_id = 2;

    IF @@ERROR <> 0
        ROLLBACK;
    ELSE
        COMMIT;

A subquery is a query nested inside another query, and it can appear in the SELECT, FROM, or WHERE clause depending on what you need. You'd use one when you need to filter based on a derived value, reference an aggregated result, or break a complex problem into steps. They're useful but can hurt performance if they run row-by-row (correlated subqueries), so it's worth considering whether a JOIN or CTE would be cleaner or faster.

-- Find employees earning above the average salary
SELECT name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary) FROM employees
);

A subquery is a SELECT statement nested inside another SQL statement. It can be used in the WHERE clause to filter based on computed values, in the FROM clause to treat a derived result set as a temporary table, or in the SELECT clause to compute a value per row. Correlated subqueries reference the outer query and execute once per row, which can be slow on large datasets, so it's often worth replacing them with JOINs or CTEs when performance is a concern.

-- Subquery in FROM clause (derived table)
SELECT dept, avg_salary
FROM (
  SELECT department AS dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_averages
WHERE avg_salary > 70000;

GROUP BY collapses rows that share the same value in one or more columns into a single row, allowing you to apply aggregate functions like COUNT, SUM, AVG, MIN, and MAX to each group. Without GROUP BY, aggregate functions operate on the entire result set as one group. A classic use case is calculating total sales per region or counting users per signup date.

SELECT region, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY region
ORDER BY total_sales DESC;

Aggregate functions operate on a set of rows and return a single summary value. Common examples are SUM(), COUNT(), AVG(), MIN(), and MAX(). They are typically used with GROUP BY to compute values per group. Scalar functions, on the other hand, operate on a single value and return a single value. Examples include UPPER(), LEN(), ROUND(), and GETDATE(). The distinction is that aggregate functions collapse multiple rows into one result, while scalar functions transform one input into one output on a row-by-row basis.

-- Aggregate: summarizes multiple rows
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

-- Scalar: transforms a single value per row
SELECT UPPER(first_name), ROUND(salary, 2) FROM employees;