Technorati 标签: ocp 007 题库 oracle

Q: 1  Examine the structure of the EMPLOYEES table:
EMPLOYEE_ID  NUMBER       Primary Key
FIRST_NAME   VARCHAR2(25)
LAST_NAME    VARCHAR2(25)
Which three statements insert a row into the table? (Choose three.)
A.   INSERT INTO employees
VALUES  ( NULL, 'John', 'Smith');
B.   INSERT INTO employees( first_name, last_name)
VALUES( 'John', 'Smith');
C.   INSERT INTO employees
VALUES  ( '1000', 'John', NULL);
D.   INSERT INTO employees (first_name, last_name, employee_id)
VALUES ( 1000, 'John', 'Smith');
E.   INSERT INTO employees (employee_id)
VALUES (1000);
F.   INSERT INTO employees (employee_id, first_name, last_name)
VALUES  ( 1000, 'John', '  ');
正确答案：c,e,f

解析：
a.因为 employee_id 为主键，不能为空，且唯一，所以是错的；
b.主键不为空，且唯一，所以也是错的；
d.指定插入字段顺序，必须按照该顺序提供值；

总结：插入一条记录到表中，为主键的字段必须非空且唯一
抛砖：查看表中字段默认顺序和修改默认顺序：
第一步，查询出表的id
select object_id from all_objects where owner='CUTM' and object_name='RM_PARTY_RELATION';
第二步，通过id查出该表中所有字段的顺序
select obj#,col#,name from sys.col$ where obj#=76109 order by col#;
第三步，更新字段的顺序
update sys.col$ set col#=8 where obj#=76109 and name='CHILD_IS_MAIN_RELATION';
commit;

Q: 2  Evaluate the SQL statement:
         SELECT ROUND(45.953, -1), TRUNC(45.936, 2)
        FROM dual;
Which values are displayed?
A.   46 and 45
B.   46 and 45.93
C.   50 and 45.93
D.   50 and 45.9
E.   45 and 45.93
F.   45.95 and 45.93
正确答案：C

解析：
round函数 四舍五入
trunc函数 直接截取
select trunc(163.12321,-2) from dual; 结果为 100
select round(163.12321,-2) from dual; 结果为200
trunc/round(数据值,保留小数) 第二个参数能够为负数，表示为小数点左边指定位数后面的部分截去，即均以0记
抛砖：
当两个函数作用于日期时，语法trunc/round(date[,fmt])
其中： date       一个日期值
       fmt        日期格式，该日期将由指定的元素格式所截去。忽略他则由最近的日期截去
fmt的形式：
          yyyy/yy/year 年
          mm/month     月
          d/day        周
          dd           日
          hh           小时
          mi           分
e.g：         
select trunc(to_date('20110820','yyyy-mm-dd'),'mm') from dual; 结果：20110801
select round(to_date('20110820','yyyy-mm-dd'),'mm') from dual; 结果：20110901

 

Q: 3  Which are DML statements? (Choose all that apply.) 
A.   COMMIT
B.   MERGE
C.   UPDATE
D.   DELETE
E.   CREATE
F.   DROP...

正确答案： B, C, D

解析：

SQL结构化查询语言的五大类：
1 .数据查询语言DQL：即select语句，有的书把select归为DML语言；

2.数据操作语言DML：

      INSERT - insert data into a table

     UPDATE - updates existing data within a table

     DELETE - deletes all records from a table, the space for the records remain

     MERGE - UPSERT operation (insert or update)

     CALL - call a PL/SQL or Java subprogram

     EXPLAIN PLAN - explain access path to data

     LOCK TABLE - control concurrency

3.事务控制语言TCL：主要有commit,rollback,savepoint,set

4.数据定义语言DDL：主要用于描述元数据，修改数据字典，DDL语句是针对数据字典的DML语句；主要有create,drop,truncate等

5.数据控制语言DCL：主要有grant,revoke等

 

Q: 4  Evaluate the set of SQL statements: 
        CREATE TABLE dept
        (deptno   NUMBER(2),
         dname     VARCHAR2(14),
         loc       VARCHAR2(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 occupied 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.

正确答案： A

解析：

create table 属于DDL语言，Oracle执行DDL前会发出一个COMMIT语句，然后执行DDL操作，最后再发出一个COMMIT操作。

 

Q: 5  Evaluate this SQL statement: 
        SELECT ename, sal, 12*sal+100
        FROM emp;
The SAL column stores the monthly salary of the employee.  Which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"?
A.   No change is required to achieve the desired results.

B.   SELECT ename, sal, 12*(sal+100) FROM emp;
C.   SELECT ename, sal, (12*sal)+100 FROM emp;
D.   SELECT ename, sal+100,*12 FROM emp;

正确答案： B

解析：

纯粹英语数学题，跟oralce没关系。

Q: 6  Examine the SQL statement that creates ORDERS table:
CREATE TABLE orders
         (SER_NO     NUMBER UNIQUE,
         ORDER_ID    NUMBER,
         ORDER_DATE  DATE NOT NULL,
         STATUS      VARCHAR2(10)
              CHECK (status IN ('CREDIT', 'CASH')),
         PROD_ID  NUMBER
                     REFERENCES PRODUCTS(PRODUCT_ID),
         ORD_TOTAL  NUMBER,
          PRIMARY KEY (order_id, order_date));  

For which columns would an index be automatically created when you execute the above SQL statement?
(Choose two.) 
A.   SER_NO
B.   ORDER_ID
C.   STATUS
D.   PROD_ID
E.   ORD_TOTAL
F.   composite index on ORDER_ID and ORDER_DATE

正确答案： A,F

解析：

Automatically: A unique index is created automatically when you define a PRIMARY KEY or UNIQUE constraint in a table definition.

来自oracle的声音：当表的字段是主键或者有唯一性约束时，会自动创建一个索引。

查找表的索引字段：

select * from all_ind_columns where index_owner='HR' and table_name='ORDERS'

抛砖：

创建索引语法：

CREATE INDEX  emp_last_name_idx ON  employees(last_name);

何时创建索引：

来自oracle的声音：

1.A column contains a wide range of values  

2.A column contains a large number of null values
3.One or more columns are frequently used together in a WHERE clause or a join condition
4.The table is large and most queries are expected to retrieve less than 2 to 4% of the rows

Q: 7  Examine the structure of the EMP_DEPT_VU view:
Column Name           Type                        Remarks
EMPLOYEE_ID       NUMBER              From the EMPLOYEES table
EMP_NAME            VARCHAR2(30)   From the EMPLOYEES table
JOB_ID                   VARCHAR2(20)   From the EMPLOYEES table
SALARY                  NUMBER             From the EMPLOYEES table
DEPARTMENT_ID   NUMBER             From the DEPARTMENTS table
DEPT_NAME           VARCHAR2(30)   From the DEPARTMENTS table

Which SQL statement produces an error? 
A.   SELECT *
FROM   emp_dept_vu;
B.   SELECT department_id, SUM(salary)
FROM   emp_dept_vu
GROUP BY department_id;
C.   SELECT department_id, job_id, AVG(salary)
FROM   emp_dept_vu
GROUP BY department_id, job_id;
D.   SELECT job_id, SUM(salary) FROM   emp_dept_vu  WHERE  department_id IN (10,20)
GROUP BY job_id
HAVING SUM(salary) > 20000;
E.   None of the statements produce an error; all are valid.

 
正确答案： E

解析：

考select语法。

group by 后面要写具体的字段名称，不能用字段代号1,2；order by 后面可以用字段代号；

Q: 8  Click the Exhibit button and examine the data from the ORDERS and CUSTOMERS tables.
Evaluate this SQL statement:
SELECT cust_id, ord_total
FROM   orders
WHERE  ord_total > ANY(SELECT ord_total FROM orders
WHERE cust_id IN (SELECT cust_id FROM  customers WHERE city LIKE 'New York'));
What is the result when the above query is executed?

p_w_picpath

p_w_picpath

正确答案：

p_w_picpath

解析：

来自oracle的声音：

<ANY means less than the maximum.  小于最大值

>ANY means more than the minimum. 大于最小值

=ANY is equivalent to IN. 与in等价，在子查询返回的结果集中

<ALL means less than the minimum. 小于最小值

>ALL means more than the maximum. 大于最大值

 

Q: 9  Evaluate this SQL statement:
  SELECT e.EMPLOYEE_ID,e.LAST_NAME,e.DEPARTMENT_ID, d.DEPARTMENT_NAME
  FROM EMPLOYEES e, DEPARTMENTS d
  WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID;
In the statement, which capabilities of a SELECT statement are performed?
A.selection, projection, join
B.difference, projection, join
C.selection, intersection, join
D.intersection, projection, join
E.difference, projection, product

正确答案：A

解析：

交(union)，并(intersect)，差(minus)，笛卡尔乘积(没有where条件的连接)是集合运算符

选择，投影，连接是关系运算符

选择是从二维表中选出符合条件的记录，它是从行的角度对关系进行的运算。
投影是从二维表中选出所需要的列，它是从列的角度对关系进行的运算。
连接是同时涉及到两个二维表的运算，它是将两个关系在给定的属性上满足给定条件的记录连接起来而得到的一个新的关系。

Q: 10  Click the Exhibit button and examine the data from the EMP table.
The COMMISSION column shows the monthly commission earned by the employee.
Which three tasks would require subqueries or joins in order to be performed in a single step?
(Choo three.)

p_w_picpath

A.   deleting the records of employees who do not earn commission
B.   increasing the commission of employee 3 by the average commission earned in department 20
C.   finding the number of employees who do NOT earn commission and are working for department 20
D.   inserting into the table a new employee 10  who works for department 20 and earns a commission that is
equal to the commission earned by employee 3
E.   creating a table called COMMISSION that has the same structure and data as the columns EMP_ID and
COMMISSION of the EMP table
F.   decreasing the commission by 150 for the employees who are working in department 30 and earning a
commission of more than 800

正确答案：B,D,E

解析：

选出需要通过子查询或者表联接才能达到目的的选项。

B:

update emp
set commission=(select avg(commission) from emp where dept_id=20)
where emp_id=3;

D:

insert into emp(emp_id,dept_id,commission)
select 10,20,commission
from emp
where emp_id=3;

E:

create table commission as(
select emp_id,commission
from emp
);

B比较好理解，D和E为什么也符合呢？

D需要先访问emp表，找出emp_id=3的commission，然后插入emp表中。

E是先访问emp表，获取表和表结构，然后再定义commission表和插入数据。

这两个不能一次性操作完成，需要访问两次表。

Q: 11  You need to modify the STUDENTS table to add a primary key on the STUDENT_ID column. 
The table is currently empty. Which statement accomplishes this task?
A.ALTER TABLE students ADD PRIMARY KEY student_id;
B.ALTER TABLE students ADD CONSTRAINT PRIMARY KEY (student_id);
C.ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY student_id;
D.ALTER TABLE students ADD CONSTRAINT stud_id_pk PRIMARY KEY (student_id);
E.ALTER TABLE students MODIFY CONSTRAINT stud_id_pk PRIMARY KEY (student_id);

正确答案：D

解析：

格式为：alter table

表格名称 add constraint 约束名称 增加的约束类型 （列名）

1.添加主键约束:alter table emp add constraint xxx primary key (id);

2.check约束：alter table emp add constraint xxx check(age&gt;20);

3.unique约束：alter table emp add constraint xxx unique(id);

4.默认约束：alter table emp add constraint xxx default 100 for salary;

5.外键约束：alter table emp add constraint xxx foreign key(id) references dept (id);

Q: 12  Which three are DATETIME data types that can be used when specifying
column definitions? (Choose three.) 
A.   TIMESTAMP
B.   INTERVAL MONTH TO DAY
C.   INTERVAL DAY TO SECOND
D.   INTERVAL YEAR TO MONTH
E.   TIMESTAMP WITH DATABASE TIMEZONE

正确答案：A,C,D

解析：

日期类型包括：DATE,TIMESTAMP,INTERVAL

TIMESTAMP包括两种变型：

1.TIMESTAMP WITH TIME ZONE;

2.TIMESTAMP WITH LOCAL TIME ZONG;

INTERVAL包括两种变型：

1.INTERVAL YEAR TO MONTH;

2.INTERVAL DAY TO SECOND;

衍生：

TIMESTAMP 精确到微妙，有0-9位选择，默认是6位；

INTERVAL中的year和day默认2位，默认是正数，要算几月之前的时间可以在数字前加负号；

e.g:

1.距离日期2011年01月01日，11年前的日期

select to_date(20110101,'yyyymmdd')+INTERVAL '-11' YEAR(3) TO MONTH from dual;

2.距离日期2011年01月01日，11个月前的日期

select to_date(20110101,'yyyymmdd')+INTERVAL '-11' month(3) from dual;

3.距离日期2011年01月01日，11年零1个月前的日期

select to_date(20110101,'yyyymmdd')+INTERVAL '-11-1' YEAR(3) TO MONTH from dual;

4.距离时间'20110101 00:00:00’1天零11分钟1微妙之后的时间

select  to_date('20110101 00:00:00','yyyymmdd hh24:mi:ss')+INTERVAL '1 00:11:00.10' day to second from dual;

5.建表例子：

create table date_test_prog(
d1 timestamp
,d2 timestamp(7)
,d3 timestamp with time zone
,d4 timestamp with local time zone
,d5 interval year to month
,d6 interval day to second
,d7 interval day(4) to second(9)
);

insert into date_test_prog
values(to_date('20110101 00:00:00','yyyymmdd hh24:mi:ss')
,to_date('20110101 00:00:00','yyyymmdd hh24:mi:ss')
,to_timestamp_tz('20110101 00:00:00 -8:00','YYYYMMDD HH24:MI:SS TZH:TZM')
,to_timestamp('20110101 00:00:00','YYYYMMDD HH24:MI:SS')
,interval '11-1' year to month
,interval '1 1:00:00.00' day to second
,interval '1 1:00:00.00' day to second);

Q: 13  The EMPLOYEES table contains these  columns:
LAST_NAME             VARCHAR2 (25)
SALARY                    NUMBER (6,2)
COMMISSION_PCT   NUMBER (6)
You need to write a query that will produce these results:
       1.  Display the salary multiplied by the commission_pct.
       2.  Exclude employees with a zero commission_pct.
       3.  Display a zero for employees with a null commission value.
Evaluate the SQL statement:
         SELECT LAST_NAME, SALARY*COMMISSION_PCT
         FROM EMPLOYEES
         WHERE COMMISSION_PCT IS NOT NULL;
What does the statement provide?
A.   all of the desired results
B.   two of the desired results
C.   one of the desired results
D.   an error statement

正确答案：C

解析：

只满足了第一个目标

第二个目标是要把commission_pct为0的剔除掉

第三个目标是要把commission_pct为null的显示为0

正确的sql为：

SELECT LAST_NAME, SALARY*nvl(COMMISSION_PCT,0)
FROM EMPLOYEES
WHERE COMMISSION_PCT<>0;

延伸：

nvl函数：

1.Converts a null to an actual value

2.Data types that can be used are date, character, and number.

3.Data types must match:NVL(commission_pct,0);NVL(hire_date,'01-JAN-97');NVL(job_id,'No Job Yet')

nvl2函数：NVL2(expr1, expr2, expr3)

如果expr1为null，就返回expr2，如果不为空，则返回expr3.

coalesce函数：COALESCE (expr1, expr2, ... exprn)

与nvl2类似，只是可以支持判断2个以上的参数。

Q: 14  Evaluate the SQL statement:  TRUNCATE TABLE DEPT;
Which three are true about the SQL statement? (Choose three.)
A.   It releases the storage space used by the table.
B.   It does not release the storage space used by the table.
C.   You can roll back the deletion of rows after the statement executes.
D.   You can NOT roll back the deletion of rows after the statement executes.
E.   An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an
error.
F.   You must be the owner of the table or have DELETE ANY TABLE system privileges to truncate the DEPT
table.

正确答案：A,D,F

解析： truncate和delete的不一样就是会降高水位线（HWM）

在Oracle数据的存储中，可以把存储空间想象为一个水库，数据想象为水库中的水。水库中的水的位置有一条线叫做水位线，在Oracle中，这条线被称为高水位线（High-warter mark, HWM）。在数据库表刚建立的时候，由于没有任何数据，所以这个时候水位线是空的，也就是说HWM为最低值。当插入了数据以后，高水位线就会上涨，但是这里也有一个特性，就是如果你采用delete语句删除数据的话，数据虽然被删除了，但是高水位线却没有降低，还是你刚才删除数据以前那么高的水位。也就是说，这条高水位线在日常的增删操作中只会上涨，不会下跌。如果用truncate，则会相应的降低水位线。数据库对表的扫描是根据hwm的位置来决定扫描多少数据的。

Q: 15  The EMP table contains these columns:
EMPLOYEE_ID  NUMBER(4)
EMPNAME      VARCHAR2 (25)
SALARY          NUMBER(9,2)
HIRE_DATE    DATE
You query the database with this SQL statement:
            SELECT  empname,hire_date HIREDATE, salary
            FROM EMP
           ORDER BY hire_date;
How will the results be sorted?
A.   randomly
B.   ascending by date
C.   descending by date
D.   ascending alphabetically
E.   descending alphabetically

正确答案：B

解析：order by 默认为升序asc.