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.

<code>INSERT ALL
WHEN order_total < 10000 THEN INTO small_orders
WHEN order_total >10000  AND order_total < 20000 THEN 

  \t\tINTO small_orders   
WHEN order_total >200000  AND order_total < 20000 THEN
  \t\tINTO small_orders       
SELECT order_id order_total,customer_id 
FROM orders;/<code>

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:

<code>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/<code>

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?

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

<code>B. SELECT        prod_id, cust_id, amount_sold
\t\tFROM        sales
    ORDER BY   amount_sold 

    FETCH FIRST 5 PERCENT ROWS ONLY WITH TIES;/<code>

<code>C. SELECT        prod_id, cust_id, amount_sold
\t\tFROM         sales
\t\tORDER BY   amount_sold
\t\tFETCH FIRST 5 PERCENT ROWS WITH TIES ONLY;/<code>

<code>D. SELECT        prod_id, cust_id, amount_sold
\t\tFROM          sales
    ORDER BY   amount_sold
    FETCH FIRST 5 PERCENT ROWS ONLY;/<code>

答案：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:

<code>Name                          NULL?                 Type

------------------              --------------         ----------------
MEMBER                     NOT NULL           VARCHAR(6)
FIRST_NAME                                           VARCHAR(50)
LAST_NAME                NOT NULL           VARCHAR(50)
ADDREDD                                               VARCHAR(50)          /<code>

You execute the SQL statement:

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

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可以通過加引號(' ')命名新的列，並對該列的每列賦值引號中的內容。

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

閱讀更多 洞察數據 的文章

關鍵字: 題庫 數據庫 子句