SQL Topics
SQL Interview Questions Intermediate
title: SQL Interview Questions Intermediate
After mastering beginner-level SQL concepts, interviewers typically move toward:
Practical SQL
Query Logic
Joins
Subqueries
Indexes
Transactions
PerformanceThese questions are commonly asked for:
SQL Developer
Backend Developer
Database Developer
Software Engineer
Data Analystroles.
Q1. What is the difference between WHERE and HAVING?
WHERE
Filters rows before grouping.
HAVING
Filters groups after grouping.
Example:
SELECT DepartmentID,
COUNT(*)
FROM Employees
WHERE Salary > 50000
GROUP BY DepartmentID
HAVING COUNT(*) > 5;Q2. What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN
Returns only matching records.
LEFT JOIN
Returns all rows from the left table and matching rows from the right table.
Example:
SELECT *
FROM Employees E
LEFT JOIN Departments D
ON E.DepartmentID =
D.DepartmentID;Q3. What is a Self Join?
A table joined with itself.
Example:
SELECT
E.EmployeeName,
M.EmployeeName AS Manager
FROM Employees E
LEFT JOIN Employees M
ON E.ManagerID =
M.EmployeeID;Q4. What is a Cross Join?
Returns Cartesian Product.
Example:
SELECT *
FROM Employees
CROSS JOIN Departments;Result:
Every Employee × Every DepartmentQ5. What is a Correlated Subquery?
A subquery that depends on the outer query.
Example:
SELECT *
FROM Employees E
WHERE Salary >
(
SELECT AVG(Salary)
FROM Employees
WHERE DepartmentID =
E.DepartmentID
);Q6. Difference Between IN and EXISTS?
IN
Compares values.
EXISTS
Checks row existence.
Large datasets often perform better with:
EXISTSQ7. What is a CTE?
CTE stands for:
Common Table ExpressionTemporary result set used within a query.
Example:
WITH HighSalary AS
(
SELECT *
FROM Employees
WHERE Salary > 50000
)
SELECT *
FROM HighSalary;Q8. What is a Recursive CTE?
A CTE that references itself.
Used for:
Hierarchies
Organization Charts
Folder StructuresQ9. What is the difference between DELETE and TRUNCATE?
| Feature | DELETE | TRUNCATE |
|---|---|---|
| WHERE Clause | Yes | No |
| Row-by-Row Removal | Yes | No |
| Faster | No | Yes |
| Logs More Data | Yes | No |
Q10. What is the difference between UNION and UNION ALL?
UNION
Removes duplicates.
UNION ALL
Keeps duplicates.
Example:
SELECT Name FROM A
UNION ALL
SELECT Name FROM B;Q11. What is an Index?
An index improves data retrieval speed.
Example:
CREATE INDEX IX_Email
ON Employees(Email);Q12. What is the difference between Clustered and Non-Clustered Index?
| Feature | Clustered | Non-Clustered |
|---|---|---|
| Data Sorted | Yes | No |
| Count | One | Multiple |
| Faster Range Queries | Yes | Moderate |
Q13. What is a Composite Index?
Index created on multiple columns.
Example:
CREATE INDEX IX_DepSalary
ON Employees
(
DepartmentID,
Salary
);Q14. What is the Leftmost Prefix Rule?
For:
(DepartmentID, Salary)index,
SQL can efficiently use:
WHERE DepartmentID = 1but not always:
WHERE Salary > 50000alone.
Q15. What is a Covering Index?
An index containing all columns needed by a query.
Example:
CREATE INDEX IX_Covering
ON Employees
(
DepartmentID,
EmployeeID,
EmployeeName
);Q16. What is Normalization?
Process of reducing redundancy.
Forms:
1NF
2NF
3NF
BCNFQ17. What is Denormalization?
Combining data to improve read performance.
Q18. What is a View?
Virtual table based on a query.
Example:
CREATE VIEW ActiveEmployees AS
SELECT *
FROM Employees
WHERE IsActive = 1;Q19. What is a Materialized View?
Stores query results physically.
Benefits:
Faster ReadsTrade-off:
Consumes StorageQ20. What is a Stored Procedure?
Reusable SQL program stored inside the database.
Example:
CREATE PROCEDURE GetEmployees
AS
SELECT *
FROM Employees;Q21. What is the difference between Procedure and Function?
| Feature | Procedure | Function |
|---|---|---|
| Returns Value | Optional | Required |
| Used in SELECT | No | Yes |
| Complex Logic | Yes | Limited |
Q22. What is a Trigger?
Automatically executes when:
INSERT
UPDATE
DELETEoccurs.
Q23. What are ACID Properties?
Atomicity
Consistency
Isolation
DurabilityQ24. What is Transaction Isolation Level?
Controls visibility of changes between transactions.
Levels:
Read Uncommitted
Read Committed
Repeatable Read
SerializableQ25. What is a Deadlock?
Occurs when two transactions wait for each other indefinitely.
Example:
Transaction A waits for B
Transaction B waits for AQ26. What is Query Optimization?
Improving query performance.
Goal:
Less CPU
Less Memory
Less I/OQ27. What is an Execution Plan?
Roadmap showing how SQL executes a query.
Used to analyze:
Indexes
Joins
Scans
SortingQ28. What is a Table Scan?
Reading every row in a table.
Usually indicates:
Missing IndexQ29. What is an Index Seek?
Direct lookup using an index.
Much faster than:
Table ScanQ30. What is Partitioning?
Dividing large tables into smaller partitions.
Benefits:
Scalability
Performance
ManageabilityQ31. What is Partition Pruning?
Accessing only relevant partitions.
Example:
WHERE TransactionDate
BETWEEN ...only reads required partitions.
Q32. What is Dynamic SQL?
SQL generated at runtime.
Example:
EXEC(@SQL);Q33. What is SQL Injection?
Security attack involving malicious SQL input.
Example:
' OR 1=1 --Q34. How to Prevent SQL Injection?
Use:
Q35. What is ROW_NUMBER()?
Assigns unique sequential numbers.
Example:
ROW_NUMBER()
OVER
(
ORDER BY Salary DESC
)Q36. Difference Between RANK() and DENSE_RANK()?
RANK()
Skips numbers after duplicates.
Example:
1
2
2
4DENSE_RANK()
No gaps.
Example:
1
2
2
3Q37. What is a Window Function?
Performs calculations across related rows while preserving individual rows.
Example:
AVG(Salary)
OVER()Q38. What is MERGE Statement?
Combines:
INSERT
UPDATE
DELETEoperations into a single statement.
Q39. What is UPSERT?
Operation that:
Updates Existing Row
OR
Inserts New RowMERGE commonly implements UPSERT.
Q40. What is the difference between OLTP and OLAP?
| Feature | OLTP | OLAP |
|---|---|---|
| Purpose | Transactions | Analytics |
| Queries | Simple | Complex |
| Data Volume | Current Data | Historical Data |
| Examples | Banking | Data Warehouse |
Most Asked Intermediate SQL Questions
INNER JOIN vs LEFT JOIN
IN vs EXISTS
UNION vs UNION ALL
DELETE vs TRUNCATE
Clustered vs Non-Clustered Index
Procedure vs Function
RANK vs DENSE_RANK
Execution Plan
Deadlock
PartitioningIntermediate Interview Quick Revision
✓ JOINs
✓ Subqueries
✓ CTEs
✓ Indexes
✓ Transactions
✓ ACID
✓ Deadlocks
✓ Window Functions
✓ Query Optimization
✓ Dynamic SQLSummary
Intermediate SQL interviews focus on practical query writing, performance tuning, indexing, transactions, joins, and database design concepts. These questions frequently appear in software engineering, backend development, and database-related interviews.
In this lesson, you learned:
- 40 Intermediate SQL Interview Questions
- Joins and Subqueries
- CTEs and Window Functions
- Transactions and Deadlocks
- Indexing Concepts
- Query Optimization
- Dynamic SQL
- Partitioning
- SQL Security
Next Step
Continue to the next lesson:
SQL Interview Questions (Advanced) →
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for SQL Interview Questions Intermediate.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, interview, questions
Related SQL Topics