Oracle Database 12c SQL OCA/OCP 1Z0-071题库(6-10题)

QUESTION 6

Which two statements are true regarding constraints? (Choose two)

A. A constraint is enforced only for an INSERT operation on a table.

B. A foreign key cannot contain NULL values.

C. A column with the UNIQUE constraint can store NULLS.

D. You can have more than one column in a table as part of a primary key.

答案：CD

解析：约束constraints：

1. 创建表时，有“内联声明”和“外部定义”两种方式，也可修改表时，ALTER .... ADD CONSTRAINT属于外部定义,MODIFY 属于内联声明；
2. (Composite)PRIMARY KEY：每个值NOT NULL，复合值UNIQUE；
3. FOREIGN KEY：确保父表中在对应的列上已经存在一个UNIQUE（PRIMARY KEY）约束，且后续添加的值已经存在父表对应列中；
4. NOT NULL：不能通过外部定义的方式声明，主键不能NOT NULL，外键和UNIQUE可以NOT NULL；
5. ON DELETE：父表中一行删除，则子表中对应的行或多行也删除。

QUESTION 7

Evaluate the following statement.

INSERT ALL
WHEN order_total < 10000 THEN INTO small_orders
WHEN order_total >10000  AND order_total < 20000 THEN
  		INTO small_orders   
WHEN order_total >200000  AND order_total < 20000 THEN
  		INTO small_orders       
SELECT order_id order_total,customer_id 
FROM orders;

Which statement is true regarding the evaluation of rows returned by the subquery in the INSERT statement?

A. They are evaluated by all the three WHEN clauses regardless of the results of the evaluation of any other WHEN clause.

B. They are evaluated by the first WHEN clause. If the condition is true, then the row would be evaluated by the subsequent WHEN clauses.

C. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequent WHEN clauses.

D. The insert statement would give an error because the ELSE clause is not present for support in case none of WHEN clauses are true.

答案：A

解析：条件多表INSERT语句，注意点：

1. INSERT option...的默认值为ALL（可省略），FIRST是一个可选的option关键字，如果使用了FIRST，只有第一个取值为TRUE的WHEN子句后面的INTO子句才能执行；
2. 每个WHEN都能跟随一个或多个INTO子句每个INTO子句都拥有自己的VALUES子句，如果省略了VALUES子句，那么插入数据的子查询中的各列；
3. 如果语句中包含了可选的ELSE。。。INTO子句，那么这个子句必须位于整体语句最后的位置,子查询前。

QUESTION 8

Examine the structure of the MEMBERS table:

Name                          NULL?                 Type

------------------              --------------         ----------------
MEMBER                     NOT NULL           VARCHAR2(6)
FIRST_NAME                                           VARCHAR2(50)
LAST_NAME                NOT NULL           VARCHAR2(50)
ADDREDD                                               VARCHAR2(50)          
CITY                                                        VARCHAR2(25)      
STATE

You want to display details of all members who reside in states starting with the letter A followed by exactly one character.

Which SQL statement must you execute?

A. SELECT * FROM MEMBERS WHERE state LIKE '%A_';

B. SELECT * FROM MEMBERS WHERE state LIKE 'A_';

C. SELECT * FROM MEMBERS WHERE state LIKE 'A_%';

D. SELECT * FROM MEMBERS WHERE state LIKE 'A%';

答案：B

解析：通配符：

1. 下划线(_)，代表一个字符；
2. 百分号(%)，代表零个、一个或多个字符。

QUESTION 9

You want to display 5 percent of the rows from the SALES table for products with the lowest MOUNT_SOLD and also want to include the rows that have the same AMOUNT_SOLD even if this causes the output to exceed 5 percent of the rows.

Which query will provide the required result?

A. SELECT        prod_id, cust_id, amount_sold
 FROM          sales
    ORDER BY   amount_sold
    FETCH FIRST 5 PERCENT ROWS WITH TIES;

B. SELECT        prod_id, cust_id, amount_sold
		FROM        sales
    ORDER BY   amount_sold
    FETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;

C. SELECT        prod_id, cust_id, amount_sold
		FROM         sales
		ORDER BY   amount_sold
		FETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;

D. SELECT        prod_id, cust_id, amount_sold
		FROM          sales
    ORDER BY   amount_sold
    FETCH FIRST 5 PERCENT ROWS ONLY;

答案：A

解析：使用FETCH来指定SELECT语句返回的行数限制，FETCH子句，注意点：

1. 关键字FIRST/NEXT和ROW/ROWS，两者必选其中一个，语法功能上无区别；
2. 可指定输入的行数，如果没有指定，FETCH 默认返回一行；
3. 子句ONLY/WITH TIES 必须有其中一个，ONLY返回指定的行数或比例，无更多的行，WITH TIES返回指定的行和与该行ORDER BY所指定值相同值的行。

QUESTION 10

Examine the structure of the MEMBERS table:

Name                          NULL?                 Type

------------------              --------------         ----------------
MEMBER                     NOT NULL           VARCHAR(6)
FIRST_NAME                                           VARCHAR(50)
LAST_NAME                NOT NULL           VARCHAR(50)
ADDREDD                                               VARCHAR(50)          

You execute the SQL statement:

SQL > SELECT member_id, ' ' , first_name, ' ' , last_name "ID FIRSTNAME LASTNAME" 
           FROM members;

What is the outcome?

A. It fails because the alias name specified after the column names is invalid.

B. It fails because the space specified in single quotation marks after the first two column names is invalid.

C. It executes successfully and displays the column details in a single column with only the alias column heading.

D. It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.

答案：D

解析：SELECT可以通过加引号(' ')命名新的列，并对该列的每列赋值引号中的内容。

后续陆续更新，转载请注明出处。

本人水平有限，欢迎指正。