首页 > 数据库 > Oracle >

ocp007题库解析

2011-09-14

Q: 1 Examine the structure of the EMPLOYEETechnorati 标签: ocp 007 题库 oracleS table:EMPLOYEE_ID NUMBER Primary KeyFIRST_NAME VARCHAR2(25)LAST_NAME VARCHAR2(25)Which three sta...

Q: 1 Examine the structure of the EMPLOYEE
Technorati 标签: ocp 007 题库 oracle
S 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', ' ');
解析:
正确答案:d,e,f
a.因为 employee_id 为主键,不能为空,且唯一,所以是错的;
b.主键不为空,且唯一,所以也是错的;
c.当在表名后没有指定插入字段顺序,插入时按照表中默认的字段顺序来插入,默认的字段顺序建表时就定下来了,所以是错的;
总结:插入一条记录到表中,为主键的字段必须非空且唯一
抛砖:查看表中字段默认顺序和修改默认顺序:
第一步,查询出表的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没关系。
作者“成长足迹”

相关文章
最新文章
热点推荐