oracle_ocp_071

#part1
[toc]
URL[https://blog.csdn.net/u013012406]

1

Q1.Evaluate the following SQL statement:

FROM customers

WHERE country_id = 10

UNION

SELECT cust_id CUST_NO, cust_last_name

FROM customers

WHERE country_id = 30;

Which ORDER BY clause are valid for the above query?(Choose all that apply.)

::A.ORDER BY 2,1::

B.ORDER BY CUST_NO

::C.ORDER BY 2,cust_id::

D.ORDER BY "CUST_NO"

::E.ORDER BY "Last Name"::

** Answer: A,C,E**

联合查询，使用order by排序的话，以第一个查询的列为准，不能使用第二个查询的列。
##2 
Q2. Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Choose all that apply.)

A.	The HAVING clause can be used with aggregate functions in subqueries.
B.	The WHERE clause can be used to exclude rows after dividing them into groups.
::C.	The WHERE clause can be used to exclude rows before dividing them into groups.::
D.	The aggregate functons and columns used in the HAVING clause must be specifed in the SELECT list of the query.
E.	The WHERE and HAVING clauses can be used in the same statement only if they are applied to difierent columns in the table.

Answer: A, C
对于 B 选项，子句要在 group 语句之前，不能之后排除。
对于 D 选项，聚合函数和 having 子句指定的列不一定要在 select 列中出现。
对于 E 选项，where 和 having 可以是同一列。
##3 
Q3. Which statement is true regarding external tables?

A. The default REJECT LIMIT for external tables is UNLIMITED.
B. The data and metadata for an external table are stored outside the database.
C. ORACLE_LOADER and ORACLE_DATAPUMP have exactly the same functionality when used with an external table.
::D. The CREATE TABLE AS SELECT statement can be used to upload data into regular table in the database from an external table.::

Correct Answer: D
Explanation/Reference:
References:
 [ https://docs.oracle.com/cd/B28359_01/server.111/b28310/tables013.htm ]
本题解释：
A 选项错误的原因是，该子句的默认值是 0 而不是无限；
B 错误是因为源数据是应存在于数据库中；
C 错误是因为他们的功能不一样，一个是 load 时用，一个是 unload 时用；
D CREATE TABLE AS SELECT语句可用于将数据从外部表中装载到数据库中的常规表中
##4 
Q4.Which two statements are true about Data Manipulation Language (DML) statements?
A. An INSERT INTO…VALUES.. statement can add ||**multiple rows**|| per execution to a table.
B. ::An UPDATE… SET… statement can modify multiple rows based on multiple conditions on a table.::
C. A DELETE FROM….. statement can remove rows based on||**only**||  a single condition on a table.
D. An INSERT INTO… VALUES….. statement can add a single row based on _multiple conditions _on a table.
E. ::A DELETE FROM….. statement can remove multiple rows based on multiple conditions on a table::.
F. An UPDATE….SET…. statement can modify multiple rows based on ||**only**|| a single condition on a table.
Answer: BE
解析：
A. An INSERT INTO…VALUES.. 语句每次执行可以向表添加多行。
错误，Oracle不支持一次插入多行数据，mysql可以。
B. An UPDATE… SET… 语句可以基于表上的多个条件修改多行。
正确，Oracle支持多条件更新多行。
C. A DELETE FROM….. 语句只能基于表上的单个条件删除行。
错误，还可以以多条件删除行数据，如选项E。
D. An INSERT INTO… VALUES….. 语句可以基于表上的多个条件添加单个行。
错误，Oracle不支持多条件插入。
E. A DELETE FROM….. 语句可以基于表上的多个条件删除多行。
正确，Oracle支持多条件删除。
F. An UPDATE….SET….语句只能基于表上的单个条件修改多行。
错误，Oracle还支持多条件更新多行，如选项B。
##5 
Q5. Which two statements are true regarding roles? (Choose two.)
A. A role can be granted to itself. 
> A role can be granted to other roles. However, a role cannot be granted to itself and cannot be granted circularly. 
::B. A role can be granted to PUBLIC.::
> A role can be granted system or object privileges.
C. A user can be granted only one role at any point of time.
> Each role granted to a user is, at a given time, either enabled or disabled.
D. The REVOKE command can be used to remove privileges but not roles from other users.
> "Revoking Privileges and Roles from a User" 
::E. Roles are named groups of related privileges that can be granted to users or other roles.::
Correct Answer: BE
[ http://docs.oracle.com/cd/E25054_01/network.1111/e16543/authorization.htm#autoId28 ]
##6
Q6. Which two statements are true regarding constraints? (Choose two)
A.A foreign key cannot contain NULL values.
B.A column with the UNIQUE constraint can contain NULL.
C.A constraint is enforced only for the INSERT operaton on a table.
D.A constraint can be disabled even if the constraint column contains data. 即使约束列包含数据，约束也可以被禁用。
E.All the constraints can be defned at the column level as well as the table level
Correct Answer: BD
对于 A 选项，foreign key 可以包含 null
对于 C 选项，constaint 在 delete，update 的时候也会应用。
对于 E 选项，not null 不能在表级别定义，只能在列级别定义。
##7
Q7.Evaluate the following statement.
INSERT ALL
WHEN order_total < 10000 THEN
INTO small_orders
WHEN order_total > 10000 AND order_total < 20000 THEN
INTO medium_orders
WHEN order_total > 200000 AND order_total < 20000 THEN
INTO large_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 subsequentWHEN clauses.
C. They are evaluated by the first WHEN clause. If the condition is false, then the row would be evaluated by the subsequentWHEN 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.
Correct Answer: A
Explanation/Reference:
References:
http://psoug.org/definition/WHEN.htm
三个 where 条件是单独分开的，不会各自影响。
##8
Q8. Examine the structure of the MEMBERS table:
Name Null? Type
-————— —————– —————————
MEMBER_ID NOT NULL VARCHAR2 (6)
FIRST_NAME VARCHAR2 (50)
LAST_NAME NOT NULL VARCHAR2 (50)
ADDRESS VARCHAR2 (50)
CITY VARCHAR2 (25)
STATE VARCHAR2 (3)
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%’;*
**c**
##9
Q9. You want to display 5 percent of the rows from the SALES table for products with the lowest AMOUNT_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;
参考：[][]
> [ OFFSET offset { ROW | ROWS} ]
[ FETCH { FIRST | NEXT }[ { rowcount | percent PERCENT } ]
    { ROW| ROWS } { ONLY | WITH TIES } ]  
##10 
Q10. Examine the structure of the MEMBERS table:
Name Null? Type
-—————– ————— ——————————
MEMBER_ID NOT NULL VARCHAR2 (6)
FIRST_NAME VARCHAR2 (50)
LAST_NAME NOT NULL VARCHAR2 (50)
ADDRESS VARCHAR2 (50)
You execute the SQL statement:
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.::
##11
Q11. 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?
A. All data in the table is deleted but the table structure remains.
_B. All data along with the table structure is deleted._
_C. All views and synonyms on the table remain but they are invalidated._
D. A pending transaction in the session is committed.
_E. All indexes on the table remain but they are invalidated._
**删除表的本质，表中数据以及表结构被删除，但是表上的视图以及索引会invalidate**
##12
Q12. 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’
Correct Answer: D
##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.
(img)
Which DELETE statement would execute successfully?
A. DELETE order_id FROM orders WHERE order_total < 1000;
B. DELETE orders WHERE order_total < 1000;
C. DELETE FROM orders WHERE (SELECT order_id FROM order_items);
D. DELETE orders o, order_items i WHERE o.order id = i.order id;_
cascade删除的效果
##14
Q14. View the Exhibit and examine the structure of CUSTOMERS table.
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?
(img)
A. SELECT NVL(cust_credit_limit), ‘Not Available’) "NEW CREDIT"
FROM customers;
B. SELECT NVL(cust_credit_limit * .15), ‘Not Available’) "NEW CREDIT"
FROM customers;
C. SELECT TO_CHAR (NVL(cust_credit_limit * .15), ‘Not Available’) "NEW CREDIT"  FROM customers;
::D. SELECT NVL (TO CHAR(cust_credit_limit * .15), ‘Not Available’) "NEW CREDIT" FROM customers;*::
**> Converts a null value to an actual value:**
**Data types that can be used are date, character, and number.**
**Data types must match:**
##15
Q15. See the Exhibit and examine the structure of the PROMOTIONS table:
Exhibit:
(img)
Using the PROMOTIONS table, you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A.
You issue the following SQL statements:
Exhibit:
(img)
What would be the outcome?
A. It generates an error because multiple conditions cannot be specified for the WHEN clause
B. It executes successfully and gives the required result
C. It generates an error because CASE cannot be used with group functions
D. It generates an error because NULL cannot be specified as a return value
> Facilitates conditional inquiries by doing the work of an
IF-THEN-ELSE statement:
> Group functions ignore null values in the column: avg 
##16
Q15. Evaluate the following two queries:
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit IN (1000, 2000, 3000);
SQL> SELECT cust_last_name, cust_city
FROM customers
WHERE cust_credit_limit = 1000 or cust_credit_limit = 2000 or cust_credit_limit = 3000
Which statement is true regarding the above two queries?
A. Performance would improve in query 2 only if there are null values in theCUST_CREDIT_LIMIT column.
::B. There would be no change in performance.::
::C. Performance would degrade in query 2.::
D. Performance would improve in query 2.
##17
Q17. Examine the business rule:
Each student can take up multiple projects and each project can have multiple students.
You need to design an Entity Relationship Model (ERD) for optimal data storage and allow for generating reports in this format:
STUDENT_ID FIRST_NAME LAST_NAME PROJECT_ID PROJECT_NAME PROJECT_TASK
Which two statements are true in this scenario?
A. The ERD must have a 1:M relationship between the STUDENTS and PROJECTS entities.
B. ::The ERD must have a M:M relationship between the STUDENTS and PROJECTS entities that must be resolved into 1:M relationships.::
C. STUDENT_ID must be the primary key in the STUDENTS entity and foreign key in the PROJECTS entity.
D. PROJECT_ID must be the primary key in the PROJECTS entity and foreign key in the STUDENTS entity.
::E. An associative table must be created with a composite key of STUDENT_ID and PROJECT_ID, which is the foreign key linked to the STUDENTS and PROJECTS entities. ::
##18
Q18. View the Exhibit and examine the details of the PRODUCT_INFORMATION table._
(img)
You have the requirement to display PRODUCT_NAME and LIST_PRICE from the table where the CATEGORYJD column has values 12 or 13, and the SUPPLIER_ID column has the value 102088.
You executed the following SQL statement:
SELECT product_name, list_price
FROM product_information
WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088;
Which statement is true regarding the execution of the query?
::A. It would execute but the output would return no rows.::
B. It would execute and the output would display the desired result.
C. It would not execute because the entire WHERE clause condition is not enclosed within the parentheses.
D. It would not execute because the same column has been used in both sides of the AND logical operator to form the condition.
##19
Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)
A. The outer query** stops** evaluating the result set of the inner query when the frst value is found.
B. It is used to test whether the values retrieved by the inner query exist in the result of the outer query.
::C. It is used to test whether the values retrieved by the outer query exist in the result set of the inner query. ::
::D. The outer query **contnues** evaluating the result set of the inner query untl all the values in the result set are::
##20 
 You want to display the NAME of the store along with theADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price.
The stores displayed must have START_DATE in the range of 36 months starting from 01-Jan-2000 and above.
Which SQL statement would get the desired output?
A. SELECT name, concat (address| | ‘,’| |city| |’, ‘, country) AS full_address, start_date, property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, ’01-JAN-2000′) <=36;
B. SELECT name, concat (address| | ‘,’| |city| |’, ‘, country) AS full_address, start_date, property_price, property_price*115/100
FROM stores
WHERE TO_NUMBER(start_date-TO_DATE(’01-JAN-2000′,’DD-MON-RRRR’)) <=36;
C. SELECT name, address||’,’||city||’,’||country AS full_address, start_date, property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE(’01-JAN-2000′,’DD-MON-RRRR’)) <=36;
D. SELECT name, concat (address||’,’| |city| |’, ‘, country) AS full_address, start_date, property_price, property_price*115/100
FROM stores
WHERE MONTHS_BETWEEN (start_date, TO_DATE(’01-JAN-2000′,’DD-MON-RRRR’)) <=36;_
##21
Q21. The BOOKSTRANSACTIONS table exists in your database.
SQL>SELECT  FROM bookstransactions ORDER BY 3;
What is the outcome on execution?
A. The execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.
B. Rows are displayed in the order that they are stored in the table only for the three rows with the lowest values in the key column.
C. Rows are displayed in the order that they are stored in the table only for the first three rows.
_D. Rows are displayed sorted in ascending order of the values in the third column in the table._
##22
(img)
You want to display all employees and their managers having 100 as the MANAGER_ID. You want the output in two columns: the first column would have the LAST_NAME of the managers and the second column would have LAST_NAME of the employees.
Which SQL statement would you execute?
##23
Q24. Which two statements are true regarding multiple-row subqueries? (Choose two.)
A. They can contain group functions.
B. They always contain a subquery within a subquery.
C. They use the < ALL operator to imply less than the maximum.
D. They can be used to retrieve multiple rows from a single table only.
E. They should not be used with the NOT IN operator in the main query if NULL is likely to be a part of the result of the subquery.
##26
Q26. Which normal form is a table in if it has no multi-valued attributes and no partial dependencies?
A. first normal form
B. second normal form
C. third normal form
D. fourth normal form
##27
 (img)
The CUSTOMERS table contains the current locaton of all currently actve customers. The CUST_HISTORY table stores historical details relatng to any changes in the locaton of all current as well as previous customers who are no longer actve with the company.
You need to fnd those customers who have never changed their address. Which SET operator would you use to get the required output?
A. INTERSECT 
B. UNION ALL 
C. MINUS
D. UNION
Answer: C
**A有B没🈶️ a -b **
##28
Q28. Evaluate the following ALTER TABLE statement:
ALTER TABLE orders
SET UNUSED (order_date);
Which statement is true?
A. The DESCRIBE command would still display the ORDER_DATE column.
B. ROLLBACK can be used to get back the ORDER_DATE column in the ORDERS table.
C. The ORDER_DATE column should be empty for the ALTER TABLE command to execute succsessfully.
::D. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table._::
>drop 锁表
> 使用set unused，等系统空闲时再drop unused。
**清除掉字典信息（撤消存储空间），不可恢复。**
**   可以使用 SET UNUSED 标记一列或者多列不可用。**
**   使用DROP SET UNUSED 删除set unused的列**
##29 
Q29. Evaluate the following SQL statements that are issued in the given order:
CREATE TABLE emp
(emp_no NUMBER(2) CONSTRAINT emp_emp_no_pk PRIMARY KEY, enameVARCHAR2(15),
salary NUMBER(8,2),
mgr_no NUMBER(2) CONSTRAINT emp_mgr_fk REFERENCES emp);

ALTER TABLE emp
DISABLE CONSTRAINT emp_emp_no_pk CASCADE;

ALTER TABLE emp
ENABLE CONSTRAINT emp_emp_no_pk;
What would be the status of the foreign key EMP_MGR_FK?
A. It would be automatically enabled and deferred.
B. It would be automatically enabled and immediate.
C. It would remain disabled and has to be enabled manually using the ALTER TABLE command.
D. It would remain disabled and can be enabled only by dropping the foreign key constraint and re- creating it.
##30
Q30. Which three statements are true regarding the data types?
::A. Only one LONG column can be used per table.::
> The use of LONG values is subject to these restrictions:A table can contain only one LONG column.
B. A TIMESTAMP data type column stores **only** time values with fractional seconds.
> The TIMESTAMP datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus hour, minute, and second values. This datatype is useful for storing precise time values.
C. The BLOB data type column is used to store binary data in an operating system file.
> The BFILE datatype enables access to binary file LOBs that are stored in file systems outside Oracle Database
::D. The minimum column width that can be specified for a VARCHAR2 data type column is one.::
> You must specify a maximum length for a VARCHAR2 column. This maximum must be at least 1 byte,
::E. The value for a CHAR data type column is blank-padded to the maximum defined column width.::
> The default length for a CHAR column is 1 byte and the maximum allowed is 2000 bytes. A 1-byte string can be inserted into a CHAR(10) column, but the string is blank-padded to 10 bytes before it is stored.
[[] https://docs.oracle.com/cd/B28359_01/server.111/b28286/sql_elements001.htm#SQLRF50950 ]
##32
Q32. Which statement is true regarding the default behavior of the ORDER BY clause?
::A. In a character sort, the values are case-sensitive.::
B. NULL values are not considered at all by the sort operation.
C. Only those columns that are specified in the SELECT list can be used in the ORDER BY clause.
D. Numeric values are displayed from the maximum to the minimum value if they have decimal positions.
##33
Q33. Examine the structure of the MEMBERS table.
Name Null? Type
-————— —————– —————————
MEMBER_ID NOT NULL VARCHAR2 (6)
FIRST_NAME VARCHAR2 (50)
LAST_NAME NOT NULL VARCHAR2 (50)
ADDRESS VARCHAR2 (50)
CITY VARCHAR2 (25)
STATE NOT NULL VARCHAR2 (3)
Which query can be used to display the last names and city names only for members from the states MO and MI?
A. SELECT last_name, city FROM members WHERE state =’MO’ AND state =’MI’;
B. SELECT last_name, city FROM members WHERE state LIKE ‘M%’;
::C. SELECT last_name, city FROM members WHERE state IN (‘MO’, ‘MI’);::
D. SELECT DISTINCT last_name, city FROM members WHERE state =’MO’ OR state =’MI’;_
##34
Q34. Which two statements are true regarding the COUNT function?
::A. COUNT () returns the number of rows including duplicate rows and rows containing NULL value in any of the columns.::
> COUNT(*) returns the number of rows in a table
B. COUNT (custid) returns the number of rows including rows with duplicate customer IDs and NULL value in the CUSTID column.
> COUNT(expr) returns the number of rows with non-null values for expr
::C. COUNT (DISTINCT invamt) returns the number of rows excluding rows containing duplicates and NULL values in the INVAMT column.::
> COUNT(DISTINCT expr) returns the number of distinct non-null values of expr.
D. A SELECT statement using the COUNT function with a DISTINCT keyword **cannot** have a WHERE clause.
E. The COUNT function can be used **only** for CHAR, VARCHAR2, and NUMBER data types.
##35
Q35. Which task can be performed by using a single Data Manipulation Language (DML) statement?
A. adding a column constraint when inserting a row into a table
B. adding a column with a default value when inserting a row into a table
::C. removing all data only from one single column on which a unique constraint is defined::
D. removing all data only from one single column on which a primary key constraint is defined
##36
Q37. Examine the structure of the BOOKS_TRANSACTIONS table:
Name Null? Type
-————— —————– —————————
TRANSACTION_ID NOT NULL VARCHAR2 (6)
BORROWED_DATE VARCHAR2 (50)
DUE_DATE DATE
BOOK_ID DATE
MEMBER_ID VARCHAR2 (6)
You want to display the member IDs, due date, and late fee as $2 for all transactions.
Which SQL statement must you execute?
A. SELECT memberid AS MEMBERID, duedate AS DUEDATE, $2 AS LATEFEE FROM BOOKSTRANSACTIONS;
B. SELECT memberid ‘MEMBER ID’, duedate ‘DUE DATE’, ‘$2 AS LATE FEE’ FROM BOOKSTRANSACTIONS;
::C. SELECT memberid AS "MEMBER ID", duedate AS "DUE DATE", ‘$2’ AS "LATE FEE" FROM BOOKSTRANSACTIONS;::
D. SELECT memberid AS "MEMBER ID", duedate AS "DUE DATE", $2 AS "LATEFEE" FROM BOOKSTRANSACTIONS;
Alias 定义，可用as 和空格，别名需要使用双引号
> A column alias:
> Renames a column heading
> Is useful with calculations
> Immediately follows the column name (There can also be the optional AS keyword between the column name and the alias.)
> Requires double quotation marks if it contains spaces or special characters, or if it is case-sensitive
##37
Q37. When does a transaction complete? (Choose all that apply.)
A. When a PL/SQL anonymous block is executed
B. When a DELETE statement is executed
::C. When a data definition language statement is executed::
::D. When a TRUNCATE statement is executed after the pending transaction::
::E. When a ROLLBACK command is executed::
> A transaction ends when any of the following occurs:
> A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
> A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If the current transaction contains any DML statements, Oracle first commits the transaction, and then runs and commits the DDL statement as a new, single statement transaction.
> A user disconnects from Oracle. The current transaction is committed.
> A user process terminates abnormally. The current transaction is rolled back.
[[] https://docs.oracle.com/cd/B19306_01/server.102/b14220/transact.htm ]
##38
Q38. View the exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.
(img)

Evaluate the following MERGE statement:
MERGE_INTO orders_master o
USING monthly_orders m
ON (o.order_id = m.order_id)
WHEN MATCHED THEN
UPDATE SET o.order_total = m.order_total
DELETE WHERE (m.order_total IS NULL)
WHEN NOT MATCHED THEN
INSERT VALUES (m.order_id, m.order_total)
What would be the outcome of the above statement?

A. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2, 3 and 4.
::B. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2 and 4.::
C. The ORDERS_MASTERtable would contain the ORDER_IDs1, 2 and 3.
D. The ORDERS_MASTERtable would contain the ORDER_IDs1 and 2.
[[] https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9016.htm#SQLRF01606 ]
> merge_update_clause
The merge_update_clause specifies the new column values of the target table. Oracle performs this update if the condition of the ON clause is true. If the update clause is executed, then all update triggers defined on the target table are activated.
Specify the where_clause if you want the database to execute the update operation only if the specified condition is true. The condition can refer to either the data source or the target table. If the condition is not true, then the database skips the update operation when merging the row into the table.
Specify the DELETE where_clause to clean up data in a table while populating or updating it. **The only rows affected by this clause are those rows in the destination table that are updated by the merge operation**. The DELETE WHERE condition evaluates the updated value, not the original value that was evaluated by the UPDATE SET ... WHERE condition. If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted. Any delete triggers defined on the target table will be activated for each row deletion.
##39
Q39. Evaluate the following SQL statement:
`SELECT product_name || ‘it’s not available for order’
FROM product_information
WHERE product_status = ‘obsolete’;
You received the following error while executing the above query:
ERROR
ORA-01756: quoted string not properly terminated
What would you do to execute the query successfully?
A. Enclose the literal character string in the SELECT clause within the double quotation marks.
B. Do not enclose the character literal string in the SELECT clause within the single quotation marks.
::C. Use Quote (q) operator and delimiter to allow the use of single quotation mark in the literal character string.::
D. Use escape character to negate the single quotation mark inside the literal character string in the SELECT clause.
> Character literals are enclosed in single quotation marks so that Oracle can distinguish them from schema object names.
##41
Q41. Examine the structure of the INVOICE table.
Name Null? Type
-—————– ——————- ————-
INV_NO NOT NULL NUMBER(3)
INV_DATE DATE
INV_AMT NUMBER(10,2)
Which two SQL statements would execute successfully?
::A. SELECT inv_no, NVL2(inv_date, ‘Pending’, ‘Incomplete’)::
::FROM invoice;::
B. SELECT inv_no, NVL2(inv_amt, inv_date, ‘Not Available’)
FROM invoice;
::C. SELECT inv_no, NVL2(inv_date, sysdate-inv_date, sysdate)::
::FROM invoice;::
D. SELECT inv_no, NVL2(inv_amt, inv_amt*.25, ‘Not Available’)
FROM invoice;;
**NVL2 类型要匹配，if -then else ,NVL返回的类型也要一致，包含了隐式类型转换  **
Char /varchar ==> number /date是implicit
**
##42
Q42. Which three statements are true about the ALTER TABLE….DROP COLUMN…. command?
A. A column can be dropped **only if** it does not contain any data.
B. A column can be dropped only if another column exists in the table.
C. A dropped column can be rolled back.
D. The column in a composite PRIMARY KEY with the CASCADE option can be dropped.
E. A parent key column in the table cannot be dropped.
##43
View the Exhibit and examine the description of the PRODUCTINFORMATION table. Which SQL statement would retrieve from the table the number of products having LISTPRICE as NULL?
(img)
A. SELECT COUNT(listprice) FROM productinformation WHERE listprice IS NULL;
B. SELECT COUNT(list-price) FROM productinformation WHERE listprice = NULL;
C. SELECT COUNT(NVL(listprice, 0)) FROM productinformation WHERE listprice IS NULL;

D. SELECT COUNT(DISTINCT listprice) FROM productinformation WHERE listprice IS NULL;

> COUNT(*) returns the number of rows in a table
> COUNT(expr) returns the number of rows with non-null values for expr
> COUNT(DISTINCT expr) returns the number of distinct non-null values of expr.
> 这里要统计null出现的次数，需要做一次现实的转换nvl(exp,0) ,where的条件 为 is null 
##44
Which three tasks can be performed using SQL functions built into Oracle Database?
A. displaying a date  in a nondefault format 
>to_char_
B. finding the number of characters in an expression
>substr
C. substituting a character string in a text expression with a specified string
>replace
D. combining **more than two columns** or expressions into a single column in the output 
>concat(ch1,ch2) 
##45
Q45. The user SCOTT who is the owner of ORDERS and ORDER_ITEMS tables issues the following GRANT command:
GRANT ALL
ON orders, order_items
TO PUBLIC;
What correction needs to be done to the above statement?
A. PUBLIC should be replaced with specific usernames.
> PUBLIC 
> Specify PUBLIC to grant the privileges to all users.
B. ALL should be replaced with a list of specific privileges.
> ALL [PRIVILEGES]
> Specify ALL to grant all the privileges for the object that you have been granted with the GRANT OPTION. The user who owns the schema containing an object automatically has all privileges on the object with the GRANT OPTION. The keyword PRIVILEGES is provided for semantic clarity and is optional.
C. WITH GRANT OPTION should be added to the statement.
D. Separate GRANT statements are required for ORDERS and ORDER_ITEMS tables._
>每次授权仅能针对一张表
##46
You are designing the structure of a table in which two columns have the specifications:
COMPONENTID – must be able to contain a maximum of 12 alphanumeric characters and uniquely identify the row
EXECUTIONDATETIME – contains Century, Year, Month, Day, Hour, Minute, Second to the maximum precision and is used for calculations and comparisons between components.
Which two options define the data types that satisfy these requirements most efficiently?
::A. The EXECUTIONDATETIME must be of INTERVAL DAY TO SECOND data type. ::
B. The EXECUTIONDATETIME must be of TIMESTAMP data type.
C. The EXECUTIONDATETIME must be of DATE data type.
D. The COMPONENTID must be of ROWID data type.
E. ::TheCOMPONENTIDmustbeofVARCHAR2datatype::. 
F. TheCOMPONENTIDcolumnmustbeofCHARdatatype.

##47
Q47. You want to display the date for the first Monday of the next month and issue the following command:
(img)
What is the outcome?
A. It executes successfully and returns the correct result.
B. It executes successfully but does not return the correct result.
C. It generates an error because TO_CHAR should be replaced with TO_DATE.
D. It generates an error because rrrr should be replaced by rr in the format string.

E. It generates an error because fm and double quotation marks should not be used in the format string.
##48
Q48. Which two statements are true regarding the GROUP BY clause in a SQL statement? (Choose two.)
A. You can use column alias in the GROUP BY clause.
> You cannot use a column alias in the GROUP BY clause.
B. Using the WHERE clause after the GROUP BY clause excludes the rows after creating groups.
> before
C. The GROUP BY clause is mandatory if you are using an aggregate function in the SELECT clause.  
>select max() min()，avg(),count()
::D. Using the WHERE clause before the GROUP BY clause excludes the rows before creating groups.::
> 正解
::E. If the SELECT clause has an aggregate function, then those individual columns without an aggregate function in theSELECT clause should be included in the GROUP BY cause.::
>正解
> All the columns in the SELECT list that are not in group functions must be in the GROUP BY clause
##49
QUESTION 49
Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:
CREATE TABLE DEPARTMENT_DETAILS (
DEPARTMENT_ID NUMBER PRIMARY KEY, 
DEPARTMENT_NAME VARCHAR2(50), 
HOD VARCHAR2(50)); 

CREATE TABLE COURSE_DETAILS (
COURSE_ID NUMBER PRIMARY KEY,
COURSE_NAME VARCHAR2(50),
DEPARTMENT_ID VARCHAR2(50));
You want to generate a list of all department IDs along with any course IDs that may have been assigned to them. Which SQL statement must you use?
##50
Which two tasks can be performed by using Oracle SQL statements?
::A. changing the password for an existing database user ::
>alter user identify by 
B. connectingtoadatabaseinstance
> sqlplus connect 
::C. querying data from tables across databases ::
::>select a.* ,b.* from xx.a ,xx.b ::
D. starting up a database instance
>startup 
E. executing operatingsystem(OS) commands in asession
>sqlplus !command
##51
View the exhibit for the structure of the STUDENT and FACULTY tables.
(img)
You need to display the faculty name followed by the number of students handled by the faculty at the base location. Examine the following two SQL statements:
Statement 1
SQL>SELECT faculty_name, COUNT(student_id) FROM student JOIN faculty
USING (faculty_id, location_id)
GROUP BY faculty_name;
Statement 2
SQL>SELECT faculty_name, COUNT(student_id) FROM student NATURAL JOIN faculty GROUP BY faculty_name;
Which statement is true regarding the outcome?
##52
Which statement correctly grants a system privilege?
A. GRANT CREATE VIEW ON table1 TO user1;
B. GRANT ALTER TABLE TO PUBLIC;
C. GRANT CREATE TABLE TO user1, user2;
::D. GRANT CREATE SESSION TO ALL;::
##53
##54
QUESTION 54
Which statements are correct regarding indexes? (Choose all that apply.)
A. A non-deferrable PRIMARYKEY or UNIQUEKEY constraint in a table automatically attempts to creates a unique index. 
B.Indexes should be created on columns that are frequently referenced as part of:: any ::expression.
C. When a table is dropped, the corresponding indexes are automatically dropped.
D. For each DML operation performed, the corresponding indexes are automatically updated.
##55 
##56
Q56. Which two statements are true about sequences created in a single instance database? (Choose two.)
::A. CURRVAL is used to refer to the last sequence number that has been generated.::
B. DELETE <sequence-name> would remove a sequence from the database.
>drop sequence 
C. The numbers generated by a sequence can be used **only** for one table.
> Sequence numbers are generated independently of tables, so the same sequence can be used for one or for multiple tables.[[] https://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_6015.htm#SQLRF01314 ]
::D. When the MAXVALUE limit for the sequence is reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement.::
E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be **available** once again when the database instance is restarted.
> If a system failure occurs, then all cached sequence values that have not been used in committed DML statements are lost. The potential number of lost values is equal to the value of the CACHE parameter.
##57

##58 

##59
Q59. Which statement is true about transactions?
A. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINT forms a single transaction.
::B. Each Data Definition Language (DDL) statement executed forms a single transaction.::
C. A set of DDL statements executed in a sequence ending with a COMMIT forms a single transaction.
D. A combination of DDL and DML statements executed in a sequence ending with a COMMIT forms a single transaction.
> A database transaction consists of one or more statements. Specifically, a transaction consists of one of the following:
1. One or more data manipulation language (DML) statements that together constitute an atomic change to the database
2. One data definition language (DDL) statement
##60 
(img)
You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATE VIEW statement would create the views successfully?
##61 
Q61. Which statement is true about an inner join specified in the WHERE clause of a query?
A. It must have primary-key and foreign-key constraints defined on the columns used in the join condition.
B. It requires the column names to be the same in all tables used for the join conditions.
::C. It is applicable for equijoin and nonequijoin conditions.::
D. It is applicable for only equijoin conditions.
##62 
Q62. Which statement is true regarding the INTERSECT operator?
A. It ignores NULL values.
B.The number of columns and data types must be identical for all SELECT statements in the query.
C. The names of columns in all SELECT statements must be identical. 
D. Reversing the order of the intersected tables alters the result.
##63 
Q63. Examine the following query:
SQL> SELECT prod_id, amount_sold
FROM sales
ORDER BY amount_sold
FETCH FIRST 5 PERCENT ROWS ONLY;
What is the output of this query?
A. It displays 5 percent of the products with the highest amount sold.
B. It displays the first 5 percent of the rows from the SALES table.
::C. It displays 5 percent of the products with the lowest amount sold.::
D. It results in an error because the ORDER BY clause should be the last clause.
##64
The first DROP operation is performed on PRODUCTS table using the following command:
DROP TABLE products PURGE;
Then you performed the FLASHBACK operation by using the following command:
FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACK command?
A. It recovers only the table structure.
B. It recovers the table structure, data, and the indexes.
C. It recovers the table structure and data but not the related indexes.
::D. It is not possible to recover the table structure, data, or the related indexes.::
> PURGE
> Specify PURGE if you want to drop the table and release the space associated with it in a single step. If you specify PURGE, then the database does not place the table and its dependent objects into the recycle bin.
> Caution:
> You cannot roll back a DROP TABLE statement with the PURGE clause, nor can you recover the table if you have dropped it with the PURGE clause.
##65
Q65. The following are the steps for a correlated subquery, listed in random order:
1. The WHERE clause of the outer query is evaluated.
2. The candidate row is fetched from the table specified in the outer query.
3. The procedure is repeated for the subsequent rows of the table, till all the rows are processed.
4. Rows are returned by the inner query, after being evaluated with the value from the candidate row in the outer query.
Identify the option that contains the steps in the correct sequence in which the Oracle server evaluates a correlated subquery.
A. 4, 2, 1, 3
::B. 4, 1, 2, 3::
C. 2, 4, 1, 3
D. 2, 1, 4, 3
##66
Q66. Evaluate the following query:
SQL> SELECT TRUNC (ROUND(156.00, -1),-1)
FROM DUAL;
What would be the outcome?
A. 200
B. 16
::C. 160::
D. 150
E. 100
ROUND(156.00, -1)===>160 
trunc(160,-1) ==> 160 
##67
Q67. Examine the data in the CUST_NAME column of the CUSTOMERS table.
CUST_NAME
-——————
Renske Ladwig
Jason Mallin
Samuel McCain
Allan MCEwen
Irene Mikilineni
Julia Nayer
You need to display customers’ second names where the second name starts with "Mc" or "MC".
Which query gives the required output?
A. SELECT SUBSTR (cust_name, INSTR (cust_name, ‘ ‘)+1)
FROM customers
WHERE SUBSTR (cust_name, INSTR (cust_name, ‘ ‘)+1)
LIKE INITCAP (‘MC%’);
B. SELECT SUBSTR (cust_name, INSTR (cust_name, ‘ ‘)+1)
FROM customers
WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ‘ ‘)+1)) = ‘Mc’;
::C. SELECT SUBSTR (cust_name, INSTR (cust_name, ‘ ‘)+1)::
::FROM customers::
::WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ‘ ‘)+1))::
::LIKE ‘Mc%’;::
D. SELECT SUBSTR (cust_name, INSTR (cust_name, ‘ ‘)+1)
FROM customers
WHERE INITCAP (SUBSTR(cust_name, INSTR (cust_name, ‘ ‘)+1)) = INITCAP ‘MC%’;
>split 
>pos =instr(cust_name,’’) +1_
> n = substr(cust_name,pos) _
> initcap(n) mC/mc ==>Mc
> like 
> Mc% 
> char 函数的使用
##68
(img)
The PROD_ID column is the foreign key in the SALES tables, which references the PRODUCTS table.
Similarly, the CUST_ID and TIME_ID columns are also foreign keys in the SALES table referencing the CUSTOMERS and TIMES tables, respectively.
Evaluate the following CREATE TABLE command:
CREATE TABLE new_sales (prod_id, cust_id, order_date DEFAULT SYSDATE)
AS
SELECT prod_id, cust_id, time_id
FROM sales;
Which statement is true regarding the above command?
A. The NEW_SALES table would get created and all the NOT NULL constraints defined on the specified columns would be passed to the new table.
B. The NEW_SALES table would not get created because the DEFAULT value cannot be specified in the column definition.
C. The NEW_SALES table would not get created because the column names in the CREATE TABLE command and the SELECT clause do not match.
D. The NEW_SALES table would get created and all the FOREIGN KEY constraints defined on the specified columns would be passed to the new table._
##69
SELECT constraintname, constrainttype, searchcondition, rconstraintname, deleterule, status FROM userconstraints WHERE tablename = ORDERS
Which two statements are true about the output? (Choose two.)
(img)
::A. In the second column, indicates a check constraint.::
B. The STATUS column indicates whether the **table** is currently in use.
C. The RCONSTRAINTNAME column gives the alternative name for the constraint.
::D. The column DELETERULE decides the state of the related rows in the child table when the corresponding row is deleted from the parent table.::
> > 对于 B 选项，status 是指约束的状态。
> 对于 C 选项，R_CONSTRAINT_NAME 指的关联的父表中主键约束的名称。
> 利用数据字典user_constraints可以查看用户模式下的表的约束信息 
> 利用数据字典user_cons_columns可以查看每张表定义的约束是在哪一列上
##70
Q70. Which three statements are true regarding group functions? (Choose three.)
::A. They can be used on columns or expressions.::
::B. They can be passed as an argument to another group function.::
C. They can be used **only** with a SQL statement that has the GROUP BY clause.
D. They can be used on **only** one column in the SELECT clause of a SQL statement.
::E. They can be used along with the single-row function in the SELECT clause of a SQL statement.::

##71
Q71. Which statements are true? (Choose all that apply.)
A. The data dictionary is **created** and maintained by the database administrator.
B. The data dictionary views consists of joins of dictionary base tables and **user-defined tables**.
C. The usernames of all the users including the database administrators are stored in the data dictionary.
D. The USER_CONS_COLUMNS view should be queried to find the names of the columns to which a constraint applies.
E. Both USER_OBJECTS and CAT views provide the same information about all the objects that are owned by the user.
F. Views with the same name but different prefixes, such as DBA, ALL and USER, use the same base tables from the data dictionary.