二年模拟题库第一版

北大青鸟华育网ACCP3.0二年题库(1)Oracle部分以下试题由北大青鸟华育网教师张策提供试题部分：1.考虑下列声明，那些是不合法的（）A.DECLARE v_name, v_dept VARCHAR2(14);B.DECLARE v_test NUMBER(5);C.DECLARE V_MAXSALARY NUMBER(7, 2) = 5000;D.DECLARE V_JOINDATE BOOLEAN := SYSDATE;2.EMPLOYEES表如下：LAST_NAME DEPARTMENT_ID SALARYGetz 10 3000Davis 20 1500King 20 2200Davis 30 5000…请根据表判断以下哪些SQL语句正确（）A.SELECT * FROM employeesWHERE salary > (SELECT MIN(salary) FROM employees GROUP BY department_id);B.SELECT * FROM employeesWHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id);C.SELECT DISTINCT department_id FROM employeesWHERE salary > ANY (SELECT AVG(salary FROM employees GROUP BYdepartment_id));D.SELECT department_id FROM employeesWHERE salary > ALL(SELECT AVG(salary) FROM employees GROUP BYdepartment_id);E.SELECT last_name FROM employeesWHERE salary > ANY(SELECT MAX(salary) FROM employees GROUP BYdepartment_id);F.SELECT department_id FROM employeesWHERE salary > ALL(SELECT AVG(salary) FROM employees GROUP BY AVG(salary));3.EMPLOYEES表结构如下：EMPLOYEE_ID NUMBER NOT NULL, PRIMARY KEYLAST_NAME VARCHAR2(30)FIRST_NAME VARCHAR2(30)JOB_ID NUMBERSAL NUMBERMGR_ID字段的数据类型为NUMBER，参照EMPLOYEE_ID列和DEPARTMENT_ID列（NUMBER）。

现在需要在FIRST_NAME和LAST_NMAE字段上建立名为NAME_IDX的索引，下列哪个SQL 语句能执行这项任务（）A.CREATE INDEX NAME_IDX(first_name, last_name);B.CREATE INDEX NAME_IDX(first_name AND last_name);C.CREATE INDEX NAME_IDX ON (first_name, last_name);D.CREATE INDEX NAME_IDX ON employees(first_name AND last_name);E.CREATE INDEX NAME_IDX ON employees(first_name, last_name);F.CREATE INDEX NAME_IDX FOR employees(first_name, last_name);4.Oracle会为下列哪些约束自动建立索引（）A.CheckB.UniqueC.Not nullD.Primary keyE.Foreign key5.定义游标如下：DECLARECURSOR query_cursor(v_salary) ISSELECT last_name, salary, dept_noFROM employeeWHERE salary>v_salary;这条语句为什么会错误（）A.在游标定义中不允许出现where子句B.select语句中缺少into子句C.参数未指定为标量数据类型6.检查下列代码：CREATE OR REPLACE TRIGGER update_empAFTER UPDATE ON empBEGININSERT INTO audit_table (who, audited)VALUES(USER, SYSDATE);END;你对EMP表执行了一条Update语句，影响10行数据，那么将会在AUDIT_TABLE中增加多少行数据（）A. 1B.10C.没有D.和EMP表中已有的数据行数一致7.创建存储过程中，局部变量应放在下列哪个关键字之后（）A.ISB.BEGINC.DECLAREDD.PROCEDURE8.定义存储过程如下：CREATE OR REPLACE PROCEDURE INSERT TEAM(V_ID in NUMBER,V_CITY in VARCHER2 DEFAULT ‘AUSTIN’V_NAMEin VARCHER2)ISBEGININSERT INTO TEAM (id, city,name)VALUES (v_id,v_city,v_name);COMMIT;END;以下哪些PL/SQL语句能够正确调用该过程（）A.EXECUTE INSERT_TEAM;B.EXECUTE INSERT_TEAM (V_NAME=.>”LONG HORNS”C.V_CITY=>”AUSTIN”;D.EXECUTE INSERT_TEAM (3, “AUSTIN”, “LONG HORNS”)E.EXECUTE INSERT_TEAM (V_ID := 3, V_NAME:=”LONG HORNS”,VCITY:=”AUSTIN”);F.EXECUTE INSERT_TEAM (3”LONG HORNS”);9.考察下面的函数：CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG(V_ID IN PLAYER_BAT_STAT. PLAYER_ID%TYPE)RETURN NUMBERISV_AVG NUMBER;BEGINSELECT HITS/AT_BATSINTO V_AVGFROM PLAYER_BAT_STATWHERE PLAYER_ID=V_ID;RETURN(V_AVG);END;下列哪条语句可以成功调用函数（）A.A、 SELECT CALC_PLAYER_AVG(PLAYER_ID)FROM PLAYER_BAT_STAT;B.B、 EXECUTE CALC_PLAYER_AVG(31);C.C、CALC_PLAYER(‘RUTH’);D.D、 CALC_PLAYER_AVG(31);E.START CALC_PLAYER_AVG(31)10.考察下面的触发器：CREATE OR REPLACE TRIGGER Emp_countAFTER DELETE ON Emp_tabFOR EACH ROWDECLAREN INTEGER;BEGINSELECT COUNT(*)INTO nFROM Emp tab;DBMS_OUT. PUT_LINE( ‘there are now’||n||‘employees’);END;该触发器当执行了以下的SQL语句后出错：Delete from emp_tab where emp no is equal to 7499;下列选项哪个能修正该错误（）A.将触发器类型改为before delete的B.因为触发器中不允许count，所以将count函数去掉C.去掉DBMS_OUTPUT语句，因为触发器中不允许D.去掉行级触发器的行级限制11.检查此包的定义：CREATE OR REPLACE PACKAGE COMPILE_THISISG_VALUE VARCHAR2(100);PROCEDURE A;PROCEDURE B;END COMPILE_THIS;/CREATE OR REPLACE PACKAGE BODY COMPILE_THISISPROCEDURE AISBEGING_VALUE := (‘HELLO WORLD’);END A;PROCEDURE BISBEGINC;DBMS_OUTPUT. PUT_LINE (‘PROCEDURE B CALLING C’);END B;PROCEDURE CISBEGINB;DBMS_OUTPUT. PUT_LINE (‘PROCEDURE C CALLING B’);END;END COMILE_THIS;/在编译包时，以下叙述哪项是正确的（）A.输出Procedure B calling CB.输出Procedure C calling BC.出现编译错误，因为C需要前项声明D.出现编译错误，因为B需要前项声明E.出现编译错误，因为g_value没有在过程A中声明12.存储过程定义如下：CREATE OR REPLACE PROCEDURE DELETE_PLAYER(V_ID IN NUMBER)ISBEGINDELETE FROM PLAYERWHERE V_ID=31;EXCEPTIONWHEN STATS_EXIST_EXCEPTIONTHEN DBMS_OUTPUT.PUT_LINE(‘can’t delete this player, child records exist in PLAYER_BAT_STAT table’);END;为什么该过程编译出错（）A.在STATA_EXIST_EXCEPTION后没有打逗号B.STATS_EXIST_EXCEPTION没有声明为NUMBER类型C.STATS_EXIST_EXCEPTION没有声明为exception类型D.在EXCEPTION区只允许使用预定义异常13.下列哪些叙述是正确的（）A.函数必须有返回值B.过程必须有返回值C.函数作为PL/SQL语句执行D.函数作为表达式的一部分执行E.过程的声明部分必须有返回值的数据类型14.以下的哪些语句中允许调用自定义函数（）A.NEXTVALB.SELECT命令中的HAVING子句C.ALTER TABLE命令D.SELECT命令或UPDATE命令中的FROM子句题解部分：1.考点：PL/SQL答案：ACD。