Oracle SQL 面试试题与标准答案呈现

OracleSQL面试试题与标准答案呈现考试时间：______分钟总分：______分姓名：______一、单项选择题1.在OracleSQL中，下列关于`DISTINCT`和`GROUPBY`语句的描述，正确的是（）。A.`DISTINCT`可以对多列进行去重，而`GROUPBY`只能对单列去重B.`DISTINCT`会对所有列进行去重，`GROUPBY`只会对分组列去重C.`SELECTDISTINCTcol1,col2`表示col1和col2的组合不能重复D.`GROUPBY`产生的结果集列数必须少于`SELECT`子句的列数2.下列关于Oracle中`NVL`、`NVL2`和`COALESCE`函数的描述，错误的是（）。A.`NVL(col1,col2)`如果col1为NULL，则返回col2，否则返回col1B.`NVL2(col1,col2,col3)`如果col1为NULL，则返回col3，否则返回col2C.`COALESCE(col1,col2,col3)`返回列表中第一个非NULL值，参数个数限制为2个D.三个函数都用于处理NULL值，提高查询结果的可用性3.在多表连接查询中，执行`SELECT*FROMAJOINBONA.id=B.idWHEREA.flag=0`，如果A表中有flag=0的数据，但B表中对应A.id的数据不存在，结果集会包含（）。A.A表中所有flag=0的数据B.B表中所有数据C.空D.交叉连接产生的笛卡尔积4.关于SQL中`UNION`和`UNIONALL`的区别，下列说法正确的是（）。A.`UNION`会自动去除重复行，`UNIONALL`不会B.`UNION`不会去除重复行，`UNIONALL`会C.`UNION`和`UNIONALL`的执行效率一样高D.`UNION`后的结果集会自动按升序排序5.在Oracle中，使用窗口函数`ROW_NUMBER()OVER(PARTITIONBYdept_idORDERBYsalaryDESC)`，其含义是（）。A.根据dept_id分组，计算每个部门工资的总和B.根据dept_id分组，计算每个部门工资的平均值C.根据dept_id分组，为每组内的员工按工资降序排名，并标记行号D.根据dept_id分组，找出工资最高的员工6.关于`ROWNUM`伪列，下列说法错误的是（）。A.`ROWNUM`是按照查询结果集的物理顺序生成的B.`ROWNUM>10`的查询结果通常为空C.`ROWNUM`可以在WHERE子句中直接用于过滤D.`ROWNUM`只能使用=或<(或<=)进行比较，不能使用>(或>=)7.在SQL查询中，`EXISTS`和`IN`的主要区别在于（）。A.`EXISTS`速度比`IN`快B.`IN`可以用于子查询返回多列，而`EXISTS`不行C.`EXISTS`侧重于检查存在性，子查询只返回TRUE或FALSE，不返回具体数据D.`IN`只能用于单行子查询8.若要查询EMP表中所有不重复的部门编号（DEPTNO），以下SQL语句正确的是（）。A.SELECTDISTINCTDEPTNOFROMEMPB.SELECTDEPTNOFROMEMPGROUPBYDEPTNOC.SELECTUNIQUEDEPTNOFROMEMPD.SELECTDISTINCTROWDEPTNOFROMEMP9.下列关于`CONNECTBY`递归查询的描述，正确的是（）。A.`CONNECTBY`是用于处理递归关系的子句，通常配合`STARTWITH`和`PRIOR`使用B.`STARTWITH`必须指定根节点条件C.`PRIOR`关键字只能出现在父节点列之前D.`CONNECTBY`只能查询树形结构，不能查询层级数据10.关于Oracle中的`MERGE`语句，下列说法正确的是（）。A.`MERGE`语句只能用于插入数据，不能用于更新数据B.`MERGE`语句中必须同时包含`INSERT`和`UPDATE`子句C.`MERGE`语句允许对同一目标表进行多次操作，但要注意条件冲突D.`MERGE`语句执行效率通常低于单独的`INSERT`或`UPDATE`二、多项选择题1.下列哪些是Oracle中的聚合函数（）。A.SUMB.AVGC.COUNTD.MAXE.SUBSTR2.关于Oracle中的连接类型，以下哪些是合法的连接方式（）。A.INNERJOINB.LEFTOUTERJOINC.RIGHTOUTERJOIND.FULLOUTERJOINE.CROSSJOIN3.在使用窗口函数时，以下哪些子句是必须的（）。A.`PARTITIONBY`B.`ORDERBY`C.`OVER`D.`ROW_NUMBER()`4.关于`DECODE`函数和`CASEWHEN`语句的对比，下列说法正确的有（）。A.`DECODE`是Oracle特有的函数，语法更简洁B.`CASEWHEN`是标准SQL语法，可读性通常更好C.`DECODE`只能处理等值判断，不能处理范围判断D.`CASEWHEN`可以作为表达式直接用于SELECT、WHERE、ORDERBY等任何位置5.下列哪些SQL操作会触发触发器（）。A.INSERTB.UPDATEC.DELETED.SELECTE.MERGE6.关于索引，以下描述正确的有（）。A.索引可以大大提高查询数据的速度B.索引会占用物理存储空间C.在经常用于连接条件的列上建立索引效果最好D.索引可以加速DML操作（如INSERT和UPDATE）E.在查询条件中如果没有使用索引列，数据库可以自动使用索引7.下列哪些函数用于处理日期类型的数据（）。A.ADD_MONTHSB.MONTHS_BETWEENC.LAST_DAYD.SYSDATEE.TRUNC8.在编写SQL时，为了提高性能，以下哪些做法是正确的（）。A.避免SELECT*，只查询需要的字段B.尽量使用EXISTS代替IN进行子查询C.在WHERE子句中避免对字段进行函数运算，这会导致索引失效D.多表连接时，确保连接条件没有使用函数E.表中数据量很大时，不需要考虑分页查询9.关于`NULL`值，下列说法正确的有（）。A.NULL值表示未知或不存在B.任何与NULL进行算术运算的结果都是NULLC.在比较运算符（如=,!=,>）中，NULL的处理比较特殊D.`COUNT(*)`会计算包含NULL值的行数E.`COUNT(col)`会忽略该列为NULL的行数10.下列哪些是Oracle中的集合运算符（）。A.UNIONB.UNIONALLC.INTERSECTD.MINUSE.JOIN三、简答题与编程题1.请简述`RANK()`、`DENSE_RANK()`和`ROW_NUMBER()`这三个窗口函数在排名结果上的主要区别。2.在OracleSQL中，`WHERE`子句和`HAVING`子句的主要区别是什么？在什么情况下必须使用`HAVING`子句？3.假设有一个员工表`EMP`（包含字段：EMPNO,ENAME,DEPTNO,SAL），请编写SQL语句，查询每个部门工资最高的前3名员工（部门内按工资降序排列）。4.解释Oracle中`ROWNUM`的局限性。为什么查询`SELECT*FROMEMPWHEREROWNUM>5`通常会返回0行？5.请写出`MERGE`语句的基本语法结构，并简述它的作用。6.什么是自连接？请给出一个使用自连接查询员工及其直接管理者的示例SQL语句（假设EMP表中有MGR字段指向管理者的EMPNO）。7.如何使用`CASEWHEN`语句将一个数值型的性别字段（1表示男，2表示女）转换为字符串（男/女）？8.简述`TRUNC`函数在处理日期和数字时的区别。9.如果需要查找没有分配任何部门的员工，应该使用哪种类型的连接查询？请写出SQL语句。10.解释Oracle中`CONNECTBYPRIOR`语句在查询层级数据时的作用。试卷答案一、单项选择题1.C解析：`DISTINCT`关键字会去除查询结果中所有指定列值的重复组合；`GROUPBY`则是将数据分组，通常配合聚合函数使用。选项A错误，因为`DISTINCT`对所有列有效，`GROUPBY`也可以对多列分组；选项B错误，`GROUPBY`不会自动去重，是分组列的值可能重复；选项D错误，`GROUPBY`后的SELECT列表可以包含分组列、聚合函数或常量，不一定少于SELECT列。2.C解析：`COALESCE(col1,col2,col3)`函数可以接受多个参数，返回列表中第一个非NULL值，而不是仅限于两个参数。选项A和B描述正确；选项D描述正确。3.C解析：在SQL语句中，显式使用`JOIN`关键字默认执行的是内连接。如果A表中有`flag=0`的数据，但B表中没有对应的关联数据（即连接条件不满足），内连接会自动丢弃这些不匹配的行，因此结果集为空。4.A解析：`UNION`操作符会自动去除结果集中的重复行，并对结果进行排序；而`UNIONALL`则会保留所有行（包括重复行），且不会进行排序，因此`UNIONALL`的执行效率通常高于`UNION`。5.C解析：该函数表示按照`dept_id`进行分组，在每个组内，按照`salary`降序排列，并为每一行分配一个唯一的行号（1,2,3...）。它用于排名，而非求和或平均值。6.A解析：`ROWNUM`是Oracle在查询结果返回时分配的伪列，其分配顺序取决于查询的物理执行顺序，而不是数据本身的大小顺序。这是`ROWNUM`最核心的局限性。7.C解析：`EXISTS`侧重于判断子查询是否存在满足条件的记录，子查询只返回布尔值（TRUE/FALSE），不返回具体的数据行；而`IN`是检查外层查询的字段值是否存在于子查询返回的列表中。虽然通常`EXISTS`效率更高，但C选项关于“侧重于检查存在性”的描述是准确的。8.A解析：`SELECTDISTINCTDEPTNOFROMEMP`可以直接去重；`SELECTDEPTNOFROMEMPGROUPBYDEPTNO`也可以去重，但通常需要结合聚合函数使用（如`COUNT(*)`）或者是为了后续分组。`SELECTUNIQUE...`是错误的语法。9.C解析：`PRIOR`关键字可以出现在父节点列之前，也可以出现在子节点列之前，用于定义父子关系的方向，不一定非要在父节点列之前。10.C解析：`MERGE`语句允许根据与源表的匹配情况对目标表执行插入或更新操作，但不允许对同一行执行多次操作（例如不能既更新又插入同一行），否则会抛出ORA-30926错误。二、多项选择题1.ABCD解析：`SUM`,`AVG`,`COUNT`,`MAX`均为Oracle的聚合函数；`SUBSTR`是字符串处理函数，不属于聚合函数。2.ABCDE解析：InnerJoin,Left/Right/FullOuterJoin,CrossJoin都是Oracle支持的合法连接类型。3.ABC解析：窗口函数必须包含`OVER`关键字，这是语法要求。`PARTITIONBY`和`ORDERBY`是可选的，但为了实现特定的排名或分组统计，通常需要用到它们。`ROW_NUMBER()`是函数名。4.ABDE解析：`DECODE`是Oracle特有的简洁语法，`CASEWHEN`是标准SQL，两者功能可以互换。`CASEWHEN`语法更通用，`DECODE`更简洁。`CASEWHEN`可以处理复杂的逻辑判断（不仅仅是等值），且是标准SQL，可读性通常更好。选项C错误，`CASEWHEN`可以处理范围和复杂逻辑。5.ABCDE解析：`INSERT`,`UPDATE`,`DELETE`,`MERGE`都是数据操作语言（DML），执行这些操作会自动触发DML触发器。`SELECT`是查询语句（DQL），不会触发触发器。6.ABC解析：索引可以加速查询（A），占用存储空间（B），在连接条件上效果最好（C）。索引会降低DML操作（如INSERT,UPDATE,DELETE）的速度，因为需要维护索引结构（D错误）。数据库不会自动使用索引，优化器会根据统计信息决定是否使用，且`WHERE`条件未使用索引列时通常不会使用（E错误）。7.ABCDE解析：`ADD_MONTHS`,`MONTHS_BETWEEN`,`LAST_DAY`,`SYSDATE`,`TRUNC`均为Oracle处理日期或时间戳数据的常用函数。8.ABCD解析：避免`SELECT*`（A），`EXISTS`通常比`IN`效率高且不易受空值影响（B），避免在索引列上使用函数会导致索引失效（C），多表连接避免在连接条件上使用函数（D）。大数据量查询必须考虑分页（E错误）。9.ABCDE解析：`NULL`表示未知（A），算术运算结果为NULL（B），NULL与NULL比较结果为False（C），`COUNT(*)`统计行数（D），`COUNT(col)`忽略NULL（E）。10.ABCD解析：`UNION`,`UNIONALL`,`INTERSECT`,`MINUS`是Oracle的集合运算符；`JOIN`是连接操作，属于表操作，不属于集合运算符。三、简答题与编程题1.解析思路：*ROW_NUMBER()：无论是否有并列，都严格按照顺序生成唯一的行号（如1,2,3,4...）。*RANK()：当有并列时，排名会跳过（如1,2,2,4...），即保留排名的空缺。*DENSE_RANK()：当有并列时，排名不跳过（如1,2,2,3...），即保持排名的连续性。2.解析思路：*WHERE：用于在分组之前过滤数据，只能基于原始表的列进行条件判断。*HAVING：用于在分组之后过滤数据，通常用于对聚合函数（如SUM,AVG,COUNT）的结果进行判断。*必须使用HAVING的情况：当过滤条件中包含聚合函数时，必须使用HAVING子句。3.SQL语句解析：*需要使用窗口函数`ROW_NUMBER()`。*按`deptno`分组(`PARTITIONBYdeptno`)。*在组内按`salary`降序排序(`ORDERBYsalaryDESC`)。*筛选`rn<=3`的记录。```sqlSELECTdeptno,ename,salary,ROW_NUMBER()OVER(PARTITIONBYdeptnoORDERBYsalaryDESC)ASrnFROMempWHERErn<=3;--注意：在WHERE中直接引用窗口函数rn需要嵌套或使用子查询```**更标准的写法：*```sqlWITHRankedEmpAS(SELECTdeptno,ename,salary,ROW_NUMBER()OVER(PARTITIONBYdeptnoORDERBYsalaryDESC)ASrnFROMemp)SELECT*FROMRankedEmpWHERErn<=3;```4.解析思路：*`ROWNUM`是Oracle在返回行时分配的，分配顺序是固定的。*当执行`WHEREROWNUM>5`时，Oracle首先处理第一行，发现`ROWNUM=1`，不满足`>5`，直接丢弃。*接着处理第二行，`ROWNUM`被重新分配为2，依然不满足`>5`，丢弃。*以此类推，所有的行都会被丢弃，因为第一行的ROWNUM永远是1，不可能大于5。因此结果集为空。5.语法解析：*`MERGE`语句用于根据与源表的匹配情况对目标表执行插入或更新操作。*基本结构：```sqlMERGEINTOtarget_tableTUSINGsource_tableSON(T.condition_column=S.condition_column)WHENMATCHEDTHENUPDATESETT.col1=S.col1,T.col2=S.col2WHENNOTMATCHEDTHENINSERT(T.col1,T.col2)VALUES(S.col1,S.col2);```6.解析思路：*自连接：是指一个表自己连接自己，即同一个表在查询中被查询了两次（表别名不同）。*应用场景：通常用于处理层级结构数据，如员工表中的“经理”与“下属”关系（MGR字段）。*SQL示例：```sqlSE