Oracle SQL 深度试题及确切答案剖析

OracleSQL深度试题及确切答案剖析考试时间：______分钟总分：______分姓名：______一、选择题（每题只有一个正确答案，请将正确选项字母填入括号内）1.在OracleSQL中，以下哪个窗口函数允许用户根据分区内的行号进行排名？A.RANK()B.DENSE_RANK()C.NTILE()D.ROW_NUMBER()OVER()2.以下关于Oracle中`WITH`子句（CommonTableExpression,CTE）的描述，哪项是正确的？A.CTE必须在SELECT语句之前定义。B.CTE的结果集在查询中只能使用一次。C.使用CTE的查询不能包含HINTS。D.CTE是一个永久性的数据库对象。3.查询员工与其直接上级的姓名，假设存在员工表`EMPLOYEES`（`EMP_ID`,`EMP_NAME`,`MANAGER_ID`），以下哪个SQL语句是正确的自连接写法？A.SELECTE1.EMP_NAMEASEmpName,E2.EMP_NAMEASManagerNameFROMEMPLOYEESE1,EMPLOYEESE2WHEREE1.MANAGER_ID=E2.EMP_ID;B.SELECTE1.EMP_NAMEASEmpName,E2.EMP_NAMEASManagerNameFROMEMPLOYEESE1JOINEMPLOYEESE2ONE1.MANAGER_ID=E2.EMP_ID;C.SELECTE1.EMP_NAMEASEmpName,E2.EMP_NAMEASManagerNameFROMEMPLOYEESE1,EMPLOYEESE2WHEREE1.EMP_ID=E2.MANAGER_ID;D.SELECTE1.EMP_NAMEASEmpName,E2.EMP_NAMEASManagerNameFROMEMPLOYEESE1LEFTOUTERJOINEMPLOYEESE2ONE1.EMP_ID=E2.MANAGER_ID;4.在OracleSQL中，`MINUS`操作符与`EXCEPT`操作符的主要区别在于？A.`MINUS`是标准SQL，而`EXCEPT`是Oracle特有。B.`MINUS`去除重复行，而`EXCEPT`不去除。C.`MINUS`去除左侧集合中存在于右侧集合的行，而`EXCEPT`去除右侧集合中存在于左侧集合的行。D.它们没有区别，行为完全相同。5.以下哪个OracleSQL日期函数用于将日期转换为字符串，并允许指定格式模型？A.TO_DATE()B.FROM_CHAR()C.TO_CHAR()D.DATE_FORMAT()6.查询每个部门的平均工资，并只显示平均工资高于全公司平均工资的部门信息。假设有`DEPARTMENTS`（`DEPT_ID`,`DEPT_NAME`）和`EMPLOYEES`（`EMP_ID`,`EMP_NAME`,`DEPT_ID`,`SALARY`）表，以下哪个SQL语句最符合要求？A.SELECTD.DEPT_ID,D.DEPT_NAME,AVG(E.SALARY)FROMDEPARTMENTSDJOINEMPLOYEESEOND.DEPT_ID=E.DEPT_IDGROUPBYD.DEPT_ID,D.DEPT_NAMEHAVINGAVG(E.SALARY)>(SELECTAVG(SALARY)FROMEMPLOYEES);B.SELECTD.DEPT_ID,D.DEPT_NAME,AVG(E.SALARY)FROMDEPARTMENTSD,EMPLOYEESEWHERED.DEPT_ID=E.DEPT_IDGROUPBYD.DEPT_ID,D.DEPT_NAMEHAVINGAVG(E.SALARY)>AVG(SALARY)OVER();C.SELECTD.DEPT_ID,D.DEPT_NAME,AVG(E.SALARY)FROMDEPARTMENTSDJOINEMPLOYEESEOND.DEPT_ID=E.DEPT_IDGROUPBYD.DEPT_ID,D.DEPT_NAMEWHEREAVG(E.SALARY)>ALL(SELECTSALARYFROMEMPLOYEES);D.SELECTD.DEPT_ID,D.DEPT_NAME,AVG(E.SALARY)FROMDEPARTMENTSDJOINEMPLOYEESEOND.DEPT_ID=E.DEPT_IDGROUPBYD.DEPT_ID,D.DEPT_NAMEHAVINGAVG(E.SALARY)>(SELECTMAX(AVG(SALARY))FROM(SELECTAVG(SALARY)ASAvgSalFROMEMPLOYEESGROUPBYDEPT_ID));7.使用`ROW_NUMBER()OVER(...)`窗口函数时，如果分区内有多行具有相同的排序值，那么这些行的`ROW_NUMBER`赋值将是？A.随机分配。B.都会是该分区内的最小行号（如1）。C.都会被赋予相同的行号。D.NULL。8.以下哪个语句片段展示了如何使用`LAG()`函数获取当前行之前一行员工的工资？A.LAG(SALARY,1,DEFAULT0)OVER(ORDERBYEMP_ID)B.LAG(SALARY,1)OVER(ORDERBYEMP_ID)C.LEAD(SALARY,-1)OVER(ORDERBYEMP_ID)D.PREVIOUS(SALARY,1)OVER(ORDERBYEMP_ID)9.假设有`SALES`（`SALE_ID`,`PRODUCT_ID`,`AMOUNT`,`SALE_DATE`）表。查询每个产品在2023年的月销售总额，并对结果按产品ID和销售额进行降序排列。以下哪个SQL语句是正确的？A.SELECTPRODUCT_ID,TO_CHAR(SALE_DATE,'YYYY-MM')ASSaleMonth,SUM(AMOUNT)FROMSALESWHEREYEAR(SALE_DATE)=2023GROUPBYPRODUCT_ID,SALE_DATEORDERBYPRODUCT_IDDESC,SUM(AMOUNT)DESC;B.SELECTPRODUCT_ID,EXTRACT(YEARFROMSALE_DATE)ASSaleYear,SUM(AMOUNT)FROMSALESWHERESALE_DATEBETWEEN'2023-01-01'AND'2023-12-31'GROUPBYPRODUCT_ID,SaleYearORDERBYPRODUCT_IDDESC,SUM(AMOUNT)DESC;C.SELECTPRODUCT_ID,TO_CHAR(SALE_DATE,'YYYY-MM')ASSaleMonth,SUM(AMOUNT)FROMSALESWHEREYEAR(SALE_DATE)=2023GROUPBYPRODUCT_ID,TO_CHAR(SALE_DATE,'YYYY-MM')ORDERBYPRODUCT_IDDESC,SUM(AMOUNT)DESC;D.SELECTPRODUCT_ID,MONTHS_BETWEEN(SALE_DATE,TRUNC(SALE_DATE))ASMonthNum,SUM(AMOUNT)FROMSALESWHERESALE_DATE>=TRUNC(TO_DATE('2023-01-01','YYYY-MM-DD'))GROUPBYPRODUCT_ID,MonthNumORDERBYPRODUCT_IDDESC,SUM(AMOUNT)DESC;10.在OracleSQL中，使用`INTERSECT`操作符时，要求参与运算的两个查询结果集必须满足什么条件？A.必须具有完全相同的列名和数据类型。B.必须具有相同的表名。C.列的数量可以不同，但每一列的数据类型必须兼容。D.结果集的顺序必须完全一致。二、多选题（每题有多个正确答案，请将正确选项字母填入括号内）1.以下哪些是OracleSQL中合法的连接类型？A.内连接(INNERJOIN)B.左外连接(LEFTOUTERJOIN)C.右外连接(RIGHTOUTERJOIN)D.全外连接(FULLOUTERJOIN)E.自连接(SELFJOIN)2.使用公用表表达式(CTE)的优点包括？A.提高查询的可读性和可维护性。B.允许递归查询。C.可能改善查询性能（尤其是在物化CTE时）。D.CTE的结果集在查询期间只能使用一次。E.替代了子查询的所有使用场景。3.以下关于Oracle中`CASE`语句的描述，哪些是正确的？A.可以使用`CASE`实现简单的条件判断（如`CASEWHEN...THEN...ELSE...END`）。B.可以使用`CASE`实现搜索条件判断（如`CASEWHENcolumn1='value1'ORcolumn2>10THEN...END`）。C.`CASE`语句中的条件必须是布尔表达式。D.`CASE`语句的结果数据类型由`THEN`子句中表达式的数据类型决定。E.搜索条件`CASE`不能包含子查询。4.查询包含管理超过5名员工的所有部门的信息。假设有`EMPLOYEES`（`EMP_ID`,`DEPT_ID`）表，以下哪些SQL语句可以实现此目标？A.SELECTDISTINCTE.DEPT_IDFROMEMPLOYEESEGROUPBYE.DEPT_IDHAVINGCOUNT(E.EMP_ID)>5;B.SELECTD.DEPT_IDFROMDEPARTMENTSDJOIN(SELECTDEPT_IDFROMEMPLOYEESGROUPBYDEPT_IDHAVINGCOUNT(EMP_ID)>5)EOND.DEPT_ID=E.DEPT_ID;C.SELECTE.DEPT_IDFROMEMPLOYEESEGROUPBYE.DEPT_IDWHERECOUNT(E.EMP_ID)>5;D.SELECTDEPT_IDFROM(SELECTDEPT_ID,COUNT(*)ASEmpCountFROMEMPLOYEESGROUPBYDEPT_ID)WHEREEmpCount>5;5.以下关于OracleSQL执行计划的说法，哪些是正确的？A.执行计划显示了SQL查询的执行步骤和访问路径。B.可以使用`EXPLAINPLANFOR`语句或`EXPLAIN`关键字来生成执行计划。C.执行计划中的操作符（如TABLEACCESS,INDEXSEEK）表示不同的数据访问方式。D.优化器总是能生成绝对最优的执行计划。E.通过分析执行计划可以识别查询性能瓶颈。三、填空题（请将答案填写在横线上）1.用来对查询结果集进行分区，并在每个分区内应用窗口函数的子句是________。2.在OracleSQL中，用于将字符串转换为日期的函数是________。3.`DECODE`函数可以看作是OracleSQL中的________结构。4.要查询所有工资高于其所在部门平均工资的员工，除了使用子查询，还可以利用窗口函数________结合________过滤条件实现。5.`UNIONALL`与`UNION`的主要区别在于________。四、问答题（请根据要求作答）1.请解释OracleSQL中`LEFTOUTERJOIN`（或`LEFTJOIN`）与`INNERJOIN`的区别。在什么场景下你会选择使用`LEFTOUTERJOIN`？2.假设有`ORDERS`（`ORDER_ID`,`CUSTOMER_ID`,`ORDER_DATE`）和`ORDER_ITEMS`（`ORDER_ID`,`PRODUCT_ID`,`QUANTITY`）表。请编写一个SQL查询，列出每个客户的总订单数量以及总订单项数量（即每个订单的所有项数之和）。要求使用公用表表达式(CTE)或WITH子句来组织查询，使查询结构清晰。3.查询所有员工的信息，包括其直接上级的姓名。如果员工没有直接上级（例如，该员工是最高管理者），则上级姓名应为'NULL'。请编写满足此要求的SQL语句，可以使用自连接或其他合适的技术。4.请详细说明在OracleSQL中使用`WITH`子句（CTE）相比多层嵌套子查询有哪些优势？请至少列举三点。试卷答案一、选择题1.D解析：`ROW_NUMBER()OVER(...)`函数根据指定的排序顺序为结果集中的每一行分配一个唯一的行号，该行号从1开始，并在每个分区内部递增。这正是根据行号进行排名的需求。2.A解析：`WITH`子句（CTE）允许将一个临时的、命名的结果集定义为查询的一部分，该结果集可以在查询中引用一次或多次。CTE必须在SELECT之前定义，其结果集仅在当前SQL查询中可见，不是永久对象。使用CTE的查询可以包含HINTS。3.C解析：选项C正确地使用了自连接，通过将`EMPLOYEES`表自身连接，连接条件是`EMPLOYEE`的`ID`等于`MANAGER_ID`，从而找到每个员工的上级。选项A使用了隐式连接（逗号分隔表并使用WHERE子句），可能导致笛卡尔积。选项B使用了显式内连接，但连接条件错误。选项D使用了左外连接，但连接条件错误。4.C解析：`MINUS`操作符用于从左侧查询结果集中移除那些也出现在右侧查询结果集中的行。`EXCEPT`操作符的行为与`MINUS`相同。因此，主要区别在于它们处理重复行的规则相反。`MINUS`是Oracle的早期特性，`EXCEPT`是标准SQL语法。5.C解析：`TO_CHAR(date,format_model)`函数用于将Oracle日期或时间戳数据类型转换为字符串，`format_model`参数指定了目标字符串的格式。6.A解析：该问题需要使用分组和子查询。首先，需要按部门分组计算平均工资(`AVG(E.SALARY)`）。然后，使用`HAVING`子句过滤出那些平均工资大于全公司平均工资的部门。全公司平均工资通过一个子查询`(SELECTAVG(SALARY)FROMEMPLOYEES)`获取。选项B中的`OVER()`用法不正确，选项C中的子查询`ALL`用法错误，选项D中的子查询嵌套过于复杂且逻辑错误。7.D解析：`ROW_NUMBER()`为每个分区内的行分配一个唯一的整数序号。如果多个行具有相同的排序值（由`ORDERBY`子句指定），则这些行的`ROW_NUMBER`会连续递增，但不会跳过数字。例如，如果排序值相同的有3行，它们的`ROW_NUMBER`将是1,2,3。如果使用`DENSE_RANK()`，相同排序值的行会获得相同的排名，下一个排名会连续。使用`RANK()`，相同排序值的行会获得相同的排名，但下一个排名会跳过相应的数字。8.B解析：`LAG(SALARY,offset,default)`函数返回当前行的前一行（或根据`offset`指定的偏移量之前的行）的`SALARY`值。`offset`为1表示前一行。`ORDERBYEMP_ID`指定了行是按`EMP_ID`排序的。`DEFAULT`用于指定当`offset`指定的行不存在时（例如，第一行）的默认值。选项A使用了`DEFAULT0`，意味着第一行的`LAG`值将是0，这可能不是预期。选项C使用`LEAD`，它是获取后一行值的函数。选项D中的`PREVIOUS`不是标准的Oracle窗口函数。9.C解析：需要按产品ID和月份分组，并计算月销售额总和。使用`TO_CHAR(SALE_DATE,'YYYY-MM')`将销售日期格式化为'YYYY-MM'格式，以便按月分组。`YEAR(SALE_DATE)=2023`用于筛选2023年的数据。`GROUPBYPRODUCT_ID,TO_CHAR(SALE_DATE,'YYYY-MM')`正确地进行分组。`ORDERBY`子句按产品ID降序和销售额降序排列。选项A使用`YEAR(SALE_DATE)`但`GROUPBY`中包含了`SALE_DATE`，错误。选项B使用`EXTRACT(YEAR)`和范围条件，分组只按`PRODUCT_ID`和`SaleYear`，错误。选项D使用`MONTHS_BETWEEN`和`TRUNC`计算月份，但逻辑和分组不清晰。10.A解析：`INTERSECT`操作符仅返回两个查询结果集中都存在的行。为了确保匹配，这两个查询必须产生相同数量和类型的列，并且对应列的数据类型必须兼容（可以相同或具有隐式转换关系）。二、多选题1.A,B,C,D,E解析：这些都是OracleSQL中定义的连接类型。内连接(`INNERJOIN`)返回匹配的行；左外连接(`LEFTOUTERJOIN`或`LEFTJOIN`)返回左侧查询的所有行，以及与右侧查询匹配的行（如果存在）；右外连接(`RIGHTOUTERJOIN`或`RIGHTJOIN`)返回右侧查询的所有行，以及与左侧查询匹配的行（如果存在）；全外连接(`FULLOUTERJOIN`或`FULLJOIN`)返回两个查询的所有行，无论是否匹配；自连接(`SELFJOIN`)是连接同一张表以关联自身行的特殊类型。2.A,B,C解析：CTE的主要优点是提高查询的可读性和维护性（A），允许编写递归查询来处理层级数据（B），并且有时可以通过物化CTE（使用`WITHREADPAST`或`WITHFASTREFRESH`的物化视图）来改善性能（C）。CTE的结果集在查询中可以引用多次（D错误）。CTE可以替代子查询，但并非所有场景都适用，有时子查询更简洁（E错误）。3.A,B,D解析：Oracle支持两种`CASE`语句：简单`CASE`（`CASEexpressionWHENvalue1THENresult1...ELSEresultNEND`）和搜索`CASE`（`CASEWHENcondition1THENresult1...ELSEresultNEND`）。简单`CASE`的条件是单个表达式，搜索`CASE`的条件可以是任何有效的布尔表达式（包括子查询）（A,B正确）。`CASE`语句的结果类型由`THEN`或`ELSE`子句中表达式结果的类型决定，不一定需要布尔表达式（C错误）。搜索`CASE`可以包含子查询（E错误）。4.A,B,D解析：选项A使用分组和`HAVING`子句，正确计算每个部门的员工数并筛选出大于5的。选项B使用子查询先找出符合条件的部门ID，再进行连接。选项C使用分组和`WHERE`子句，但在`WHERE`子句中不能使用聚合函数`COUNT()`直接过滤分组结果，这是错误的。选项D使用子查询进行聚合并在外层查询中筛选，也是正确的。5.A,B,C,E解析：执行计划是数据库执行SQL查询的详细步骤说明（A）。可以使用`EXPLAINPLANFORquery;`或`EXPLAIN;`（在12c及以后版本中，`EXPLAIN`会返回执行计划表）来生成（B）。计划中的操作符（如`TABLEACCESS`,`INDEXSEEK`,`HASHJOIN`）表示不同的操作方式（C）。优化器尽力生成最优计划，但并非绝对，可能受统计信息、HINT或版本限制影响（D错误）。分析执行计划有助于识别如全表扫描、高成本连接等性能瓶颈（E正确）。三、填空题1.OVER解析：`OVER()`子句是窗口函数（如`ROW_NUMBER()`,`SUM()OVER()`,`LAG()`）的一部分，它定义了函数作用的窗口或分区（由`PARTITIONBY`,`ORDERBY`等子句指定）。2.TO_DATE解析：`TO_DATE(string,format)`函数将符合指定格式的字符串转换为Oracle日期或时间戳数据类型。3.IF-THEN-ELSE解析：`DECODE`函数根据提供的条件表达式值，返回对应`THEN`子句的值。其行为类似于编程语言中的`IFconditionTHENvalue1ELSEvalue2END`结构。4.RANK/COUNT,DENSE_RANK/COUNT,WHERE,CURRENTROW解析：可以使用`RANK()`或`DENSE_RANK()`函数为每个员工计算在其所在部门内的工资排名或计数。然后在外层查询中，使用`WHERE`子句结合`CURRENTROW`或子查询来过滤出排名/计数大于1（或等于部门平均人数）的行。例如，`WHERERANK()OVER(PARTITIONBYDEPT_IDORDERBYSALARYDESC)=1`或`WHERECOUNT(*)OVER(PARTITIONBYDEPT_ID)>AVG(SALARY)OVER(PARTITIONBYDEPT_ID)`（虽然第二个例子直接比较平均工资可能更简洁）。5.是否去除结果集中的重复行解析：`UNION`操作符会自动去除结果集中的重复行，只返回唯一的行集。而`UNIONALL`操作符则将两个查询的结果集直接合并，不去除重复行。这是它们最核心的区别。四、问答题1.`LEFTOUTERJOIN`（或`LEFTJOIN`）与`INNERJOIN`的区别在于返回的结果集不同。`INNERJOIN`仅返回两个表中连接条件匹配的行，即两个表都有对应记录的情况。而`LEFTOUTERJOIN`返回左侧表的所有行，以及与右侧表匹配的行（如果存在）。如果左侧表的某行在右侧表中没有匹配的行，该行仍会出现在结果集中，但其右侧表的相关列将显示为`NULL`。选择使用`LEFTOUTERJOIN`的场景是当你需要包含左侧表的所有记录，即使它们在右侧表中没有对应项。例如，你想列出所有部门及其部门经理姓名，即使某些部门没有经理（其`MANAGER_ID`为`NULL`），你也需要这些部门的信息，这时就需要使用左连接。2.```sqlWITHCustomerOrderStatsAS(SELECTO.CUSTOMER_ID,COUNT(DISTINCTO.ORDER_ID)ASTotalOrders,SUM(OI.QUANTITY)ASTotalOrderItemsFROMORDERSOLEFTJOINORDER_ITEMSOIONO.ORDER_ID=OI.ORDER_IDGROUPBYO.CUSTOMER_ID)SELECTCOS.CUSTOMER_ID,COS.TotalOrders,COS.TotalOrderItemsFROMCustomerOrderStatsCOS;```解析：该问题要求计算每个客户的总订单数和