SQL interview questions Part-3

 21. What is data partitioning?  

    Splitting large tables into smaller, manageable pieces (partitions) based on a key like date or region, improving query performance and maintenance.

22. How do you find duplicates in a table?  
    Use GROUP BY with HAVING:

SQL

SELECT column, COUNT(*)  
FROM table_name  
GROUP BY column  
HAVING COUNT(*) > 1;  

23. What is the difference between DELETE and TRUNCATE?
⦁ DELETE removes rows one by one, can have WHERE clause, logs each row, slower.
⦁ TRUNCATE removes all rows instantly, no WHERE, resets identity, faster but less flexible.

24. Explain window functions with examples.  
    Window functions perform calculations across sets of rows related to the current row without collapsing results. Example:

SQL

SELECT name, salary, RANK() OVER (ORDER BY salary DESC) AS salary_rank  
FROM employees;  

25. What is the difference between correlated and non-correlated subqueries?
⦁ Correlated subqueries depend on the outer query and execute for each row.
⦁ Non-correlated subqueries run independently once.

26. How do you enforce data integrity?  
    Using constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL), triggers, and transactions.

27. What are CTEs (Common Table Expressions)?  
    Temporary named result sets within SQL statements to improve query readability and recursion:

SQL

WITH cte AS (SELECT * FROM employees WHERE salary > 5000)  
SELECT * FROM cte;  

28. Explain EXISTS and NOT EXISTS operators.
⦁ EXISTS returns TRUE if a subquery returns any rows.
⦁ NOT EXISTS returns TRUE if subquery returns no rows.

29. How do SQL constraints work?  
    Constraints enforce rules at the database level to ensure data validity and integrity during insert/update/delete operations.

30. What is an execution plan? How do you use it?  
    A detailed roadmap of how SQL Server executes a query. Used to analyze and optimize query performance by revealing bottlenecks.