SQL机试创新题目与答案揭晓

SQL机试创新题目与答案揭晓考试时间：______分钟总分：______分姓名：______一、选择题（每题2分，共20分）1.以下哪个SQL子句用于对查询结果进行分组？A.WHEREB.GROUPBYC.ORDERBYD.HAVING2.在SQL中，以下哪个函数用于计算某列的平均值？A.SUM()B.AVG()C.COUNT()D.MAX()3.执行以下SQL语句后，结果将显示：SELECTuser_id,COUNT(*)FROMordersGROUPBYuser_idHAVINGCOUNT(*)>5;A.所有用户的订单数量B.订单数量大于5的用户及其订单数C.订单数量大于5的订单详情D.所有订单数量大于5的用户ID4.关于LEFTJOIN和INNERJOIN的区别，以下说法正确的是：A.LEFTJOIN一定返回左表所有记录B.INNERJOIN返回两个表匹配的记录C.LEFTJOIN只返回左表有匹配的记录D.INNERJOIN返回左表所有记录5.以下哪个窗口函数用于为分区内的行分配排名？A.ROW_NUMBER()B.RANK()C.DENSE_RANK()D.以上都是6.在SQL中，以下哪个操作符用于模糊查询？A.=B.<>C.LIKED.IN7.事务处理中的COMMIT语句的作用是：A.回滚事务B.提交事务，使修改永久生效C.开始事务D.设置事务隔离级别8.以下哪个SQL语句用于创建索引？A.CREATEINDEXB.ADDINDEXC.INDEXCREATED.SETINDEX9.在SQL中，以下哪个聚合函数可以忽略NULL值？A.COUNT(*)B.COUNT(column)C.SUM(column)D.AVG(column)10.以下关于子查询的说法，正确的是：A.子查询必须放在WHERE子句中B.相关子查询可以引用外部查询的列C.子查询只能返回单列结果D.所有子查询都可以用连接替代二、多选题（每题3分，共15分）1.以下哪些SQL子句可以与GROUPBY一起使用？A.WHEREB.HAVINGC.ORDERBYD.LIMIT2.以下哪些是常见的SQL连接类型？A.INNERJOINB.LEFTJOINC.RIGHTJOIND.CROSSJOIN3.窗口函数中，可以使用的子句包括：A.PARTITIONBYB.ORDERBYC.ROWSD.GROUPBY4.以下哪些场景适合使用窗口函数？A.计算每个部门的员工排名B.计算每个用户的累计消费金额C.统计每个产品的销售数量D.查找重复的订单记录5.以下哪些操作可以提高SQL查询性能？A.创建适当的索引B.避免使用SELECT*C.合理使用JOIND.增加查询返回的数据量三、简答题（每题10分，共30分）1.某电商数据库中有用户表(users)和订单表(orders)，users表包含user_id和username字段，orders表包含order_id、user_id和amount字段。请编写SQL语句查询每个用户的总消费金额，并按消费金额降序排列。2.某公司员工表(employees)包含employee_id、name、department和salary字段，请使用窗口函数查询每个部门内薪资排名前3的员工信息。3.某系统日志表(logs)包含log_id、user_id、action_type和action_time字段，请编写SQL语句查询"在2023年11月1日之后，每个用户最后一次执行'action_type'为'login'操作的时间"。四、综合应用题（共35分）某在线教育平台数据库包含以下表：-users表：user_id（用户ID），username（用户名），register_date（注册日期）-courses表：course_id（课程ID），course_name（课程名称），price（课程价格）-enrollments表：enrollment_id（报名记录ID），user_id（用户ID），course_id（课程ID），enroll_date（报名日期），completion_status（完成状态：completed/incomplete）请完成以下SQL任务：1.查询"报名了课程数量超过3门且完成状态为'completed'的用户数量"（10分）2.查询"每个课程的报名人数、完成人数以及完成率（完成人数/报名人数），并按完成率降序排列"（15分）3.分析"注册时间在2023年1月1日之后，且报名了至少2门课程但完成率低于50%的用户"，请说明SQL实现思路并编写查询语句（10分）试卷答案一、选择题1.答案：B解析：GROUPBY子句用于对查询结果进行分组，而WHERE用于过滤行，ORDERBY用于排序结果，HAVING用于过滤分组后的结果。2.答案：B解析：AVG()函数专门用于计算某列的平均值，SUM()用于求和，COUNT()用于计数行数，MAX()用于找出最大值。3.答案：B解析：该语句先按user_id分组，然后HAVINGCOUNT(*)>5筛选出订单数量大于5的用户组，最终显示这些组的user_id和订单数量。4.答案：B解析：INNERJOIN只返回两个表中匹配的记录，LEFTJOIN返回左表所有记录（即使右表无匹配），选项C错误（LEFTJOIN返回左表所有记录），选项D错误（INNERJOIN不返回左表所有记录）。5.答案：D解析：ROW_NUMBER()、RANK()和DENSE_RANK()都是窗口函数，用于为分区内的行分配排名，但处理并列排名的方式不同。6.答案：C解析：LIKE操作符用于模糊查询（如使用通配符%或_），=用于精确匹配，<>用于不等于，IN用于匹配列表中的值。7.答案：B解析：COMMIT语句提交事务，使修改永久生效；ROLLBACK回滚事务，BEGIN开始事务，设置隔离级别是SETTRANSACTIONISOLATIONLEVEL。8.答案：A解析：CREATEINDEX是标准SQL语句用于创建索引；ADDINDEX不是标准语法，INDEXCREATE和SETINDEX均错误。9.答案：B解析：COUNT(column)忽略NULL值，只计算非NULL值的数量；COUNT(*)计算所有行（包括NULL），SUM和AVG忽略NULL值但COUNT(column)更直接。10.答案：B解析：相关子查询可以引用外部查询的列；子查询可放在WHERE、FROM等子句中，不必须放在WHERE；子查询可返回多列；并非所有子查询都能用连接替代。二、多选题1.答案：B,C解析：HAVING用于过滤分组后的结果，ORDERBY用于排序结果；WHERE用于过滤行（不能直接与GROUPBY一起使用），LIMIT用于限制返回行数。2.答案：A,B,C,D解析：常见SQL连接类型包括INNERJOIN（内连接）、LEFTJOIN（左连接）、RIGHTJOIN（右连接）、CROSSJOIN（交叉连接）。3.答案：A,B,C解析：窗口函数中可使用PARTITIONBY（分区）、ORDERBY（排序）、ROWS（行框架）；GROUPBY是分组子句，不能在窗口函数中使用。4.答案：A,B解析：窗口函数适合分组内排名（如部门员工排名）、累计值（如用户累计消费）；统计销售数量和查找重复记录通常用GROUPBY和HAVING。5.答案：A,B,C解析：创建索引、避免SELECT*、合理使用JOIN可提高性能；增加查询返回数据量会降低性能。三、简答题1.答案：```sqlSELECTu.user_id,u.username,SUM(o.amount)AStotal_amountFROMusersuJOINordersoONu.user_id=o.user_idGROUPBYu.user_id,u.usernameORDERBYtotal_amountDESC;```解析：使用JOIN连接users和orders表，按user_id分组，计算每个用户的总消费金额（SUM(o.amount)），然后按总金额降序排列。2.答案：```sqlSELECTemployee_id,name,department,salary,RANK()OVER(PARTITIONBYdepartmentORDERBYsalaryDESC)ASrankFROMemployeesWHERErank<=3;```解析：使用窗口函数RANK()OVER(PARTITIONBYdepartmentORDERBYsalaryDESC)为每个部门内员工按薪资降序排名，然后筛选出排名前3的员工（需注意WHERE子句中不能直接使用窗口函数别名，实际应使用子查询）。3.答案：```sqlSELECTuser_id,MAX(action_time)ASlast_login_timeFROMlogsWHEREaction_type='login'ANDaction_time>'2023-11-01'GROUPBYuser_id;```解析：筛选出action_type为'login'且action_time在2023年11月1日之后的记录，按user_id分组，使用MAX(action_time)获取每个用户最后一次登录时间。四、综合应用题1.答案：```sqlSELECTCOUNT(DISTINCTuser_id)ASuser_countFROMenrollmentsWHEREcompletion_status='completed'GROUPBYuser_idHAVINGCOUNT(course_id)>3;```解析：先筛选出完成状态为'completed'的报名记录，按user_id分组，统计每个用户报名的课程数量（COUNT(course_id)），使用HAVING筛选出课程数量大于3的用户，最后计算这些用户的数量。2.答案：```sqlSELECTc.course_id,c.course_name,COUNT(e.enrollment_id)AStotal_enrollments,SUM(CASEWHENpletion_status='completed'THEN1ELSE0END)AScompleted_count,ROUND(SUM(CASEWHENpletion_status='completed'THEN1ELSE0END)*100.0/COUNT(e.enrollment_id),2)AScompletion_rateFROMcoursescLEFTJOINenrollmentseONc.course_id=e.course_idGROUPBYc.course_id,c.course_nameORDERBYcompletion_rateDESC;```解析：使用LEFTJOIN连接courses和enrollments表保留所有课程，按course_id分组，计算总报名人数（COUNT(e.enrollment_id)）、完成人数（CASEWHEN统计），计算完成率（完成人数/总报名人数），最后按完成率降序排列。3.答案：实现思路：先筛选注册时间在2023年1月1日之后的用户，计算每个用户报名的课程数量和完成率，再筛选报名至少2门课程且完成率低于50%的用户。SQL语句：```sqlSELECTu.user_id,u.username,COUNT(e.course_id)ASenroll