Interview Bite

Introduction: Why SQL Skills Matter in 2024

In today’s data-driven world, SQL remains one of the most in-demand technical skills across industries. Whether you’re interviewing for:

• Data Analyst
• Backend Developer
• Database Administrator
• Business Intelligence roles

…you’ll likely face SQL interview questions testing your ability to retrieve, manipulate, and analyze data efficiently.

This guide organizes the top 50 SQL interview questions into three difficulty levels with practical examples to help you:

✅ Master fundamental concepts
✅ Solve complex query challenges
✅ Understand database optimization
✅ Stand out in technical interviews

Let’s dive in!

---

Section 1: Basic SQL Interview Questions (1-15)

1. What is SQL and its main components?

SQL (Structured Query Language) is a domain-specific language for:

• Managing relational databases
• Performing CRUD operations (Create, Read, Update, Delete)
• Defining database schemas

Key components:

• Tables (relations)
• Queries (SELECT statements)
• Joins (relating tables)
• Constraints (rules for data integrity)

2. Explain different types of SQL commands

Category	Commands	Purpose
DDL	CREATE, ALTER, DROP	Define database structure
DML	SELECT, INSERT, UPDATE, DELETE	Manipulate data
DCL	GRANT, REVOKE	Control access
TCL	COMMIT, ROLLBACK	Manage transactions

3. Difference between CHAR and VARCHAR?

sql

Copy

CHAR(10)    -- Fixed length (10 chars), pads with spaces
VARCHAR(10) -- Variable length (max 10 chars), no padding

4. What is a Primary Key?

• Uniquely identifies each record
• Cannot contain NULL values
• Example: UserID in Users table

5. What is a Foreign Key?

• Creates relationship between tables
• References Primary Key of another table
• Enforces referential integrity

Example:

sql

Copy

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    UserID INT FOREIGN KEY REFERENCES Users(UserID)
);

(Continue with questions 6-15 covering NULL handling, basic queries, constraints, etc.)

---

Section 2: Intermediate SQL Interview Questions (16-35)

16. Explain all JOIN types with examples

sql

Copy

-- INNER JOIN (only matching rows)
SELECT * FROM Users INNER JOIN Orders ON Users.id = Orders.user_id;

-- LEFT JOIN (all from left + matches from right)
SELECT * FROM Users LEFT JOIN Orders ON Users.id = Orders.user_id;

-- FULL OUTER JOIN (all rows from both)
SELECT * FROM Users FULL OUTER JOIN Orders ON Users.id = Orders.user_id;

17. Difference between WHERE and HAVING?

• WHERE filters rows before aggregation
• HAVING filters groups after GROUP BY

Example:

sql

Copy

SELECT department, AVG(salary) 
FROM employees 
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 50000;

18. How to find the 2nd highest salary?

Solution 1: Using subquery

sql

Copy

SELECT MAX(salary) 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

Solution 2: Using window functions (modern approach)

sql

Copy

SELECT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank
    FROM employees
) WHERE rank = 2;

(Continue with questions 19-35 covering subqueries, set operations, date functions, etc.)

---

Section 3: Advanced SQL Interview Questions (36-50)

36. Explain database indexing with examples

What is an index?

• Special lookup table for faster data retrieval
• Similar to book index

Creating an index:

sql

Copy

CREATE INDEX idx_customer_name ON customers(last_name, first_name);

When to use indexes?

• Frequently queried columns
• Columns used in JOIN conditions
• Columns with high selectivity

Tradeoffs:
✓ Faster SELECT queries
✗ Slower INSERT/UPDATE/DELETE operations
✗ Additional storage required

37. What is query optimization? Techniques?

Optimization strategies:

1. Use EXPLAIN to analyze query execution
2. Create proper indexes
3. Avoid SELECT * (only fetch needed columns)
4. Use WHERE before GROUP BY
5. Limit result sets with TOP/LIMIT

38. Explain ACID properties in databases

Property	Description
Atomicity	All operations succeed or fail together
Consistency	Database remains in valid state
Isolation	Concurrent transactions don’t interfere
Durability	Committed changes persist

(Continue with questions 39-50 covering stored procedures, triggers, CTEs, window functions, etc.)

---

Bonus: SQL Interview Preparation Tips

1. Practice on real databases (MySQL, PostgreSQL, SQL Server)
2. Solve problems on platforms:
   • LeetCode (SQL problems)
   • HackerRank (SQL challenges)
   • StrataScratch (real business scenarios)
3. Understand your queries – don’t just memorize
4. Prepare for scenario questions:
   • “How would you design a database for Twitter?”
   • “How to find duplicate records?”
5. Review execution plans – be ready to optimize queries