SQL Query 面试题与答案

问题 36. What is the difference between UNION and UNION ALL?

UNION combines and returns unique rows from multiple SELECT statements, while UNION ALL returns all rows including duplicates.

Example:

SELECT column1 FROM table1 UNION SELECT column1 FROM table2;

问题 37. Write a SQL query to find the average salary of employees in each department, excluding departments with fewer than five employees.

SELECT department, AVG(salary) FROM Employee GROUP BY department HAVING COUNT(*) >= 5;

问题 38. Explain the purpose of the SQL INDEX.

An INDEX is a performance optimization feature that allows faster retrieval of records from a table. It is created on one or more columns of a table.

Example:

CREATE INDEX index_name ON table(column);

问题 39. What is a self-referencing foreign key?

A self-referencing foreign key is a foreign key in a table that references the primary key of the same table, creating a relationship within the same table.

问题 40. Write a SQL query to find the total salary expense for each department.

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