SQL Query Pertanyaan dan Jawaban Wawancara

Ques 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.

Ques 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;

Ques 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;

Ques 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;

Ques 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());