Q1. - (Topic 2) 

In which four clauses can a sub query be used? (Choose four.) 

A. in the INTO clause of an INSERT statement 

B. in the FROM clause of a SELECT statement 

C. in the GROUP BY clause of a SELECT statement 

D. in the WHERE clause of a SELECT statement 

E. in the SET clause of an UPDATE statement 

F. in the VALUES clause of an INSERT statement 

View Answer

Q2. - (Topic 1) 

See the Exhibit and examine the structure and data in the INVOICE table: Exhibit: 

Which two SQL statements would execute successfully? (Choose two.) 

A. SELECT MAX(inv_date),MIN(cust_id) FROM invoice; 

B. SELECT AVG(inv_date-SYSDATE),AVG(inv_amt) FROM invoice; 

C. SELECT MAX(AVG(SYSDATE-inv_date)) FROM invoice; 

D. SELECT AVG(inv_date) FROM invoice; 

View Answer

Q3. - (Topic 1) 

Which three statements/commands would cause a transaction to end? (Choose three.) 

A. COMMIT 

B. SELECT 

C. CREATE 

D. ROLLBACK 

E. SAVEPOINT 

View Answer

Q4. - (Topic 1) 

You want to create an ORD_DETAIL table to store details for an order placed having the following business requirement: 

1) The order ID will be unique and cannot have null values. 

2) The order date cannot have null values and the default should be the current date. 

3) The order amount should not be less than 50. 

4) The order status will have values either shipped or not shipped. 

5) The order payment mode should be cheque, credit card, or cash on delivery (COD). 

Which is the valid DDL statement for creating the ORD_DETAIL table? 

A. 

CREATE TABLE ord_details 

(ord_id NUMBER(2) CONSTRAINT ord_id_nn NOT NULL, 

ord_date DATE DEFAULT SYSDATE NOT NULL, 

ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min 

CHECK (ord_amount > 50), 

ord_status VARCHAR2(15) CONSTRAINT ord_status_chk 

CHECK (ord_status IN ('Shipped', 'Not Shipped')), 

ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk 

CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 

'Cash On Delivery'))); 

B. 

CREATE TABLE ord_details 

(ord_id NUMBER(2) CONSTRAINT ord_id_uk UNIQUE NOT NULL, 

ord_date DATE DEFAULT SYSDATE NOT NULL, 

ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min 

CHECK (ord_amount > 50), 

ord_status VARCHAR2(15) CONSTRAINT ord_status_chk 

CHECK (ord_status IN ('Shipped', 'Not Shipped')), 

ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk 

CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 

'Cash On Delivery'))); 

C. 

CREATE TABLE ord_details 

(ord_id NUMBER(2) CONSTRAINT ord_id_pk PRIMARY KEY, 

ord_date DATE DEFAULT SYSDATE NOT NULL, 

ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min 

CHECK (ord_amount >= 50), 

ord_status VARCHAR2(15) CONSTRAINT ord_status_chk 

CHECK (ord_status IN ('Shipped', 'Not Shipped')), 

ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk 

CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 

'Cash On Delivery'))); 

D. 

CREATE TABLE ord_details 

(ord_id NUMBER(2), 

ord_date DATE NOT NULL DEFAULT SYSDATE, 

ord_amount NUMBER(5, 2) CONSTRAINT ord_amount_min 

CHECK (ord_amount >= 50), 

ord_status VARCHAR2(15) CONSTRAINT ord_status_chk 

CHECK (ord_status IN ('Shipped', 'Not Shipped')), 

ord_pay_mode VARCHAR2(15) CONSTRAINT ord_pay_chk 

CHECK (ord_pay_mode IN ('Cheque', 'Credit Card', 

'Cash On Delivery'))); 

View Answer

Q5. - (Topic 1) 

Evaluate the following SQL statements: Exhibit: 

You issue the following command to create a view that displays the IDs and last names of the sales staff in the organization. 

Exhibit: 

Which two statements are true regarding the above view? (Choose two.) 

A. It allows you to update job IDs of the existing sales staff to any other job ID in the EMPLOYEES table 

B. It allows you to delete details of the existing sales staff from the EMPLOYEES table 

C. It allows you to insert rows into the EMPLOYEES table 

D. It allows you to insert IDs, last names, and job IDs of the sales staff from the view if it is used in multitable INSERT statements 

View Answer

Q6. - (Topic 1) 

Examine the structure of the EMPLOYEES and NEW_EMPLOYEES tables: 

Which MERGE statement is valid? 

A. 

MERGE INTO new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET 

B. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name); 

C. 

MERGE new_employees c USING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET 

D. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT valueS(e.employee_id, e.first_name ||', '||e.last_name); 

E. 

MERGE INTO new_employees cUSING employees e ON (c.employee_id = e.employee_id) WHEN EXISTS THEN UPDATE SET 

F. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT value S(e.employee_id, e.first_name ||', '||e.last_name); 

G. 

MERGE new_employees c FROM employees e ON (c.employee_id = e.employee_id) WHEN MATCHED THEN UPDATE SET 

H. name = e.first_name ||','|| e.last_name WHEN NOT MATCHED THEN INSERT INTO new_employees valueS(e.employee_id, e.first_name ||', '||e.last_name); 

View Answer

Q7. - (Topic 2) 

Examine the structure of the ORDERS table: 

You want to find the total value of all the orders for each year and issue the following command: 

SQL>SELECT TO_CHAR(order_date,'rr'), SUM(order_total) 

FROM orders 

GROUP BY TO_CHAR(order_date,'yyyy'); 

Which statement is true regarding the outcome? 

A. It executes successfully and gives the correct output. 

B. It gives an error because the TO_CHAR function is not valid. 

C. It executes successfully but does not give the correct output. 

D. It gives an error because the data type conversion in the SELECT list does not match the data type conversion in the GROUP BY clause. 

View Answer

Q8. - (Topic 2) 

In which two cases would you use an outer join? (Choose two.) 

A. The tables being joined have NOT NULL columns. 

B. The tables being joined have only matched data. 

C. The columns being joined have NULL values. 

D. The tables being joined have only unmatched data. 

E. The tables being joined have both matched and unmatched data. 

F. Only when the tables have a primary key/foreign key relationship. 

View Answer

Q9. - (Topic 2) 

Examine the data in the CUSTOMERS table: 

You want to list all cities that have more than one customer along with the customer details. Evaluate the following query: 

SQL>SELECT c1.custname, c1.city FROM Customers c1 __________________ Customers c2 ON (c1.city=c2.city AND c1.custname<>c2.custname); 

Which two JOIN options can be used in the blank in the above query to give the correct output? (Choose two.) 

A. JOIN 

B. NATURAL JOIN 

C. LEFT OUTER JOIN 

D. FULL OUTER JOIN 

E. RIGHT OUTER JOIN 

View Answer

Q10. - (Topic 1) 

Which two statements are true regarding working with dates? (Choose two.) 

A. The default internal storage of dates is in the numeric format 

B. The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required 

C. The default internal storage of dates is in the character format 

D. The RR date format automatically calculates the century from the SYSDATE function and does not allow the user to enter the century 

View Answer