1Z0-071 Oracle Exam Questions and Free Practice Test

Question 25

Examine the structure of the PROMOTIONS table: (Choose the best answer.)
1Z0-071 dumps exhibit
Management requires a report of unique promotion costs in each promotion category. Which query would satisfy this requirement?

A. SELECT DISTINCT promo_category, promo_cost FROM promotions ORDER BY 1

B. SELECT promo_category, DISTINCT promo_cost FROM promotions

C. SELECT DISTINCT promo_cost, promo_category FROM promotions

D. SELECT DISTINCT promo_cost, DISTINCT promo_category FROM promotions;

Correct Answer:A

Question 26

Which three statements are true regarding subqueries?

A. Multiple columns or expressions can be compared between the main query and subquery.

B. Subqueries can contain ORDER BY but not the GROUP BY clause.

C. Main query and subquery can get data from different tables.

D. Subqueries can contain GROUP BY and ORDER BY clauses.

E. Main query and subquery must get data from the same tables.

F. Only one column or expression can be compared between the main query and subquery.

Correct Answer:ACD
References:
http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html

Question 27

View the Exhibit and examine the details of PRODUCT_INFORMATION table.
PRODUCT_NAME CATEGORY_ID SUPPLIER_ID
Inkjet C/8/HQ 12
102094
Inkjet C/4 12
102090
LaserPro 600/6/BW 12
102087
LaserPro 1200/8/BW 12
102099
Inkjet B/6 12
102096
Industrial 700/ID 12
102086
Industrial 600/DQ 12
102088
Compact 400/LQ 12
102087
Compact 400/DQ 12
102088
HD 12GB /R 13
102090
HD 10GB /I 13
102071
HD 12GB @7200 /SE 13
102057
HD 18.2GB @10000 /E 13
102078
HD 18.2GB @10000 /I 13
102050
HD 18GB /SE 13
102083
HD 6GB /I 13
102072
HD 8.2GB@5400 13
102093
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?

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

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

C. It would execute and the output would display the desired result.

D. It would execute but the output would return no rows.

Correct Answer:D

Question 28

View the Exhibit and examine the structure of the CUSTOMERS table.
1Z0-071 dumps exhibit
Using the CUSTOMERS table, you must generate a report that displays a credit limit increase of 15% for all customers.
Customers with no credit limit should have “Not Available” displayed. Which SQL statement would produce the required result?

A. SELECT NVL (TO_CHAR(cust_credit_limit*.15), ‘Not Available’) “NEW CREDIT” FROM customers

B. SELECT TO_CHAR(NVL(cust_credit_limit*.15), ‘Not Available’)) “NEW CREDIT” FROMcustomers

C. SELECT NVL (cust_credit_limit*.15, ‘Not Available’) “NEW CREDIT” FROM customers

D. SELECT NVL (cust_credit_limit, ‘Not Available’)*.15 “NEW CREDIT” FROM customers

Correct Answer:C

Question 29

Which three statements are true about multiple-row subqueries?

A. They can contain a subquery within a subquery.

B. They can return multiple columns as well as rows.

C. They cannot contain a subquery within a subquery.

D. They can return only one column but multiple rows.

E. They can contain group functions and GROUP BY and HAVING clauses.

F. They can contain group functions and the GROUP BY clause, but not the HAVING clause.

Correct Answer:ABE

Question 30

View the Exhibit and examine the structure of the PROMOTION table.
1Z0-071 dumps exhibit
You have to generate a report that displays the promo named start data for all promos that started after that last promo in the ‘INTTERNET’ category.

A. Select promo_name, promo_being_date FROM promoptions WHERE promo_being_data > ANY (SELCT promo_being-date FROM promotionsWHERE promo_category = ‘INTERNET’

B. SELECT promo_neme, promo_being_date FROM promotions WHERE promo_being_date > All (SELECT promo_beinjg-date FROM promotionsWHERE promo_category =’INTERNET’ );

C. SELECT promo-name, promo-being _date FROM promotionsWhere promo_being_data >ALL (SELECT MAX (promo_being-date) FROM promotions ) ANDPromo-category =’INTERNET’;

D. SELECT promo-name, promo-being_date FROM promotion WHERE promo-being-date IN (SELECT promo_biing_date FROM promotionsWHERE promo_category=’INTYERNET’);

Correct Answer:B

START 1Z0-071 EXAM