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

QUESTION 11

You issue the following command to drop the PRODUCTS table:

SQL > DROP TABLE products;

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:

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

  SQL >SELECT 	employee_id, first_name, salary
					FROM	employees
					WHERE hire_date > '&hiredate'
					AND manager_id > &mgr_id;

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?

A. DELETE orders o, order_items i
    WHERE o.order_id = i.order_id;

B. DELETE
    FROM orders
    WHERE (SELECT order_id
    FROM order_items);

C. DELETE orders
    WHERE order_total < 1000;

D. DELETE order_id
    FROM orders
    WHERE order_total < 1000;

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

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

FROM customers;

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

FROM customers;

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

FROM customers;

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

FROM customers;

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

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)

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：

SQL > UPDATE  employees
									SET   department_id  =
                          (SELECT department_id
                               FROM departments
                               WHERE location_id =2100),
                          (salary,commission) =
                          (SELECT 1.1*AVG(salary),1.5*AVG(commission))
          									FROM employees ,departments
                            WHERE departments.location_id IN (2900,2700,2100))
                  WHERE department_ID IN
                               (SELECT department_id 
                                  FROM departments 
            										WHERE location_id =2900
            																	or location_id =2700)

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)。

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

本人水平有限，欢迎指正