Advanced SQL Interview Questions – Part 3: Keys, Views, Indexing & Best Practices

 

Advanced SQL Interview Questions – Part 3: Keys, Views, Indexing & Best Practices

Welcome to the final installment of our three-part series on Advanced SQL Interview Questions. In Part 1 and Part 2, we covered essential SQL concepts like UNION, transactions, subqueries, and storage engines. In Part 3, we’re wrapping up with high-impact topics:

• Keys and constraints
• Views and virtual tables
• Temporary tables
• Full-text search
• Best practices and interview tips

If you’re preparing for a database role or full-stack development interview, this section will help you confidently answer both conceptual and practical questions.

---

🔹 16. What is the difference between a Primary Key and a Unique Key in MySQL?

✅ Answer:

Both Primary Keys and Unique Keys are used to enforce uniqueness in a table. However, there are some key differences in how they behave.

Feature	Primary Key	Unique Key
Uniqueness	✅ Enforced	✅ Enforced
Null Values	❌ Not allowed	✅ Allowed (only one NULL per column)
Count per Table	Only one Primary Key	Can have multiple Unique Keys
Index Type	Clustered (InnoDB default)	Non-clustered

🧠 Summary:

• Primary Key = Main identifier for each row. Must be not null and unique.
• Unique Key = Ensures uniqueness but allows nulls (unless otherwise defined).

---

🔹 17. What is a view in MySQL, and how is it used?

✅ Answer:

A view is a virtual table based on the result of a SELECT statement. It does not store data physically but pulls data from base tables when queried.

🔍 Syntax:

 

CREATE VIEW employee_summary AS

SELECT department_id, COUNT(*) AS total_employees, AVG(salary) AS avg_salary

FROM employees

GROUP BY department_id;

📌 Uses of Views:

• Simplify complex joins or queries
• Provide a layer of abstraction for end-users
• Restrict access to sensitive columns
• Standardize queries across applications

🔐 Security Benefit:

 

GRANT SELECT ON employee_summary TO analyst_user;

This allows access without exposing the entire employees table.

🧠 Views are read-only by default. Use WITH CHECK OPTION or updatable views cautiously.

---

🔹 18. What is a temporary table in MySQL, and how is it used?

✅ Answer:

A temporary table is a short-lived table that exists only for the duration of a session or until it is dropped manually.

🔍 Syntax:

 

CREATE TEMPORARY TABLE temp_sales AS

SELECT product_id, SUM(amount) AS total_sales

FROM sales

GROUP BY product_id;

✅ Use Cases:

• Store intermediate results
• Break down large queries
• Cache filtered data for reuse

⚠️ Notes:

• Temporary tables are session-specific
• Automatically dropped when the session ends
• You can use indexes and constraints on them

---

🔹 19. What is a full-text index in MySQL, and how is it used?

✅ Answer:

A full-text index allows you to perform natural language search on large text fields. Supported in MySQL by InnoDB and MyISAM (as of newer versions).

🔍 Creating a Full-Text Index:

 

CREATE FULLTEXT INDEX idx_description ON products(description);

🔍 Example Search:

 

SELECT * FROM products

WHERE MATCH(description) AGAINST ('"wireless headphones"' IN NATURAL LANGUAGE MODE);

🔎 Modes:

• Natural Language Mode (default)
• Boolean Mode: supports AND/OR/NOT operators
• Query Expansion: includes semantically related terms

🧠 Full-text search is case-insensitive by default and ideal for blog posts, descriptions, FAQs, and document metadata.

---

🔹 20. What is a stored procedure in MySQL, and how is it used?

✅ Answer:

A stored procedure is a named block of SQL statements stored in the database and executed with a simple CALL.

🔍 Example:

 

DELIMITER $$

 

CREATE PROCEDURE update_salary_bonus(IN bonus_percent DECIMAL(5,2))

BEGIN

  UPDATE employees

  SET salary = salary + (salary * bonus_percent / 100);

END $$

 

DELIMITER ;

 

CALL update_salary_bonus(10);

✅ Benefits Recap:

• Encapsulate business logic
• Improve performance
• Reusable across applications
• Reduces network traffic

Stored procedures are great for automation, batch processing, and data migrations.

---

💡 Interview Tips and Optimization Best Practices

Here are some pro-level SQL tips that you can use to impress interviewers:

🚀 Performance Tips:

• Use EXPLAIN to analyze slow queries
• Prefer JOINs over subqueries for large datasets
• Use indexes selectively—don’t index everything
• Use LIMIT in test queries to avoid full-table scans
• Use stored procedures/functions for repeatable logic

⚠️ Common Pitfalls:

• Forgetting NULL behavior in comparisons (IS NULL, not = NULL)
• Overusing triggers (can lead to hard-to-debug logic)
• Neglecting to use transactions for multi-step operations
• Using SELECT * instead of specifying columns (slows down large joins)

---

✅ Related Post

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