21. What is a transaction in SQL?

    • Answer: A transaction is a sequence of one or more SQL operations treated as a single unit of work.
    • Example: BEGIN TRANSACTION; UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 1; COMMIT;

22. What is ACID property?

    • Answer: ACID stands for Atomicity, Consistency, Isolation, and Durability, which are key properties of a transaction.

23. How do you implement error handling in SQL?

    • Answer: Using TRY…CATCH blocks.
    • Example: BEGIN TRY; -- SQL statements END TRY BEGIN CATCH; -- Error handling END CATCH;

24. What is a trigger in SQL?

    • Answer: A trigger is a set of SQL statements that automatically executes in response to certain events on a particular table.
    • Example: CREATE TRIGGER trgAfterInsert ON Employees AFTER INSERT AS BEGIN PRINT 'Record Inserted'; END;

25. What are indexes in SQL?

    • Answer: Indexes are used to speed up the retrieval of data from a database.
    • Example: CREATE INDEX idx_name ON Employees (Name);

26. What is a clustered index?

    • Answer: A clustered index determines the physical order of data in a table and is unique for each table.
    • Example: CREATE CLUSTERED INDEX idx_EmployeeID ON Employees (EmployeeID);

27. What is a non-clustered index?

    • Answer: A non-clustered index is an index that does not alter the physical order of the table and has a separate structure from the data row.
    • Example: CREATE INDEX idx_Department ON Employees (Department);

28. How do you optimize a SQL query?

    • Answer: By using indexes, avoiding unnecessary columns in SELECT, using joins efficiently, and analyzing the execution plan.

29. What is an execution plan?

    • Answer: An execution plan is a graphical or textual representation of how SQL Server executes a query.
    • Example: EXPLAIN SELECT * FROM Employees;

30. What is a composite key?

    • Answer: A composite key is a combination of two or more columns used to create a unique identifier for a table.
    • Example: CREATE TABLE Orders (OrderID INT, ProductID INT, PRIMARY KEY (OrderID, ProductID));

31. What is the difference between DELETE and TRUNCATE?

    • Answer: DELETE removes rows one at a time and logs each deletion, whereas TRUNCATE removes all rows in a table by deallocating the data pages and does not log individual row deletions.

32. What is a cursor in SQL?

    • Answer: A cursor is a database object used to retrieve, manipulate, and navigate through a result set row by row.
    • Example: DECLARE cursor_name CURSOR FOR SELECT Name FROM Employees;

33. How do you create a recursive query?

    • Answer: Using Common Table Expressions (CTE).
    • Example: WITH RECURSIVE EmployeeCTE AS (SELECT EmployeeID, ManagerID FROM Employees WHERE ManagerID IS NULL UNION ALL SELECT e.EmployeeID, e.ManagerID FROM Employees e INNER JOIN EmployeeCTE ecte ON e.ManagerID = ecte.EmployeeID) SELECT * FROM EmployeeCTE;

34. What is a window function?

    • Answer: A window function performs calculations across a set of table rows that are somehow related to the current row.
    • Example: SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) FROM Employees;

35. What is the difference between RANK() and DENSE_RANK()?

    • Answer: RANK() assigns a rank with gaps between ranks, while DENSE_RANK() assigns consecutive ranks without gaps.
    • Example: SELECT Name, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank, DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank FROM Employees;

36. What is the COALESCE function?

    • Answer: COALESCE returns the first non-null value in a list of arguments.
    • Example: SELECT COALESCE(NULL, 'First', 'Second');

37. What is a CASE statement in SQL?

    • Answer: The CASE statement is used to execute a sequence of statements based on a set of conditions.
    • Example: SELECT Name, Salary, CASE WHEN Salary < 50000 THEN 'Low' WHEN Salary BETWEEN 50000 AND 100000 THEN 'Medium' ELSE 'High' END AS SalaryRange FROM Employees;

38. What is the difference between IN and EXISTS?

    • Answer: IN is used to compare a value against a list of values, while EXISTS is used to check the existence of a result of a subquery.
    • Example: SELECT Name FROM Employees WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE Location = 'New York');

39. What is the difference between a temporary table and a table variable?

    • Answer: Temporary tables are created in the TempDB database and can have indexes, whereas table variables are stored in memory and have more limitations.
    • Example: CREATE TABLE #TempTable (ID INT, Name VARCHAR(50)); DECLARE @TableVar TABLE (ID INT, Name VARCHAR(50));

40. 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.
    • Example: WITH EmployeeCTE AS (SELECT EmployeeID, Name FROM Employees WHERE DepartmentID = 1) SELECT * FROM EmployeeCTE;

41. How do you handle NULL values in SQL?

    • Answer: Using IS NULL and IS NOT NULL operators to check for NULL values.
    • Example: SELECT * FROM Employees WHERE ManagerID IS NULL;

42. What is the difference between CHAR and VARCHAR?

    • Answer: CHAR is a fixed-length data type, whereas VARCHAR is a variable-length data type.
    • Example: CREATE TABLE Test (FixedChar CHAR(10), VariableChar VARCHAR(10));

43. What is the GROUP BY clause used for?

    • Answer: GROUP BY is used to arrange identical data into groups.
    • Example: SELECT Department, COUNT(*) FROM Employees GROUP BY Department;

44. What is the difference between HAVING and WHERE?

    • Answer: WHERE filters rows before grouping, while HAVING filters groups after grouping.
    • Example: SELECT Department, COUNT(*) FROM Employees GROUP BY Department HAVING COUNT(*) > 10;

45. What is a self-join?

    • Answer: A self-join is a regular join, but the table is joined with itself.
    • Example: SELECT A.Name AS Employee, B.Name AS Manager FROM Employees A INNER JOIN Employees B ON A.ManagerID = B.EmployeeID;

46. What is the difference between a LEFT JOIN and a RIGHT JOIN?

    • Answer: LEFT JOIN returns all records from the left table and matched records from the right table, while RIGHT JOIN returns all records from the right table and matched records from the left table.
    • Example: SELECT * FROM Employees LEFT JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

47. What are aggregate functions?

    • Answer: Aggregate functions perform a calculation on a set of values and return a single value.
    • Example: SELECT AVG(Salary), COUNT(*), MAX(Salary), MIN(Salary), SUM(Salary) FROM Employees;

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

    • Answer: COUNT(*) counts all rows in a table, while COUNT(column) counts only non-NULL values in a specific column.
    • Example: SELECT COUNT(*) FROM Employees; SELECT COUNT(Salary) FROM Employees;

49. What is the difference between an INNER JOIN and an OUTER JOIN?

    • Answer: INNER JOIN returns only matched rows, while OUTER JOIN returns matched and unmatched rows from one or both tables.
    • Example: SELECT * FROM Employees INNER JOIN Departments ON Employees.DepartmentID = Departments.DepartmentID;

50. What is the difference between ROW_NUMBER() and RANK()?

    • Answer: ROW_NUMBER() assigns a unique number to each row, while RANK() assigns a rank to each row with gaps between ranks for duplicate values.
    • Example: SELECT Name, Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNumber, RANK() OVER (ORDER BY Salary DESC) AS Rank FROM Employees;

These questions cover a broad range of SQL topics, from basic concepts to more advanced techniques. Whether you’re preparing for an interview or looking to deepen your SQL knowledge, these questions and answers provide a solid foundation. Happy studying!