SQL INTERVIEW

Complete Guide for Deloitte NLA

Technical Interview Preparation

Questions • Interview Answers • Explanations

Prepared for: Sunanda Manne | Deloitte US-India Offices

60+ Questions 6 Topic Areas Interview-Ready

How to use this guide: For every question, first cover the answer and try to recall it yourself.
Then read the INTERVIEW ANSWER (the exact phrasing you should use). Then read the
EXPLANATION to understand WHY the answer is correct. The Interview Tips give you bonus
points in your actual interview.
 TABLE OF CONTENTS

Chapter
1 Fundamentals — Database, DBMS, RDBMS, SQL Qs 1–8

Chapter
2 SQL Commands — DDL, DML, DCL, TCL Qs 9–16

Chapter Qs
3 Constraints, Keys & Relationships 17–24

Chapter Qs
4 Queries, Clauses & Functions 25–36

Chapter Qs
5 Joins, Subqueries & Views 37–48

Chapter Qs
6 Normalization & ACID Properties 49–60
 CHAPTER 1 — Fundamentals: Database, DBMS,
RDBMS, SQL

Q1
What is a Database?
INTERVIEW ANSWER — SAY THIS

A database is an organized collection of structured data stored electronically in a computer

system. It allows data to be easily accessed, managed, modified, updated, and organized.

EXPLANATION — UNDERSTAND THIS

Think of a database like a digital filing cabinet. Instead of paper files, data is stored in tables. A
hospital database, for example, stores patient records, doctor info, and appointment history — all
organized so the system can retrieve any record in milliseconds.

■ INTERVIEW TIP: Always give a real-world example after your definition. It shows you understand the
concept practically.

Q2
What is DBMS? What are its types?
INTERVIEW ANSWER — SAY THIS

DBMS stands for Database Management System. It is software that acts as an interface between
the user and the database, allowing users to create, read, update, and delete data. Types of
DBMS include: Hierarchical DBMS, Network DBMS, Relational DBMS (RDBMS), and
Object-Oriented DBMS.

EXPLANATION — UNDERSTAND THIS

DBMS handles everything from storing data on disk to providing security, backup, and concurrent
access to multiple users. Examples of DBMS: MySQL, PostgreSQL, Oracle, MongoDB. Without a
DBMS, you would need to manually manage files on disk with no querying capability.

■ INTERVIEW TIP: The most commonly asked follow-up is: 'Give an example of a DBMS.' Always say
MySQL or Oracle.

Q3
What is the difference between DBMS and RDBMS?
INTERVIEW ANSWER — SAY THIS
DBMS stores data as files without enforcing relationships between them, and does not follow
ACID properties. RDBMS stores data in structured tables with defined relationships using primary
and foreign keys, and follows ACID properties. Every RDBMS is a DBMS, but not every DBMS is
an RDBMS. Examples of RDBMS: MySQL, PostgreSQL, Oracle.

EXPLANATION — UNDERSTAND THIS

The key word in RDBMS is 'Relational' — data is stored in related tables. A school RDBMS might
have a Students table and a Courses table linked by a StudentID foreign key. DBMS (like early
file systems or XML stores) had no such formal relationship structure.

■ INTERVIEW TIP: This is one of the top 3 most asked SQL questions. Memorise: RDBMS = tables +
relationships + ACID.

Q4
What is SQL? What is the difference between SQL and MySQL?
INTERVIEW ANSWER — SAY THIS

SQL stands for Structured Query Language. It is a standard language used to communicate with
and manipulate relational databases. MySQL, on the other hand, is a Relational Database
Management System — it is software that uses SQL as its query language. SQL is the language;
MySQL is the tool.

EXPLANATION — UNDERSTAND THIS

Think of it this way: SQL is like the English language, and MySQL is like a person who speaks
English. Other RDBMS systems that also use SQL include PostgreSQL, Oracle, SQL Server, and
SQLite. You write SQL queries inside MySQL to interact with the database.

■ INTERVIEW TIP: A very common beginner trap — many people think SQL and MySQL are the same.
Making this distinction clearly will impress the interviewer.

Q5
What are the sub-languages of SQL? (DDL, DML, DCL, TCL)
INTERVIEW ANSWER — SAY THIS

SQL is divided into four sub-languages: DDL (Data Definition Language) for defining structure —
CREATE, ALTER, DROP, TRUNCATE. DML (Data Manipulation Language) for manipulating
data — SELECT, INSERT, UPDATE, DELETE. DCL (Data Control Language) for access control
— GRANT, REVOKE. TCL (Transaction Control Language) for managing transactions —
COMMIT, ROLLBACK, SAVEPOINT.

EXPLANATION — UNDERSTAND THIS

This classification helps understand what each SQL command does at a high level. DDL changes
the schema (structure). DML changes the data. DCL controls who can access what. TCL
manages whether changes are saved or reversed. Interview shortcut: DDL = structure, DML =
data, DCL = permissions, TCL = transactions.

■ INTERVIEW TIP: Draw a quick mental table: 4 categories, 3-4 commands each. If asked to give
examples for each — always be ready.

Q6
What are Tables and Fields in SQL?
INTERVIEW ANSWER — SAY THIS

A table is a collection of related data organized in rows and columns, similar to a spreadsheet.
Fields are the column names in the table that define what type of data is stored. Each row in a
table is called a record or tuple.

EXPLANATION — UNDERSTAND THIS

Example: An 'employees' table might have fields like: emp_id, name, department, salary, age.
Each row in this table represents one employee. The value in each cell is the data for that field for
that record.

■ INTERVIEW TIP: This is a basic question but don't rush it. Use a real-world example to show clarity.

Q7
What is the difference between a Schema and a Database?
INTERVIEW ANSWER — SAY THIS

A database is the overall container that holds all the data. A schema is the logical structure or
blueprint of the database — it defines the tables, fields, data types, relationships, and constraints.
In MySQL, schema and database are often used interchangeably. In Oracle and PostgreSQL, a
schema is a namespace within a database.

EXPLANATION — UNDERSTAND THIS

Analogy: A database is like a building. The schema is the architectural blueprint of that building —
it tells you how many rooms there are, what each room is for, and how they are connected.

■ INTERVIEW TIP: This is a nuance question — just knowing it puts you ahead of most candidates.

Q8
What is Data Integrity? What are its types?
INTERVIEW ANSWER — SAY THIS

Data integrity refers to the accuracy, consistency, and reliability of data over its entire lifecycle.
Types: Entity integrity ensures each row is uniquely identified (Primary Key). Referential integrity
ensures foreign key values match primary keys in the referenced table. Domain integrity ensures
data values fall within defined acceptable ranges. User-defined integrity enforces
business-specific rules.

EXPLANATION — UNDERSTAND THIS

Data integrity is enforced through constraints in SQL. For example, a NOT NULL constraint
enforces domain integrity. A FOREIGN KEY enforces referential integrity. Without integrity, a
database could have orphan records — for example, an order record pointing to a customer ID
that no longer exists.

■ INTERVIEW TIP: This shows you understand database design — not just querying. Great way to stand
out.
 CHAPTER 2 — SQL Commands: DDL, DML, DCL, TCL

Q9
What is the difference between DELETE, TRUNCATE, and DROP?
INTERVIEW ANSWER — SAY THIS

DELETE is a DML command that removes specific rows based on a WHERE condition. It can be
rolled back. TRUNCATE is a DDL command that removes all rows from a table at once. It cannot
be rolled back and is faster than DELETE. DROP is a DDL command that removes the entire
table — both data and structure — permanently.

EXPLANATION — UNDERSTAND THIS

Memory trick: DELETE = selective removal (surgeon), TRUNCATE = wipe everything (eraser),
DROP = destroy the table (demolition). DELETE fires triggers and writes to the transaction log
row by row, making it slower. TRUNCATE deallocates data pages directly, making it much faster
for large tables.

■ INTERVIEW TIP: This is asked in almost every SQL interview. Know the rollback difference by heart:
DELETE can rollback, TRUNCATE cannot.

Q10
What is the difference between DROP and TRUNCATE?
INTERVIEW ANSWER — SAY THIS

TRUNCATE removes all rows from the table but preserves the table structure, constraints, and
indexes. DROP removes everything — the data, the table structure, all constraints, indexes, and
relationships. After TRUNCATE, the table can still be used. After DROP, the table no longer
exists.

EXPLANATION — UNDERSTAND THIS

Practical example: If you want to empty a temporary log table to start fresh, use TRUNCATE. If
you want to permanently get rid of a table that's no longer needed in your application, use DROP.

■ INTERVIEW TIP: Common follow-up: 'Can you use WHERE with TRUNCATE?' Answer: No.
TRUNCATE removes all rows unconditionally.

Q11
What is the difference between DELETE and TRUNCATE?
INTERVIEW ANSWER — SAY THIS
DELETE removes rows one by one based on a WHERE condition, logs each deletion, fires
triggers, and can be rolled back using ROLLBACK. TRUNCATE removes all rows at once, does
not fire row-level triggers, cannot use WHERE clause, and generally cannot be rolled back.

EXPLANATION — UNDERSTAND THIS

Both DELETE (without WHERE) and TRUNCATE appear to do the same thing — remove all
rows. But DELETE logs each row deletion, which takes time and space. TRUNCATE simply
marks the data pages as empty, making it significantly faster for large tables.

■ INTERVIEW TIP: Remember: DELETE = slow, logged, reversible. TRUNCATE = fast, not logged per
row, not reversible.

Q12
What are the CREATE, ALTER, and DROP commands?
INTERVIEW ANSWER — SAY THIS

CREATE is used to create database objects like tables, views, indexes, and stored procedures.
ALTER is used to modify the structure of an existing table — add, drop, or modify columns.
DROP permanently removes the object from the database.

EXPLANATION — UNDERSTAND THIS

Example: CREATE TABLE students (id INT, name VARCHAR(50)). ALTER TABLE students
ADD COLUMN age INT — adds a new column. DROP TABLE students — permanently deletes
the entire table.

■ INTERVIEW TIP: These are the three foundational DDL commands. In your Bluestock internship, you
likely used ALTER when modifying ETL schemas.

Q13
What are COMMIT, ROLLBACK, and SAVEPOINT?
INTERVIEW ANSWER — SAY THIS

COMMIT permanently saves all changes made during the current transaction to the database.
ROLLBACK undoes all changes made since the last COMMIT, reverting the database to its
previous state. SAVEPOINT sets a checkpoint within a transaction so you can ROLLBACK to that
specific point without undoing the entire transaction.

EXPLANATION — UNDERSTAND THIS

Real-world analogy: Think of it like editing a document. SAVEPOINT is Ctrl+S at a point in time.
ROLLBACK goes back to the last save. COMMIT publishes the final document. Banking
example: A fund transfer debits Account A and credits Account B. If the credit fails, ROLLBACK
undoes the debit too — ensuring atomicity.

■ INTERVIEW TIP: COMMIT and ROLLBACK are always connected to ACID properties (Atomicity). Be
ready to link them.

Q14
What is AutoCommit in SQL?
INTERVIEW ANSWER — SAY THIS

AutoCommit is a mode in which every SQL statement is automatically committed immediately

after execution. By default, MySQL operates in AutoCommit mode. When AutoCommit is
disabled, changes are only saved when you explicitly execute a COMMIT command, and can be
undone using ROLLBACK.

EXPLANATION — UNDERSTAND THIS

When AutoCommit is ON: every INSERT, UPDATE, DELETE is instantly permanent. When
AutoCommit is OFF: you control when changes are finalized. This is important for batch
operations where multiple steps must either all succeed or all fail together.

■ INTERVIEW TIP: Connect this to transactions — when you need transactional control, you turn
AutoCommit off.

Q15
What are GRANT and REVOKE commands?
INTERVIEW ANSWER — SAY THIS

GRANT is a DCL command used to give specific privileges to users or roles on database objects.
REVOKE is a DCL command used to remove those previously granted privileges. Example:
GRANT SELECT ON employees TO user1 gives user1 the ability to read the employees table.

EXPLANATION — UNDERSTAND THIS

DCL commands control database security. In a real production environment, not everyone should
have DELETE or DROP access. A junior analyst might only have SELECT access. An admin has
all privileges. GRANT and REVOKE manage this access control matrix.

■ INTERVIEW TIP: DCL is less commonly asked in interviews but knowing it shows you understand
real-world database security.
Q16
What is the difference between CHAR and VARCHAR?
INTERVIEW ANSWER — SAY THIS

CHAR is a fixed-length data type. If you define CHAR(10) and store 'Hello', it uses 10 bytes —
padding with spaces. VARCHAR is a variable-length data type. If you define VARCHAR(10) and
store 'Hello', it uses only 5 bytes plus 1-2 bytes overhead. Use CHAR for data that is always the
same length (like country codes). Use VARCHAR for variable-length data (like names).

EXPLANATION — UNDERSTAND THIS

CHAR wastes space when the stored value is shorter than the defined length, but is slightly faster
because the database always knows exactly how many bytes to read. VARCHAR is more
space-efficient but slightly slower. For Deloitte interviews, the most important thing to say is: fixed
vs variable length.

■ INTERVIEW TIP: A bonus point: CHAR pads with spaces, so 'Hello' stored in CHAR(10) is actually 'Hello
' internally.
 CHAPTER 3 — Constraints, Keys & Relationships

Q17
What are Constraints in SQL? List all types.
INTERVIEW ANSWER — SAY THIS

Constraints are rules applied to columns to enforce data accuracy and integrity. Types: NOT
NULL — column cannot be empty. UNIQUE — all values must be distinct. PRIMARY KEY —
uniquely identifies each row, cannot be NULL. FOREIGN KEY — enforces referential integrity
between two tables. CHECK — values must satisfy a condition. DEFAULT — assigns a default
value when none is provided.

EXPLANATION — UNDERSTAND THIS

Constraints are the guardians of data quality. Without constraints, someone could insert a
negative salary, a duplicate email, or an order referencing a customer that doesn't exist.
Constraints prevent all these problems at the database level, regardless of the application.

■ INTERVIEW TIP: When asked, list all 6 constraint types with one sentence each. That's a complete
answer.

Q18
What is a Primary Key? What are its properties?
INTERVIEW ANSWER — SAY THIS

A Primary Key is a column or set of columns that uniquely identifies each row in a table.
Properties: It must be UNIQUE — no two rows can have the same primary key value. It cannot be
NULL. There can be only ONE primary key per table. It automatically creates a clustered index on
the column.

EXPLANATION — UNDERSTAND THIS

Primary key is like an Aadhar number for a table row — no two rows can have the same value,
and every row must have one. Common choices: auto-increment integer IDs (emp_id,
student_id). A primary key can be composite — using two or more columns together as the
unique identifier.

■ INTERVIEW TIP: Know the difference between primary key and unique key (covered next). Both enforce
uniqueness but differ on NULL.

Q19
What is the difference between Primary Key and Unique Key?
INTERVIEW ANSWER — SAY THIS

Primary Key: cannot be NULL, only one per table, creates a clustered index by default. Unique
Key: can have one NULL value, multiple unique keys allowed per table, creates a non-clustered
index. Both enforce uniqueness of values in the column.

EXPLANATION — UNDERSTAND THIS

Example: In a Students table, student_id is the Primary Key. email could be a Unique Key
because it must be unique but could occasionally be NULL (if not provided yet). A table can have
only 1 primary key but can have 5, 10, or more unique keys.

■ INTERVIEW TIP: Classic interview trap: 'Can a Unique Key be NULL?' Answer: Yes, exactly one NULL
is allowed per column with UNIQUE constraint.

Q20
What is a Foreign Key?
INTERVIEW ANSWER — SAY THIS

A Foreign Key is a column in one table that references the Primary Key of another table. It
enforces referential integrity — meaning the value in the foreign key column must either match an
existing value in the referenced table or be NULL. The table with the foreign key is the child table;
the referenced table is the parent table.

EXPLANATION — UNDERSTAND THIS

Example: An 'orders' table has a customer_id column that references the 'customers' table's id
column. You cannot insert an order with a customer_id that doesn't exist in the customers table
— the foreign key constraint prevents orphan records. ON DELETE CASCADE means if a parent
record is deleted, all related child records are automatically deleted too.

■ INTERVIEW TIP: Always mention ON DELETE CASCADE as a bonus — it shows you know the practical
side of foreign keys.

Q21
What is a Composite Key?
INTERVIEW ANSWER — SAY THIS

A Composite Key is a Primary Key that consists of two or more columns together. No single
column is unique on its own, but the combination of columns is unique across all rows.

EXPLANATION — UNDERSTAND THIS

Example: In an 'enrollments' table tracking which student is enrolled in which course, neither
student_id alone nor course_id alone is unique. But the combination (student_id, course_id) is
unique — one student can only be enrolled in a specific course once.

■ INTERVIEW TIP: Composite keys are common in many-to-many relationship junction tables.

Q22
What is a Candidate Key and Super Key?
INTERVIEW ANSWER — SAY THIS

A Candidate Key is any column (or set of columns) that can uniquely identify every row in a table.
From all candidate keys, one is chosen as the Primary Key. The rest are called Alternate Keys. A
Super Key is any combination of columns that uniquely identifies a row — a super key may
include extra unnecessary columns, while a candidate key is the minimal form.

EXPLANATION — UNDERSTAND THIS

Example: In a students table, both student_id and email can uniquely identify a student — both
are candidate keys. We choose student_id as the primary key. Email becomes the alternate key.
A super key could be (student_id, name) — it uniquely identifies rows, but name is redundant.

■ INTERVIEW TIP: Super Key and Candidate Key are theory questions that show database design depth.
Know the hierarchy: Super Key > Candidate Key > Primary Key.

Q23
What are the types of Relationships in SQL?
INTERVIEW ANSWER — SAY THIS

One-to-One: Each record in Table A relates to exactly one record in Table B. Example: Each
person has one passport. One-to-Many: One record in Table A relates to many records in Table
B. Example: One customer can have many orders. Many-to-Many: Many records in Table A
relate to many records in Table B. Example: Students and Courses — a student takes many
courses, a course has many students. Implemented via a junction table.

EXPLANATION — UNDERSTAND THIS

Many-to-many relationships cannot be directly implemented in RDBMS without a junction/bridge

table. Example: 'enrollments' table with (student_id, course_id) is the junction table between
Students and Courses.

■ INTERVIEW TIP: Drawing these relationships mentally helps. One-to-Many is the most common
relationship in real databases.
Q24
What is Referential Integrity?
INTERVIEW ANSWER — SAY THIS

Referential integrity is a constraint that ensures the relationship between tables remains
consistent. It means a foreign key value in the child table must always match a valid primary key
value in the parent table, or be NULL. It prevents orphan records.

EXPLANATION — UNDERSTAND THIS

Without referential integrity, you could have an order in the 'orders' table pointing to customer_id
= 999, but no customer with id 999 exists in the 'customers' table. This is an orphan record — it
makes the data meaningless and causes errors in applications.

■ INTERVIEW TIP: Foreign keys enforce referential integrity automatically at the database level.
 CHAPTER 4 — Queries, Clauses & Functions

Q25
What is the order of execution of a SQL query?
INTERVIEW ANSWER — SAY THIS

SQL does not execute in the order you write it. The actual execution order is: 1. FROM — identify
tables. 2. JOIN — combine tables. 3. WHERE — filter rows. 4. GROUP BY — group rows. 5.
HAVING — filter groups. 6. SELECT — choose columns. 7. DISTINCT — remove duplicates. 8.
ORDER BY — sort. 9. LIMIT — restrict rows.

EXPLANATION — UNDERSTAND THIS

This is why you cannot use a column alias defined in SELECT inside a WHERE clause —
WHERE executes before SELECT, so the alias doesn't exist yet when WHERE runs. But you
CAN use aliases in ORDER BY because ORDER BY runs after SELECT.

■ INTERVIEW TIP: This is a tricky question that separates average candidates from good ones. Know this
order perfectly.

Q26
What is the difference between WHERE and HAVING?
INTERVIEW ANSWER — SAY THIS

WHERE filters individual rows BEFORE they are grouped, and cannot use aggregate functions
like COUNT() or SUM(). HAVING filters groups AFTER GROUP BY has been applied, and CAN
use aggregate functions. Rule: WHERE works on rows, HAVING works on groups.

EXPLANATION — UNDERSTAND THIS

Example: WHERE salary > 50000 filters individual employees. HAVING COUNT(*) > 3 filters only
departments that have more than 3 employees. You cannot write WHERE COUNT(*) > 3 — this
will throw an error because WHERE doesn't work with aggregates.

■ INTERVIEW TIP: One of the top 5 most asked SQL clauses questions. Always give an example of what
CANNOT be done with WHERE.

Q27
What is GROUP BY? How does it work?
INTERVIEW ANSWER — SAY THIS
GROUP BY is used to group rows that have the same values in specified columns. It is typically
used with aggregate functions to compute summary values for each group. Every column in the
SELECT statement must either be in the GROUP BY clause or be an aggregate function.

EXPLANATION — UNDERSTAND THIS

Example: SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id — this returns
the average salary for each department. If you GROUP BY dept_id, you cannot SELECT name
(non-aggregated) without adding it to GROUP BY.

■ INTERVIEW TIP: Common interview trap: 'Why does this query fail?' — usually because a
non-aggregated column is in SELECT without being in GROUP BY.

Q28
What are Aggregate Functions? List them.
INTERVIEW ANSWER — SAY THIS

Aggregate functions perform a calculation on a set of values and return a single result. COUNT()
— counts rows. SUM() — total of a column. AVG() — average value. MIN() — minimum value.
MAX() — maximum value. Important: All aggregate functions ignore NULL values, EXCEPT
COUNT(*) which counts all rows including NULLs.

EXPLANATION — UNDERSTAND THIS

Example: SELECT COUNT(*) FROM employees returns total rows including NULLs. SELECT
COUNT(email) FROM employees returns only rows where email is NOT NULL. This distinction is
often tested in interviews.

■ INTERVIEW TIP: COUNT(*) vs COUNT(column) — knowing this difference is a bonus point in

interviews.

Q29
What are Scalar Functions? Give examples.
INTERVIEW ANSWER — SAY THIS

Scalar functions operate on a single value and return a single value. String functions: UPPER(),
LOWER(), LENGTH(), CONCAT(), TRIM(), SUBSTRING(). Math functions: ROUND(), FLOOR(),
CEIL(), MOD(). Date functions: NOW(), CURDATE(), YEAR(), MONTH(), DATEDIFF(),
TIMESTAMPDIFF(). Conditional: IF(), CASE WHEN.

EXPLANATION — UNDERSTAND THIS

Example: SELECT UPPER(name) FROM employees returns all names in uppercase. SELECT
DATEDIFF(CURDATE(), date_of_birth) FROM employees returns how many days old each
person is. Scalar functions transform values at the row level, unlike aggregate functions which
summarize groups.

■ INTERVIEW TIP: Scalar vs Aggregate is a classic interview distinction. Know at least 3 examples of
each.

Q30
What is the difference between UNION and UNION ALL?
INTERVIEW ANSWER — SAY THIS

UNION combines the results of two SELECT queries and removes duplicate rows. It is slower.
UNION ALL combines results and keeps ALL rows including duplicates. It is faster. Both require
the same number of columns with compatible data types in both queries.

EXPLANATION — UNDERSTAND THIS

When to use: If you know there are no duplicates, use UNION ALL — it's faster. Use UNION only
when you need duplicates removed. Example: SELECT name FROM students UNION SELECT
name FROM alumni — gives unique names across both tables.

■ INTERVIEW TIP: Also know: INTERSECT returns common rows between two queries. MINUS/EXCEPT
returns rows in first query not in second.

Q31
What is ORDER BY? What is the default sort order?
INTERVIEW ANSWER — SAY THIS

ORDER BY sorts the result set by one or more columns. ASC sorts in ascending order (A to Z, 1
to 9) — this is the DEFAULT if not specified. DESC sorts in descending order (Z to A, 9 to 1). You
can sort by multiple columns: ORDER BY dept_id ASC, salary DESC.

EXPLANATION — UNDERSTAND THIS

Default order is ASC. If you don't specify ASC or DESC, the result is sorted ascending. You can
also ORDER BY column position: ORDER BY 2 means sort by the second column in the
SELECT list.

■ INTERVIEW TIP: ORDER BY runs last in the execution order — after SELECT — so you CAN use
aliases here.

Q32
What is LIMIT and OFFSET?
INTERVIEW ANSWER — SAY THIS

LIMIT restricts the number of rows returned by a query. OFFSET skips a specified number of
rows before starting to return results. Example: LIMIT 10 OFFSET 20 skips the first 20 rows and
returns the next 10.

EXPLANATION — UNDERSTAND THIS

LIMIT is essential for pagination in web applications. Page 1 = LIMIT 10 OFFSET 0. Page 2 =
LIMIT 10 OFFSET 10. Page 3 = LIMIT 10 OFFSET 20. Getting the top 5 highest paid employees:
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 5.

■ INTERVIEW TIP: LIMIT with OFFSET is the foundation of pagination — mention this real-world use
case.

Q33
What is DISTINCT?
INTERVIEW ANSWER — SAY THIS

DISTINCT is used in a SELECT statement to return only unique (non-duplicate) values from a
column or combination of columns.

EXPLANATION — UNDERSTAND THIS

Example: SELECT DISTINCT dept_id FROM employees — returns each department ID only
once, even if 50 employees share the same dept. SELECT COUNT(DISTINCT dept_id) FROM
employees — counts how many unique departments exist.

■ INTERVIEW TIP: DISTINCT operates after SELECT but before ORDER BY in the execution order.

Q34
What is LIKE operator and Wildcards?
INTERVIEW ANSWER — SAY THIS

LIKE is used for pattern matching in string values. % wildcard matches zero or more characters. _
wildcard matches exactly one character. Example: WHERE name LIKE 'S%' — names starting
with S. WHERE name LIKE '%kumar' — names ending with kumar. WHERE name LIKE '_a%' —
second character is 'a'.

EXPLANATION — UNDERSTAND THIS

LIKE is case-insensitive by default in MySQL. The % symbol is the most used wildcard. You can
combine them: WHERE name LIKE 'S__na' matches 'Sunanda' (S + 2 chars + na).
■ INTERVIEW TIP: LIKE with % at both ends ('%text%') is a full-text search — it's slow on large tables
because it can't use indexes.

Q35
What is the CASE WHEN statement?
INTERVIEW ANSWER — SAY THIS

CASE WHEN is SQL's conditional expression, similar to if-else in programming. Syntax: CASE
WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END. It
returns different values based on conditions and can be used in SELECT, WHERE, ORDER BY.

EXPLANATION — UNDERSTAND THIS

Example: SELECT name, salary, CASE WHEN salary > 70000 THEN 'High' WHEN salary >
50000 THEN 'Medium' ELSE 'Low' END AS salary_band FROM employees. This creates a
computed column categorizing employees by salary band without changing the actual data.

■ INTERVIEW TIP: CASE WHEN is very powerful and shows SQL maturity. Mentioning it in an interview is
a strong signal.

Q36
What is a NULL value? How to handle it in SQL?
INTERVIEW ANSWER — SAY THIS

NULL represents an unknown or missing value — it is NOT the same as zero or an empty string.
To check for NULL, use IS NULL or IS NOT NULL — you cannot use = NULL.
COALESCE(column, default_value) returns the first non-NULL value. IFNULL(column,
replacement) replaces NULL with a specified value.

EXPLANATION — UNDERSTAND THIS

NULL is special because NULL = NULL evaluates to UNKNOWN, not TRUE. Example: WHERE
email = NULL will return 0 rows even if NULLs exist — you must use WHERE email IS NULL.
COALESCE(salary, 0) would replace NULL salaries with 0 in output.

■ INTERVIEW TIP: NULL handling is tested in every SQL interview. Always say: use IS NULL, not =
NULL.
 CHAPTER 5 — Joins, Subqueries, Views & Indexes

Q37
What is a JOIN? List all types.
INTERVIEW ANSWER — SAY THIS

A JOIN combines rows from two or more tables based on a related column. Types: INNER JOIN
— returns only rows with matching values in both tables. LEFT JOIN — returns all rows from the
left table and matching rows from right; NULLs where no match. RIGHT JOIN — returns all rows
from the right table and matching rows from left; NULLs where no match. FULL OUTER JOIN —
returns all rows from both tables; NULLs on non-matching sides. CROSS JOIN — returns the
Cartesian product of both tables (every combination). SELF JOIN — joins a table with itself.

EXPLANATION — UNDERSTAND THIS

Memory technique: INNER = only the overlap. LEFT = all left + overlap. RIGHT = all right +
overlap. FULL = everything. If employees table has 4 rows and departments has 3 rows, CROSS
JOIN returns 4x3 = 12 rows.

■ INTERVIEW TIP: Draw a Venn diagram in your mind: INNER JOIN = centre overlap. LEFT JOIN = left
circle + centre.

Q38
What is the difference between INNER JOIN and LEFT JOIN?
INTERVIEW ANSWER — SAY THIS

INNER JOIN returns ONLY the rows that have matching values in both tables. If a row in the left
table has no matching row in the right table, it is excluded from the result. LEFT JOIN returns ALL
rows from the left table. For rows with no match in the right table, the right table columns show
NULL.

EXPLANATION — UNDERSTAND THIS

Example with employees and departments tables: If employee Ravi has dept_id = NULL, INNER
JOIN excludes Ravi. LEFT JOIN includes Ravi with NULL dept_name. Use INNER JOIN when
you only want complete data. Use LEFT JOIN when you want all records even without a match.

■ INTERVIEW TIP: Ask yourself: 'Do I want to keep unmatched rows?' If yes, LEFT JOIN. If no, INNER
JOIN.

Q39
What is a Self Join? When is it used?
INTERVIEW ANSWER — SAY THIS

A Self Join is when a table is joined with itself. It uses table aliases to differentiate the two
instances. It is used when rows in the same table have a relationship with other rows in the same
table.

EXPLANATION — UNDERSTAND THIS

Classic example: An employees table where each row has a manager_id that references another
employee's id. To find each employee's manager name: SELECT [Link] AS Employee, [Link]
AS Manager FROM employees a LEFT JOIN employees b ON a.manager_id = [Link]. Your DRDO
project example: referral tracking — who referred whom in a users table.

■ INTERVIEW TIP: Self Join always uses aliases (a and b) to distinguish the two copies of the same table.

Q40
What is a Cross Join?
INTERVIEW ANSWER — SAY THIS

A Cross Join returns the Cartesian product of two tables — every row from Table A combined
with every row from Table B. If Table A has 4 rows and Table B has 3 rows, the result has 4 x 3 =
12 rows. It has no ON condition.

EXPLANATION — UNDERSTAND THIS

Use case: generating all possible combinations. Example: combining all colors and sizes for a
clothing catalogue. Cross Join can produce very large result sets — use with caution on large
tables.

■ INTERVIEW TIP: Cross joins are rarely used in production but are commonly asked in theory interviews
to test your understanding of Cartesian products.

Q41
What is a Subquery? What are its types?
INTERVIEW ANSWER — SAY THIS

A Subquery is a query nested inside another query. The inner query's result is used by the outer
query. Types: Scalar subquery — returns a single value. Row subquery — returns a single row.
Table subquery — returns multiple rows and columns (used in FROM clause). Correlated
subquery — references columns from the outer query, executed once per outer row.
Non-correlated subquery — independent of outer query, executed once.

EXPLANATION — UNDERSTAND THIS

Example: SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM
employees). The inner query calculates average salary first, then the outer query uses that value
to filter. Correlated subqueries are slower because they execute once per row of the outer query.

■ INTERVIEW TIP: Key difference between correlated and non-correlated: does the inner query depend on
the outer query? If yes = correlated.

Q42
What is a View? How is it different from a Table?
INTERVIEW ANSWER — SAY THIS

A View is a virtual table based on the result of a SELECT query. It does not physically store data
— it always reflects the current data from the underlying base tables. A Table physically stores
data on disk. A View is just a saved query definition.

EXPLANATION — UNDERSTAND THIS

Use cases for Views: Security — hide sensitive columns like salary from certain users.
Simplification — wrap complex JOIN queries into a simple name. Consistency — present data in
a standardized format. Example: CREATE VIEW active_employees AS SELECT id, name FROM
employees WHERE is_active = TRUE. Now any user can query SELECT * FROM
active_employees without writing the complex query.

■ INTERVIEW TIP: Important: Updating a view usually updates the underlying table. But views with
GROUP BY or DISTINCT cannot be updated.

Q43
What is an Index? Why is it used?
INTERVIEW ANSWER — SAY THIS

An Index is a data structure that improves the speed of data retrieval operations on a table at the
cost of additional storage space and slower write operations (INSERT, UPDATE, DELETE). It
works like a book's index — instead of reading every page, you jump directly to the page you
need.

EXPLANATION — UNDERSTAND THIS

Without an index, a query like WHERE email = 'xyz@[Link]' does a full table scan —
checking every row until it finds the match. With an index on email, the database goes directly to
the relevant rows. Trade-off: indexes speed up reads but slow down writes because the index
must be updated every time data changes.
■ INTERVIEW TIP: When NOT to use indexes: small tables, columns with very few unique values (like
gender), columns updated very frequently.

Q44
What is the difference between Clustered and Non-Clustered Index?
INTERVIEW ANSWER — SAY THIS

Clustered Index physically sorts and stores the table rows based on the indexed column. Only
ONE clustered index can exist per table. The Primary Key creates a clustered index by default.
Non-Clustered Index creates a separate structure that stores pointers to the actual data rows.
Multiple non-clustered indexes can exist on a table.

EXPLANATION — UNDERSTAND THIS

Analogy: Clustered index is like a dictionary where words are physically sorted A-Z (the data IS
the index). Non-clustered index is like a book's back index — a separate section pointing you to
page numbers. Clustered index retrieval is faster for range queries; non-clustered is used for
specific lookups.

■ INTERVIEW TIP: Key fact: ONE clustered index per table (usually the primary key), MANY non-clustered
indexes allowed.

Q45
What is a Stored Procedure?
INTERVIEW ANSWER — SAY THIS

A Stored Procedure is a precompiled set of SQL statements stored in the database that can be
executed on demand. It accepts input parameters (IN), output parameters (OUT), and can
perform complex logic including loops and conditions. Called using the CALL statement.

EXPLANATION — UNDERSTAND THIS

Advantages: Code reuse — write once, call many times. Performance — precompiled so faster
than ad-hoc queries. Security — users can execute procedures without direct table access.
Disadvantage: Harder to debug, and logic is locked inside the database making it harder to
version-control.

■ INTERVIEW TIP: If asked for difference between stored procedure and function: procedures don't need
to return a value; functions must return a value and can be used in SELECT statements.

Q46
What is a Trigger?
INTERVIEW ANSWER — SAY THIS

A Trigger is a special type of stored procedure that automatically executes when a specified
event occurs on a table. Events: INSERT, UPDATE, or DELETE. Timing: BEFORE or AFTER the
event. Triggers use NEW (new row values) and OLD (old row values) keywords.

EXPLANATION — UNDERSTAND THIS

Use cases: Audit logging — automatically log every change to a sensitive table. Business rule
enforcement — prevent inserting a salary below minimum wage. Data synchronization — update
a summary table when detail records change. Example: AFTER INSERT on orders, automatically
reduce inventory count.

■ INTERVIEW TIP: Triggers can cause performance issues if overused. Always mention this trade-off in
interviews.

Q47
What is a Cursor in SQL?
INTERVIEW ANSWER — SAY THIS

A Cursor is a database object used to retrieve rows from a result set one at a time, allowing
row-by-row processing. Steps: DECLARE the cursor, OPEN it, FETCH rows one at a time,
CLOSE it, DEALLOCATE it.

EXPLANATION — UNDERSTAND THIS

Cursors are used when you need to process each row individually — for example, applying
different business logic based on each row's values. However, cursors are slow because they
process rows sequentially. Set-based operations (using regular SQL without cursors) are almost
always preferred.

■ INTERVIEW TIP: Knowing what a cursor is AND knowing it's slow and should be avoided shows
interview maturity.

Q48
What is the difference between a Correlated and Non-Correlated Subquery?
INTERVIEW ANSWER — SAY THIS

Non-Correlated Subquery: executes independently of the outer query. It runs once and its result
is used by the outer query. Correlated Subquery: references a column from the outer query. It
re-executes for every single row processed by the outer query. Correlated subqueries are slower
but more flexible.

EXPLANATION — UNDERSTAND THIS

Non-correlated example: WHERE salary > (SELECT AVG(salary) FROM employees) — inner
query runs once. Correlated example: WHERE salary > (SELECT AVG(salary) FROM employees
e2 WHERE e2.dept_id = e1.dept_id) — inner query runs once per employee, using the outer
row's dept_id each time.

■ INTERVIEW TIP: Correlated subqueries can often be rewritten as JOINs for better performance —
mentioning this is a bonus.
 CHAPTER 6 — Normalization, ACID & Advanced
Concepts

Q49
What is Normalization? Why is it important?
INTERVIEW ANSWER — SAY THIS

Normalization is the process of organizing a relational database to reduce data redundancy and
improve data integrity. It involves decomposing tables into smaller tables and defining
relationships between them. It eliminates three types of anomalies: Insertion anomaly, Update
anomaly, and Deletion anomaly.

EXPLANATION — UNDERSTAND THIS

Without normalization: If you store a student's address in every course enrollment row, updating
the address requires changing hundreds of rows (update anomaly). Normalization separates data
into appropriate tables so each fact is stored exactly once. Trade-off: more normalization = less
redundancy but more JOINs needed to query data.

■ INTERVIEW TIP: Always mention the three anomalies (insertion, update, deletion) when explaining why
normalization matters.

Q50
What is 1NF (First Normal Form)?
INTERVIEW ANSWER — SAY THIS

A table is in 1NF if: every column contains atomic (indivisible) values — no multiple values in a
single cell. There are no repeating groups or arrays. Each row is unique.

EXPLANATION — UNDERSTAND THIS

Violation example: A student table where the 'courses' column contains 'Maths, Physics,
Chemistry' in one cell. This violates 1NF because the value is not atomic. Fix: Create a separate
row for each course-student combination, or a separate courses table.

■ INTERVIEW TIP: 1NF is about atomicity — one value per cell. If you see comma-separated values in a
column, it violates 1NF.

Q51
What is 2NF (Second Normal Form)?
INTERVIEW ANSWER — SAY THIS
A table is in 2NF if: it is already in 1NF AND it has no partial dependencies — every non-key
column must depend on the ENTIRE primary key, not just part of it. Partial dependency only
applies when the primary key is composite (multiple columns).

EXPLANATION — UNDERSTAND THIS

Example: A table with composite primary key (student_id, course_id) and columns:
student_name, course_name, grade. student_name depends only on student_id (partial
dependency). course_name depends only on course_id (partial dependency). grade depends on
both (student_id, course_id) — this is fine. Fix: Split into Students(student_id, student_name),
Courses(course_id, course_name), Enrollments(student_id, course_id, grade).

■ INTERVIEW TIP: 2NF only matters with composite primary keys. If your PK is a single column, you
automatically satisfy 2NF if in 1NF.

Q52
What is 3NF (Third Normal Form)?
INTERVIEW ANSWER — SAY THIS

A table is in 3NF if: it is in 2NF AND there are no transitive dependencies — non-key columns
must not depend on other non-key columns. Every non-prime attribute must depend ONLY on the
candidate keys.

EXPLANATION — UNDERSTAND THIS

Example: employees(emp_id, dept_id, dept_name). Here emp_id -> dept_id and dept_id ->
dept_name. dept_name depends on dept_id (non-key column), not directly on emp_id. This is a
transitive dependency. Fix: Move dept_name to a departments(dept_id, dept_name) table.
employees only keeps dept_id as foreign key.

■ INTERVIEW TIP: Transitive dependency: A -> B -> C where C depends on B, not directly on A. B and C
must go into a separate table.

Q53
What is BCNF (Boyce-Codd Normal Form)?
INTERVIEW ANSWER — SAY THIS

BCNF is a stricter version of 3NF. A table is in BCNF if: it is in 3NF AND for every functional
dependency X -> Y, X must be a superkey (a key that uniquely identifies rows). BCNF eliminates
anomalies that 3NF misses in edge cases involving multiple candidate keys.

EXPLANATION — UNDERSTAND THIS

Most practical databases aim for 3NF. BCNF is for very specific edge cases. The difference: in
3NF, a non-prime attribute can determine another non-prime attribute if neither is a candidate key
— BCNF disallows this entirely.

■ INTERVIEW TIP: For Deloitte interviews: know 1NF, 2NF, 3NF deeply. BCNF just needs a definition —
one or two sentences is enough.

Q54
What is Denormalization? When is it used?
INTERVIEW ANSWER — SAY THIS

Denormalization is the process of intentionally introducing redundancy into a database (reversing

normalization) to improve read performance. It reduces the number of JOINs needed by storing
redundant data together.

EXPLANATION — UNDERSTAND THIS

Use case: In a reporting database or data warehouse, queries need to aggregate millions of rows
fast. Doing 5 JOINs on every query is too slow. Denormalizing stores all relevant columns in one
wide table — reads are much faster, but writes and storage are more expensive. OLAP systems
(analytics) are often denormalized; OLTP systems (transactions) are normalized.

■ INTERVIEW TIP: Normalization vs Denormalization is a classic trade-off question: data integrity vs query
performance.

Q55
What are ACID Properties?
INTERVIEW ANSWER — SAY THIS

ACID stands for four properties that guarantee reliable database transactions: Atomicity — a
transaction is all-or-nothing. If any step fails, all steps are rolled back. Consistency — the
database moves from one valid state to another, maintaining all rules and constraints. Isolation —
concurrent transactions don't interfere with each other. Each transaction behaves as if it runs
alone. Durability — once a transaction is committed, it remains permanent even in the event of a
crash.

EXPLANATION — UNDERSTAND THIS

Real-world example — Bank transfer of Rs. 1000 from Account A to Account B: Atomicity: Both
debit A and credit B must succeed, or neither happens. Consistency: Total money in the system
remains the same before and after. Isolation: Another simultaneous transfer doesn't see partial
state. Durability: After COMMIT, the transfer persists even if server crashes immediately after.
■ INTERVIEW TIP: ACID is one of the highest-probability theory questions in any DBMS interview. Know
each property with the bank transfer example.

Q56
What is a Transaction?
INTERVIEW ANSWER — SAY THIS

A transaction is a sequence of one or more SQL operations that are treated as a single unit of
work. Either ALL operations in the transaction succeed and are committed, or ALL fail and are
rolled back. Transactions ensure ACID properties are maintained.

EXPLANATION — UNDERSTAND THIS

Example: Online shopping checkout involves: (1) deducting items from inventory, (2) charging
payment, (3) creating order record. All three must succeed together. If payment fails, inventory
should not be reduced. This entire sequence is wrapped in a transaction.

■ INTERVIEW TIP: Transactions are the mechanism that implements ACID. Every COMMIT ends a
transaction, every ROLLBACK aborts one.

Q57
What is the difference between OLTP and OLAP?
INTERVIEW ANSWER — SAY THIS

OLTP (Online Transaction Processing): handles day-to-day transactions, many short queries,
highly normalized, optimized for fast INSERT/UPDATE/DELETE. Examples: banking systems,
e-commerce. OLAP (Online Analytical Processing): handles complex analytical queries on large
historical datasets, often denormalized, optimized for fast SELECT with aggregations. Examples:
data warehouses, BI reports.

EXPLANATION — UNDERSTAND THIS

Think of it this way: OLTP is for running the business (recording every transaction in real-time).
OLAP is for understanding the business (analyzing months of sales data to find trends). OLTP =
many small fast writes. OLAP = few complex reads over huge data.

■ INTERVIEW TIP: This is asked because Deloitte works in both consulting and analytics. Knowing OLTP
vs OLAP shows business awareness.

Q58
What is a Deadlock in databases?
INTERVIEW ANSWER — SAY THIS
A deadlock occurs when two or more transactions each hold a lock on a resource that the other
needs, causing all of them to wait indefinitely for each other — neither can proceed. Example:
Transaction A locks Table1 and waits for Table2. Transaction B locks Table2 and waits for
Table1.

EXPLANATION — UNDERSTAND THIS

Deadlocks are resolved by the database's deadlock detection algorithm — it picks one
transaction as the victim and rolls it back, releasing its locks and allowing the other to proceed.
Prevention strategies: acquire locks in the same order, use shorter transactions, use appropriate
isolation levels.

■ INTERVIEW TIP: Deadlocks are more of an OS+DBMS intersection concept. Knowing it shows depth —
great bonus point.

Q59
What are Indexes and when should you NOT use them?
INTERVIEW ANSWER — SAY THIS

Indexes should NOT be used on: small tables (full scan is faster), columns with very low
cardinality (few unique values like gender — only M/F/Other), columns that are frequently
updated (index must be rebuilt each time), tables that have mostly INSERT/UPDATE/DELETE
operations (writes become slower).

EXPLANATION — UNDERSTAND THIS

Over-indexing is a real problem. Every extra index slows down write operations and wastes disk
space. The right strategy: index columns used in WHERE clauses, JOIN conditions, ORDER BY
columns, and frequently searched columns.

■ INTERVIEW TIP: This question tests whether you understand trade-offs, not just how to create an index.
Show both sides.

Q60
What is the difference between a Function and a Stored Procedure?
INTERVIEW ANSWER — SAY THIS

Function: MUST return a value. Can be used inside a SELECT statement. Cannot modify
database state (generally). Types: scalar (returns one value) and table-valued (returns a table).
Stored Procedure: Does NOT need to return a value. Cannot be used inside SELECT. Can
perform INSERT, UPDATE, DELETE. Has IN, OUT, and INOUT parameters.

EXPLANATION — UNDERSTAND THIS

Example: A function to calculate age from date_of_birth returns a number and can be used in:
SELECT name, calculate_age(dob) FROM employees. A stored procedure to process payroll
cannot be embedded in a SELECT — it is called separately with CALL.

■ INTERVIEW TIP: One-liner to remember: Function = returns value, used in queries. Procedure =
performs action, called independently.
 QUICK REVISION SHEET — Last 30 Minutes Before
Interview

SQL vs MySQL SQL = language. MySQL = software that uses SQL.

DBMS vs RDBMS RDBMS = tables + relationships + ACID. DBMS = just file storage.

DELETE vs DELETE = rows + WHERE + rollback. TRUNCATE = all rows, no rollback. DROP
TRUNCATE vs DROP = entire table gone.

WHERE vs HAVING WHERE = before grouping, no aggregates. HAVING = after GROUP BY, can use
aggregates.

Primary Key vs PK = no NULL, one per table. Unique = one NULL allowed, many per table.
Unique Key

INNER vs LEFT JOIN INNER = only matches. LEFT = all left rows + nulls for no match.

1NF Atomic values, no repeating groups.

2NF 1NF + no partial dependency (non-key column depends on FULL composite PK).

3NF 2NF + no transitive dependency (non-key column depends only on PK, not other
non-key).

ACID Atomicity, Consistency, Isolation, Durability.

Atomicity All or nothing — if one step fails, all steps rollback.

Durability Committed data persists even after a crash.

Clustered Index Physically sorts data. One per table. Auto-created by PK.

Non-Clustered Index Separate pointer structure. Many allowed per table.

Stored Procedure vs Procedure = action, no mandatory return. Function = must return value, usable in
Function SELECT.

View Virtual table based on SELECT. No physical storage. Always reflects live data.

Trigger Auto-runs on INSERT/UPDATE/DELETE. Uses NEW and OLD keywords.

Transaction Sequence of SQL ops treated as one unit. All succeed or all rollback.

SQL Execution Order FROM > JOIN > WHERE > GROUP BY > HAVING > SELECT > DISTINCT >
ORDER BY > LIMIT

OLTP vs OLAP OLTP = real-time transactions, normalized. OLAP = analytics, denormalized.

You've got this, Sunanda. Deloitte reached back out to YOU. Walk into that interview
with confidence. You have the preparation, the experience, and the DRDO internship.
Just speak clearly, give examples, and show them you understand not just WHAT but
WHY. Good luck! ■