Q1. View the Exhibit and examine the structure of the ORDERS table. Which task would require subqueries?
A. displaying the total order value for sales representatives 161 and 163
B. displaying the order total for sales representative 161 in the year 1999
C. displaying the number of orders that have order mode online and order date in 1999
D. displaying the number of orders whose order total is more than the average order total for all online orders
Answer: D
Q2. View the Exhibit and examine PRODUCTS and ORDER_ITEMS tables.
You executed the following query to display PRODUCT_NAME and the number of times the product has been ordered:
SELECT p.product_name, i.item_cnt
FROM (SELECT product_id, COUNT (*) item_cnt
FROM order_items
GROUP BY product_id) i RIGHT OUTER JOIN products p
ON i.product_id = p.product_id;
What would happen when the above statement is executed?
A. Thestatement would execute successfullytoproducetherequired output.
B. Thestatement wouldnotexecute because inlineviewsandouterjoins cannot be usedtogether.
C. The statementwouldnot execute because the ITEM_CNT alias cannotbedisplayedintheouter query.
D. The statement wouldnot execute because the GROUP BYclausecannot be used intheinline view.
Answer: A
Q3. Evaluate the following SQL statements in the given order:
DROP TABLE dept;
CREATE TABLE dept
(deptno NUMBER(3) PRIMARY KEY,
deptname VARCHAR2(10));
DROP TABLE dept;
FLASHBACK TABLE dept TO BEFORE DROP; Which statement is true regarding the above FLASHBACK operation?
A. It recovers only the firstDEPTtable.
B. Itrecovers onlythesecondDEPTtable.
C. It does not recoveranyof the tables becauseFLASHBACKis not possible in this case.
D. Itrecovers both the tables but the names would be changed to the ones assigned intheRECYCLEBIN.
Answer: B
Q4. View the Exhibit and examine the description of the CUSTOMERS table.
You want to add a constraint on the CUST_FIRST_NAME column of the CUSTOMERS table so that the value inserted in the column does not have numbers.
Which SOL statement would you use to accomplish the task?
A. ALTER TABLE CUSTOMERS
ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name1'^A-Z’))NOVALIDATE;
B. ALTER TABLE CUSTOMERS
ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'^[0-9]’))NOVALIDATE;
C. ALTER TABLE CUSTOMERS
ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:alpha: ]]’))NOVALIDATE;
D. ALTER TABLE CUSTOMERS
ADD CONSTRAINT cust_f_name CHECK(REGEXP_LIKE(cust_first_name,'[[:digit: ]]’))NOVALIDATE ;
Answer: C
Q5. Evaluate the SQL statements:
CREATE TABLE new_order
(orderno NUMBER(4),
booking_date TIMESTAMP WITH LOCAL TIME ZONE);
The database is located in San Francisco where the time zone is -8:00.
The user is located in New York where the time zone is -5:00.
A New York user inserts the following record:
INSERT INTO new_order
VALUES(1, TIMESTAMP 007-05-10 6:00:00 -5:00");
Which statement is true?
A. When theNewYorkuserselects the row, booking_date is displayed as 007-05-10 3.00.00.000000'
B. When the New York user selectstherow, booking_date is displayed as 2007-05-10 6.00.00.000000 -5:00'.
C. WhentheSan Francisco user selectstherow, booking_date is displayed as 007-05-103.00.00.000000'
D. When the San Francisco user selectstherow,booking_dateis displayed
as 007-05-103.00.00.000000-8:00'
Answer: C
Q6. Which two statements are true regarding roles? (Choose two.)
A. A role can be granted to itself.
B. A role can be granted to PUBLIC.
C. A user can be granted only one role at any point of time.
D. The REVOKE command can be used to remove privileges but not roles from other users.
E. Roles are named groups of related privileges that can be granted to users or other roles.
Answer: BE
Q7. Which statement is true regarding synonyms?
A. Synonyms can be created for tables but not views.
B. Synonyms are used to reference only those tables that are owned by another user.
C. A public synonym and a private synonym can exist with the same name for the same table.
D. The DROP SYNONYM statement removes the synonym, and the status of the table on which the synonym has been created becomes invalid.
Answer: C
Q8. Evaluate the CREATE TABLE statement:
CREATE TABLE products
(product_id NUMBER(6) CONSTRAINT prod_id_pk PRIMARY KEY,
product_name VARCHAR2(15));
Which statement is true regarding the PROD_ID_PK constraint?
A. Itwould becreated only if a unique index is manually created first.
B. Itwould becreated andwould use an automatically created unique index.
C. It would be createdandwould use an automaticallycreatednonunique index.
D. Itwouldbecreated and remainsinadisabledstatebecauseno indexis specified in the command.
Answer: B
Q9. View the Exhibit and examine the data in ORDERS_MASTER and MONTHLY_ORDERS tables.
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_MASTER table would contain the ORDER_IDs 1 and 2.
B. The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 3.
C. The ORDERS_MASTER table would contain the ORDER_IDs 1,2 and 4.
D. The ORDERS_MASTER table would contain the ORDER IDs 1,2,3 and 4.
Answer: C
Q10. View the Exhibit and examine the structure of the EMPLOYEES and JOB_HISTORY tables.
The query should display the employee IDs of all the employees who have held the job SA_MAN at any time during their tenure.
Choose the correct SET operator to fill in the blank space and complete the following query.
SELECT employee_id
FROM employees
WHERE job_id = 'SA_MAN'
SELECT employee_id
FROMjob_history
WHERE job_id='SA_MAN'
A. UNION
B. MINUS
C. INTERSECT
D. UNIONALL
Answer: AD