ocp 1Z0-51 23-70题解析
23. Examine thestructure proposed for the TRANSACTIONS table: name Null Type TRANS_ID NOT NULLNUMBER(6) CUST_NAME NOT NULLVARCHAR2(20) CUST_STATUS NOT NULLCHAR TRANS_DATE NOT NULL DATE TRANS_VALIDITY VARCHAR2 CUST_CREDIT_LIMIT NUMBER Which
23. Examine thestructure proposed for the TRANSACTIONS table:
name Null Type
TRANS_ID NOT NULLNUMBER(6)
CUST_NAME NOT NULLVARCHAR2(20)
CUST_STATUS NOT NULLCHAR
TRANS_DATE NOT NULL DATE
TRANS_VALIDITY VARCHAR2
CUST_CREDIT_LIMIT NUMBER
Which statements aretrue regarding the creation and storage of data in the above table structure?
(Choose all that apply.)
A. The CUST_STATUScolumn would give an error.
B. The TRANS_VALIDITYcolumn would give an error.
C. The CUST_STATUScolumn would store exactly one character.
D. The CUST_CREDIT_LIMITcolumn would not be able to store decimal values.
E. The TRANS_VALIDITY columnwould have a maximum size of one character.
F. The TRANS_DATE columnwould be able to store day, month, century, year, hour, minutes, seconds,
and fractions ofseconds.
Answer: BC
解析：
引用官方文档：
The default length for a CHAR column is 1 byte and themaximum allowed is 2000
bytes. A 1-byte stringcan be inserted into a CHAR(10) column, but the string is
blank-padded to 10 bytesbefore it is stored.
所以C正确，A错误
You mustspecify a maximum length for a VARCHAR2 column.This maximum must be
at least 1 byte, althoughthe actual string stored is permitted to be a zero-length string
('').
所以B正确，E错误
引用官方文档的一个表格：
Actual Data Specified AsStored As
123.89 NUMBER 123.89
测试：
scott@ORCL>createtable zbcxy(id number);
表已创建。
scott@ORCL>insertinto zbcxy values(123.68);
已创建 1 行。
scott@ORCL>select *from zbcxy;
ID
----------
123.68
D选项错误
The DATE data typestores date and time information. Although date and time
information can berepresented in both character and number data types, the DATE
data type has specialassociated properties. For each DATE value, Oraclestores the
followinginformation: year, month, day, hour, minute, and second.
选项F错误
24. Examine thestructure proposed for the TRANSACTIONS table:
name Null Type
TRANS_ID NOT NULL NUMBER(6)
CUST_NAME NOT NULLVARCHAR2(20)
CUST_STATUS NOT NULLVARCHAR2
TRANS_DATE NOT NULL DATE
TRANS_VALIDITY INTERVALDAY TO SECOND
CUST_CREDIT_VALUENUMBER(10)
Which two statements aretrue regarding the storage of data in the above table structure? (Choose two.)
A. The TRANS_DATE columnwould allow storage of dates only in the dd-mon-yyyy format.
B. The CUST_CREDIT_VALUEcolumn would allow storage of positive and negative integers.
C. The TRANS_VALIDITYcolumn would allow storage of a time interval in days, hours, minutes, and
seconds.
D. The CUST_STATUScolumn would allow storage of data up to the maximum VARCHAR2 size of 4,000
characters.
Answer: BC
解析：
A选项中可以使用to_char设置多种格式
引用官方文档：
The NUMBER data typestores zero as well as positive and negativefixed numbers with
absolute values from 1.0x 10-130 to but not including 1.0 x 10126.
所以B正确
INTERVAL DAY TO SECONDstores a period of time in terms of days, hours,minutes,
and seconds. This data type is useful for representing the precisedifference between
two datetime values.
所以C正确
D选项根据上题可知，使用varchar类型，必须指定长度，所以D选项错误
25. You need to create atable with the following column specifications:
1. Employee ID (numericdata type) for each employee
2. Employee Name(character data type) that stores the employee name
3. Hire date, whichstores the date of joining the organization for each employee
4. Status (characterdata type), that contains the value 'ACTIVE' if no data is entered
5. Resume (characterlarge object [CLOB] data type), which contains the resume submitted by the
employee
Which is the correctsyntax to create this table?
A. CREATE TABLE EMP_1
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
e_status VARCHAR2(10)DEFAULT 'ACTIVE',
resume CLOB(200));
B. CREATE TABLE 1_EMP
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10)DEFAULT 'ACTIVE',
resume CLOB);
C. CREATE TABLE EMP_1
(emp_id NUMBER(4),
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10)DEFAULT "ACTIVE",
resume CLOB);
D. CREATE TABLE EMP_1
(emp_id NUMBER,
emp_name VARCHAR2(25),
start_date DATE,
emp_status VARCHAR2(10)DEFAULT 'ACTIVE',
resume CLOB);
Answer: D
解析：
引用官方文档：
The CLOB data typestores single-byte and multibyte character data. Both fixed-width
and variable-widthcharacter sets are supported, and both use the database character set
使用clob无需指定长度，所以A错
B选项 表名不能以数字开头
C选项 default 用单引号
所以D正确
26. Which is the validCREATE TABLE statement?
A. CREATE TABLE emp9$#(emp_no NUMBER (4));
B. CREATE TABLE 9emp$#(emp_no NUMBER(4));
C. CREATE TABLE emp*123(emp_no NUMBER(4));
D. CREATE TABLE emp9$#(emp_no NUMBER(4), date DATE);
Answer: A
解析：
创建表时，表的名称必须是合法标示符，长度为1-30字节，并且以字母开头，可以包含字母，数字，下划线，美元符号和#，此外表名称不能与所属模式中其他对象同名，也不能是oracle数据库的保留字
由此判断B和C错误
D选项不能使用oracle中关键字作为属性名称
所以A正确
27. Which two statementsare true regarding tables? (Choose two.)
A. A table name can beof any length.
B. A table can have anynumber of columns.
C. A column that has aDEFAULT value cannot store null values.
D. A table and a viewcan have the same name in the same schema.
E. A table and a synonymcan have the same name in the same schema.
F. The same table namecan be used in different schemas in the same database.
Answer: EF
根据上题所知，表名的长度在1-30字节，所以A选项错误
B选项，测试：
BEGIN
FOR I IN 1..999 LOOP
EXECUTE IMMEDIATE 'ALTERTABLE zbcxy ADD id' || I || ' NUMBER(1)';
END LOOP;
END;
scott@ORCL>altertable zbcxy add id1000 number(1);
alter table zbcxy addid1000 number(1)
*
第 1 行出现错误:
ORA-01792: 表或视图中的最大列数为 1000
可知oracle限制最大列数为1000，所以B选项错误
C选项，测试：
scott@ORCL>createtable zbcxy(name varchar(20) default 'zbcxy');
表已创建。
scott@ORCL>insertinto zbcxy values(' ');
已创建 1 行。
scott@ORCL>select *from zbcxy;
NAME
所以C选项错误
D选项，一个模式下面的对象是不能同名的，所以D选项错误
E选项，测试：
scott@ORCL>createpublic synonym emp for emp;
同义词已创建。
scott@ORCL>createsynonym dept for dept;
create synonym dept fordept
*
第 1 行出现错误:
ORA-01471: 无法创建与对象同名的同义词
这里题意估计是指的公共的
所以E选项正确
F选项，不同模式下可以有同名的对象
28. Which two statementsare true regarding constraints? (Choose two.)
A. A foreign key cannot containNULL values.
B. A column with theUNIQUE constraint can contain NULL values.
C. A constraint isenforced only for the INSERT operation on a table.
D. A constraint can bedisabled even if the constraint column contains data.
E. All constraints can bedefined at the column level as well as the table level.
Answer: BD
解析：
选项A，如果参照表中有空值，则外键也可以为空值，可以采用
Insert into zbcxy valuesselecet ename from emp;这种方式将emp中ename的控制插入到zbcxy表中
所以选项A是错误的
B选项，很明显unique约束时可以是空值，这里就不演示测试了
C选项，明显错误，不止insert ，执行update操作也是必须遵守约束的
D选项，测试：
scott@ORCL>create table zbcxy(id number(4) constraint p_pk primary key);
表已创建。
scott@ORCL>insertinto zbcxy values(1);
已创建 1 行。
scott@ORCL>altertable zbcxy disable constraint p_pk;
表已更改。
所以D选项正确
E选项，非空约束只能定义列级约束，所以E选项是错误的
29. Which two statementsare true regarding constraints? (Choose two.)
A. A foreign key cannotcontain NULL values.
B. The column with aUNIQUE constraint can store NULLS .
C. A constraint isenforced only for an INSERT operation on a table.
D. You can have morethan one column in a table as part of a primary key.
Answer: BD
解析：
A选项，由上题可知是错误的
B选项，由上题可知是正确的
C选项，由上题可知是错误的
D选项，一个表中只能有虽然只有一个约束，但是可以建立一个联合主键约束，这样就有多列为主键约束，所以D选项正确
30. Evaluate thefollowing CREATE TABLE commands:
CREATE TABLE orders
(ord_no NUMBER(2)CONSTRAINT ord_pk PRIMARY KEY,
ord_date DATE,
cust_id NUMBER(4));
CREATE TABLE ord_items
(ord_no NUMBER(2),
item_no NUMBER(3),
qty NUMBER(3) CHECK (qtyBETWEEN 100 AND 200),
expiry_date date CHECK(expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARYKEY (ord_no,item_no),
CONSTRAINT ord_fkFOREIGN KEY(ord_no) REFERENCES orders(ord_no));
The above command failswhen executed. What could be the reason?
A. SYSDATE cannot beused with the CHECK constraint.
B. The BETWEEN clausecannot be used for the CHECK constraint.
C. The CHECK constraintcannot be placed on columns having the DATE data type.
D. ORD_NO and ITEM_NOcannot be used as a composite primary key because ORD_NO is also the
FOREIGN KEY.
Answer: A
解析：
scott@ORCL>CREATETABLE ord_items
2 (ord_no NUMBER(2),
3 item_no NUMBER(3),
4 qtyNUMBER(3) CHECK (qty BETWEEN 100 AND 200),
5 expiry_date date CHECK (expiry_date > SYSDATE),
6 CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
7 CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));
expiry_date date CHECK(expiry_date > SYSDATE),
*
第 5 行出现错误:
ORA-02436: 日期或系统变量在 CHECK 约束条件中指定错误
所以A选项正确
31. Evaluate thefollowing SQL commands:
SQL>CREATE SEQUENCEord_seq
INCREMENT BY 10
START WITH 120
MAXVALUE 9999
NOCYCLE;
SQL>CREATE TABLEord_items
(ord_no NUMBER(4)DEFAULTord_seq.NEXTVAL NOT NULL,
item_no NUMBER(3),
qty NUMBER(3) CHECK (qtyBETWEEN 100 AND 200),
expiry_date date CHECK(expiry_date > SYSDATE),
CONSTRAINT it_pk PRIMARYKEY (ord_no,item_no),
CONSTRAINT ord_fkFOREIGN KEY(ord_no) REFERENCES orders(ord_no));
The command to create atable fails. Identify the reason for the SQL statement failure? (Choose allthat
apply.)
A. You cannot useSYSDATE in the condition of a CHECK constraint.
B. You cannot use theBETWEEN clause in the condition of a CHECK constraint.
C. You cannot use theNEXTVAL sequence value as a DEFAULT value for a column.
D. You cannot use ORD_NOand ITEM_NO columns as a composite primary key because ORD_NO is
also the FOREIGN KEY.
Answer: AC
解析：
scott@ORCL>CREATETABLE ord_items
2 (ord_no NUMBER(4)DEFAULT ord_seq.NEXTVAL NOT NULL,
3 item_no NUMBER(3),
4 qtyNUMBER(3) CHECK (qty BETWEEN 100 AND 200),
5 expiry_date date CHECK (expiry_date > SYSDATE),
6 CONSTRAINT it_pk PRIMARY KEY (ord_no,item_no),
7 CONSTRAINT ord_fk FOREIGN KEY(ord_no) REFERENCES orders(ord_no));
(ord_no NUMBER(4)DEFAULTord_seq.NEXTVAL NOT NULL,
*
第 2 行出现错误:
ORA-00984: 列在此处不允许
由上题可知A选项正确，B，D选项错误，经上面测试，C选项正确
32. Which CREATE TABLEstatement is valid?
A. CREATE TABLEord_details
(ord_no NUMBER(2)PRIMARY KEY,
item_no NUMBER(3)PRIMARY KEY,
ord_date DATE NOT NULL);
B. CREATE TABLEord_details
(ord_no NUMBER(2)UNIQUE, NOT NULL,
item_no NUMBER(3),
ord_date DATE DEFAULTSYSDATE NOT NULL);
C. CREATE TABLEord_details
(ord_no NUMBER(2) ,
item_no NUMBER(3),
ord_date DATE DEFAULTNOT NULL,
CONSTRAINT ord_uq UNIQUE(ord_no),
CONSTRAINT ord_pkPRIMARY KEY (ord_no));
D. CREATE TABLEord_details
(ord_no NUMBER(2),
item_no NUMBER(3),
ord_date DATE DEFAULTSYSDATE NOT NULL,
CONSTRAINT ord_pkPRIMARY KEY (ord_no, item_no));
Answer: D
解析：
A选项：一个表中不能有两个主键
B选项：一个列不能同时附加上两个约束
C选项：default用法错误，以及一个列不能同时附加两个 约束
D选项正确
33. You want to createan ORD_DETAIL table to store details for an order placed having the following
business requirement:
1) The order ID will beunique and cannot have null values.
2) The order date cannothave null values and the default should be the current date.
3) The order amountshould not be less than 50.
4) The order status willhave values either shipped or not shipped.
5) The order paymentmode should be cheque, credit card, or cash on delivery (COD).
Which is the valid DDLstatement for creating the ORD_DETAIL table?
A. CREATE TABLEord_details
(ord_id NUMBER(2)CONSTRAINT ord_id_nn NOT NULL,
ord_date DATE DEFAULTSYSDATE NOT NULL,
ord_amount NUMBER(5, 2)CONSTRAINT ord_amount_min
CHECK (ord_amount >50),
ord_status VARCHAR2(15)CONSTRAINT ord_status_chk
CHECK (ord_status IN('Shipped', 'Not Shipped')),
ord_pay_modeVARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN('Cheque', 'Credit Card',
'Cash On Delivery')));
B. CREATE TABLEord_details
(ord_id NUMBER(2)CONSTRAINT ord_id_uk UNIQUE NOT NULL,
ord_date DATE DEFAULTSYSDATE NOT NULL,
ord_amount NUMBER(5, 2)CONSTRAINT ord_amount_min
CHECK (ord_amount >50),
ord_status VARCHAR2(15)CONSTRAINT ord_status_chk
CHECK (ord_status IN('Shipped', 'Not Shipped')),
ord_pay_modeVARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN('Cheque', 'Credit Card',
'Cash On Delivery')));
C. CREATE TABLEord_details
(ord_id NUMBER(2)CONSTRAINT ord_id_pk PRIMARY KEY,
ord_date DATE DEFAULTSYSDATE NOT NULL,
ord_amount NUMBER(5, 2)CONSTRAINT ord_amount_min
CHECK (ord_amount >=50),
ord_status VARCHAR2(15)CONSTRAINT ord_status_chk
CHECK (ord_status IN('Shipped', 'Not Shipped')),
ord_pay_modeVARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN('Cheque', 'Credit Card',
'Cash On Delivery')));
D. CREATE TABLEord_details
(ord_id NUMBER(2),
ord_date DATE NOT NULLDEFAULT SYSDATE,
ord_amount NUMBER(5, 2)CONSTRAINT ord_amount_min
CHECK (ord_amount >=50),
ord_status VARCHAR2(15)CONSTRAINT ord_status_chk
CHECK (ord_status IN('Shipped', 'Not Shipped')),
ord_pay_modeVARCHAR2(15) CONSTRAINT ord_pay_chk
CHECK (ord_pay_mode IN('Cheque', 'Credit Card',
'Cash On Delivery')));
Answer: C
解析：
根据题目第一句话：The order ID willbe unique and cannot have null values
只有主键才能满足这个条件，unique是可以为空值的
正确答案只能为C选项
34. You created anORDERS table with the following description:
name Null Type
ORD_ID NOT NULLNUMBER(2)
CUST_ID NOT NULLNUMBER(3)
ORD_DATE NOT NULL DATE
ORD_AMOUNT NOT NULLNUMBER (10,2)
You inserted some rowsin the table. After some time, you want to alter the table by creating the
PRIMARY KEY constrainton the ORD_ID column. Which statement is true in this scenario?
A. You cannot have twoconstraints on one column.
B. You cannot add aprimary key constraint if data exists in the column.
C. The primary keyconstraint can be created only at the time of table creation .
D. You can add theprimary key constraint even if data exists, provided that there are noduplicate
values.
Answer: D
解析：
题目意思是在一个已有数据的表中，将第一列改为主键约束，只要该列没有重复值是可以更改的，测试：
scott@ORCL>select * from zbcxy;
ID
----------
1
2
3
scott@ORCL>altertable zbcxy add constraint p_pk primary key(id);
表已更改。
如果表中存在相同列：
scott@ORCL>select * from zbcxy;
ID
----------
1
2
3
2
scott@ORCL>altertable zbcxy add constraint p_pk primary key(id);
alter table zbcxy addconstraint p_pk primary key(id)
*
第 1 行出现错误:
ORA-02437: 无法验证 (SCOTT.P_PK) - 违反主键
所以选项D正确
35. Which two statementsare true regarding constraints? (Choose two.)
A. A table can have onlyone primary key and one foreign key.
B. A table can have onlyone primary key but multiple foreign keys.
C. Only the primary keycan be defined at the column and table levels.
D. The foreign key andparent table primary key must have the same name.
E. Both primary key andforeign key constraints can be defined at both column and table levels.
Answer: BE
解析：
一个表中只能有一个主键约束，多个外键约束，除了非空约束不能定位表级约束，其他约都可以，外键只需数据类型一样，无需字段名一样
所以选项为BE
36. Examine thefollowing SQL commands:
SQL>CREATE TABLEproducts (
prod_id NUMBER(3) CONSTRAINTp_ck CHECK (prod_id > 0),
prod_name CHAR(30),
prod_qty NUMBER(6),
CONSTRAINT p_name NOTNULL,
CONSTRAINT prod_pkPRIMARY KEY (prod_id));
SQL>CREATE TABLEwarehouse (
warehouse_id NUMBER(4),
roomno NUMBER(10)CONSTRAINT r_id CHECK(roomno BETWEEN 101 AND 200),
location VARCHAR2(25),
prod_id NUMBER(3),
CONSTRAINT wr_pr_pkPRIMARY KEY (warehouse_id,prod_id),
CONSTRAINT prod_fkFOREIGN KEY (prod_id) REFERENCES products(prod_id));
Which statement is trueregarding the execution of the above SQL commands?
A. Both commands executesuccessfully.
B. The first CREATETABLE command generates an error because the NULL constraint is not valid.
C. The second CREATETABLE command generates an error because the CHECK constraint is not valid.
D. The first CREATE TABLEcommand generates an error because CHECK and PRIMARY KEY
constraints cannot beused for the same column.
E. The first CREATETABLE command generates an error because the column PROD_ID cannot be used
in the PRIMARY KEY andFOREIGN KEY constraints.
Answer: B
解析：
非空约束不能作为表级约束，所以B选项正确
37. You issued thefollowing command to drop the PRODUCTS table:
SQL> DROP TABLEproducts;
What is the implicationof this command? (Choose all that apply.)
A. All data along withthe table structure is deleted.
B. The pendingtransaction in the session is committed.
C. All indexes on thetable will remain but they are invalidated.
D. All views andsynonyms will remain but they are invalidated.
E. All data in the tableare deleted but the table structure will remain.
Answer: ABD
解析：
引用官方文档：
table
Specify the name of the table to be dropped. OracleDatabase automatically performs
the following operations:
■ All rows from the table are dropped.
■ All table indexes and domain indexesare dropped, as well as any triggers defined
on the table, regardless of who created them or whoseschema contains them. If
table is partitioned, then any corresponding local indexpartitions are also
dropped.
If the table is a base table for a view, a container or master table of a materialized
view, or if it is referenced in a stored procedure,function, or package, then the
database invalidates these dependent objects but does not drop them. You cannot
use these objects unless you re-create the table or dropand re-create the objects so
that they no longer depend on the table.
当然oracle使用drop删除是数据和表结构一起删除，delete是只删除数据，当前和该表的事务也应当马上提交
38. Which two statementsare true regarding views? (Choose two.)
A. A simple view in which column aliases have been usedcannot be updated.
B. Rows cannot bedeleted through a view if the view definition contains the DISTINCT keyword.
C. Rows added through aview are deleted from the table automatically when the view is dropped.
D. The OR REPLACE optionis used to change the definition of an existing view without dropping and
re-creating it.
E. The WITH CHECK OPTIONconstraint can be used in a view definition to restrict the columns
displayed through theview.
Answer: BD
解析：
引用官方文档：
Specify names for the expressions selected by the definingquery of the view. The
number of aliases must match the number of expressionsselected by the view. Aliases
must follow the rules for naming Oracle Database schemaobjects. Aliases must be
unique within the view.
If you omit the aliases, then the database derives themfrom the columns or column
aliases in the query. For this reason, you must usealiases if the query contains
expressions rather than only column names. Also, you mustspecify aliases if the view
definition includes constraints.
所以选项A错误
You cannot delete from a view except through INSTEAD OFtriggers if the defining
query of the view contains one of the followingconstructs:
A set operator
A DISTINCT operator
An aggregate or analytic function
A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITHclause
A collection expression in a SELECT list
A subquery in a SELECT list
A subquery designated WITH READ ONLY
Joins, with some exceptions, as documented in OracleDatabase Administrator's
Guide
所以B选项正确
对视图操作最终会转换对基表操作，所以之前在视图上的操作，视图被删除之后，依然会保留在表上
所以C选项错误
Specify OR REPLACE to re-create the view if it alreadyexists. You can use this clause to
change the definition of an existing view withoutdropping, re-creating, and
regranting object privileges previously granted on it.
所以D选项正确
Specify WITH CHECK OPTION to indicate that Oracle
Database prohibits any changes to the table or view thatwould produce rows that are
not included in the subquery. When used in the subqueryof a DML statement, you
can specify this clause in a subquery in the FROM clausebut not in subquery in the
WHERE clause
所以E选项错误
39. Evaluate thefollowing command:
CREATE TABLE employees
(employee_id NUMBER(2)PRIMARY KEY,
last_name VARCHAR2(25)NOT NULL,
department_idNUMBER(2)NOT NULL,
job_id VARCHAR2(8),
salary NUMBER(10,2));
You issue the followingcommand to create a view that displays the IDs and last names of the salesstaff
in the organization:
CREATE OR REPLACE VIEWsales_staff_vu AS4 ? 4;
SELECT employee_id,last_name,job_id4 ? 4;
FROM employees4 ? 4;
WHERE job_id LIKE'SA_%'4 ? 4;
WITH CHECK OPTION;
Which two statements aretrue regarding the above view? (Choose two.)
A. It allows you toinsert rows into the EMPLOYEES table .
B. It allows you todelete details of the existing sales staff from the EMPLOYEES table.
C. It allows you toupdate job IDs of the existing sales staff to any other job ID in the EMPLOYEEStable.
D. It allows you to insertIDs, last names, and job IDs of the sales staff from the view if it is used in
multitable INSERTstatements.
Answer: BD
解析：
A选项，因为视图只有employee_id, last_name,job_id三个字段，无法进行插入操作，因为基表中的department_id是不允许为空的，而在视图中又不能指定department_id的值
所以A选项错误
B选项中，因为有创建视图时指定了last_name字段，所以视图可以根据last_name删除基表中数据
所以B选项正确
C选项，因为视图创建时，指定了WHERE job_id LIKE 'SA_%'4 ? 4;
WITH CHECK OPTION; 所以更改job_id也只能是'SA_%'4 ? 4这种格式
所以C选项错误
D选项 暂时不知道怎么实现
40. View the Exhibit toexamine the description for the SALES and PRODUCTS tables.
You want to create aSALE_PROD view by executing the following SQL statement:
CREATE VIEW sale_prod
AS SELECT p.prod_id,cust_id, SUM(quantity_sold) "Quantity" , SUM(prod_list_price)"Price"
FROM products p, sales s
WHERE p.prod_id=s.prod_id
GROUP BY p.prod_id,cust_id;
Which statement is trueregarding the execution of the above statement?
A. The view will becreated and you can perform DML operations on the view.
B. The view will becreated but no DML operations will be allowed on the view.
C. The view will not becreated because the join statements are not allowed for creating a view.
D. The view will not becreated because the GROUP BY clause is not allowed for creating a view.
Answer: B
解析：该视图为两个表连接的结果，所以不能dml操作的，因为做了相应的操作视图不知道最后应将操作转换为那个表
所以正确选项为B
41. Which two statementsare true regarding views? (Choose two.)
A. A subquery thatdefines a view cannot include the GROUP BY clause.
B. A view that iscreated with the subquery having the DISTINCT keyword can be updated.
C. A view that iscreated with the subquery having the pseudo column ROWNUM keyword cannot
be updated.
D. A data manipulationlanguage ( DML) operation can be performed on a view that is created with
the subquery having allthe NOT NULL columns of a table.
Answer: CD
解析：
由上题可知，A选项错误
You cannot delete from a view except through INSTEAD OFtriggers if the defining
query of the view contains one of the following constructs:
A set operator
A DISTINCT operator
An aggregate or analytic function
A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITHclause
A collection expression in a SELECT list
A subquery in a SELECT list
A subquery designated WITH READ ONLY
Joins, with some exceptions, as documented in OracleDatabase Administrator's
Guide
所以B选项错误
C选项，rownum当然不可以被修改不管是在视图中，还是在表中
所以C选项正确
D选项，基表中如果没有非空约束，不管视图中的字段有多少，都可以进行操作，视图中没有的字段在基表中都默认为空
所以D选项正确
42. Which threestatements are true regarding views? (Choose three.)
A. Views can be createdonly from tables.
B. Views can be createdfrom tables or other views.
C. Only simple views canuse indexes existing on the underlying tables.
D. Both simple andcomplex views can use indexes existing on the underlying tables.
E. Complex views can becreated only on multiple tables that exist in the same schema.
F. Complex views can becreated on multiple tables that exist in the same or different schemas.
Answer: BDF
解析：
视图是可以根据视图创建的，所以A选项错误，B选项正确
C选项，复杂视图做一些操作时也可以使用基表中的索引
这里说明一下简单视图和复杂视图的定义：
简单视图：数据来源于基表，不包含函数，分组等，可以直接记性dml操作
复杂视图：来源于一个表或者多个表，可以包含连接，函数，分组等，能否直接进行dml操作取决于视图的具体定义
所以D选项正确
创建视图时候，只要指定该表属于哪个模式，是可以跨模式创建视图的，所以E选项错误
F选项正确
43. Evaluate thefollowing CREATE SEQUENCE statement:
CREATE SEQUENCE seq1
START WITH 100
INCREMENT BY 10
MAXVALUE 200
CYCLE
NOCACHE;
The SEQ1 sequence hasgenerated numbers up to the maximum limit of 200. You issue the following
SQL statement:
SELECT seq1.nextval FROMdual;
What is displayed by theSELECT statement?
A. 1
B. 10
C. 100
D. an error
Answer: A
解析：
scott@ORCL>CREATE SEQUENCE seq1
2 START WITH 100
3 INCREMENT BY 10
4 MAXVALUE 200
5 CYCLE
6 NOCACHE;
序列已创建。
scott@ORCL>SELECT seq1.nextval FROM dual;
NEXTVAL
----------
100
不知道答案为什么是A
44. View the Exhibit andexamine the structure of the ORD table.
Evaluate the followingSQL statements that are executed in a user session in the specified order:
CREATE SEQUENCE ord_seq;
SELECT ord_seq.nextval
FROM dual;
INSERT INTO ord
VALUES (ord_seq.CURRVAL,'25-jan-2007',101);
UPDATE ord
SET ord_no=ord_seq.NEXTVAL
WHERE cust_id =101;
What would be theoutcome of the above statements?
A. All the statementswould execute successfully and the ORD_NO column would contain the value 2 for
the CUST_ID 101.
B. The CREATE SEQUENCEcommand would not execute because the minimum value and maximum
value for the sequencehave not been specified.
C. The CREATE SEQUENCEcommand would not execute because the starting value of the sequence
and the increment valuehave not been specified.
D. All the statementswould execute successfully and the ORD_NO column would have the value 20 for
the CUST_ID 101 becausethe default CACHE value is 20.
Answer: A
这里主要考察oracle默认队列的一些值，测试：
scott@ORCL>create sequence zbcxy;
序列已创建。
scott@ORCL>select zbcxy.currval from dual;
select zbcxy.currval from dual
*
第 1 行出现错误:
ORA-08002: 序列 ZBCXY.CURRVAL 尚未在此会话中定义
scott@ORCL>select zbcxy.nextval from dual;
NEXTVAL
----------
1
可以看出序列默认的开始值为0，并且以1增长，最大值为：
Specify NOMAXVALUE to indicate a maximum value of 1028-1 for an
ascending sequence or -1for a descending sequence. This is the default
并且：nocycle
Specify NOCYCLE toindicate that the sequence cannot generate more
values after reachingits maximum or minimum value. This is the default.
所以正确答案为A
45. Which two statementsare true about sequences created in a single instance database? (Choose
two.)
A. The numbers generatedby a sequence can be used only for one table.
B. DELETE
C. CURRVAL is used torefer to the last sequence number that has been generated.
D. When the MAXVALUElimit for a sequence is reached, you can increase the MAXVALUE limit by using
the ALTER SEQUENCEstatement.
E. When a databaseinstance shuts down abnormally, the sequence numbers that have been cached but
not used would beavailable once again when the database instance is restarted.
Answer: CD
解析：
一个序列可以被多个表使用，所以A选项错误
B选项，删除序列：
scott@ORCL>drop sequence zbcxy;
序列已删除。
C选项，当前值就是最后序列最后一次生成的值
D选项：
scott@ORCL>altersequence zbcxy maxvalue 10;
序列已更改。
E选项：
Specify NOCACHE to indicate that values of the sequence are not
preallocated. If youomit both CACHE and NOCACHE, then the database caches 20
sequence numbers by default.
46. Which statements arecorrect regarding indexes? (Choose all that apply.)
A. When a table isdropped, the corresponding indexes are automatically dropped.
B. A FOREIGN KEYconstraint on a column in a table automatically creates a nonunique index.
C. A nondeferrablePRIMARY KEY or UNIQUE KEY constraint in a table automatically creates a unique
index.
D. For each datamanipulation language (DML) operation performed, the corresponding indexes are
automatically updated.
Answer: ACD
解析：
引用官方文档：
All table indexes anddomain indexes are dropped, as well as any triggers defined
on the table, regardlessof who created them or whose schema contains them. If
table is partitioned,then any corresponding local index partitions are also
dropped.
所以A选项正确
B选项，外键不自动创建索引，主键和唯一约束自动创建约束
C选项 主键和唯一性约束自动创建索引，正确
D选项 执行dml操作，索引自动更新，正确
47. View the Exhibit andexamine the structure of ORD and ORD_ITEMS tables.
The ORD_NO column isPRIMARY KEY in the ORD table and the ORD_NO and ITEM_NO columns are
composite PRIMARY KEY inthe ORD_ITEMS table.
Which two CREATE INDEXstatements are valid? (Choose two.)
A. CREATE INDEX ord_idx1
ON ord(ord_no);
B. CREATE INDEX ord_idx2
ON ord_items(ord_no);
C. CREATE INDEX ord_idx3
ON ord_items(item_no);
D. CREATE INDEX ord_idx4
ONord,ord_items(ord_no, ord_date,qty);
Answer: BC
解析：
主键自动创建索引，所以A选项错误
B选项和C选项，因为ord_no和item_no是联合主键，所以可以为它们单独创建索引
D选项：
CREATE INDEX ord_idx4
ON ord,ord_items(ord_no,ord_date,qty);
相当于创建了ord_no,ord_no和ord_date，ord_no和ord_date和qty三个索引
因为ord_no和ord_date索引由ord_no和ord_date联合主键自动创建
所以D选项错误
48. Which two statementsare true regarding indexes? (Choose two.)
A. They can be createdon tables and clusters.
B. They can be createdon tables and simple views.
C. You can create onlyone index by using the same columns.
D. You can create morethan one index by using the same columns if you specify distinctly different
combinations of thecolumns.
Answer: AD
解析：
索引可以表和clusters上面创建，所以A选项正确
索引是不能在视图上创建的，所以B选项错误
由上题D选项可得，C选项错误，D选项正确
49. The ORDERS tablebelongs to the user OE. OE has granted the SELECT privilege on the ORDERS
table to the user HR.
Which statement wouldcreate a synonym ORD so that HR can execute the following query successfully?
SELECT * FROM ord;
A. CREATE SYNONYM ordFOR orders; This command is issued by OE.
B. CREATE PUBLIC SYNONYMord FOR orders; This command is issued by OE.
C. CREATE SYNONYM ordFOR oe.orders; This command is issued by the database administrator.
D. CREATE PUBLIC SYNONYMord FOR oe.orders; This command is issued by the database
administrator.
Answer: D
解析：
由管理员创建的公有同义词，被用户组public所拥有，数据库所有用户都可以使用共有同义词
所以选项D正确
50. SLS is a privatesynonym for the SH.SALES table.
The user SH issues thefollowing command:
DROP SYNONYM sls;
Which statement is trueregarding the above SQL statement?
A. Only the synonymwould be dropped.
B. The synonym would bedropped and the corresponding table would become invalid.
C. The synonym would bedropped and the packages referring to the synonym would be dropped.
D. The synonym would bedropped and any PUBLIC synonym with the same name becomes invalid.
Answer: A
解析：
同义词就像一个指针指向它的对象，如果删除同义词，对该对象毫无影响
详解见我的文章：
http://blog.csdn.net/zbdba/article/details/16938969
所以选项A正确
51. Which statement istrue regarding synonyms?
A. Synonyms can becreated only for a table.
B. Synonyms are used toreference only those tables that are owned by another user.
C. A public synonym anda private synonym can exist with the same name for the same table.
D. The DROP SYNONYMstatement removes the synonym, and the table on which the synonym has
been created becomesinvalid.
Answer: C
解析：
A选项，除了表，还可以为其他对象创建同义词，引用官方文档：
Use the CREATE SYNONYMstatement to create a synonym, which is an alternative
name for a table, view,sequence, operator, procedure, stored function, package,
materialized view, Javaclass schema object, user-defined object type, or another
synonym. A synonymplaces a dependency on its target object and becomes invalid if
the target object is changedor dropped
B选项，引用官方文档：
Synonyms provide both data independence and location transparency. Synonyms
permit applications tofunction without modification regardless of which user owns
the table or view andregardless of which database holds the table or view. However,
synonyms are not asubstitute for privileges on database objects. Appropriate
privileges must begranted to a user before the user can use the synonym
C选项，测试：
scott@ORCL>createsynonym synonmy_emp for emp;
同义词已创建。
scott@ORCL>createpublic synonym synonmyz_emp for emp;
同义词已创建，
所以C选项正确
D选项，由上题可知，删除同义词对对象毫无影响
52. View the Exhibit andexamine the structure of the PRODUCTS table.
Using the PRODUCTStable, you issue the following query to generate the names, current list price,
and discounted listprice for all those products whose list price falls below $10 after a discountof 25% is
applied on it.
SQL>SELECT prod_name,prod_list_price,
prod_list_price -(prod_list_price * .25) "DISCOUNTED_PRICE"
FROM products
WHERE discounted_price
The query generates anerror.
What is the reason forthe error?
A. The parenthesisshould be added to enclose the entire expression.
B. The double quotationmarks should be removed from the column alias.
C. The column aliasshould be replaced with the expression in the WHERE clause.
D. The column aliasshould be put in uppercase and enclosed with in double quotation marks in the
WHERE clause.
Answer: C
解析：
Where 后面的条件是不能用别名的，可以直接使用前面的那个表达式，测试：
scott@ORCL>select sal+1000 "zbcxy" from emp where zbcxy
select sal+1000"zbcxy" from emp where zbcxy
*
第 1 行出现错误:
ORA-00904:"ZBCXY": 标识符无效
scott@ORCL>selectsal+1000 "zbcxy" from emp where (sal+1000)
zbcxy
----------
1968
1950
所以选项C正确
53. View the Exhibit andexamine the data in the PROMOTIONS table.
PROMO_BEGIN_DATE isstored in the default date format, dd-mon-rr.
You need to produce areport that provides the name, cost, and start date of all promos in the POST
category that werelaunched before January 1, 2000.
Which SQL statementwould you use?
A. SELECT promo_name,promo_cost, promo_begin_date
FROM promotions
WHERE promo_category ='post' AND promo_begin_date
B. SELECT promo_name,promo_cost, promo_begin_date
FROM promotions
WHERE promo_cost LIKE'post%' AND promo_begin_date
C. SELECT promo_name,promo_cost, promo_begin_date
FROM promotions
WHERE promo_categoryLIKE 'P%' AND promo_begin_date
D. SELECT promo_name,promo_cost, promo_begin_date
FROM promotions
WHERE promo_categoryLIKE '%post%' AND promo_begin_date
Answer: D
解析：
日期要比较，则需要格式相同，所以只能选D选项
54. View the Exhibit andexamine the structure of the CUSTOMERS table.
Evaluate the querystatement:
SQL> SELECT cust_last_name,cust_city, cust_credit_limit
FROM customers
WHERE cust_last_nameBETWEEN 'A' AND 'C' AND cust_credit_limit BETWEEN
1000 AND 3000;
What would be theoutcome of the above statement?
A. It executessuccessfully.
B. It produces an errorbecause the condition on CUST_LAST_NAME is invalid.
C. It executessuccessfully only if the CUST_CREDIT_LIMIT column does not contain any nullvalues.
D. It produces an errorbecause the AND operator cannot be used to combine multiple BETWEEN
clauses.
Answer: A
解析：
可以正确执行，测试：
scott@ORCL>select *from emp where ename between 'A' and 'D';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ------------------- ---------- -------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-2月 -81 1600 160 30
7698 BLAKE MANAGER 7839 01-5月 -81 3324.24 30
7782 CLARK MANAGER 7839 09-6月 -81 2450 10
7876 ADAMS CLERK 7788 23-5月 -87 1100 20
输出第一个字母为A到D范围的员工
55. Evaluate thefollowing two queries:
SQL> SELECTcust_last_name, cust_city
FROM customers
WHERE cust_credit_limitIN (1000, 2000, 3000);
SQL> SELECTcust_last_name, cust_city
FROM customers
WHERE cust_credit_limit= 1000 OR cust_credit_limit = 2000 OR
cust_credit_limit =3000;
Which statement is trueregarding the above two queries?
A. Performance wouldimprove in query 2.
B. Performance woulddegrade in query 2.
C. There would be nochange in performance.
D. Performance wouldimprove in query 2 only if there are null values in the CUST_CREDIT_LIMIT
column.
Answer: C
解析：
这两种写法没什么差别，都需要和三个值一一比较
56. View the Exhibit andexamine the structure of the PROMOTIONS table.
Using the PROMOTIONStable, you need to find out the names and cost of all the promos done on 'TV'
and 'internet' thatended in the time interval 15th March '00 to 15th October '00.
Which two queries wouldgive the required result? (Choose two.)
A. SELECT promo_name,promo_cost
FROM promotions
A. SELECT promo_name,promo_cost
FROM promotions
WHERE promo_category IN('TV', 'internet') AND
promo_end_date BETWEEN'15-MAR-00' AND '15-OCT-00';
B. SELECT promo_name,promo_cost
FROM promotions
WHERE promo_category ='TV' OR promo_category ='internet' AND
promo_end_date>='15-MAR-00' OR promo_end_date
C. SELECT promo_name,promo_cost
FROM promotions
WHERE (promo_categoryBETWEEN 'TV' AND 'internet') AND
(promo_end_date IN('15-MAR-00','15-OCT-00'));
D. SELECT promo_name,promo_cost
FROM promotions
WHERE (promo_category ='TV' OR promo_category ='internet') AND
(promo_end_date>='15-MAR-00' AND promo_end_date
Answer: AD
解析：
B选项应该用AND连接两个表达式
C选项IN的逻辑错误，题意是在这两个时间之间，IN的意思是等于这两个时间
选项A和D无误
57. The CUSTOMERS tablehas the following structure:
name Null Type
CUST_ID NOT NULL NUMBER
CUST_FIRST_NAME NOT NULLVARCHAR2(20)
CUST_LAST_NAME NOT NULLVARCHAR2(30)
CUST_INCOME_LEVEL VARCHAR2(30)
CUST_CREDIT_LIMIT NUMBER
You need to write aquery that does the following tasks:
1. Display the firstname and tax amount of the customers. Tax is 5% of their credit limit.
2. Only those customerswhose income level has a value should be considered.
3. Customers whose taxamount is null should not be considered.
Which statementaccomplishes all the required tasks?
A. SELECTcust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE cust_income_levelIS NOT NULL AND
tax_amount IS NOT NULL;
B. SELECTcust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE cust_income_levelIS NOT NULL AND
cust_credit_limit IS NOTNULL;
C. SELECTcust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE cust_income_level NULL AND
tax_amount NULL;
D. SELECTcust_first_name, cust_credit_limit * .05 AS TAX_AMOUNT
FROM customers
WHERE(cust_income_level,tax_amount) IS NOT NULL;
Answer: B
解析：
A选项，别名不能用在where后面作为条件
B选项，正确
C选项，别名不能用在where后面作为条件
D选项，别名不能用在where后面作为条件，并且where后面(cust_income_level,tax_amount) IS NOT NULL是错误的
58. The PART_CODE columnin the SPARES table contains the following list of values:
PART_CODE
A%_WQ123
A%BWQ123
AB_WQ123
Evaluate the followingquery:
SQL> SELECT part_code
FROM spares
WHERE part_code LIKE'%\%_WQ12%' ESCAPE '\';
Which statement is trueregarding the outcome of the above query?
A. It produces an error.
B. It displays allvalues.
C. It displays only thevalues A%_WQ123 and AB_WQ123 .
D. It displays only thevalues A%_WQ123 and A%BWQ123 .
E. It displays only thevalues A%BWQ123 and AB_WQ123.
Answer: D
解析：
scott@ORCL>select *from zbcxy;
NAME
------------------------------
A%_WQ123
A%BWQ123
AB_WQ123
scott@ORCL>SELECTname
2 FROMzbcxy
3 WHERE name LIKE '%\%_WQ12%' ESCAPE '\';
NAME
------------------------------
A%_WQ123
A%BWQ123
所以正确选项为D
59. View the Exhibit andexamine the data in the PRODUCTS table.
You need to displayproduct names from the PRODUCTS table that belong to the 'Software/Other '
category with minimumprices as either $2000 or $4000 and no unit of measure.
You issue the followingquery:
SQL>SELECT prod_name,prod_category, prod_min_price
FROM products
WHERE prod_category LIKE'%Other%' AND (prod_min_price = 2000 OR
prod_min_price = 4000)AND prod_unit_of_measure '';
Which statement is trueregarding the above query?
A. It executessuccessfully but returns no result.
B. It executessuccessfully and returns the required result.
C. It generates an errorbecause the condition specified for PROD_UNIT_OF_MEASURE is not valid.
D. It generates an errorbecause the condition specified for the PROD_CATEGORY column is not valid.
Answer: A
解析：
prod_unit_of_measure ''，这里有问题 prod_unit_of_measureis null
所以选项A正确
60. View the Exhibit andexamine the structure of CUSTOMERS table.
Evaluate the followingquery:
SQL>SELECT cust_id,cust_city
FROM customers
WHERE cust_first_nameNOT LIKE 'A_%g_%' AND
cust_credit_limitBETWEEN 5000 AND 15000 AND
cust_credit_limit NOT IN(7000, 11000) AND
cust_city NOT BETWEEN'A' AND 'B';
Which statement is trueregarding the above query?
A. It executessuccessfully.
B. It produces an errorbecause the condition on the CUST_CITY column is not valid.
C. It produces an errorbecause the condition on the CUST_FIRST_NAME column is not valid.
D. It produces an errorbecause conditions on the CUST_CREDIT_LIMIT column are not valid.
Answer: A
解析：
它是能正确执行的，没有任何的语法错误，这里就不做测试了
61. View the Exhibit andexamine the structure of the PROMOTIONS table.
You need to generate areport of all promos from the PROMOTIONS table based on the following
conditions:
1. The promo name shouldnot begin with 'T' or 'N'.
2. The promo should costmore than $20000.
3. The promo should haveended after 1st January 2001.
Which WHERE clause wouldgive the required result?
A. WHERE promo_name NOTLIKE 'T%' OR promo_name NOT LIKE 'N%' AND promo_cost > 20000
AND promo_end_date >'1-JAN-01'
B. WHERE (promo_name NOTLIKE 'T%' AND promo_name NOT LIKE 'N%')OR promo_cost > 20000
OR promo_end_date >'1-JAN-01'
C. WHERE promo_name NOTLIKE 'T%' AND promo_name NOT LIKE 'N%' AND promo_cost > 20000
AND promo_end_date >'1-JAN-01'
D. WHERE (promo_name NOTLIKE '%T%' OR promo_name NOT LIKE '%N%') AND(promo_cost >
20000 AND promo_end_date> '1-JAN-01')
Answer: C
解析：
根据题意，姓名第一个字母中既不能包含T字母也不能包含N字母
所以A和D选项错误
B选项的 or应该换为and条件，所有条件都必须满足，不是
上一篇： Java 编程之IO流资料详细整理