Advanced SQL Interview Questions – Part 1: Core Concepts and Optimization

 

Advanced SQL Interview Questions – Part 1: Core Concepts and Optimization

Welcome to Part 1 of our three-part series on Advanced SQL Interview Questions. Whether you're a software developer, data engineer, or database administrator, having a solid grasp of advanced SQL is essential in today’s data-intensive world.

In this part, we focus on foundational yet tricky concepts that often appear in interviews—such as set operations, subqueries vs. joins, stored procedures, normalization, triggers, and indexing. These questions test not only your SQL syntax knowledge but also your understanding of how databases work under the hood.

Let’s dive into the first six essential questions, explained in detail to help you master the logic and impress your interviewers.

---

🔹 1. What is the difference between UNION and UNION ALL in SQL?

✅ Answer:

Both UNION and UNION ALL are SQL operators used to combine the results of two or more SELECT statements. However, they handle duplicates differently, which affects both output and performance.

Feature	UNION	UNION ALL
Removes Duplicates	✅ Yes	❌ No
Performance	Slower (due to sorting/filter)	Faster (no duplicate check)
Use Case	When unique results are needed	When performance matters and duplicates are acceptable

🔍 Example:

 

SELECT city FROM customers

UNION

SELECT city FROM suppliers;

This returns a distinct list of cities from both tables.

 

SELECT city FROM customers

UNION ALL

SELECT city FROM suppliers;

This returns all cities, including duplicates.

🧠 Tip:

Use UNION ALL for better performance unless eliminating duplicates is essential.

---

🔹 2. What is a subquery in SQL, and how is it different from a JOIN?

✅ Answer:

A subquery is a query nested inside another SQL query, often enclosed in parentheses. It can appear in SELECT, FROM, or WHERE clauses.

A JOIN, on the other hand, combines columns from two or more tables based on a related column.

🔄 Key Differences:

Aspect	Subquery	JOIN
Execution	Runs inner query first	Merges tables row-by-row
Use Case	When intermediate data is needed	When data from multiple tables is needed
Performance	May be slower for large data sets	Often faster with indexed joins
Readability	Better for simple logic	Preferred for complex, relational queries

🔍 Example of Subquery:

 

SELECT name FROM employees

WHERE department_id = (

  SELECT department_id FROM departments

  WHERE department_name = 'HR'

);

🔍 Example of JOIN:

 

SELECT e.name, d.department_name

FROM employees e

JOIN departments d ON e.department_id = d.department_id

WHERE d.department_name = 'HR';

🧠 Tip:

Prefer JOINS for performance when working with large datasets. Use subqueries for clarity in simple lookups or filtering.

---

🔹 3. What is a stored procedure in SQL, and what are its advantages?

✅ Answer:

A stored procedure is a group of SQL statements saved in the database that can be executed with a single call. It allows encapsulating logic, making code reusable and secure.

🔑 Key Features:

• Can accept input/output parameters
• Supports conditional logic (IF, CASE, loops)
• Can be scheduled or triggered
• Stored on the server, reducing client-side logic

🧰 Advantages:

1.     Performance: Compiled once and reused, reducing parsing overhead.

2.     Security: Users can execute procedures without knowing internal logic.

3.     Maintainability: Centralized code updates are easier.

4.     Reduced Network Traffic: Single call instead of multiple statements.

5.     Modularity: Encourages clean architecture and reusable code.

🔍 Example (MySQL):

 

DELIMITER $$

 

CREATE PROCEDURE GetEmployeesByDept(IN dept_name VARCHAR(50))

BEGIN

  SELECT e.name, e.salary

  FROM employees e

  JOIN departments d ON e.department_id = d.department_id

  WHERE d.department_name = dept_name;

END $$

 

DELIMITER ;

To execute:

 

CALL GetEmployeesByDept('Sales');

---

🔹 4. What is normalization, and why is it important in database design?

✅ Answer:

Normalization is the process of organizing data in a database to eliminate redundancy and improve data integrity. It involves breaking down large tables into smaller, related tables and defining relationships between them.

🔁 Normal Forms:

Normal Form	Purpose
1NF	Eliminate repeating groups
2NF	Eliminate partial dependencies
3NF	Eliminate transitive dependencies
BCNF	Strengthen 3NF where applicable

🔍 Example (Unnormalized to 1NF):

Before:

Student	Courses
John	Math, Science

After:

Student	Course
John	Math
John	Science

🚀 Benefits:

• Minimizes data duplication
• Makes updating data easier and safer
• Reduces inconsistencies and anomalies
• Enhances scalability and storage efficiency

---

🔹 5. What is a trigger in SQL, and how is it used?

✅ Answer:

A trigger is a special stored procedure that runs automatically in response to specific table events like INSERT, UPDATE, or DELETE.

🧩 Types of Triggers:

• BEFORE Triggers: Run before the change is applied
• AFTER Triggers: Run after the change is applied

🔍 Example:

 

CREATE TRIGGER log_salary_update

AFTER UPDATE ON employees

FOR EACH ROW

BEGIN

  INSERT INTO salary_log (emp_id, old_salary, new_salary, updated_at)

  VALUES (OLD.id, OLD.salary, NEW.salary, NOW());

END;

🧰 Use Cases:

• Enforcing business rules
• Auditing and logging changes
• Maintaining derived data in other tables
• Validating data before updates

⚠️ Be cautious with triggers—overuse can lead to hidden logic and performance issues.

---

🔹 6. What is indexing in MySQL, and how is it used?

✅ Answer:

An index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space.

🔎 Types of Indexes in MySQL:

Index Type	Description
B-Tree	Default for most storage engines (InnoDB, MyISAM)
Hash	Used in MEMORY engine
Full-Text	For text-based searches
Spatial Index	For geometric data (MySQL Spatial Extensions)

🔍 Example:

 

CREATE INDEX idx_lastname ON employees(last_name);

This index speeds up queries like:

 

SELECT * FROM employees WHERE last_name = 'Smith';

🚀 Benefits:

• Faster SELECT queries
• Improved JOIN performance
• Efficient sorting (ORDER BY) and filtering (WHERE)

⚠️ Over-indexing can slow down inserts/updates. Index strategically!

---

🧩 Coming Next in Part 2:

In Part 2, we’ll explore advanced MySQL concepts including transactions, storage engines (InnoDB vs. MyISAM), JOIN types, and views, with practical use cases and optimization tips.

✅ Related Post

• 👉 Read Part 1 – SQL Core Concepts & Queries
• 👉 Read Part 2 – Transactions, Joins & Engines
• 👉 Read Part 3 – Keys, Views, Indexing