Top 50 SQL Interview Questions

A primary key uniquely identifies each row in a table. It cannot contain NULL values. A table can have one primary-key constraint, and that key can contain multiple columns.

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(100)
);

A foreign key is a column or set of columns that references a key in another table. It is used to establish and enforce relationships between tables.

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY.

SELECT department_id, COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY department_id
HAVING COUNT(*) > 5;

INNER JOIN returns only rows where the join condition matches in both tables.

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

LEFT JOIN returns every row from the left table and matching rows from the right table. If there is no match, right-table columns contain NULL.

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

DELETE removes rows and can use WHERE. TRUNCATE removes all rows while keeping the table structure. DROP removes the table itself. Transaction and identity behavior varies by database system.

DELETE FROM employees
WHERE employee_id = 10;

TRUNCATE TABLE employees;

DROP TABLE employees;

An index is a data structure that helps the database find rows faster. Indexes can improve reads but require storage and can add overhead to INSERT, UPDATE, and DELETE operations.

CREATE INDEX idx_employee_email
ON employees(email);

UNIQUE prevents duplicate values in a column or combination of columns. NULL behavior can vary between database systems.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE
);

NULL represents a missing, unknown, or not-applicable value. It is not the same as zero, an empty string, or false.

SELECT *
FROM employees
WHERE manager_id IS NULL;

UNION combines result sets and removes duplicate rows. UNION ALL combines result sets without removing duplicates and is generally faster.

SELECT email FROM customers
UNION
SELECT email FROM employees;

SELECT email FROM customers
UNION ALL
SELECT email FROM employees;

A subquery is a query nested inside another SQL query.

SELECT name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

A Common Table Expression, or CTE, is a named temporary result defined with WITH. It can make complex queries easier to read and can support recursive queries in databases that implement them.

WITH high_paid AS (
    SELECT *
    FROM employees
    WHERE salary > 100000
)
SELECT *
FROM high_paid;

A window function calculates a value across related rows without collapsing those rows into one result row.

SELECT
    name,
    salary,
    RANK() OVER (
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees;

Normalization organizes data to reduce unnecessary duplication and improve data integrity by separating related information into appropriate tables.

customers
---------
customer_id
name

orders
------
order_id
customer_id
order_date

Denormalization intentionally introduces some redundancy to reduce joins or improve read performance. It trades some storage and consistency complexity for faster or simpler reads.

orders
------
order_id
customer_id
customer_name
total

A transaction groups multiple database operations into a logical unit. The transaction is committed if the work succeeds or rolled back when appropriate.

BEGIN;

UPDATE accounts
SET balance = balance - 100
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 100
WHERE account_id = 2;

COMMIT;

ACID describes four transaction properties: Atomicity, Consistency, Isolation, and Durability.

GROUP BY groups rows with the same values so aggregate functions such as COUNT, SUM, and AVG can be calculated per group.

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id;

DISTINCT removes duplicate result rows from the selected columns.

SELECT DISTINCT department_id
FROM employees;

COUNT(*) counts rows. COUNT(column) counts non-NULL values in that column.

SELECT
    COUNT(*) AS total_rows,
    COUNT(manager_id) AS rows_with_manager
FROM employees;

COALESCE returns the first non-NULL value from the supplied expressions.

SELECT
    name,
    COALESCE(phone, 'No phone')
FROM employees;

CASE provides conditional logic inside a SQL expression.

SELECT
    name,
    CASE
        WHEN salary >= 100000 THEN 'High'
        WHEN salary >= 60000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_level
FROM employees;

A normal view is a stored query that can be queried like a table. It usually stores the query definition rather than a separate copy of the data. Materialized views are different.

CREATE VIEW active_employees AS
SELECT employee_id, name
FROM employees
WHERE active = 1;

A stored procedure is database-side executable logic stored in the database. Its syntax and capabilities depend on the database system.

-- Syntax varies by database

CALL get_employee(10);

A composite key uses two or more columns together to uniquely identify a row.

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

A self join joins a table to itself. It is useful for hierarchical relationships such as employees and managers.

SELECT
    e.name AS employee,
    m.name AS manager
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.employee_id;

CROSS JOIN returns the Cartesian product: every row from the first table combined with every row from the second table.

SELECT *
FROM colors
CROSS JOIN sizes;

Group by the column or columns that should be unique and use HAVING COUNT(*) > 1.

SELECT email, COUNT(*) AS total
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

One simple approach is to find the maximum salary below the overall maximum. This returns the second distinct salary.

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

Use DENSE_RANK when you want distinct salary rankings and want ties to share the same rank.

WITH ranked AS (
    SELECT
        salary,
        DENSE_RANK() OVER (
            ORDER BY salary DESC
        ) AS salary_rank
    FROM employees
)
SELECT salary
FROM ranked
WHERE salary_rank = 3;

Use a LEFT JOIN and find rows where the department side is NULL.

SELECT e.*
FROM employees e
LEFT JOIN departments d
    ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

Group by email and return values that occur more than once.

SELECT email
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

A common approach is NOT EXISTS. LEFT JOIN with an IS NULL check can also be used.

SELECT c.*
FROM customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

Use DENSE_RANK partitioned by department. This returns all employees tied within the top three distinct salary levels.

WITH ranked AS (
    SELECT
        name,
        department_id,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS salary_rank
    FROM employees
)
SELECT *
FROM ranked
WHERE salary_rank <= 3;

ROW_NUMBER assigns a sequential number to rows within a window. Ties still receive different row numbers.

SELECT
    name,
    salary,
    ROW_NUMBER() OVER (
        ORDER BY salary DESC
    ) AS row_num
FROM employees;

Both give equal values the same rank. RANK leaves gaps after ties, while DENSE_RANK does not.

Salary: 100, 100, 90

RANK:
1, 1, 3

DENSE_RANK:
1, 1, 2

Use SUM as a window function ordered by the desired sequence. Include a unique ordering column when deterministic ordering is required.

SELECT
    order_id,
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date, order_id
        ROWS BETWEEN UNBOUNDED PRECEDING
        AND CURRENT ROW
    ) AS running_total
FROM orders;

Isolation levels control how much one transaction can observe changes made by other concurrent transactions. Common levels are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE. Exact behavior depends on the database.

A deadlock occurs when two or more transactions wait for locks held by each other, so none can continue.

Locking controls concurrent access to data so conflicting operations do not corrupt results. Databases can use different lock types and granularities.

An execution plan shows how the database intends to execute a query, including operations such as scans, index access, joins, sorting, and aggregation.

EXPLAIN
SELECT *
FROM employees
WHERE email = 'user@example.com';

Start by measuring the query with an execution plan. Then consider appropriate indexes, better filtering, efficient joins, avoiding unnecessary columns or rows, and reviewing schema and query design.

SQL injection occurs when untrusted input is incorporated into SQL in a way that allows the input to change the intended query.

// Use a parameterized query

SELECT *
FROM users
WHERE email = ?;

EXISTS tests whether a matching row exists. IN compares a value against values returned by a subquery. Performance depends on the database, data, indexes, and query shape.

SELECT c.*
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.customer_id
);

CHAR represents fixed-length character data, while VARCHAR represents variable-length character data. Exact storage and padding behavior depends on the database.

country_code CHAR(2)

name VARCHAR(100)

NULL represents an unknown or missing value, so comparisons with NULL using = or <> do not produce TRUE. Use IS NULL or IS NOT NULL instead.

SELECT *
FROM employees
WHERE manager_id IS NULL;

Identify duplicates with ROW_NUMBER and keep the first row. The DELETE syntax is database-specific, so the exact statement depends on the database.

SELECT *,
       ROW_NUMBER() OVER (
           PARTITION BY email
           ORDER BY user_id
       ) AS rn
FROM users;

Calculate the department average and compare each employee's salary against it.

SELECT e.*
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

Use the LAG window function to access a value from the previous row within an ordered result.

SELECT
    order_id,
    order_date,
    amount,
    LAG(amount) OVER (
        ORDER BY order_date, order_id
    ) AS previous_amount
FROM orders;

A primary key identifies the row and cannot contain NULL values. A table has one primary-key constraint, while it can have multiple UNIQUE constraints. NULL handling for UNIQUE constraints varies by database.

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(100) UNIQUE
);