Below are 25 advanced SQL interview questions and answers tailored for experienced developers. These questions delve into deeper SQL concepts, optimization techniques, and best practices.
1. What is the difference between INNER JOIN and OUTER JOIN?
Answer:
INNER JOIN: Returns only the matching rows from both tables.OUTER JOIN: Returns all rows from one table and matching rows from the other table. It includes:LEFT JOIN: All rows from the left table and matching rows from the right table.RIGHT JOIN: All rows from the right table and matching rows from the left table.FULL JOIN: All rows from both tables, withNULLvalues where there is no match.
2. What is a correlated subquery?
Answer:
A correlated subquery is a subquery that depends on the outer query for its values. It is executed repeatedly, once for each row processed by the outer query.
Example:
SELECT Name
FROM Employees E
WHERE Salary > (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = E.DepartmentID);3. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?
Answer:
ROW_NUMBER(): Assigns a unique number to each row, starting from 1.RANK(): Assigns a unique rank to each row, with gaps in ranking for ties.DENSE_RANK(): Assigns a unique rank to each row, without gaps in ranking for ties.
Example:
SELECT Name, Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,
RANK() OVER (ORDER BY Salary DESC) AS Rank,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;4. What is a CTE (Common Table Expression)?
Answer:
A CTE is a temporary result set that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement. It improves readability and reusability of complex queries.
Example:
WITH CTE AS (
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY DepartmentID
)
SELECT * FROM CTE WHERE AvgSalary > 50000;5. What is the difference between UNION and UNION ALL?
Answer:
UNION: Combines the results of two queries and removes duplicates.UNION ALL: Combines the results of two queries without removing duplicates.
6. What is a window function in SQL?
Answer:
A window function performs a calculation across a set of table rows related to the current row. It is used with the OVER() clause.
Example:
SELECT Name, Salary,
AVG(Salary) OVER (PARTITION BY DepartmentID) AS AvgDeptSalary
FROM Employees;7. What is the difference between GROUP BY and PARTITION BY?
Answer:
GROUP BY: Aggregates data into summary rows (reduces the number of rows returned).PARTITION BY: Divides the result set into partitions for window functions (retains the original number of rows).
8. What is a recursive CTE?
Answer:
A recursive CTE is a CTE that references itself. It is used to query hierarchical data, such as organizational charts or tree structures.
Example:
WITH RecursiveCTE AS (
SELECT EmployeeID, ManagerID, Name
FROM Employees
WHERE ManagerID IS NULL
UNION ALL
SELECT E.EmployeeID, E.ManagerID, E.Name
FROM Employees E
INNER JOIN RecursiveCTE R ON E.ManagerID = R.EmployeeID
)
SELECT * FROM RecursiveCTE;9. What is the difference between DELETE and TRUNCATE?
Answer:
DELETE: Removes specific rows from a table. It can be rolled back and fires triggers.TRUNCATE: Removes all rows from a table. It cannot be rolled back and does not fire triggers.
10. What is the difference between DROP and TRUNCATE?
Answer:
DROP: Deletes the entire table structure and data. The table no longer exists.TRUNCATE: Removes all rows but retains the table structure.
11. What is the difference between HAVING and WHERE?
Answer:
WHERE: Filters rows before grouping (used withSELECT,UPDATE,DELETE).HAVING: Filters groups after grouping (used withGROUP BY).
12. What is the difference between CHAR and VARCHAR?
Answer:
CHAR: Fixed-length character data (pads with spaces if the data is shorter than the defined length).VARCHAR: Variable-length character data (uses only the required space).
13. What is the difference between IN and EXISTS?
Answer:
IN: Checks if a value matches any value in a list or subquery.EXISTS: Checks if a subquery returns any rows (more efficient for large datasets).
Example:
SELECT * FROM Employees WHERE DepartmentID IN (SELECT DepartmentID FROM Departments);
SELECT * FROM Employees E WHERE EXISTS (SELECT 1 FROM Departments D WHERE D.DepartmentID = E.DepartmentID);14. What is the difference between CROSS JOIN and INNER JOIN?
Answer:
CROSS JOIN: Returns the Cartesian product of two tables (all possible combinations of rows).INNER JOIN: Returns only the matching rows based on a condition.
15. What is the difference between UNIQUE and PRIMARY KEY?
Answer:
UNIQUE: Ensures that all values in a column are unique. AllowsNULLvalues.PRIMARY KEY: Ensures that all values in a column are unique and does not allowNULLvalues.
16. What is the difference between INDEX and PRIMARY KEY?
Answer:
INDEX: Improves the speed of data retrieval operations. Can be created on one or more columns.PRIMARY KEY: Uniquely identifies each row in a table. Automatically creates a unique index.
17. What is the difference between BETWEEN and IN?
Answer:
BETWEEN: Checks if a value falls within a range (inclusive).IN: Checks if a value matches any value in a list.
18. What is the difference between IS NULL and IS NOT NULL?
Answer:
IS NULL: Checks if a column containsNULLvalues.IS NOT NULL: Checks if a column does not containNULLvalues.
19. What is the difference between CASCADE and RESTRICT in foreign keys?
Answer:
CASCADE: Automatically updates or deletes related rows in the child table when the parent table is updated or deleted.RESTRICT: Prevents updates or deletions in the parent table if related rows exist in the child table.
20. What is the difference between TRANSACTION and SAVEPOINT?
Answer:
TRANSACTION: A sequence of SQL operations performed as a single unit of work.SAVEPOINT: A point within a transaction to which you can roll back without affecting the entire transaction.
Example:
BEGIN TRANSACTION;
INSERT INTO Orders (OrderID, Product) VALUES (1, 'Laptop');
SAVEPOINT SP1;
INSERT INTO Orders (OrderID, Product) VALUES (2, 'Phone');
ROLLBACK TO SP1; -- Rolls back to the savepoint
COMMIT;21. What is the difference between VIEW and MATERIALIZED VIEW?
Answer:
VIEW: A virtual table that does not store data. It is a saved SQL query.MATERIALIZED VIEW: A physical copy of the data stored as a table. It needs to be refreshed periodically.
22. What is the difference between STORED PROCEDURE and FUNCTION?
Answer:
STORED PROCEDURE: Can perform multiple operations and does not return a value (can useOUTparameters).FUNCTION: Must return a single value and can be used in SQL statements.
23. What is the difference between TRIGGER and STORED PROCEDURE?
Answer:
TRIGGER: Automatically executes in response to specific events (e.g.,INSERT,UPDATE,DELETE).STORED PROCEDURE: Must be explicitly called to execute.
24. What is the difference between NOLOCK and WITH (NOLOCK)?
Answer:
NOLOCK: A table hint that allows dirty reads (reads uncommitted data).WITH (NOLOCK): The same asNOLOCK, but used in theFROMclause.
Example:
SELECT * FROM Employees WITH (NOLOCK);25. What is the difference between CLUSTERED INDEX and NON-CLUSTERED INDEX?
Answer:
CLUSTERED INDEX: Determines the physical order of data in a table. Only one clustered index per table.NON-CLUSTERED INDEX: Creates a separate structure to store the index. Multiple non-clustered indexes per table.
These advanced questions and answers should help experienced developers prepare for SQL interviews effectively. Make sure to practice writing queries and understand the underlying concepts thoroughly!
*************** ALL THE BEST *****************
Visit JaganInfo youtube channel for more valuable content https://www.youtube.com/@jaganinfo
- article:
- “Advanced SQL Interview Questions & Answers for Experienced Professionals”
- “Master Advanced SQL Interviews: Expert-Level Q&A for Experienced Candidates”
- “Ace Your Advanced SQL Interview: Easy Answers for Experienced Candidates”
- “Top Advanced SQL Interview Questions & Answers for Experienced Developers”
- “Crack the Advanced SQL Interview: Simple Solutions for Experienced Professionals”
- “Advanced SQL Interview Prep: Easy-to-Answer Q&A for Experienced Candidates”
- “Expert SQL Interview Guide: Advanced Questions & Simple Answers”
- “Advanced SQL Interview Mastery: Easy Answers for Experienced Interviewees”
- “Advanced SQL Q&A: Expert Interview Preparation for Experienced Professionals”
- “Top Advanced SQL Interview Questions: Clear Answers for Experienced Testers”