Oracle 1Z0-071 Quiz 57 Topic 6 Questions 281-285

Question: 1

View the Exhibit and examine the details of PRODUCT_INFORMATION table.

q1_1Z0-071

You have the requirement to display PRODUCT_NAME from the table where the CATEGORY_ID column has values 12 or 13, and the SUPPLIER_ID column has the value 102088. You executed the following SQL statement:

SELECT product_name

FROM product_information

WHERE (category_id = 12 AND category_id = 13) AND supplier_id = 102088;

Which statement is true regarding the execution of the query?

AIt would not execute because the same column has been used in both sides of the AND logical operator to form the condition.

BIt would not execute because the entire WHERE clause condition is not enclosed within the parentheses.

CIt would execute and the output would display the desired result.

DIt would execute but the output would return no rows.

Show Answer

Question: 2

Which two statements are true regarding the EXISTS operator used in the correlated subqueries? (Choose two.)

AThe outer query stops evaluating the result set of the inner query when the first value is found.

BIt is used to test whether the values retrieved by the inner query exist in the result of the outer query.

CIt is used to test whether the values retrieved by the outer query exist in the result set of the inner query.

DThe outer query continues evaluating the result set of the inner query until all the values in the result set are processed.

Show Answer

Question: 3

View the exhibit and examine the structure of the STORES table.

q3_1Z0-071

You must display the NAME of stores along with the ADDRESS, START_DATE, PROPERTY_PRICE, and the projected property price, which is 115% of property price.

The stores displayed must have START_DATE in the range of 36 months starting from 01-Jan-2000 and above.

Which SQL statement would get the desired output?

ASELECT name, concat(address||', '||city||', ',country) AS full_address, start_date, property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN(start_date,'01-JAN-2000') <=36;

BSELECT name, concat(address||', '||city||', ',country) AS full_address, start_date,property_price, property_price*115/100FROM storesWHERE TO_NUMBER(start_date-TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

CSELECT name, address||', '||city||', '||country AS full_address, start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

DSELECT name, concat(address||', '||city||', ', country) AS full_address, start_date,property_price, property_price*115/100FROM storesWHERE MONTHS_BETWEEN(start_date,TO_DATE('01-JAN-2000','DD-MON-RRRR')) <=36;

Show Answer

Question: 4

The BOOKS_TRANSACTIONS table exists in your database.

SQL>SELECT * FROM books_transactions ORDER BY 3;

What is the outcome on execution?

AThe execution fails unless the numeral 3 in the ORDER BY clause is replaced by a column name.

BRows are displayed in the order that they are stored in the table only for the three rows with the lowest values in the key column.

CRows are displayed in the order that they are stored in the table only for the first three rows.

DRows are displayed sorted in ascending order of the values in the third column in the table.

Show Answer

Question: 5

Examine the command:

q5_1Z0-071

What does ON DELETE CASCADE imply?

AWhen the BOOKS table is dropped, the BOOK_TRANSACTIONS table is dropped.

BWhen the BOOKS table is dropped, all the rows in the BOOK_TRANSACTIONS table are deleted but the table structure is retained.

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

DWhen a value in the BOOKS.BOOK_ID column is deleted, the corresponding value is updated in the BOOKS_TRANSACTIONS.BOOK_ID column.

Show Answer

Oracle 1Z0-071 Quiz:57 Topic:6 Questions:281-285