Top 50 Placement Questions - Part 1

1. What is a Primary Key, and how does it differ from a Unique Key?

Answer: A Primary Key uniquely identifies each row and can never be NULL. A Unique Key also ensures uniqueness but allows one NULL value.

Primary Key: It’s the main identifier of a row, like an ID card.

It must be unique.

It can never be empty.

A table has only one primary key.

Unique Key: It also makes sure values don’t repeat.

But it allows one NULL.

A table can have many unique keys.

Example:

CREATE TABLE employees (
  employee_id INT PRIMARY KEY,          -- primary key
  email VARCHAR(255) UNIQUE,            -- unique key
  phone VARCHAR(20) UNIQUE              -- another unique key
);

Here employee_id can never be NULL and never repeats.

email can’t repeat, but one employee could have no email (NULL).

Key differences table:

	Primary Key	Unique Key
Purpose	Main row identifier	Prevent duplicate values
Can be NULL	No	Yes (one NULL)
Per table	One	Many
Automatically indexed	Yes	Yes

Key takeaway: Both prevent duplicates, but the primary key is special — it’s the table’s main identifier, never NULL, and only one per table.

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

Answer: WHERE filters rows before grouping. HAVING filters groups after aggregation.

The order of work: A query processes data in steps: read rows → filter → group → aggregate.

WHERE works at the filter step, on individual rows.

HAVING works after grouping, on the groups.

That’s why you can’t put COUNT() inside WHERE — the count doesn’t exist yet.

Example — departments with more than 10 employees:

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

• WHERE keeps only recent hires (rows).
• GROUP BY forms groups.
• HAVING keeps groups with more than 10.

Key differences table:

	WHERE	HAVING
When it runs	Before grouping	After grouping
Works on	Rows	Groups
Can use aggregates	No	Yes

Key takeaway: Rows go through WHERE first; groups go through HAVING after. Aggregates belong in HAVING.

3. What are the different types of JOINs?

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

The sample data:

Employees:

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

Departments:

department_id	department_name
10	Sales
20	IT
30	HR

INNER JOIN — only rows that match in both tables. Cam is 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

LEFT JOIN — all rows from the left table, plus matches. Cam stays, with no department.

RIGHT JOIN — all rows from the right table, plus matches. HR stays, with no employee.

FULL JOIN — all rows from both tables.

CROSS JOIN — every row of one table paired with every row of the other.

Key differences table:

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

Key takeaway: Choose the join by which side must keep all its rows. INNER for matches only, LEFT/RIGHT to preserve one side, FULL to preserve both.

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

Answer: DELETE removes specific rows, TRUNCATE removes all rows but keeps the table, and DROP removes the entire table.

DELETE: Removes rows one by one.

Supports WHERE to pick specific rows.

Can be rolled back.

DELETE FROM employees WHERE department_id = 10;

TRUNCATE: Removes all rows instantly.

Keeps the table structure.

Cannot use WHERE.

Rarely rollback-able.

TRUNCATE TABLE employees;

DROP: Removes the whole table — structure and data.

The table is gone.

DROP TABLE employees;

Key differences table:

	DELETE	TRUNCATE	DROP
Removes	Selected rows	All rows	Whole table
Keeps structure	Yes	Yes	No
Supports WHERE	Yes	No	No
Can roll back	Yes	Hardly	No
Type	DML	DDL	DDL

Key takeaway: DELETE for selected rows, TRUNCATE to clear a table fast, DROP to remove the table itself.

5. What is Database Normalization?

Answer: Normalization organizes data to reduce redundancy and improve data integrity. It splits large tables into smaller, related ones.

The problem it solves: Repeating the same data everywhere causes errors and wasted space.

Example — bad design:

order_id	customer_name	product
1	Ali	Pen
2	Ali	Book

The name is repeated. Change it once and you must change both rows.

Normalized design: Customers table:

customer_id	name
1	Ali

Orders table:

order_id	customer_id	product
1	1	Pen
2	1	Book

The name is stored once, referenced by ID.

The normal forms:

• 1NF — single values per column, unique rows.
• 2NF — 1NF plus every column depends on the whole key.
• 3NF — 2NF plus no column depends on another non-key column.

Key takeaway: Normalization removes duplication and keeps data consistent. The cost is more tables and more joins.

6. What is a Foreign Key?

Answer: A foreign key is a column that references the primary key of another table. It links two tables and keeps the relationship valid.

The idea: One table’s column “points” to another table’s primary key.

This makes sure every reference exists.

Example: Departments:

department_id	department_name
10	Sales
20	IT

Employees:

employee_id	name	department_id
1	Ali	10
2	Bob	20

employees.department_id is a foreign key pointing to departments.department_id.

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

What it prevents: You can’t insert an employee with department_id = 99 if no such department exists.

The database rejects it, keeping data consistent.

Key takeaway: A foreign key is a reference from one table to another’s primary key. It stops orphan rows — data that points to nothing.

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

Answer: UNION removes duplicate rows. UNION ALL keeps every row, including duplicates.

Example:

Employees_2024: {Ali, Bob}

Employees_2025: {Bob, Cam}

UNION:

SELECT name FROM employees_2024
UNION
SELECT name FROM employees_2025;

Result: {Ali, Bob, Cam} — Bob appears once.

UNION ALL:

SELECT name FROM employees_2024
UNION ALL
SELECT name FROM employees_2025;

Result: {Ali, Bob, Bob, Cam} — Bob appears twice.

Key differences table:

	UNION	UNION ALL
Removes duplicates	Yes	No
Extra sort step	Yes	No
Speed	Slower	Faster

Key takeaway: Use UNION ALL when duplicates are fine — it’s faster. Use UNION only when you need distinct rows.

8. What are Aggregate Functions in SQL?

Answer: Aggregate functions take many values and return a single result. Common ones are SUM, AVG, COUNT, MIN, and MAX.

The functions:

Function	What it does	Example
SUM()	Adds values	Total salary
AVG()	Average	Average salary
COUNT()	Number of rows	Total employees
MIN()	Smallest value	Lowest salary
MAX()	Largest value	Highest salary

Example:

SELECT AVG(salary) AS average_salary,
       MAX(salary) AS highest_salary
FROM employees;

If salaries are 50000, 60000, 70000:

• average = 60000
• highest = 70000

With GROUP BY: Aggregates are often used per group.

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;

Key takeaway: Aggregate functions summarize many rows into one value. Pair them with GROUP BY to summarize per group.

9. What is a View in SQL?

Answer: A view is a virtual table based on the result of a SELECT statement. It stores no data itself.

The idea: A view is a saved query you can treat like a table.

It doesn’t hold data.

Every time you query it, it runs the underlying SELECT.

Example:

CREATE VIEW it_employees AS
SELECT employee_id, name
FROM employees
WHERE department_id = 5;

Now you can query it like a table:

SELECT * FROM it_employees;

Why use views:

• Simplify complex queries — write the join once, reuse it.
• Restrict access — show only some columns or rows.
• Hide complexity from other users.

Key takeaway: A view is a named, saved query that acts like a table. It always shows fresh data because it runs the query each time.

10. What is an Index and why is it used?

Answer: An index is a data structure that speeds up data retrieval. It lets the database find rows without scanning the whole table.

The analogy: A book’s index at the back tells you “topic X is on page 42”.

Without it, you read every page.

An SQL index works the same way.

Example:

CREATE INDEX idx_emp_dept ON employees(department_id);

Now this query is fast:

SELECT * FROM employees WHERE department_id = 5;

The database jumps straight to department 5’s rows instead of scanning everything.

The trade-offs:

• Extra storage.
• Slower writes (index must be updated).

Key takeaway: Indexes make reads fast but cost storage and slow writes. Index columns you query often — not every column.

11. What are ACID properties?

Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability. They guarantee transactions are reliable.

Atomicity — all or nothing: A transaction’s steps all succeed, or none do.

Consistency — always valid: The database stays valid, following all rules and constraints.

Isolation — no interference: Concurrent transactions don’t see each other’s unfinished work.

Durability — permanent: Committed data survives crashes.

Example — money transfer: Debit A, credit B.

Atomicity: if the credit fails, the debit rolls back.

Durability: once committed, the money move is saved forever.

Key takeaway: ACID is the guarantee that makes databases safe for money, orders, and anything where data loss is unacceptable.

12. What is a NULL value?

Answer: NULL means the absence of a value. It is not zero and not an empty string.

The confusion: Zero is a number.

An empty string '' is a value that happens to be empty.

NULL means “no value / unknown”.

Example:

employee_id	name	phone
1	Ali	12345
2	Bob	(NULL)

Bob’s phone is NULL — the number is unknown, not zero.

How to check for NULL: You can’t use = NULL. That never matches.

SELECT * FROM employees WHERE phone IS NULL;

Key takeaway: NULL means unknown or missing, distinct from 0 and ''. Always test it with IS NULL, not = NULL.

13. What is a Subquery?

Answer: A subquery is a query nested inside another query. It’s also called an inner query.

The idea: A query inside parentheses, used by the outer query.

Example — employees in the IT department:

SELECT name
FROM employees
WHERE department_id = (
  SELECT department_id FROM departments WHERE department_name = 'IT'
);

The inner query finds IT’s ID: 20.

The outer query then finds all employees in department 20.

Where subqueries can be used:

• In SELECT (as a value).
• In WHERE (for comparison).
• In FROM (as a derived table).
• In INSERT, UPDATE, DELETE.

Key takeaway: A subquery is a query inside another query. It’s often used to get a value to compare against, like finding an ID first.

14. What is the difference between clustered and non-clustered indexes?

Answer: A clustered index determines the physical order of the data rows. A non-clustered index is a separate structure pointing to the data.

Clustered index: Rearranges the actual table data in sorted order.

Like a dictionary sorted alphabetically.

Only one per table.

Usually the primary key.

Non-clustered index: A separate list pointing to the data.

Like a textbook’s index at the back.

Many per table.

Key differences table:

	Clustered	Non-clustered
Sorts data	Yes	No
Separate structure	No	Yes
Per table	One	Many
Lookup speed	Fastest	Slightly slower

Key takeaway: One clustered index rearranges the table itself. Many non-clustered indexes just point at it.

15. What is the logical order of execution for a SELECT query?

Answer: SQL doesn’t run a query top-to-bottom. It follows a fixed order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.

The order:

1. FROM — pick the tables.
2. WHERE — filter rows.
3. GROUP BY — group rows.
4. HAVING — filter groups.
5. SELECT — pick the columns.
6. ORDER BY — sort.
7. LIMIT — cut the row count.

Why this matters: You can’t use an alias from SELECT in WHERE, because WHERE runs first.

SELECT name, salary * 1.1 AS bonus
FROM employees
WHERE bonus > 50000;   -- ERROR: bonus doesn't exist yet

Aliases created in SELECT can be used in ORDER BY but not WHERE.

Key takeaway: Know the order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. It explains which parts of a query can reference what.