SQL Interview Questions

1. What is the difference between INNER JOIN and OUTER JOIN?

• INNER JOIN returns only the matching rows from both tables.
• OUTER JOIN returns all rows from one table and matches from the other.

Example:

SELECT * FROM A INNER JOIN B ON A.id = B.id;

2. How do you use the COALESCE() function?

• COALESCE() returns the first non-null value from the list.

Example: SELECT COALESCE(NULL, 'apple', 'orange');

Explanation: The output will be 'apple' because it's the first non-null value.

3. What is the difference between a subquery and a correlated subquery?

• A subquery runs independently, while a correlated subquery depends on the outer query.

Example of Subquery:

SELECT * FROM Employees WHERE salary > (SELECT AVG(salary) FROM Employees);

4. Explain the LEAD() and LAG() functions.

• LEAD() looks at the next row, and LAG() looks at the previous row.
• Explanation: This helps you compare a row with its previous one.

SELECT employee, salary, LAG(salary,1) OVER (ORDER BY salary) AS previous_salary FROM Employees;

5. How does GROUP BY work?

• GROUP BY groups rows with the same values in specified columns and allows aggregate functions.

SELECT department, COUNT(*) FROM Employees GROUP BY department;

6. What are window functions in SQL?

• Window functions perform calculations across rows related to the current row.

SELECT salary, RANK() OVER (ORDER BY salary DESC) AS rank FROM Employees;

7. What is UNION vs. UNION ALL?

• UNION removes duplicates, while UNION ALL keeps duplicates.

SELECT name FROM A UNION SELECT name FROM B;

8. What is the use of the HAVING clause?

• HAVING filters rows after the GROUP BY clause (similar to WHERE but for aggregated data).

SELECT department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 5;

9. What are aggregate functions in SQL?

• Functions like COUNT(), SUM(), AVG(), MAX(), and MIN() to perform calculations on a set of rows.

SELECT AVG(salary) FROM Employees;

10. How does EXISTS work?

• EXISTS checks if a subquery returns any rows.

SELECT * FROM Employees WHERE EXISTS (SELECT 1 FROM Departments WHERE department _id = 10);

• Python: Focus on loops, string manipulation, list comprehensions, and special keywords like lambda, pass, with, and basic data structures.
• SQL: Master joins, subqueries, window functions (LEAD(), LAG()), and common clauses like GROUP BY, HAVING, and COALESCE().
• Data Processing: Understand preprocessing, mapping, and validation techniques.

11. What is the difference between HAVING and WHERE?

• WHERE filters rows before aggregation.
• HAVING filters rows after aggregation.

SELECT department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 5;

12. What is the difference between IN and EXISTS in SQL?

• IN checks if a value is in a list of values.
• EXISTS checks if a subquery returns any results.

SELECT * FROM Employees WHERE EXISTS (SELECT 1 FROM Departments WHERE department_id =10);

13. How do you use the CASE statement in SQL?

• CASE is used for conditional logic in SQL.

SELECT anme,
      CASE
          WHEN salary > 50000 THEN 'High'
          ELSE 'LOW'
      END AS salary_level
FROM Employees;

14. What is the difference between COUNT(*) and COUNT(column)?

• COUNT(*) counts all rows, including rows with NULL values.
• COUNT(column) counts non-null values in the specified column.

SELECT COUNT(*) FROM Employees;
SELECT COUNT(Salary) FROM EMPLOYees

15. What is a CROSS JOIN in SQL?

• A CROSS JOIN returns the Cartesian product of two tables, meaning all possible combinations of rows.

SELECT * FROM tableA CROSS JOIN TableB;

16. Explain RANK() and DENSE_RANK() window functions.

• RANK() assigns a rank, leaving gaps for ties.
• DENSE_RANK() assigns ranks without gaps.

SELECT name, salary, RANK() OVER(ORDER BY salary DESC) FROM Employees;

17. What is a recursive CTE in SQL?

• A recursive Common Table Expression (CTE) allows a query to reference itself.

WITH RECRUSIVE EmployeeHierracby AS(
  SELECT id,name,manager_id FROm Employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.name, e.manager_id
  FROM Employees e
  INNER JOIN EmployeeHierarchy eh ON e.manager_id = eh.id)

SELECT * FROM EmployeeHierarchy;

18. What is the NULLIF() function?

• NULLIF() returns NULL if two values are equal, otherwise, it returns the first value.

SELECT NULLIF(10, 10), NULLIF(10, 5);  -- Returns NULL and 10

19. How do you calculate the cumulative sum in SQL?

• Use SUM() as a window function.

SELECT name,salary, SUM(salary) OVER (ORDER BY salary) AS cumulative_salary FROM Employees;

20. What is the difference between DELETE and TRUNCATE?

• DELETE removes rows one at a time and can be rolled back.
• TRUNCATE removes all rows quickly and cannot be rolled back in most databases.

DELETE FROM Employees WHERE id = 1;
TRUNCATE TABLE Employees;