2025年SQL专项测试突破卷

2025年SQL专项测试突破卷考试时间：______分钟总分：______分姓名：______一、假设有三张表：员工表（Employee，字段：EmployeeID,姓名,部门ID,入职日期），部门表（Department，字段：部门ID,部门名称），薪资表（Salary，字段：员工ID,月薪,发薪月份）。请写出SQL语句查询：每个部门平均薪资最高的前2名员工的姓名和部门名称。要求使用窗口函数实现。二、请写出SQL语句，查询出在2023年入职，并且月薪高于其所在部门平均月薪的所有员工的姓名和月薪。至少使用子查询。三、请写出SQL语句，查询出所有部门的员工人数，并按员工人数从多到少排序。如果员工人数相同，则按部门名称升序排序。四、请写出SQL语句，查询出所有员工的名字及其直接上级的名字。假设存在一个字段ManagerID在员工表中，表示其直接上级的员工ID。如果某员工没有上级（例如是部门主管），则上级名字显示为'无'。五、假设有一个订单表（Order，字段：订单ID,客户ID,订单日期,订单金额），请写出SQL语句查询：每个客户的最近3笔订单的订单ID和订单金额。要求结果按客户ID升序，每笔订单按订单日期降序排列。六、请解释SQL中JOINON和USING子句的区别，并说明在什么情况下使用哪一个更合适。七、请解释SQL中聚合函数（如SUM,COUNT,AVG,MAX,MIN）与分组（GROUPBY）的关系。如果需要对分组后的结果再次进行分组或应用聚合函数，应该如何操作？八、假设查询一个大型表的特定数据（例如millionsofrows）非常慢，请列出至少三种可能的优化方法，并简要说明其原理。九、请写出SQL语句，将表A中的数据插入到表B中，要求只插入表A中存在而表B中不存在的记录。表A和表B结构相同。十、请解释什么是SQL的“事务”，并说明事务需要满足的四个基本特性（ACID）分别代表什么。试卷答案一、```sqlWITHRankedSalariesAS(SELECTd.部门名称,e.姓名,e.月薪,AVG(e.月薪)OVER(PARTITIONBYd.部门ID)ASAvgDeptSalary,RANK()OVER(PARTITIONBYd.部门IDORDERBYAVG(e.月薪)DESC)ASSalaryRankFROMEmployeeeJOINDepartmentdONe.部门ID=d.部门IDJOINSalarysONe.EmployeeID=s.员工IDWHEREs.发薪月份BETWEEN'2023-01-01'AND'2023-12-31')SELECT姓名,部门名称FROMRankedSalariesWHERESalaryRank<=2;```解析：使用CTE（RankedSalaries）计算每个部门的平均月薪，并为每个部门内的员工按平均月薪进行排名。首先通过JOIN连接员工、部门和薪资表，并筛选出2023年的薪资记录。然后使用窗口函数`AVG()OVER(PARTITIONBY部门ID)`计算每个部门的平均月薪。接着使用`RANK()OVER(PARTITIONBY部门IDORDERBYAVG(月薪)DESC)`为每个部门内的员工按平均月薪从高到低排名。最后从CTE中选择排名前2的员工姓名和部门名称。二、```sqlSELECTe.姓名,e.月薪FROMEmployeeeWHEREe.入职日期BETWEEN'2023-01-01'AND'2023-12-31'ANDe.月薪>(SELECTAVG(s.月薪)FROMSalarysWHEREs.员工ID=e.EmployeeID)```解析：使用子查询来比较每个员工的月薪与其所在部门的平均月薪。外层查询选择2023年入职的员工及其月薪。子查询计算与该员工`EmployeeID`相同的记录的`月薪`平均值。通过在外层查询的条件中比较员工月薪是否大于子查询结果，筛选出月薪高于其所在部门平均月薪的员工。三、```sqlSELECTd.部门名称,COUNT(e.EmployeeID)AS员工人数FROMDepartmentdLEFTJOINEmployeeeONd.部门ID=e.部门IDGROUPBYd.部门名称ORDERBY员工人数DESC,d.部门名称ASC;```解析：使用`LEFTJOIN`连接部门表和员工表，确保即使部门没有员工也能被包含在结果中。使用`COUNT(e.EmployeeID)`计算每个部门的员工数量。通过`GROUPBYd.部门名称`按部门分组。最后使用`ORDERBY`对结果进行排序，首先按员工人数降序排列，员工人数相同的部门按部门名称升序排列。四、```sqlSELECTe1.姓名AS员工名字,COALESCE(e2.姓名,'无')AS上级名字FROMEmployeee1LEFTJOINEmployeee2ONe1.ManagerID=e2.EmployeeID;```解析：使用`LEFTJOIN`连接员工表自身，连接条件是当前员工`e1`的`ManagerID`等于另一个员工`e2`的`EmployeeID`。这表示`e2`是`e1`的上级。选择`e1.姓名`作为员工名字，使用`COALESCE(e2.姓名,'无')`来处理没有上级的情况，如果`e2.姓名`为NULL（即`e1`没有上级），则显示'无'。五、```sqlWITHRankedOrdersAS(SELECT客户ID,订单ID,订单金额,ROW_NUMBER()OVER(PARTITIONBY客户IDORDERBY订单日期DESC,订单IDDESC)ASOrderRankFROMOrder)SELECT客户ID,订单ID,订单金额FROMRankedOrdersWHEREOrderRank<=3ORDERBY客户IDASC,订单日期DESC,订单IDDESC;```解析：使用CTE（RankedOrders）为每个客户的订单按订单日期降序、订单ID降序排列，并赋予一个行号`OrderRank`。`PARTITIONBY客户ID`确保行号是按客户分组的。然后从CTE中选择行号小于等于3的记录，即每个客户的最近3笔订单。最后对结果按客户ID升序、订单日期降序、订单ID降序排序，确保输出格式整齐。六、解析：`ON`子句用于指定两个表进行JOIN操作的连接条件，该条件在执行JOIN时使用。`USING`子句用于当两个表中具有相同名称的列时，自动匹配这些同名列作为连接条件，它简化了语法，但只能用于等值连接且列名必须完全相同。当连接条件涉及多个列或列名不同时，应使用`ON`子句；当连接条件简单且列名完全相同时，可以使用`USING`子句。七、解析：聚合函数（SUM,COUNT,AVG,MAX,MIN）用于对一组值进行计算，产生单个汇总值。`GROUPBY`子句用于将查询结果按一个或多个列的值分组，通常与聚合函数一起使用，以便对每个组应用聚合函数。如果需要对分组后的结果再次进行分组或应用聚合函数，可以在外层查询再次使用`GROUPBY`子句对聚合函数的结果进行分组，或者使用窗口函数在内部聚合之前就进行计算。八、解析：1.增加合适的索引：为经常用于JOIN、WHERE、ORDERBY子句的列添加索引，可以显著加快查找速度。2.优化查询语句：避免使用SELECT*,只选择需要的列；避免在WHERE子句中对索引列使用函数或进行计算；使用EXPLAIN分析查询执行计划，找出瓶颈。3.分批处理或使用临时表：对于非常大的数据集，可以考虑分批次查询或将中间结果存入临时表。九、```sqlINSERTINTO表BSELECT*FROM表AWHERENOTEXISTS(SELECT1FROM表BWHERE表A.列1=表B.列1AND表A.列2=表B.列2AND...--列名需与表B一致);```解析：使用`INSERTINTO...SELECT`语句从表A选择数据。`SELECT*`表示选择所有列。`WHERENOTEXISTS`子句用于确保只有当表B中不存在与表A当前行完全匹配的记录时，才插入该行。子查询中`SELECT1`是占位符，关键是`WHERE`条件，需要指定表B中用于匹配的列，并确保这些列的值与表A当前行的值相同。如果表A和表B结构完全相同，可以简化子查询条件为`WHERENOTEXISTS(SELECT1FROM表B)`.（注意：此SQL假设主键或唯一约束能唯一标识记录，或所有列组合唯一）。十、解析：事务是数据库管理系统（DBMS）执行操作的一个逻辑工作单元，这些操作要么全部执行，要么全部不执行，以确保数据库的一致性。事务需要满足ACID特性：1