SQL Query 面试题与答案

问题 51. Explain the purpose of the SQL GROUP_CONCAT() function.

The GROUP_CONCAT() function is used to concatenate values from multiple rows into a single string, grouped by a specified column.

Example:

SELECT department, GROUP_CONCAT(employee_name) FROM Employee GROUP BY department;

问题 52. What is a common use case for the SQL COALESCE() function?

The COALESCE() function is used to return the first non-null expression among its arguments. A common use case is handling null values in a query.

Example:

SELECT COALESCE(column1, 'DefaultValue') FROM table;

问题 53. Explain the purpose of the SQL ROLLUP operator.

The ROLLUP operator is used in conjunction with the GROUP BY clause to generate subtotals and grand totals for a set of columns in the result set.

Example:

SELECT department, city, SUM(salary) FROM Employee GROUP BY ROLLUP (department, city);

问题 54. Write a SQL query to find the nth highest salary without using the LIMIT clause.

SELECT salary FROM Employee e1 WHERE n-1 = (SELECT COUNT(DISTINCT salary) FROM Employee e2 WHERE e1.salary <= e2.salary);

问题 55. Explain the purpose of the SQL LEAD() and LAG() functions.

The LEAD() function is used to access the next row's data in the result set, while the LAG() function is used to access the previous row's data.

Example:

SELECT employee_name, salary, LEAD(salary) OVER (ORDER BY salary) AS NextSalary FROM Employee;