Q1. Evaluate the following SQL statement:

SQL> SELECT cust_id, cust_last_name "Last Name" 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"

View Answer

Q2. View the Exhibit and examine the data in ORDERS and ORDER_ITEMS tables.

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 view successfully?

A. 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;

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)

GROUP BY o.order_id,o.order_date;

C. 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;

D. CREATE OR REPLACE VIEW ord_vu

AS SELECT o.order_id, o.order_date, COUNT(i.line_item_id)ll’ 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 WITH CHECK OPTION;

View Answer

Q3. 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 successfully.

D. After executing the ALTER TABLE command, you can add a new column called ORDER_DATE to the ORDERS table.

View Answer

Q4. Examine the commands used to createDEPARTMENT_DETAILS andCOURSE_DETAILS:

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? 

A)

B)

C)

D)

A. Option A

B. Option B

C. Option C

D. Option D

View Answer

Q5. Examine the data in the CUST_NAME column of the CUSTOMERS table. CUST_NAME

------------------------

Renske Ladwig Jason Mallin Samuel McCain Allan MCEwen Irene Mikkilineni 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 INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1))='Mc'

B. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) LIKE 'Mc%'

C. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers

WHERE SUBSTR(cust_name, INSTR(cust_name,' ')+1) LIKE INITCAP('MC%');

D. SELECT SUBSTR(cust_name, INSTR(cust_name,' ')+1) FROM customers

WHERE INITCAP(SUBSTR(cust_name, INSTR(cust_name,' ')+1)) = INITCAP('MC%');

View Answer

Q6. You issue the following command to drop the PRODUCTS table: SQL>DROP TABLE products;

What is the implication of this command? (Choose all that apply.)

A. All data in the table are deleted but the table structure will remain

B. All data along with the table structure is deleted

C. All viewsand synonyms will remain but they are invalidated

D. The pending transaction in the session is committed

E. All indexes on the table will remain but they are invalidated

View Answer