Q1. In which scenario would you use the ROLLUP operator for expression or columns within a GROUP BY clause? 

A. to find the groups forming the subtotal in a row 

B. to create group-wise grand totals for the groups specified within a GROUP BY clause 

C. to create a grouping for expressions or columns specified within a GROUP BY clause in one direction, from right to left for calculating the subtotals 

D. to create a grouping for expressions or columns specified within a GROUP BY clause in all possible directions, which is cross-tabular report for calculating the subtotals 

View Answer

Q2. 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. 

View Answer

Q3. View the Exhibit and examine the structure of the CUSTOMERS table. 

CUSTOMER_VU is a view based on CUSTOMERS_BR1 table which has the same structure as CUSTOMERS table. CUSTOMERS needs to be updated to reflect the latest information about the customers. 

What is the error in the following MERGE statement? 

MERGE INTO customers c 

USING customer_vu cv 

ON (c.customer_id = cv.customer_id) 

WHEN MATCHED THEN 

UPDATE SET 

c.customer_id = cv.customer_id, 

c.cust_name = cv.cust_name, 

c.cust_email = cv.cust_email, 

c.income_level = cv. Income_level 

WHEN NOT MATCHED THEN 

INSERT VALUESfcv.customer_id.cv.cus_name.cv.cus_email.cv.income_level) 

WHERE cv. Income_level >100000; 

A. The CUSTOMERJD column cannot be updated. 

B. The INTO clause is misplaced in the command. 

C. The WHERE clause cannot be used with INSERT. 

D. CUSTOMER VU cannot be used as a data source 

View Answer

Q4. View the Exhibit and examine the description of the EMPLOYEES table. 

You want to calculate the total remuneration for each employee. Total remuneration is the sum of the annual salary and the percentage commission earned for a year. Only a few employees earn commission. 

Which SOL statement would you execute to get the desired output? 

A. SELECTfirst_name, salary, salary*12+salary*commission_pct "Total" 

FROM EMPLOYEES; 

B. SELECTfirst_name,salary, salary*12+NVL((salary*commission_pct), 0) "Total" 

FROMEMPLOYEES; 

C. SELECTfirst_name, salary, salary*12 + NVL(salary, O)*commission_pct "Total" 

FROM EMPLOYEES; 

D. SELECTfirst_name, salary, salary*12+(salary*NVL2(commission_pct, 

salary,salary+commission_pct))"Total" 

FROM EMPLOYEES; 

View Answer

Q5. 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 unload data into regular table in the database from an external table. 

View Answer

Q6. View the Exhibit and examine the data in ORDERS_MASTER and MONTHLYjDRDERS 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 ORDERJDs1and 2. 

B. TheORDERS_MASTERtablewould containtheORDERJDs 1,2and3. 

C. TheORDERS_MASTERtable would containtheORDERJDs 1,2 and 4. 

D. The ORDERSMASTER table would containtheORDER IDs 1,2,3 and4. 

View Answer

Q7. Which statement correctly grants a system privilege? 

A. GRANT EXECUTE ON prod TO PUBLIC; 

B. GRANT CREATE VIEW ON tablel TO used; 

C. GRANT CREATE TABLE TO used ,user2; 

D. GRANT CREATE SESSION TO ALL; 

View Answer

Q8. Which three possible values can be set for the TIME_ZONE session parameter by using the ALTER SESSION command? (Choose three.) 

A. 'os' 

B. local 

C. -8:00' 

D. dbtimezone Li 

E. 'Australia' 

View Answer

Q9. View the Exhibit and examine the descriptions for ORDERS and ORDER_ITEMS tables. 

Evaluate the following SQL statement: 

SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi. quantity) "Order Amount" 

FROM orde_items oi JOIN orders o 

ON oi.order_id = o.order_id 

GROUP BY CUBE (o.customer_id, oi.product_id); 

Which three statements are true regarding the output of this SQL statement? (Choose three.) 

A. It would return the subtotals for the Order Amount of every CUSTOMER_ID. 

B. It would return the subtotals for the Order Amount for every PRODUCT_ID. 

C. It would return the subtotals for the Order Amount of every PRODUCT_ID and CUSTOMER_ID as one group. 

D. It would return the subtotals for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group. 

E. It would return only the grand total for the Order Amount of every CUSTOMER_ID and PRODUCT_ID as one group. 

View Answer

Q10. You need to create a table with the following column specifications: 

1. Employee ID (numeric data type) for each employee 

2. Employee Name, (character data type) which stores the employee name 

3. Hire date, to store the date when the employee joined the organization 

4. Status (character data type). It should contain the value if no data is entered. 

5. Resume (character large object [CLOB] data type), which would contain the resume submitted by the employee 

Which is the correct syntax to create this table? 

A. CREATETABLEEMP_1 

(emp_id NUMBER(4), 

emp_name VARCHAR2(25), 

start_dateDATE, 

e_status VARCHAR2(10) DEFAULTACTIVE', 

resume CLOB(200)); 

B. CREATETABLE1_EMP 

(emp_idNUMBER(4), 

emp_nameVARCHAR2(25), 

start_dateDATE, 

emp_status VARCHAR2(10) DEFAULT ACTIVE', 

resume CLOB); 

C. CREATETABLE1_EMP 

(emp_id NUMBER(4), 

emp_name VARCHAR2(25),start_date DATE, 

emp_status VARCHAR2(10) DEFAULT "ACTIVE", 

resume CLOB); 

D. CREATE TABLEEMP_1 

(emp_id NUMBER, emp_nameVARCHAR2(25), 

start_dateDATE, 

emp_statusVARCHAR2(10) DEFAULTACTIVE', 

resume CLOB); 

View Answer