NEW QUESTION NO: 6
The first DROPoperation is performed on PRODUCTStable using the following command:
DROP TABLE products PURGE;
Then you performed the FLASHBACKoperation by using the following command:
FLASHBACK TABLE products TO BEFORE DROP;
Which statement describes the outcome of the FLASHBACKcommand?
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.
Answer: D
Explanation/Reference:
References:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_9003.htm

NEW QUESTION NO: 7
Which statement is true about transactions?
A. A set of Data Manipulation Language (DML) statements executed in a sequence ending with a SAVEPOINTforms 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 COMMITforms a single transaction.
D. A combination of DDL and DML statements executed in a sequence ending with a COMMITforms a single transaction.
Answer: B
Explanation/Reference:
References:
https://docs.oracle.com/database/121/CNCPT/transact.htm#CNCPT038

NEW QUESTION NO: 8
You want to display 5 percent of the rows from the SALEStable for products with the lowest AMOUNT_SOLD and also want to include the rows that have the same AMOUNT_SOLDeven 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 ONLY;
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 WITH TIES;
Answer: D

NEW QUESTION NO: 9
View the exhibit and examine the structure in ORDERSand ORDER_ITEMStables.

You need to create a view that displays the ORDER_ID, ORDER_DATE, and the total number of items in each order.
Which CREATEVIEWstatement would create the views successfully?
A. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date;
B. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id) ||
"NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
WHITH CHECK OPTION;
C. CREATE OR REPLACE VIEW ord_vu
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
" NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date;
D. CREATE OR REPLACE VIEW ord_vu (order_id, order_date)
AS SELECT o.order_id, o.order_date, COUNT (i.line_item_id)
" NO OF ITEMS"
FROM orders o JOIN order_items i
ON (o.order_id = i.order_id)
GROUP BY o.order_id, o.order_date;
Answer: C

NEW QUESTION NO: 10
Which statement is true about an inner join specified in the WHERE clause of a query?
A. It is applicable for only equijoin conditions.
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 must have primary-key and foreign-key constraints defined on the columns used in the join condition.
Answer: C

NEW QUESTION NO: 11
Which three statements are true about multiple-row subqueries?
A. They can contain group functions and GROUP BYand HAVINGclauses.
B. They can contain group functions and the GROUP BYclause, but not the HAVINGclause.
C. They can contain a subquery within a subquery.
D. They cannot contain a subquery within a subquery.
E. They can return only one column but multiple rows.
F. They can return multiple columns as well as rows.
Answer: A,C,F

NEW QUESTION NO: 12
You issue the following command to drop the PRODUCTStable:
SQL > DROP TABLE products;
Which three statements are true about the implication of this command?
A. All data along with the table structure is deleted.
B. All views and synonyms on the table remain but they are invalidated.
C. All data in the table is deleted but the table structure remains.
D. All indexes on the table remain but they are invalidated.
E. A pending transaction in the session is committed.
Answer: A,B,E

NEW QUESTION NO: 13
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 STUDENTSand PROJECTSentities.
B. The ERD must have a M:M relationship between the STUDENTSand PROJECTSentities that must be resolved into 1:M relationships.
C. STUDENT_IDmust be the primary key in the STUDENTSentity and foreign key in the PROJECTSentity.
D. PROJECT_IDmust be the primary key in the PROJECTSentity and foreign key in the STUDENTSentity.
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.
Answer: B,E
Explanation/Reference:
References:
http://www.oracle.com/technetwork/issue-archive/2011/11-nov/o61sql-512018.html

NEW QUESTION NO: 14
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 GROUPBYclause.
D. They can be used on only one column in the SELECTclause of a SQL statement.
E. They can be used along with the single-row function in the SELECTclause of a SQL statement.
Answer: A,B,E
Explanation/Reference:
References:
https://www.safaribooksonline.com/library/view/mastering-oracle-sql/0596006322/ch04.html

NEW QUESTION NO: 15
View the exhibit and examine the structures of the EMPLOYEESand DEPARTMENTStables.
EMPLOYEES
Name Null? Type
- ---------------- ----- -------------
EMPLOYEE_ID NOT NULL NUMBER(6)
FIRST_NAME VARCHAR2(20)
LAST_NAME NOT NULL VARCHAR2(25)
HIRE_DATE 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)
DEPARTMENTS
Name Null? Type
----------------- ----- -------------
DEPARTMENT_ID NOT NULL NUMBER(4)
DEPARTMENT_NAME NOT NULL VARCHAR2(30)
MANAGER_ID NUMBER(6)
LOCATION_ID NUMBER(4)
You want to update EMPLOYEEStable as follows:
Update only those employees who work in Boston or Seattle (locations 2900 and 2700).

Set department_idfor these employees to the department_idcorresponding to London

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

Set the employees' commission in location_id2100 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 a subquery cannot have a join condition in a UPDATEstatement.
B. It executes successfully and gives the correct result.
C. It executes successfully but does not give the correct result.
D. It generates an error because multiple columns (SALARY, COMMISSION)cannot be specified together in an UPDATEstatement.
Answer: C

NEW QUESTION NO: 16
View the Exhibit and examine the structure of CUSTOMERStable.
Using the CUSTOMERStable, 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;
Answer: A