Top 10 Most Repeated Questions

1. What are the different types of JOINs, and how do they differ?

Answer: JOINs combine rows from two or more tables using a common column. The main types are INNER, LEFT, RIGHT, FULL, and CROSS JOIN.

Understanding tables first: A table is like a spreadsheet. Rows are records, columns are fields.

We’ll use two small tables to see every join in action.

Employees table:

employee_id	name	department_id
1	Ali	10
2	Bob	20
3	Cam	(none)

Departments table:

department_id	department_name
10	Sales
20	IT
30	HR

1. INNER JOIN Returns only rows where a match exists in both tables. Rows that don’t match on either side are dropped.

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

Result:

name	department_name
Ali	Sales
Bob	IT

Cam is missing because he has no department, so there’s no match.

2. LEFT JOIN Returns every row from the left table, plus matching rows from the right. Unmatched right columns become NULL.

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

Result:

name	department_name
Ali	Sales
Bob	IT
Cam	(none)

Cam is kept because LEFT JOIN preserves all left rows.

3. RIGHT JOIN Same idea as LEFT, but keeps every row from the right table, plus matches from the left.

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

Result:

name	department_name
Ali	Sales
Bob	IT
(none)	HR

HR is kept even though no employee belongs to it.

4. FULL OUTER JOIN Returns all rows from both tables. Wherever a match is missing, the absent side is NULL.

5. CROSS JOIN Pairs every row of one table with every row of the other. No condition is needed.

Key differences table:

Join	Keeps unmatched left rows?	Keeps unmatched right rows?
INNER	No	No
LEFT	Yes	No
RIGHT	No	Yes
FULL	Yes	Yes

When to use which: Use INNER when you only want data that exists on both sides.

Use LEFT when you must keep every row from the left table, even those with no match.

2. What is the difference between the WHERE and HAVING clauses?

Answer: WHERE filters rows before grouping. HAVING filters groups after grouping. You can’t use aggregate functions like COUNT() or SUM() inside WHERE — those must go in HAVING.

Why the order matters: A query does its work in steps. First it reads the rows from the table, then it filters them, then it groups them, and finally it aggregates them.

WHERE acts at the row-filtering step. It decides which rows are fed into the grouping.

HAVING acts after grouping. It decides which groups appear in the result.

Because of this, WHERE can only use plain column values. It cannot use the result of COUNT() or AVG(), because those don’t exist yet at that step.

Example — finding departments with more than 10 employees hired recently:

SELECT department_id, COUNT(*)
FROM employees
WHERE hire_date >= '2025-01-01'
GROUP BY department_id
HAVING COUNT(*) > 10;

Step by step:

1. WHERE hire_date >= '2025-01-01' keeps only recently hired employees.
2. GROUP BY department_id groups them by department.
3. HAVING COUNT(*) > 10 keeps only departments with more than 10.

Key differences table:

	WHERE	HAVING
Runs before grouping	Yes	No
Runs after grouping	No	Yes
Can use aggregate functions	No	Yes
Works with GROUP BY	Sometimes	Always

Key takeaway: Filtering rows with WHERE before grouping is also more efficient, because the database aggregates fewer rows.

3. What is the difference between UNION and UNION ALL?

Answer: Both combine the results of two queries into one result set. UNION removes duplicate rows. UNION ALL keeps every row, including duplicates.

How they work: UNION must check for duplicates, so it sorts or hashes the combined results and drops repeats. That extra step makes it slower.

UNION ALL simply stacks the results of the two queries together. No duplicate check, so it’s faster.

Example with the two tables:

Employees_2024 table:

name
Ali
Bob

Employees_2025 table:

name
Bob
Cam

UNION:

SELECT name FROM employees_2024
UNION
SELECT name FROM employees_2025;

Result:

name
Ali
Bob
Cam

Bob appears only once because UNION removed the duplicate.

UNION ALL:

SELECT name FROM employees_2024
UNION ALL
SELECT name FROM employees_2025;

Result:

name
Ali
Bob
Bob
Cam

Bob appears twice because UNION ALL keeps every row.

Key differences table:

	UNION	UNION ALL
Removes duplicates	Yes	No
Faster	No	Yes
Good when results never overlap	No	Yes

Key takeaway: Use UNION only when you genuinely need distinct rows. Use UNION ALL when duplicates don’t matter, because it’s faster. Both queries must have the same number and type of columns.

4. How do you find the Nth highest salary in a table?

Answer: Sort the salaries from highest to lowest, skip the top N-1 rows, and take the next one. Two common approaches are DENSE_RANK() and LIMIT/OFFSET.

Understanding the idea: Imagine a leaderboard of salaries from biggest to smallest. The 1st highest is the top. The 2nd highest is next. The Nth highest is N positions down.

Employees table:

employee_id	name	salary
1	Ali	90000
2	Bob	85000
3	Cam	85000
4	Dan	70000

Approach 1 — using DENSE_RANK():

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;

DENSE_RANK() gives every distinct salary a rank with no gaps. Ali is rank 1, Bob and Cam both rank 2, Dan rank 3. So rank 2 returns 85000.

If you used RANK() instead, Bob and Cam would both be rank 2 but Dan would be rank 4, because ties skip numbers. That can give the wrong answer when there are ties.

Approach 2 — using LIMIT and OFFSET:

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

OFFSET 1 skips the highest row. LIMIT 1 takes the next one. For the 2nd highest, OFFSET is N-1, so OFFSET 1 means “skip 1 row”.

How the ranks work for each N:

N	What to skip	Result
1	0 rows (OFFSET 0)	90000
2	1 row (OFFSET 1)	85000
3	2 rows (OFFSET 2)	70000

Key takeaway: Use DENSE_RANK() when salaries can have ties. Use LIMIT/OFFSET for a quick and simple answer. Some databases don’t allow an expression like OFFSET N-1, so substitute the actual number.

5. What is the difference between DELETE, TRUNCATE, and DROP?

Answer: All three remove data, but at different levels. DELETE removes specific rows, TRUNCATE removes all rows but keeps the table, and DROP removes the entire table.

1. DELETE Removes rows one by one.

It supports a WHERE clause, so you can remove only certain rows.

It is fully logged, so it can be rolled back inside a transaction.

Because it’s row-by-row and logged, it’s the slowest of the three.

DELETE FROM employees WHERE department_id = 10;

2. TRUNCATE Removes all rows instantly.

It keeps the table structure, so you can still insert into it afterward.

It uses minimal logging and cannot easily be rolled back.

It doesn’t support a WHERE clause.

TRUNCATE TABLE employees;

3. DROP Removes the entire table — structure, data, indexes, everything.

The table no longer exists.

You must recreate it before using it again.

DROP TABLE employees;

Key differences table:

	DELETE	TRUNCATE	DROP
Removes rows	Yes (with WHERE)	All rows	All rows
Keeps table structure	Yes	Yes	No
Supports WHERE	Yes	No	No
Can roll back	Yes	Hardly	No
Speed	Slowest	Fast	Fast
Command type	DML	DDL	DDL

Key takeaway: Use DELETE for selected rows with recovery options. Use TRUNCATE to clear a table completely but keep it. Use DROP when the table is no longer needed.

6. What are Window Functions (e.g., RANK, DENSE_RANK)?

Answer: Window functions perform calculations across a set of related rows while keeping every individual row in the result. Normal aggregate functions collapse many rows into one; window functions do not.

Understanding the difference: A normal SUM() with GROUP BY returns one row per group. The individual rows are gone.

A window function returns the same number of rows as the input, with the calculation added as a new column.

Example — ranking employees within each department: Employees table:

name	department_id	salary
Ali	1	90000
Bob	1	85000
Cam	2	80000
Dan	2	80000

SELECT name, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rnk
FROM employees;

Result:

name	department_id	salary	rnk	dense_rnk
Ali	1	90000	1	1
Bob	1	85000	2	2
Cam	2	80000	1	1
Dan	2	80000	1	1

RANK() leaves gaps when values tie. DENSE_RANK() never leaves gaps.

Key parts of a window function:

• PARTITION BY splits the rows into groups (here, by department).
• ORDER BY defines the order inside each group.
• The function (like RANK() or SUM()) does the calculation over the window.

Other uses: Window functions can also make running totals and moving averages.

SELECT date, sales,
       SUM(sales) OVER (ORDER BY date) AS running_total
FROM daily_sales;

Key takeaway: Window functions replace complex self-joins and keep row detail that GROUP BY loses. RANK() and DENSE_RANK() are the two you’ll most often be asked about in interviews.

7. What is Normalization and why is it used?

Answer: Normalization is the process of organizing tables to reduce duplicate data and keep the database consistent. Each piece of data is stored once and referenced elsewhere through keys.

The problem normalization solves: Imagine an Orders table that repeats the customer’s full name and address on every order.

If the customer moves, you have to update every single order row. Miss one row and the data becomes inconsistent.

Normalization splits the data into two tables. Customer details live once in a Customers table. Orders only store a customer ID that points back to it.

Before normalization (bad design):

order_id	customer_name	customer_address	product
1	Ali	Street 1	Pen
2	Ali	Street 1	Book

The address is repeated. If Ali moves, both rows must change.

After normalization (good design):

Customers table:

customer_id	name	address
1	Ali	Street 1

Orders table:

order_id	customer_id	product
1	1	Pen
2	1	Book

The address is stored once. A change touches only one row.

The normal forms:

• 1NF — every column holds a single value, and every row is unique.
• 2NF — 1NF plus every non-key column depends on the whole primary key.
• 3NF — 2NF plus no non-key column depends on another non-key column.

Key takeaway: Normalization keeps data consistent, shrinks storage, and makes updates safe. The trade-off is more tables and more joins, which is why read-heavy systems sometimes denormalize on purpose.

8. How do you optimize a slow-running SQL query?

Answer: Start by checking the query execution plan, then fix the bottleneck. The most common fix is adding an index on the columns used in WHERE and JOIN.

Step 1 — look at the execution plan: The database can show you how it plans to run the query. Use EXPLAIN in PostgreSQL or MySQL.

EXPLAIN ANALYZE
SELECT id, name FROM employees WHERE department_id = 5;

The plan shows whether the database is scanning the whole table or using an index. A full table scan over millions of rows is usually the main problem.

Step 2 — apply the common fixes:

Add indexes: An index on department_id lets the database jump straight to matching rows instead of reading everything.

CREATE INDEX idx_emp_dept ON employees(department_id);

Avoid SELECT *: Only select the columns you need. This reduces the data transferred.

Make conditions index-friendly: Don’t wrap indexed columns in functions. WHERE LOWER(name) = 'ali' blocks the index. Use WHERE name = 'Ali' instead.

Example — before and after:

Without an index, the query reads every row:

SELECT id, name FROM employees WHERE department_id = 5;

With an index on department_id, the same query finds the rows instantly.

Key takeaway: Optimization is iterative. Change one thing, re-run the plan, and measure. Also remember that too many indexes slow down INSERT and UPDATE, so index only what slow queries actually need.

9. How do you find and remove duplicate records?

Answer: First, group the data and count to find the duplicates. Then delete all but one copy of each duplicate using ROW_NUMBER().

Step 1 — find the duplicates: Group by the column that should be unique and count how many times each value appears.

Users table:

id	email
1	ali@mail.com
2	bob@mail.com
3	ali@mail.com

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

Result:

email	COUNT(*)
ali@mail.com	2

This tells you ali@mail.com appears twice.

Step 2 — remove the duplicates, keeping one copy: ROW_NUMBER() assigns a number to each row inside a group. Row 1 of each group is kept; the rest are deleted.

WITH ranked AS (
  SELECT id,
         ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
  FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

After this, only id 1 (ali@mail.com) remains; id 3 is deleted.

Step 3 — prevent future duplicates: Add a UNIQUE constraint so duplicates can’t be inserted again.

ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

Key takeaway: Find duplicates with GROUP BY and HAVING COUNT(*) > 1. Remove them with ROW_NUMBER() and a CTE. Then add a UNIQUE constraint so they can’t come back.

10. What are Primary and Foreign Keys?

Answer: A Primary Key uniquely identifies each row in a table. A Foreign Key is a column that points to a primary key in another table, keeping the relationship between tables valid.

Primary Key: It must be unique and can never be empty.

No two rows can have the same value.

A table has only one primary key.

It identifies each row, like an ID card identifies a person.

Foreign Key: It’s a column in one table that references the primary key of another table.

It makes sure every reference points to a row that really exists.

So you can’t create an order for a customer who isn’t there.

Example — two linked tables:

Departments table:

department_id	department_name
10	Sales
20	IT

Employees table:

employee_id	name	department_id
1	Ali	10
2	Bob	20

Here departments.department_id is the primary key of the Departments table.

employees.department_id is a foreign key pointing to it.

CREATE TABLE departments (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(100)
);

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,
  name VARCHAR(100),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

What the foreign key prevents: You cannot insert an employee with department_id = 99 because no such department exists.

The database rejects the insert and keeps the relationship consistent.

Key differences table:

	Primary Key	Foreign Key
Purpose	Uniquely identifies a row	Links to another table
Unique	Yes	No
Can be NULL	No	Yes
Per table	One	Many

Key takeaway: Primary keys make each row addressable. Foreign keys make the relationships between tables trustworthy.