SQL Interview Bundle

Find the Nth highest salary without using LIMIT or TOP.

Use a correlated subquery - count how many distinct salaries are strictly greater than the current row. When that count equals N-1, you've found the Nth highest:

SELECT salary
FROM Employee e1
WHERE N-1 = (
  SELECT COUNT(DISTINCT salary)
  FROM Employee e2
  WHERE e2.salary > e1.salary
);

Rank employees by salary within each department - no gaps in rank numbers.

DENSE_RANK with PARTITION BY resets the counter per department and never skips a number when there are ties:

SELECT
  name, department, salary,
  DENSE_RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_rank
FROM Employee;

How would you optimize a query that takes too long to execute?

Start by running EXPLAIN to see the execution plan and spot the bottleneck. Add indexes on the columns used in WHERE, JOIN, and ORDER BY clauses, simplify complex joins and subqueries where possible, and partition very large tables to shrink how much data each query scans.

-- Diagnose
EXPLAIN SELECT * FROM orders WHERE customer_id = 101;

-- Fix: index the filtered column
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

How does a LEFT JOIN differ from an INNER JOIN? Provide an example query.

INNER JOIN returns only rows with matching values in both tables, silently dropping anything without a match. LEFT JOIN keeps every row from the left table regardless of a match, filling in NULLs for the right table's columns when there isn't one - useful when you need to see rows with no match, like customers with zero orders.

-- INNER JOIN: only customers with at least one order
SELECT c.name, o.order_id
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;

-- LEFT JOIN: every customer, even with no orders
SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;

How does SQL help in managing large datasets for analysis and reporting?

SQL covers the whole reporting pipeline: SELECT retrieves exactly the data you need, aggregate functions like SUM, AVG, and COUNT summarize it, WHERE and HAVING filter out anything irrelevant, JOINs pull related data together from multiple tables, and indexes keep all of this fast even as the dataset grows.

SELECT region, COUNT(*) AS orders, SUM(amount) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'completed'
GROUP BY region
HAVING SUM(amount) > 100000;

Write a query to retrieve the second highest salary in an employee table without using TOP or LIMIT.

Take the maximum salary that is strictly less than the overall maximum - that's the second highest by definition, and it works without any database-specific TOP/LIMIT syntax.

SELECT MAX(salary) AS second_highest_salary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

Define a primary key and explain how it ensures data uniqueness.

A primary key is one or more columns that uniquely identify every row in a table. The database enforces that automatically - it rejects duplicate values, builds a unique index behind the scenes for fast lookups, and disallows NULLs in the key column(s).

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

Can a table have more than one primary key? Why or why not?

No - a table can only have one PRIMARY KEY constraint, since its job is to be the single, unambiguous identifier for a row. It can still span multiple columns as a composite key, but that's still one constraint, not several.

-- One PRIMARY KEY constraint, made of two columns
CREATE TABLE order_items (
  order_id INT,
  product_id INT,
  quantity INT,
  PRIMARY KEY (order_id, product_id)
);

What are the main objectives of normalization? Explain with examples.

Normalization reduces redundancy, protects data integrity, and keeps queries fast. For example, moving repeated department details into their own table removes duplication, storing just an employee_id as a foreign key in a transactions table keeps that data consistent instead of copying it everywhere, and smaller, well-structured tables make joins and lookups more efficient.

-- Before: department name repeated on every employee row
-- After normalization: a separate departments table
CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  name VARCHAR(100)
);
CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(100),
  department_id INT REFERENCES departments(department_id)
);