SQL Query Questions et reponses d'entretien

Question 66. Explain the purpose of the SQL ROW and RANGE clauses in window functions.

ROW and RANGE are used in the OVER() clause of window functions to define the window or subset of rows for the calculation. ROW refers to a physical count, while RANGE considers values within a specified range.

Question 67. Write a SQL query to find the employees with the highest salary in each department using the DENSE_RANK() function.

SELECT department, employee_name, salary FROM (SELECT department, employee_name, salary, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rnk FROM Employee) AS ranked WHERE rnk = 1;

Question 68. What is the purpose of the SQL TRY...CATCH statement?

The TRY...CATCH statement is used to handle errors in a SQL Server stored procedure. It allows for structured error handling and graceful error recovery.

Example:

BEGIN TRY -- SQL statements END TRY BEGIN CATCH -- Handling of errors END CATCH;

Question 69. Explain the purpose of the SQL STRING_AGG() function.

The STRING_AGG() function is used to concatenate values from multiple rows into a single string with a specified separator.

Example:

SELECT department, STRING_AGG(employee_name, ', ') AS EmployeeList FROM Employee GROUP BY department;

Question 70. Write a SQL query to find the employees who joined the company in the last N months.

SELECT * FROM Employee WHERE JOIN_DATE >= DATEADD(MONTH, -N, GETDATE());