oracle plsql考试试卷.doc

姓名：_Personal Number:_班级：_Oracle SQL&PL/SQL Test一、 选择题(45 * 2)1. 用下列代码块回答问题：SQL select _(-45) as output from dual;OUTPUT-45 下列哪个单行函数无法产生这个输出？Aabs() Bceil() Cfloor() Dround()2. 用下列代码回答问题：Evaluate the set of SQL statements:CREATE TABLE dept(deptno NUMBER(2),dname VARCNAR2(14),loc VARCNAR2(13);ROLLBACK;DESCRIBE DEPTWhat is true about the set?哪个语句是正确的？A. The DESCRIBE DEPT statement displays the structure of the DEPT table.B. The ROLLBACK statement frees the storage space occupies by the DEPT table.C. The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist.D. The DESCRIBE DEPT statement displays the structure of the DEPT table only ifthere is a COMMIT statement introduced before the ROLLBACK statement.3. 用下列代码回答问题：Examine the structure of the EMPLOYEES and DEPARTMENTS tables:检查如下表结构EMPLOYEESColumn name Data type RemarksEMPLOYEE_ID NUMBER NOT NULL, Primary KeyEMP_NAME VARCHAR2 (30)JOB_ID VARCHAR2 (20)SALARY NUMBERMGR_ID NUMBER References EMPLOYEE_ID COLUMNDEPARTMENT ID NUMBER Foreign key to DEPARTMENT IDcolumn of the DEPARTMENTS tableDEPARTMENTSColumn name Data type RemarksDEPARTMENT_ID NUMBER NOT NULL, Primary KeyDEPARTMENT_NAME VARCHAR2(30)MGR_ID NUMBER References MGR_ID column of theEMPLOYEES tableEvaluate this SQL statement:SELECT employee_id, e.department_id, department_name,salaryFROM employees e, departments dWHERE e.department_id = d.department_id;Which SQL statement is equivalent to the above SQL statement?一下哪个语句和上面的语句执行结果相同？A. SELECT employee_id, department_id, department_name,salaryFROM employeesWHERE department_id IN (SELECT department_idFROM departments);B. SELECT employee_id, department_id, department_name,salaryFROM employeesNATURAL JOIN departments;C. SELECT employee_id, d.department_id, department_name,salaryFROM employees eJOIN departments dON e.department_id = d.department_id;D. SELECT employee_id, department_id, department_name,SalaryFROM employeesJOIN departmentsUSING (e.department_id, d.department_id);4. 运行下列哪个查询时会产生错误？Aselect deptno, job, sum(sal) from emp group by job, deptno; Bselect sum(sal), deptno, job from emp group by job, deptno;Cselect deptno, job, sum(sal) from emp;Dselect deptno, sum(sal), job from emp group by job, deptno;5. 检查下列SQL的输出SQL select a.deptno,a.job,b.loc,sum(a.sal) 2 from emp a,dept b 3 where a.deptno = b.deptno 4 group by a.deptno,a.job,b.loc 5 order by sum(a.sal);这个查询结果将以哪个列的顺序输出？A.DEPTON BA.JOB CB.LOC DSUM(A.SAL)6. 要查询的PROFITS表存储公司不同地区、产品类型和季度的利润信息。下列哪个SQL语句按不同地区、产品类型和季度求出平均利润大于100000美元的利润？Aselect region, prod_type, period, avg(profits) from profits where avg(prodit) 100000 group by region, prod_type, period;Bselect region, prod_type, period, avg(profits) from profits where avg(prodit) 100000 order by region, prod_type, period;Cselect region, prod_type, period, avg(profits) from profits group by region, prod_type, period having avg(prodit) 100000;Dselect region, prod_type, period, avg(profits) from profits group by region, prod_type, period having avg(prodit) select ename 2 from emp 3 where empno in 4 ( select empno 5 from expense 6 where vouch_amt 10000 );下列哪个SQL语句产生与上面相同的输出，改写成谓词运算符Aselect e.ename from emp e where exists(select x.empno from expense x where x.vouch_amt10000) and x.empno = e.empno;Bselect e.ename from emp e where exists(select x.empno from expense x where x.vouch_amt10000 and x.empno = e.empno);Cselect e.ename from emp e where x.empno = e.empno and exists(select x.empno from expense x where x.vouch_amt10000)Dselect e.ename from emp e, expense x where x.empno = e.empno and x.vouch_amt10000 and exists(select x.empno from expense x where)8. 公司销售数据库有两个表，PROFITS存储不同地区不同季度的产品销售利润，REGIONS存储每个部门地区名称、该地区总部地址和该地区副总裁姓名。下列哪个查询取得SMITHERS、FUJIMORI与LIKKARAJU领导的地区的玩具销售利润？Aselect sum(profit) from profits where region in (select region from regions where reg_head in (SMITHERS, FUJMORI, LAKKARAJU) and product =TOYS;Bselect sum(profit) from profits where region like (select region from regions where reg_head in (SMITHERS, FUJMORI, LAKKARAJU) and product =TOYS );Cselect sum(profit) from profits where region = (select region from regions where reg_head in (SMITHERS, FUJMORI, LAKKARAJU) and product =TOYS;Dselect sum(profit) from profits where region is (select region from regions where reg_head in (SMITHERS, FUJMORI, LAKKARAJU) and product =TOYS;9. 用下列代码回答问题：The EMP table contains these columns:LAST NAME VARCHAR2(25)SALARY NUMBER(6,2)DEPARTMENT_ID NUMBER(6)You need to display the employees who have not been assigned to any department. 如果需要显示还没有被分配部门的雇员You write the SELECT statement:SELECT LAST_NAME, SALARY, DEPARTMENT_IDFROM EMPWHERE DEPARTMENT_ID = NULL;What is true about this SQL statement?以下语句哪个正确A. The SQL statement displays the desired results.B. The column in the WHERE clause should be changed to display the desired results.C. The operator in the WHERE clause should be changed to display the desired results.D. The WHERE clause should be changed to use an outer join to display the desiredresults.10. 公司销售数据库包含一个PROFITS表，按产品名、销售地区和季度列出利润信息。如果要取得公司五个最畅销产品清单，可以用下列哪个SQL语句：Aselect d_name, fit from (select prod_name, profit from profits order by profit desc) where rownum = 5;Bselect d_name, fit from (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) subq where d_name = d_name;Cselect d_name, fit from (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) where rownum = 5;Dselect d_name, fit from (select prod_name, sum(profit) from profits order by sum(profit) desc) where rownum 5;12. 试图在Oracle生成表时遇到下列错误：ORA-00955-name is already used by existing object。下列哪个选项无法纠正这个错误？A以不同的用户身份生成对象。 B删除现有同名对象C改变生成对象中的列名。 D更名现有同名对象。13. SALES表中的PROFITS列声明为NUMBER(10, 2)。下列哪个值无法在这个列中存储？A5392845.324 B871039453.1 C75439289.34 D60079829.2514. 用下列代码回答问题：Evaluate the SQL statement:SELECT ROUND(TRUNC(MOD(1600,10),-1),2)FROM dual;What will be displayed?以下显示结果哪个正确？A. 0B. 1C. 0.00D. An error statement15. 用下列代码回答问题：Examine the description of the MARKS table:STD_ID NUMBER(4)STUDENT_NAME VARCHAR2(30)SUBJ1 NUMBER(3)SUBJ2 NUMBER(3)SUBJ1 and SUBJ2 indicate the marks obtained by a student in two subjects.SUBJ1 and SUBJ2分别描述的是一个学生两门课程的成绩Examine this SELECT statement based on the MARKS table:SELECT subj1+subj2 total_marks, std_idFROM marksWHERE subj1 AVG(subj1) AND subj2 AVG(subj2)ORDER BY total_marks;What is the result of the SELECT statement?以上语句的执行结果是哪一个？A. The statement executes successfully and returns the student ID and sum of all marksfor each student who obtained more than the average mark in each subject.B. The statement returns an error at the SELECT clause.C. The statement returns an error at the WHERE clause.D. The statement returns an error at the ORDER BY clause.E. This feature cannot be implemented through /SQL*Plus16. SALES表中定义的检查约束包含两个列PRODUCT_NAME与SALE_PERIOD。下列哪些选项表示定义的检查约束？选择两个。Aalter table sales add constraint ck_sales_01 check(product_type in(TOYS, HOT DOGS,PALM PILOTS);Balter table sales add constraint ck_sales_01 check(product_type in(select product_type from valid_products);Calter table sales modify(product_type varchar2(30) check(product_type in(TOYS, HOT DOGS,PALM PILOTS);Dalter table sales add(product_name varchar2(30) check(product_name AK-47);17. 你要关闭SALES表中UNIT_PRICE列的非NULL约束。下列哪个语句能完成这个操作？Aalter table sales modify(unit_prices null); Balter table sales modify(unit_prices not null);Calter table sales add(unit_prices null);Dalter table sales add(unit_prices not null);18. 在Oracle中，可用于提取日期时间类型特定部分（如年、月、日、时、分、秒）的函数有（），选择两个 A. DATEPARTB. EXTRACTC. TO_CHARD. TRUNC19. 在Oracle中，有一个教师表teacher的结构如下：ID NUMBER(5)NAME VARCHAR2(25)EMAIL VARCHAR2(50)下面哪个语句显示没有Email地址的教师姓名（）。A. SELECT name FROM teacher WHERE email = NULL;B. SELECT name FROM teacher WHERE email NULL;C. SELECT name FROM teacher WHERE email IS NULL;D. SELECT name FROM teacher WHERE email IS NOT NULL;20. 在Oracle中，下面哪条语句当COMM字段为空时显示0，不为空时显示COMM的值（）。A. SELECT ename, NVL(comm, 0) FROM emp;B. SELECT ename, NULL(comm, 0) FROM emp;C. SELECT ename, NULLIF(comm, 0) FROM emp;D. SELECT ename, DECODE(comm, NULL, 0) FROM emp;21. 在Oracle中，下面用于限制分组函数的返回值的子句是（）。A. WHEREB. HAVINGC. ORDER BYD. 无法限定分组函数的返回值22. 在Oracle中，有一个名为seq的序列对象，以下语句能返回序列值但不会引起序列值增加的是（）。A. select seq.ROWNUM from dual;B. select seq.ROWID from dual;C. select seq.CURRVAL from dual;D. select seq.NEXTVAL from dual;23. 有表一的查询结果如下，该表为学生成绩表select id,grade from student_grade ID GRADE - - 1 50 2 40 3 70 4 80 5 30 6 90 表二为补考成绩表 select id,grade from student_makeup ID GRADE - - 1 60 2 80 5 60 现在通过如下语句把补考成绩更新到成绩表中，并提交： update student_grade s set s.grade = (select t.grade from student_makeup t where s.id=t.id); commit; 请问之后查询： select GRADE from student_grade where id = 3;结果为：A. 0B. 70C. NullD. 以上都不对 24. 根据以下的在不同会话与时间点的操作，判断结果是多少 session1 session2 - - T1 select count(*) from t; -显示结果(1000)条 T2 delete from t where rownum =100; T3 begin delete from t where rownum =100; commit; end;/ T4 truncate table t; T5 select count(*) from t; -这里显示的结果是多少 A. 1000B. 900C. 800D. 0 25. 有数据”test”分别存放到char(10)和varchar2(10)类型的字段中，其实际存储长度为A. 10 10B. 4 4C. 10 4D. 4 1026. ()sql语句将为计算列Sal*12生成别名Annual SalaryA. select ename, sal*12 Annual Salary from emp;B. select ename, sal*12 “Annual Salary” from emp;C. select ename, sal*12 as Annual Salary from emp;D. select ename, sal*12 as initcap(“Annual Salary”) from emp;27. 以下对约束描述正确的是哪两个A. 在建表的时候必须定义该表的主键约束，否则不能通过语句修改B. 约束中唯一约束、主键约束都会自动创建唯一索引C. 一个字段不能同时有主键约束和唯一约束D. 一个字段可以即为主键又为外键E. 被Check约束修饰的字段不能为空28. Student表的结构如下：Std_idnumber(4)Course_idvarchar2(10)Start_datedateEnd_date date下面那两个函数可以使用在Start_date字段上A. Sum(start_date)B. avg(start_date)C. count(start_date)D. min(start_date)E. maxmum(start_date)29. Employee表的结构如下：Employee_idnumber(4)Enamevarchar(25)Job_idvarchar(10)那条sql语句能够返回ename、ename的长度、字符a在ename列的位置，并且ename的结束字符为n的所有记录。A. Select ename,length(ename),instr(ename,a) from employees where substr(ename,-1,1)=n;B. Select ename,length(ename),instr(ename,-1,1) from employees where substr(ename,-1,1)=n;C. Select ename,length(ename),substr(ename,-1,1) from employees where instr(ename,1,1)=n;D. Select ename,length(ename),substr(ename,-1,1) from employees where instr(ename,-1,1)=n;30. 下面关于完全外连接的说法那一条是正确的？A. 两个表都包含Null的值。B. 能得到一个表中所有未匹配的数据。C. 能得到所有表中所有匹配的数据。D. 能从所有表中得到所有未匹配的数据。E. 能从一个表中得到比其他表中更多的数据。F. 能从一个表中得到所有匹配和未匹配上的数据。31. 用下列代码回答问题：You want to display the titles of books that meet these criteria:按照以下条件查询出满足条件的书名1. Purchased before January 21, 20012. Price is less then $500 or greater than $900You want to sort the results by their date of purchase, starting with the most recently bought book.按照采购日期由近到远排序Which statement should you use?A. SELECT book_title FROM books WHERE price between 500 and 900 AND purchase_date 21-JAN-2001 ORDER BY purchase_date;B. SELECT book_title FROM books WHERE price IN (500,900) AND purchase_date 21-JAN-2001 ORDER BY purchase date ASC;C. SELECT book_title FROM books WHERE price 900 AND purchase_date 21-JAN-2001 ORDER BY purchase date DESC;D. SELECT book_title FROM books WHERE (price 900)AND purchase_date “$2,000.00”(Choose three)A. SELECT TO_CHAR(2000, $#,#.#) FROM dual;B. SELECT TO_CHAR (2000, $0,000.00) FROM dual;C. SELECT TO_CHAR (2000, $9,999.00) FROM dual;D. SELECT TO_CHAR (2000, $9,999.99) FROM dual;E. SELECT TO_CHAR (2000, $2,000.00) FROM dual;F. SELECT TO_CHAR (2000, $N,NNN.NN) FROM dual;36. 用下列代码回答问题：Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables:EMPLOYEESEMPLOYEE_ID NUMBER Primary KeyFIRST_NAME VARCHARD2(25)LAST_NAME VARCHARD2(25)HIRE_DATE DATENEW EMPLOYEESEMPLOYEE_ID NUMBER Primary KeyNAME VARCHAR2(60)Which UPDATE statement is valid?以下哪个update语句是正确的？A. UPDATE new_employees SET name = (Select last_name|first_nameFROM employeesWhere employee_id=180)WHERE employee_id =180;B. UPDATE new_employees SET name = (SELECT last_name|first_nameFROM employees)WHERE employee_id =180;C. UPDATE new_employees SET name = (SELECT last_name|first_nameFROM employeesWHERE employee_id=180)WHERE employee_id =(SELECT employee_idFROM new employees);D. UPDATE new_employees SET name = (SELECT last name|first_nameFROM employeesWHERE employee_id=(SELECT employee_idFROM new_employees)WHERE employee_id=180;37. 用下列代码回答问题：Examine the structure of the EMPLOYEES, DEPARTMENTS, and LOCATIONStables. 观察表结构Which two SQL statements produce the name, department name, and the city of all the employees who earn more than 10000? (Choose two)以下哪两个语句能够获取收入大于10000的相关信息？A. SELECT emp_name, department_name, city FROM employees e JOIN departments d USING (department_id)JOIN locations 1 USING (location_id) WHERE salary 10000;B. SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 JOIN ON (e.department_id = d.department id) AND (d.location_id =1.location_id) AND salary 10000;C. SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 WHERE salary 10000;D. SELECT emp_name, department_name, city FROM employees e, departments d, locations 1 WHERE e.de