姓名：_______________________ Personal Number:_________________ 班级：____________________
Oracle SQL&PL/SQL Test
一、
1. 用下列代码块回答问题：
SQL> select ______(-45) as output from dual; OUTPUT ------
-45
下列哪个单行函数无法产生这个输出？
A．abs() B．ceil() C．floor() D．round()
2. 用下列代码回答问题：
Evaluate the set of SQL statements: CREATE TABLE dept (deptno NUMBER(2), dname VARCNAR2(14), loc VARCNAR2(13)); ROLLBACK; DESCRIBE DEPT What 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 if there is a COMMIT statement introduced before the ROLLBACK statement.
3. 用下列代码回答问题：
Examine the structure of the EMPLOYEES and DEPARTMENTS tables: 检查如下表结构 EMPLOYEES
Column name Data type Remarks
EMPLOYEE_ID NUMBER NOT NULL, Primary Key EMP_NAME VARCHAR2 (30) JOB_ID VARCHAR2 (20) SALARY NUMBER
MGR_ID NUMBER References EMPLOYEE_ID COLUMN DEPARTMENT ID NUMBER Foreign key to DEPARTMENT ID column of the DEPARTMENTS table DEPARTMENTS
选择题(45 * 2)
姓名：_______________________
Column name Data type Remarks
Personal Number:_________________ 班级：____________________
DEPARTMENT_ID NUMBER NOT NULL, Primary Key DEPARTMENT_NAME VARCHAR2(30)
MGR_ID NUMBER References MGR_ID column of the EMPLOYEES table
Evaluate this SQL statement:
SELECT employee_id, e.department_id, department_name, salary
FROM employees e, departments d WHERE e.department_id = d.department_id;
Which SQL statement is equivalent to the above SQL statement? 一下哪个语句和上面的语句执行结果相同？
A. SELECT employee_id, department_id, department_name,salary FROM employees
WHERE department_id IN (SELECT department_id FROM departments);
B. SELECT employee_id, department_id, department_name,salary FROM employees
NATURAL JOIN departments;
C. SELECT employee_id, d.department_id, department_name,salary FROM employees e JOIN departments d
ON e.department_id = d.department_id;
D. SELECT employee_id, department_id, department_name,Salary FROM employees JOIN departments
USING (e.department_id, d.department_id);
4. 运行下列哪个查询时会产生错误？
A．select deptno, job, sum(sal) from emp group by job, deptno; B．select sum(sal), deptno, job from emp group by job, deptno; C．select deptno, job, sum(sal) from emp;
D．select 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 B．A.JOB C．B.LOC D．SUM(A.SAL)
6. 要查询的PROFITS表存储公司不同地区、产品类型和季度的利润信息。下列哪个SQL语句按不同地区、
产品类型和季度求出平均利润大于100000美元的利润？
姓名：_______________________
Personal Number:_________________ 班级：____________________
A．select region, prod_type, period, avg(profits) from profits where avg(prodit) > 100000 group by region, prod_type, period;
B．select region, prod_type, period, avg(profits) from profits where avg(prodit) > 100000 order by region, prod_type, period;
C．select region, prod_type, period, avg(profits) from profits group by region, prod_type, period having avg(prodit) > 100000;
D．select region, prod_type, period, avg(profits) from profits group by region, prod_type, period having avg(prodit) < 100000;
7. 看看下列语句：
SQL> select ename 2 from emp 3 where empno in 4 ( select empno 5 from expense
6 where vouch_amt > 10000 );
下列哪个SQL语句产生与上面相同的输出，改写成谓词运算符
A．select e.ename from emp e where exists(select x.empno from expense x where x.vouch_amt>10000) and x.empno = e.empno;
B．select e.ename from emp e where exists(select x.empno from expense x where x.vouch_amt>10000 and x.empno = e.empno);
C．select e.ename from emp e where x.empno = e.empno and exists(select x.empno from expense x where x.vouch_amt>10000)
D．select e.ename from emp e, expense x where x.empno = e.empno and x.vouch_amt>10000 and exists(select x.empno from expense x where)
8. 公司销售数据库有两个表，PROFITS存储不同地区不同季度的产品销售利润，REGIONS存储每个部门地
区名称、该地区总部地址和该地区副总裁姓名。下列哪个查询取得SMITHERS、FUJIMORI与LIKKARAJU领导的地区的玩具销售利润？
A．select sum(profit) from profits where region in (select region from regions where reg_head in (‘SMITHERS’, ‘FUJMORI’, ‘LAKKARAJU’)) and product =’TOYS’;
B．select sum(profit) from profits where region like (select region from regions where reg_head in (‘SMITHERS’, ‘FUJMORI’, ‘LAKKARAJU’) and product =’TOYS’ );
C．select sum(profit) from profits where region = (select region from regions where reg_head in (‘SMITHERS’, ‘FUJMORI’, ‘LAKKARAJU’)) and product =’TOYS’;
D．select 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 write the SELECT statement:
Personal Number:_________________ 班级：____________________
You need to display the employees who have not been assigned to any department. 如果需要显示还没有被分配
SELECT LAST_NAME, SALARY, DEPARTMENT_ID FROM EMP
WHERE 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 desired results.
10. 公司销售数据库包含一个PROFITS表，按产品名、销售地区和季度列出利润信息。如果要取得公司五个
最畅销产品清单，可以用下列哪个SQL语句：
A．select p.prod_name, p.profit from (select prod_name, profit from profits order by profit desc) where rownum <= 5; B．select p.prod_name, p.profit from (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) subq where p.prod_name = subq.prod_name;
C．select p.prod_name, p.profit from (select prod_name, sum(profit) from profits group by prod_name order by sum(profit) desc) where rownum <= 5;
D．select p.prod_name, p.profit from (select prod_name, sum(profit) from profits order by sum(profit) desc) where rownum <= 5;
11. Oracle中要生成数据库表,下列哪个表生成语句是无效的？
A．create table cats(c_name varchar2(10), c_weight number, c_owner varchar2(10)); B．create table my_cats as select * from cats where owner = ‘ME’;
C．create global temporary table temp_cats(c_name varchar2(10), c_weight number, c_owner varchar2(10)); D．create table 51cats as select c_name, c_weight from cats where c_weight > 5;
12. 试图在Oracle生成表时遇到下列错误：ORA-00955-name is already used by existing object。下列哪个选项
无法纠正这个错误？
A．以不同的用户身份生成对象。 B．删除现有同名对象 C．改变生成对象中的列名。 D．更名现有同名对象。
13. SALES表中的PROFITS列声明为NUMBER(10, 2)。下列哪个值无法在这个列中存储？
A．5392845.324 B．871039453.1 C．75439289.34 D．60079829.25
14. 用下列代码回答问题：
Evaluate the SQL statement:
SELECT ROUND(TRUNC(MOD(1600,10),-1),2) FROM dual;
What will be displayed?以下显示结果哪个正确？
姓名：_______________________
A. 0 B. 1 C. 0.00
D. An error statement
15. 用下列代码回答问题：
Personal Number:_________________ 班级：____________________
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_id FROM marks
WHERE 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 marks for 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*Plus
16. SALES表中定义的检查约束包含两个列PRODUCT_NAME与SALE_PERIOD。下列哪些选项表示定义
的检查约束？选择两个。
A．alter table sales add constraint ck_sales_01 check(product_type in(‘TOYS’, ‘HOT DOGS’‘,PALM PILOTS’)); B．alter table sales add constraint ck_sales_01 check(product_type in(select product_type from valid_products)); C．alter table sales modify(product_type varchar2(30) check(product_type in(‘TOYS’, ‘HOT DOGS’,‘PALM PILOTS’)));
D．alter table sales add(product_name varchar2(30) check(product_name <> ‘AK-47’));
17. 你要关闭SALES表中UNIT_PRICE列的非NULL约束。下列哪个语句能完成这个操作？
A．alter table sales modify(unit_prices null); B．alter table sales modify(unit_prices not null); C．alter table sales add(unit_prices null); D．alter table sales add(unit_prices not null);
18. 在Oracle中，可用于提取日期时间类型特定部分（如年、月、日、时、分、秒）的函数有（），选择两个
A. DATEPART