Q1. Which two tasks can be performed by using Oracle SQL statements?

A. changing the password for an existing database

B. connecting to a database instance

C. querying data from tables across databases

D. starting up a database instance

E. executing operating system (OS) commands in a session

View Answer

Q2. See the Exhibit and examine the structure of the PROMOTIONS table: Exhibit:

Using the PROMOTIONS table, you need to find out the average cost for all promos in the range $0-2000 and $2000-5000 in category A.

You issue the following SQL statements: Exhibit:

What would be the outcome?

A. It generates an error because multiple conditions cannot be specified for the WHEN clause

B. It executes successfully and gives the required result

C. It generates an error because CASE cannot be used with group functions

D. It generates an error because NULL cannot be specified as a return value

View Answer

Q3. Evaluate the following SQL query;

What would be the outcome?

A. 200

B. 16

C. 160

D. 150

E. 100

View Answer

Q4. View the Exhibit and examine the structure of ORDERS and ORDER_ITEMS tables.

ORDER ID is the primary key in the ORDERS table. It is also the foreign key in the ORDER_ITEMS table wherein it is created with the ON DELETE CASCADE option.

Which DELETE statement would execute successfully?

A. DELETE order_id FROM orders

WHERE order_total < 1000;

B. DELETE orders

WHERE order_total < 1000;

C. DELETE

FROM orders

WHERE (SELECT order_id FROM order_items);

D. DELETE orders o, order_items i WHERE o.order id = i.order id;

View Answer

Q5. Examine the command:

What does ON DELETE CASCADE Imply?

A. When the books table is dropped, the BOOK_TRANSACTIONS table is dropped.

B. When the books table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted but the table structure is retained.

C. When a row in the books table is deleted, the rows in the BOOK TRANSACTIONS table whose BOOK_ID matches that of the deleted row in the books table are also deleted.

D. When a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the books transactions. BOOK_ID column.

View Answer

Q6. Which two statements are true regarding constraints? (Choose two.)

A. A foreign key cannot contain NULL values.

B. A column with the UNIQUE constraint can contain NULL.

C. A constraint is enforced only for the INSERT operation on a table.

D. A constraint can be disabled even if the constraint column contains data.

E. All the constraints can be defined at the column level as well as the table level

View Answer

Q7. 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. The statement would execute successfully to produce the required output.

B. The statement would not execute because inline views and outer joins cannot be used together.

C. The statement would not execute because the ITEM_CNT alias cannot be displayed in the outer query.

D. The statement would not execute because the GROUP BY clause cannot be used in the inline view.

View Answer

Q8. Examine the following query:

What is the output of this query?

A. It displays 5 percent of the products with the highest amount sold.

B. It displays the first 5 percent of the rows from the SALES table.

C. It displays 5 percent of the products with the lowest amount sold.

D. It results in an error because the ORDER BY clause should be the last clause.

View Answer

Q9. Examine the structure of the members table:

What is the outcome?

A. It fails because the alias name specified after the column names is invalid.

B. It fails because the space specified in single quotation marks after the first two column names is invalid.

C. It executes successfully and displays the column details in a single column with only the alias column heading.

D. It executes successfully and displays the column details in three separate columns and replaces only the last column heading with the alias.

View Answer

Q10. View the Exhibits and examine the structures of the costs and promotions tables? 

/1.

/2.

Evaluate the following SQL statement: SQL> SELECT prod_id FROM costs

WHERE promo_id IN (SELECT promo_id FROM promotions WHERE promo_cost < ALL

(SELECT MAX(promo_cost) FROM promotions GROUP BY (promo_end_datepromo_begin_date)));

What would be the outcome of the above SQL statement?

A. It displays prod IDs in the promo with the lowest cost.

B. It displays prod IDs in the promos with the lowest cost in the same time interval.

C. It displays prod IDs in the promos with the highest cost in the same time interval.

D. It displays prod IDs in the promos with cost less than the highest cost in the same time interval.

View Answer