SQL Query 面试题与答案

问题 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.

问题 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;

问题 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;

问题 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;

问题 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());