SQL Query 面试题与答案

问题 61. Explain the purpose of the SQL XML functions (XMLAGG, XMLPARSE, XMLQUERY, etc.).

XML functions in SQL are used to process XML data. XMLAGG is used to concatenate XML values, XMLPARSE is used to convert a string to XML, and XMLQUERY is used to extract data from XML.

问题 62. What is the purpose of the SQL PIVOT and UNPIVOT operators?

The PIVOT operator is used to rotate rows into columns, while the UNPIVOT operator is used to rotate columns into rows.

Example:

SELECT * FROM (SELECT department, salary FROM Employee) AS SourceTable PIVOT (SUM(salary) FOR department IN ([Dept1], [Dept2], [Dept3])) AS PivotTable;

问题 63. Write a SQL query to find the cumulative sum of a column in a result set.

SELECT column, SUM(column) OVER (ORDER BY some_order_column) AS CumulativeSum FROM table;

问题 64. Explain the purpose of the SQL SESSION_USER and SYSTEM_USER functions.

SESSION_USER returns the current username, and SYSTEM_USER returns the login name of the current user.

Example:

SELECT SESSION_USER, SYSTEM_USER;

问题 65. What is the purpose of the SQL JSON functions (JSON_VALUE, JSON_QUERY, etc.)?

JSON functions in SQL are used to process JSON data. JSON_VALUE is used to extract a scalar value, and JSON_QUERY is used to extract an object or an array.

Example:

SELECT JSON_VALUE(json_column, '$.key') AS Value FROM table;