2025年SQL考试试题及答案

2025年SQL考试试题及答案一、单项选择题（每题2分，共10分）1.以下关于SQLJOIN操作的描述中，正确的是（）A.LEFTJOIN会返回右表所有记录，左表匹配的记录B.INNERJOIN仅返回两表中满足连接条件的记录C.FULLOUTERJOIN在MySQL中可直接使用D.CROSSJOIN会提供两表记录数的差值结果答案：B2.若要为表t_user的username字段创建一个前缀索引（取前10个字符），正确的SQL语句是（）A.CREATEINDEXidx_usernameONt_user(username(10))B.CREATEINDEXidx_username(10)ONt_user(username)C.ALTERTABLEt_userADDINDEXidx_username(username(10))D.CREATEINDEXidx_usernameONt_user(username)USINGHASH(10)答案：A3.事务隔离级别为“可重复读”（REPEATABLEREAD）时，可能出现的问题是（）A.脏读B.不可重复读C.幻读D.所有读问题都被解决答案：C4.以下关于窗口函数的说法，错误的是（）A.窗口函数使用OVER子句定义窗口范围B.ROW_NUMBER()会为相同值的行分配不同的序号C.RANK()在遇到相同值时会跳过后续序号D.AVG()作为窗口函数时不会减少结果行数答案：C（RANK()遇到相同值时会重复序号但不跳过，DENSE_RANK()才会跳过）5.对于JSONB类型字段（存储用户偏好数据），若要查询“偏好中包含键'favorite_color'且值为'red'”的记录，正确的条件表达式是（）A.user_preferences->'favorite_color'='red'B.user_preferences@>'{"favorite_color":"red"}'::jsonbC.user_preferences?'favorite_color'ANDuser_preferences->>'favorite_color'='red'D.user_preferences>>'{favorite_color}'='red'答案：C（?检查键是否存在，->>提取值）二、填空题（每空2分，共10分）1.在GROUPBY子句中，若要对分组后的结果进一步筛选，需使用__________子句。答案：HAVING2.视图（VIEW）是一个虚拟表，其数据基于__________的查询结果，本身不存储数据。答案：基表（或基础表）3.EXPLAIN语句的作用是__________。答案：显示SQL语句的执行计划（或分析查询执行步骤）4.公共表表达式（CTE）通过__________关键字定义，可在后续查询中引用。答案：WITH5.若要限制SELECT语句返回的记录数，MySQL中使用__________，PostgreSQL中使用__________。答案：LIMIT；LIMIT（注：实际两者均支持LIMIT，但MySQL可用LIMITn，PostgreSQL也支持LIMITn，此处可能用户需区分，正确应为MySQL和PostgreSQL均用LIMIT，但更严谨的答案是MySQL用LIMIT，PostgreSQL也用LIMIT，或补充OFFSET，此处可能题目设计为考察基础，故答案为LIMIT）三、简答题（每题8分，共32分）1.简述索引的优缺点。答案：优点：加速数据查询，减少全表扫描的I/O消耗；优化排序和分组操作（如B树索引可直接利用索引顺序）；支持唯一性约束（如UNIQUE索引）。缺点：增加存储开销（索引需要额外空间）；写操作（INSERT/UPDATE/DELETE）变慢（需同步更新索引）；过多索引可能导致查询优化器选择错误索引，影响性能；维护索引需要额外的CPU资源（如索引重建）。2.子查询与JOIN在使用场景上的主要区别是什么？答案：子查询适用于逻辑上需分步处理的场景（如先计算一个中间结果集，再用该结果过滤主查询），或需要返回标量值（单行单列）的情况（如WHERE子句中的IN、EXISTS）。JOIN更适合多表数据关联查询，通过连接条件直接关联表，通常执行效率更高（优化器可更有效地规划连接顺序和方式）。子查询可能因嵌套层级过深导致可读性下降，而JOIN通过显式连接条件更易理解。此外，相关子查询（依赖外层查询的子查询）的执行效率可能低于JOIN，因为需逐行执行。3.事务的ACID特性分别指什么？并说明“隔离性”的作用。答案：ACID是原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）的缩写。原子性保证事务的所有操作要么全部成功，要么全部回滚；一致性确保事务执行前后数据库状态符合业务规则；隔离性控制多个事务并发执行时的相互影响；持久性保证事务提交后数据永久保存。隔离性的作用是通过隔离级别（如读未提交、读已提交等）限制事务间的可见性，防止脏读、不可重复读、幻读等问题，确保并发事务的执行结果与串行执行结果一致（或接近）。4.窗口函数与聚合函数的核心区别是什么？举例说明。答案：聚合函数（如SUM、AVG）会将多行数据聚合为一行，减少结果行数；窗口函数（如SUM()OVER()）在保留原行数的基础上，对每个行计算一个基于窗口范围的聚合值。例如，计算每个员工的薪资与部门平均薪资的差值时，聚合函数需先按部门分组得到平均薪资，再与原表JOIN；而窗口函数可直接在SELECT子句中使用AVG(salary)OVER(PARTITIONBYdept_id)获取部门平均薪资，无需分组，结果保留所有员工记录。四、综合题（第1题20分，第2题28分，共48分）（注：以下题目基于某电商数据库，表结构如下）-用户表t_user（user_idINTPRIMARYKEY,usernameVARCHAR(50),register_timeTIMESTAMP）-订单表t_order（order_idBIGINTPRIMARYKEY,user_idINT,total_amountDECIMAL(10,2),create_timeTIMESTAMP,statusTINYINTCOMMENT'1-未支付，2-已支付，3-已取消'）-订单详情表t_order_detail（detail_idBIGINTPRIMARYKEY,order_idBIGINT,product_idINT,quantityINT,priceDECIMAL(10,2)）-商品表t_product（product_idINTPRIMARYKEY,product_nameVARCHAR(100),categoryVARCHAR(20),cost_priceDECIMAL(10,2)）-日志表t_log（log_idBIGINTPRIMARYKEY,user_idINT,actionVARCHAR(20)COMMENT'如login、add_cart、place_order',log_timeTIMESTAMP,extra_infoJSONBCOMMENT'扩展信息，如{"device":"mobile","os":"iOS"}'）1.请编写SQL语句，查询2024年1月1日至2024年6月30日期间注册的用户，且这些用户在注册后30天内有过至少1笔已支付（status=2）的订单，要求返回用户ID、注册时间、首单金额（首单指该用户最早的已支付订单）。答案：```sqlWITHuser_regAS(SELECTuser_id,register_timeFROMt_userWHEREregister_timeBETWEEN'2024-01-0100:00:00'AND'2024-06-3023:59:59'),user_first_orderAS(SELECTo.user_id,o.total_amountASfirst_order_amount,MIN(o.create_time)OVER(PARTITIONBYo.user_id)ASfirst_order_timeFROMt_orderoINNERJOINuser_regurONo.user_id=ur.user_idWHEREo.status=2ANDo.create_time<=ur.register_time+INTERVAL'30days')SELECTur.user_id,ur.register_time,ufo.first_order_amountFROMuser_regurINNERJOIN(SELECTuser_id,first_order_amount,first_order_timeFROMuser_first_orderQUALIFYROW_NUMBER()OVER(PARTITIONBYuser_idORDERBYfirst_order_time)=1)ufoONur.user_id=ufo.user_id;```（注：使用CTE先筛选注册用户，再通过窗口函数计算每个用户的首单时间，最后用QUALIFY子句（或子查询中的ROW_NUMBER）获取首单记录）2.业务需求：分析2024年Q4（10-12月）用户加购（action='add_cart'）行为，需输出以下指标：（1）每个用户的加购次数；（2）加购时使用手机（extra_info->>'device'='mobile'）的次数占比；（3）加购商品中，成本价（t_product.cost_price）超过100元的商品数量（需关联订单详情表获取加购的商品ID）；（4）按加购次数降序排列，取前100名用户。要求：用一条SQL语句实现，需处理可能的NULL值（如extra_info中无device字段时视为非手机）。答案：```sqlSELECTl.user_id,COUNT()ASadd_cart_count,ROUND(SUM(CASEWHENl.extra_info->>'device'='mobile'THEN1ELSE0END)1.0/COUNT(),4)ASmobile_add_cart_ratio,COUNT(DISTINCTduct_id)AShigh_cost_product_countFROMt_loglLEFTJOINt_order_detailodONl.extra_info->>'order_detail_id'=od.detail_id::VARCHARLEFTJOINt_productpdONduct_id=duct_idWHEREl.action='add_cart'ANDl.log_timeBETWEEN'2024-10-0100:00:00'AND'2024-12-3123:59:59'GROUP