sql笔试题及答案

sql笔试题及答案选择题（20分，每题2分）A.SELECTB.GETC.EXTRACTD.SHOWA.GROUPB.SORTC.ORDERD.RANKA.COUNTB.SUMC.AVGD.TOTALA.=B.!=C.LIKED.INA.WHEREB.HAVINGC.GROUPBYD.ORDERBYA.LEFTJOINB.RIGHTJOINC.INNERJOIND.FULLJOINA.INSERTINTOB.ADDTOC.CREATED.APPENDA.LIMITB.TOPC.RESTRICTD.CUTA.UCASE()B.UPPER()C.CAPITALIZE()D.BIG()A.多行子查询B.多列子查询C.单行单列子查询D.相关子查询填空题（20分，每空2分）1.SQL语言的全称是______。2.在SQL中，使用______关键字可以连接多个表。3.用于计算表中记录总数的聚合函数是______。4.在SQL中，使用______子句可以对结果进行过滤。5.在SQL中，使用______函数可以获取当前日期和时间。6.在SQL中，使用______关键字可以创建一个新表。7.在SQL中，使用______运算符可以进行模糊查询。8.在SQL中，使用______关键字可以对结果集进行分组后过滤。9.在SQL中，使用______语句可以更新表中的数据。10.在SQL中，使用______关键字可以对结果进行分页。简答题（20分，每题5分）1.请解释SQL中INNERJOIN、LEFTJOIN和RIGHTJOIN的区别。2.请解释SQL中HAVING子句和WHERE子句的区别。3.请解释SQL中事务的ACID特性。4.请解释SQL中窗口函数和聚合函数的区别。编程题（20分，每题10分）1.假设有一个"employees"表，包含以下字段：employee_id（员工ID）、employee_name（员工姓名）、department（部门）、salary（薪资）。请编写SQL语句，查询各部门员工薪资的平均值，并按照平均薪资从高到低排序。2.假设有一个"orders"表，包含以下字段：order_id（订单ID）、customer_id（客户ID）、order_date（订单日期）、amount（订单金额）。请编写SQL语句，查询每个客户的订单总数和订单总金额，并筛选出订单总金额超过10000的客户。综合应用题（20分）假设有一个电子商务数据库，包含以下表结构：1.customers表：customer_id（客户ID），customer_name（客户姓名），email（邮箱地址），registration_date（注册日期）2.products表：product_id（产品ID），product_name（产品名称），price（价格），category（类别）3.orders表：order_id（订单ID），customer_id（客户ID），order_date（订单日期），total_amount（订单总金额）4.order_items表：item_id（项目ID），order_id（订单ID），product_id（产品ID），quantity（数量），unit_price（单价）请编写SQL语句解决以下问题：1.查询过去6个月内注册的客户中，购买了至少3种不同类别产品的客户及其购买总金额。2.查询每个产品类别的销售总额，以及该类别中销售最好的产品名称。3.查询订单金额最高的前5个订单的详细信息，包括客户名称、订单日期和订单金额。4.查询平均订单金额超过500的客户名称及其订单数量。---标准答案及解析选择题答案及解析1.A.SELECT解析：SELECT语句用于从数据库表中检索数据。GET、EXTRACT和SHOW都不是标准的SQL关键字。2.C.ORDER解析：ORDERBY子句用于对结果集进行排序。GROUP用于分组，SORT不是标准SQL关键字，RANK是一个函数。3.C.AVG解析：AVG()函数用于计算平均值。COUNT()用于计数，SUM()用于求和，TOTAL()不是标准SQL函数。4.C.LIKE解析：LIKE运算符用于通配符匹配。=用于精确匹配，!=用于不等于，IN用于指定多个可能的值。5.C.GROUPBY解析：GROUPBY子句用于对结果集进行分组。WHERE用于过滤行，HAVING用于过滤组，ORDERBY用于排序。6.C.INNERJOIN解析：INNERJOIN返回两个表中匹配的记录。LEFTJOIN返回左表的所有记录和右表中匹配的记录，RIGHTJOIN返回右表的所有记录和左表中匹配的记录，FULLJOIN返回两个表中的所有记录。7.A.INSERTINTO解析：INSERTINTO语句用于向表中插入新数据。ADDTO不是标准SQL语法，CREATE用于创建表或数据库，APPEND不是标准SQL关键字。8.A.LIMIT和B.TOP解析：LIMIT和TOP都用于限制返回的记录数，但LIMIT是MySQL和PostgreSQL等数据库中的关键字，而TOP是SQLServer和Access中的关键字。RESTRICT和CUT不是标准SQL关键字。9.B.UPPER()解析：UPPER()函数用于将字符串转换为大写。UCASE()在某些数据库中可用，但不是标准SQL函数，CAPITALIZE()和BIG()不是标准SQL函数。10.C.单行单列子查询解析：单行单列子查询返回单个值，可以与比较运算符一起使用。多行子查询返回多行，多列子查询返回多列，相关子查询依赖于外部查询。填空题答案及解析1.SQL语言的全称是StructuredQueryLanguage（结构化查询语言）。2.在SQL中，使用JOIN关键字可以连接多个表。3.用于计算表中记录总数的聚合函数是COUNT()。4.在SQL中，使用WHERE子句可以对结果进行过滤。5.在SQL中，使用NOW()或GETDATE()函数可以获取当前日期和时间，具体取决于数据库系统。6.在SQL中，使用CREATE关键字可以创建一个新表。7.在SQL中，使用LIKE运算符可以进行模糊查询。8.在SQL中，使用HAVING关键字可以对结果集进行分组后过滤。9.在SQL中，使用UPDATE语句可以更新表中的数据。10.在SQL中，使用LIMIT或OFFSET关键字可以对结果进行分页，具体取决于数据库系统。简答题答案及解析1.请解释SQL中INNERJOIN、LEFTJOIN和RIGHTJOIN的区别。解析：-INNERJOIN（内连接）：只返回两个表中匹配的记录。如果表A中的记录在表B中没有匹配项，则这些记录不会出现在结果中。-LEFTJOIN（左连接）：返回左表（第一个表）的所有记录，以及右表中匹配的记录。如果右表中没有匹配项，则结果中右表的字段将显示为NULL。-RIGHTJOIN（右连接）：返回右表（第二个表）的所有记录，以及左表中匹配的记录。如果左表中没有匹配项，则结果中左表的字段将显示为NULL。实际应用中，LEFTJOIN和RIGHTJOIN可以互换，只需交换表的位置即可。INNERJOIN用于只需要匹配数据的情况，而LEFTJOIN和RIGHTJOIN用于需要保留一侧表所有数据的情况。2.请解释SQL中HAVING子句和WHERE子句的区别。解析：-WHERE子句用于在分组之前过滤行。它应用于原始数据，基于单个行的条件进行筛选。-HAVING子句用于在分组之后过滤组。它应用于聚合函数的结果，基于组的条件进行筛选。简单来说，WHERE在GROUPBY之前执行，而HAVING在GROUPBY之后执行。WHERE不能包含聚合函数，而HAVING可以包含聚合函数。在实际应用中，如果需要在分组前过滤行，使用WHERE；如果需要在分组后过滤组，使用HAVING。3.请解释SQL中事务的ACID特性。解析：事务是SQL中的一系列操作，作为一个单一的逻辑单元执行。事务具有ACID特性：-原子性（Atomicity）：事务中的所有操作要么全部完成，要么全部不完成。如果事务中的任何操作失败，整个事务将回滚到事务开始前的状态。-一致性（Consistency）：事务必须使数据库从一个一致的状态转换到另一个一致的状态。事务的执行不应破坏数据库的完整性约束。-隔离性（Isolation）：并发执行的事务是隔离的，一个事务的执行不应影响其他事务的执行。数据库系统通过隔离级别（如READUNCOMMITTED、READCOMMITTED、REPEATABLEREAD、SERIALIZABLE）来控制事务之间的可见性。-持久性（Durability）：一旦事务提交，它对数据库的修改就是永久性的，即使系统发生故障，修改也不会丢失。ACID特性确保了数据库操作的可靠性和一致性，是数据库系统的重要特性。4.请解释SQL中窗口函数和聚合函数的区别。解析：聚合函数和窗口函数都是SQL中用于计算数据的函数，但它们有显著区别：-聚合函数（如COUNT、SUM、AVG等）将多行数据聚合为单个结果行，通常会与GROUPBY子句一起使用。-窗口函数（如ROW_NUMBER、RANK、DENSE_RANK、SUM()OVER()等）也在多行上计算，但它们不会将多行合并为一行，而是为每一行返回一个计算结果。关键区别在于：-聚合函数会减少结果集中的行数，而窗口函数不会。-窗口函数可以访问分区内的行，而不需要显式地连接表。-窗口函数可以在不使用GROUPBY的情况下对结果进行分组计算。窗口函数在需要排名、移动平均、累计总和等计算时特别有用，而聚合函数更适合于需要对数据进行汇总统计的场景。编程题答案及解析1.假设有一个"employees"表，包含以下字段：employee_id（员工ID）、employee_name（员工姓名）、department（部门）、salary（薪资）。请编写SQL语句，查询各部门员工薪资的平均值，并按照平均薪资从高到低排序。解析：这道题需要使用GROUPBY子句按部门分组，然后使用AVG()函数计算每个部门的平均薪资，最后使用ORDERBY子句按平均薪资降序排序。MySQL/PostgreSQL/SQLite版本：```sqlSELECTdepartment,AVG(salary)ASaverage_salaryFROMemployeesGROUPBYdepartmentORDERBYaverage_salaryDESC;```SQLServer版本：```sqlSELECTdepartment,AVG(salary)ASaverage_salaryFROMemployeesGROUPBYdepartmentORDERBYaverage_salaryDESC;```Oracle版本：```sqlSELECTdepartment,AVG(salary)ASaverage_salaryFROMemployeesGROUPBYdepartmentORDERBYaverage_salaryDESC;```解析要点：-使用GROUPBYdepartment按部门分组-使用AVG(salary)计算每个部门的平均薪资-使用ASaverage_salary为计算结果列指定别名-使用ORDERBYaverage_salaryDESC按平均薪资降序排序-不同数据库的语法略有不同，但基本逻辑相同2.假设有一个"orders"表，包含以下字段：order_id（订单ID）、customer_id（客户ID）、order_date（订单日期）、amount（订单金额）。请编写SQL语句，查询每个客户的订单总数和订单总金额，并筛选出订单总金额超过10000的客户。解析：这道题需要使用GROUPBY子句按客户ID分组，然后使用COUNT()和SUM()函数分别计算每个客户的订单总数和订单总金额，最后使用HAVING子句筛选出订单总金额超过10000的客户。MySQL/PostgreSQL/SQLite版本：```sqlSELECTcustomer_id,COUNT(order_id)AStotal_orders,SUM(amount)AStotal_amountFROMordersGROUPBYcustomer_idHAVINGSUM(amount)>10000;```SQLServer版本：```sqlSELECTcustomer_id,COUNT(order_id)AStotal_orders,SUM(amount)AStotal_amountFROMordersGROUPBYcustomer_idHAVINGSUM(amount)>10000;```Oracle版本：```sqlSELECTcustomer_id,COUNT(order_id)AStotal_orders,SUM(amount)AStotal_amountFROMordersGROUPBYcustomer_idHAVINGSUM(amount)>10000;```解析要点：-使用GROUPBYcustomer_id按客户ID分组-使用COUNT(order_id)计算每个客户的订单总数-使用SUM(amount)计算每个客户的订单总金额-使用AS为计算结果列指定别名-使用HAVINGSUM(amount)>10000筛选出订单总金额超过10000的客户-注意：这里使用HAVING而不是WHERE，因为我们需要在分组后对聚合结果进行过滤-不同数据库的语法略有不同，但基本逻辑相同综合应用题答案及解析1.查询过去6个月内注册的客户中，购买了至少3种不同类别产品的客户及其购买总金额。解析：这道题需要：-首先筛选出过去6个月内注册的客户-然后找到这些客户购买的订单-再通过order_items表和products表找出购买的产品的类别-统计每个客户购买的类别数量-筛选出购买了至少3种不同类别产品的客户-计算这些客户的购买总金额MySQL/PostgreSQL/SQLite版本：```sqlSELECTc.customer_id,c.customer_name,SUM(o.total_amount)AStotal_spentFROMcustomerscJOINordersoONc.customer_id=o.customer_idWHEREc.registration_date>=DATE_SUB(CURRENT_DATE(),INTERVAL6MONTH)ANDc.customer_idIN(SELECToi.order_idFROMorder_itemsoiJOINproductspONduct_id=duct_idWHEREoi.order_idIN(SELECTorder_idFROMordersWHEREcustomer_idIN(SELECTcustomer_idFROMcustomersWHEREregistration_date>=DATE_SUB(CURRENT_DATE(),INTERVAL6MONTH)))GROUPBYoi.order_idHAVINGCOUNT(DISTINCTp.category)>=3)GROUPBYc.customer_id,c.customer_nameORDERBYtotal_spentDESC;```解析要点：-使用DATE_SUB()函数计算6个月前的日期-使用JOIN连接多个表，获取所需数据-使用子查询找出购买了至少3种不同类别产品的订单-使用COUNT(DISTINCTp.category)统计不同类别产品的数量-使用HAVING子句筛选出符合条件的订单-最后使用GROUPBY和SUM()计算每个客户的购买总金额-使用ORDERBY按购买总金额降序排序2.查询每个产品类别的销售总额，以及该类别中销售最好的产品名称。解析：这道题需要：-按产品类别分组-计算每个类别的销售总额-找出每个类别中销售金额最高的产品MySQL/PostgreSQL/SQLite版本：```sqlSELECTp.category,SUM(oi.quantityoi.unit_price)AScategory_total,(SELECTduct_nameFROMproductsp2JOINorder_itemsoi2ONduct_id=duct_idWHEREp2.category=p.categoryGROUPBYduct_nameORDERBYSUM(oi2.quantityoi2.unit_price)DESCLIMIT1)AStop_productFROMproductspJOINorder_itemsoiONduct_id=duct_idGROUPBYp.categoryORDERBYcategory_totalDESC;```解析要点：-使用JOIN连接products和order_items表-使用GROUPBY按产品类别分组-使用SUM(oi.quantityoi.unit_price)计算每个类别的销售总额-使用子查询找出每个类别中销售金额最高的产品-在子查询中，再次使用SUM(oi2.quantityoi2.unit_price)计算每个产品的销售金额-使用LIMIT1获取销售金额最高的产品-最后使用ORDERBY按类别销售总额降序排序3.查询订单金额最高的前5个订单的详细信息，包括客户名称、订单日期和订单金额。解析：这道题需要：-找出订单金额最高的前5个订单-获取这些订单的客户名称、订单日期和订单金额MySQL/PostgreSQL/SQLite版本：```s