Oracle Database 12c SQL OCA/OCP 1Z0-071題庫（11-15題）

QUESTION 11

You issue the following command to drop the PRODUCTS table:

<code>SQL > DROP TABLE products;/<code>

Which three statements are true about the implication of this command? (Choose three.)

A. All data along with the table structure is deleted.

B. A pending transaction in the session is committed.

C. All indexes on the table remain but they are invalidated.

D. All views and synonyms on the table remain but they are invalidated.

E. All data in the table is deleted but the table structure remains.

答案：ABD

解析：DROP table 注意點：

1. 刪除數據以及表結構，作為DDL不可回滾，但是可以恢復（DROP table Purge 不可恢復）；
2. 有約束限制，可增加CASCADE CINSTRAINTS；
3. 索引被刪除；
4. 視圖和別名保留，但是無效。

QUESTION 12

You execute the following commands:

<code>SQL > DEFINE hiredate = '01-APR-2011'；

  SQL >SELECT \temployee_id, first_name, salary
\t\t\t\t\tFROM\temployees
\t\t\t\t\tWHERE hire_date > '&hiredate'
\t\t\t\t\tAND manager_id > &mgr_id;/<code>

For which substitution variables are you prompted for the input?

A. none, because no input required

B. both the substitution variables ''hiredate' and 'mgr_id'.

C. only hiredate'

D. only 'mgr_id'

答案：D

解析：&符號替換變量是一項SQL*PLus功能，注意點：

1. 使用 DEFINE 語句給替換變量的賦值， 可用UNDEFINE 語句取消賦值；
2. SET VARIFY /SET DEFINE來改變系統的默認值，這兩個參數的默認值是 ON和&

QUESTION 13

View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.

ORDER_ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.

Which DELETE statement would execute successfully?

<code>A. DELETE orders o, order_items i
    WHERE o.order_id = i.order_id;/<code>

<code>B. DELETE
    FROM orders
    WHERE (SELECT order_id
    FROM order_items);/<code>

<code>C. DELETE orders
    WHERE order_total < 1000;/<code>

<code>D. DELETE order_id
    FROM orders
    WHERE order_total < 1000;/<code> 

答案：C

解析：DELETE語句用來從數據庫的表中刪除行，注意點：

1. DELETE FROM table 中的FROM為可選字；
2. WHERE子句 為可選字，如省略，則刪除所有行。

QUESTION 14

Using the CUSTOMERS table, you need to generate a report that shows an increase in the credit limit by 15% for all customers. Customers whose credit limit has not been entered should have the message "Not Available" displayed.

Which SQL statement would produce the required result?

<code>A. SELECT NVL (TO CHAR(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"

FROM customers;/<code>

<code>B. SELECT TO_CHAR (NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"

FROM customers;/<code>

<code>C. SELECT NVL(cust_credit_limit * .15), 'Not Available') "NEW CREDIT"

FROM customers;/<code>

<code>D. SELECT NVL(cust_credit_limit), 'Not Available') "NEW CREDIT"

FROM customers;/<code>

答案：A

解析：NVL函數注意點：

1. NVL(exp1,exp2)：如果exp1為NULL，則返回exp2，如果exp1非空，則返回原值exp1；
2. NVL2(exp1,expr2,exp3)：如果exp1為NULL，則返回exp3，如果exp1非空，則返回exp2。

QUESTION 15

View the exhibit and examine the structures of the EMPLOYEES and DEPARTMENTS tables.

<code>Name                          NULL?                 Type

------------------              --------------         ----------------
EMPLOYEE_ID              NOT NULL           NUMBER(6)
FIRST_NAME                                           VARCHAR(20)
LAST_NAME                NOT NULL           VARCHAR(25)
HIRE_DATA                  NOT NULL           DATE          
JOB_ID                         NOT NULL            VARCHAR2(10)
SALARY                                                    NUMBER(10,2)
COMMISSION                                         NUMBER(6,2)
MANAGER_ID                                         NUMBER(6)
DEPARTMENT_ID                                   NUMBER(4)/<code>

You want to update EMPLOYEES table as follows:

• Update only those employees who work in Boston or Seattle (locations 2900 and 2700).

• Set department_id for these employees to the department_id corresponding to London (location_id 2100).

• Set the employees' salary in location_id 2100 to 1.1 times the average salary of their department.

• Set the employees' commission in location_id 2100 to 1.5 times the average commission of their department.

You issue the following command：

<code>SQL > UPDATE  employees
\t\t\t\t\t\t\t\t\tSET   department_id  =
                          (SELECT department_id
                               FROM departments
                               WHERE location_id =2100),
                          (salary,commission) =
                          (SELECT 1.1*AVG(salary),1.5*AVG(commission)) 

          \t\t\t\t\t\t\t\t\tFROM employees ,departments
                            WHERE departments.location_id IN (2900,2700,2100))
                  WHERE department_ID IN
                               (SELECT department_id 
                                  FROM departments 
            \t\t\t\t\t\t\t\t\t\tWHERE location_id =2900
            \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\tor location_id =2700)/<code>

What is outcome?

A. It generates an error because multiple columns (SALARY, COMMISSION) cannot be specified together in an UPDATE statement.

B. It generates an error because a subquery cannot have a join condition in a UPDATE statement.

C. It executes successfully and gives the desired update

D. It executes successfully but does not give the desired update

答案：D

解析：WHERE子句有問題，WHERE departments.location_id IN (2900,2700,2100)。

本人水平有限，歡迎指正

閱讀更多 洞察數據 的文章

關鍵字: SQL 題庫 數據庫