21. What is the difference between INNER JOIN and LEFT JOIN?
Answer:
-
INNER JOIN returns matching rows from both tables.
-
LEFT JOIN returns all rows from the left table and matching rows from the right table. If no match, NULLs are returned.
Example:
— INNER JOIN
SELECT E.Name, D.DepartmentName
FROM Employees E
INNER JOIN Departments D ON E.DepartmentID = D.DepartmentID;
— LEFT JOIN
SELECT E.Name, D.DepartmentName
FROM Employees E
LEFT JOIN Departments D ON E.DepartmentID = D.DepartmentID;
22. What is a CROSS JOIN?
Answer: A CROSS JOIN returns the Cartesian product — every row from the first table joined with every row from the second table.
Example: SELECT A.Name, B.ProductName
FROM Customers A
CROSS JOIN Products B;
23. What are ACID properties in SQL?
Answer:
-
Atomicity: All or nothing.
-
Consistency: Maintains database rules.
-
Isolation: Transactions are independent.
-
Durability: Once committed, data is saved.
24. What is a transaction?
Answer: A transaction is a group of SQL operations executed as a single unit. If one fails, the entire transaction fails.
Example:
BEGIN TRANSACTION;
UPDATE Accounts SET Balance = Balance – 100 WHERE AccountID = 1;
UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 2;
COMMIT;
25. How do you ROLLBACK a transaction?
Answer: If something goes wrong, you can cancel all changes using ROLLBACK.
Example:
BEGIN TRANSACTION;
DELETE FROM Employees WHERE Department = ‘Sales’;
ROLLBACK;
(This will undo the delete operation.)
26. What is normalization?
Answer: Normalization organizes data to minimize redundancy and improve data integrity by splitting into smaller related tables.
27. What are the normal forms in SQL?
Answer:
-
1NF: Atomic values only (no multi-valued columns).
-
2NF: 1NF + no partial dependency.
-
3NF: 2NF + no transitive dependency.
28. What is denormalization?
Answer: Denormalization is the process of combining tables to improve read performance by intentionally introducing redundancy.
29. What is a subquery?
Answer: A subquery is a query inside another query.
Example:
SELECT Name
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = ‘New York’);
30. What is a correlated subquery?
Answer: A correlated subquery uses values from the outer query for its execution. It runs row-by-row.
Example:
SELECT Name
FROM Employees E
WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = E.DepartmentID);
Top 50 Basic Level SQL Interview Questions and Answers with Examples
Top 50 Advanced SQL Questions asked in Top Companies (with Answers + Examples)
2 thoughts on “Top 50 Intermediate SQL Interview Questions You Must Practice [With Examples]”