SQL interview questions Part-4

 31. Describe how to handle errors in SQL.  

    Use TRY...CATCH blocks (in SQL Server) or exception handling constructs provided by the database to catch and manage runtime errors, ensuring graceful failure or rollback.

32. What are temporary tables?  
    Temporary tables store intermediate results temporarily during a session or procedure, usually with names prefixed by # (local) or ## (global) in SQL Server.

33. Explain the difference between CHAR and VARCHAR.
⦁ CHAR is fixed-length and pads unused spaces, faster for fixed-size data.
⦁ VARCHAR is variable-length, saves space for variable data but may be slightly slower.

34. How do you perform pagination in SQL?  
    Use LIMIT and OFFSET (MySQL/PostgreSQL):

SQL

SELECT * FROM table_name ORDER BY id LIMIT 10 OFFSET 20;

Or in SQL Server:

SQL

SELECT * FROM table_name ORDER BY id OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

35. What is a composite key?  
    A primary key made up of two or more columns that uniquely identify a record.

36. How do you convert data types in SQL?  
    Using CAST() or CONVERT() functions, e.g.,

SQL

SELECT CAST(column_name AS INT) FROM table_name;

37. Explain locking and isolation levels in SQL.  
    Locks control concurrent access to data. Isolation levels (READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE) define visibility of changes between concurrent transactions, balancing consistency and performance.

38. How do you write recursive queries?  
    Using Recursive CTEs with WITH clause:

SQL

WITH RECURSIVE cte AS (  
  SELECT id, parent_id FROM table WHERE parent_id IS NULL  
  UNION ALL  
  SELECT t.id, t.parent_id FROM table t INNER JOIN cte ON t.parent_id = cte.id  
)  
SELECT * FROM cte;

39. What are the advantages of using prepared statements?  
    Improved performance (query plan reuse), security (prevents SQL injection), and ease of use with parameterized inputs.

40. How to debug SQL queries?  
    Analyze execution plans, check syntax errors, use descriptive aliases, test subqueries separately, and monitor performance metrics.