SQL Query 面接の質問と回答

質問 56. What is the purpose of the SQL CROSS APPLY operator?

The CROSS APPLY operator is used to invoke a table-valued function for each row returned by the outer query. It is similar to the INNER JOIN clause.

Example:

SELECT * FROM table1 CROSS APPLY function(table1.column) AS alias;

質問 57. Write a SQL query to find the employees who have the highest salary in each department.

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

質問 58. Explain the purpose of the SQL MERGE statement.

The MERGE statement is used to perform insert, update, or delete operations on a target table based on the results of a join with a source table. It is also known as an 'upsert' operation.

Example:

MERGE INTO target_table USING source_table ON condition WHEN MATCHED THEN UPDATE SET column1 = value1 WHEN NOT MATCHED THEN INSERT (column1, column2) VALUES (value1, value2);

質問 59. What is the purpose of the SQL WINDOW functions?

WINDOW functions perform a calculation across a set of table rows related to the current row. They are used with the OVER() clause to define a window or a subset of rows for the calculation.

Example:

SELECT employee_name, salary, AVG(salary) OVER (PARTITION BY department) AS AvgSalary FROM Employee;

質問 60. Write a SQL query to find the employees with the second-highest salary in each department.

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