Free 1Z0-071 Exam Dumps

Question 81

View the Exhibit and examine the data in the PRODUCT_INFORMATION table.
1Z0-071 dumps exhibit
Which two tasks would require subqueries? (Choose two.)

A. displaying all the products whose minimum list prices are more than average list price of products having the status orderable
B. displaying the total number of products supplied by supplier 102071 and having product status OBSOLETE
C. displaying the number of products whose list prices are more than the average list price
D. displaying all supplier IDs whose average list price is more than 500
E. displaying the minimum list price for each product status

Correct Answer:AC

Question 82

Examine the commands used to create DEPARTMENT_DETAILS and COURSE_DETAILS:
SQL>CREATE TABLE DEPARTMENT_DETAILS (DEPARTMENT_ID NUMBER PRIMARY KEY, DEPARTMENT_NAMEVARCHAR2(50), HODVARCHAR2(50));
SQL>CREATE TABLE COURSE_DETAILS (COURSE_IDNUMBER PRIMARY KEY, COURSE_NAMEVARCHAR2(50), DEPARTMENT_IDVARCHAR2(50));
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. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (d.department_id=
B. department_id);
C. SELECT d.department_id, c.course_id FROM department_details d LEFT OUTER JOIN course_details c ON (d.department_id=
D. department_id);
E. SELECT d.department_id, c.course_id FROM course_details c LEFT OUTER JOIN department_details d ON (c.department_id=
F. department_id);
G. SELECT d.department_id, c.course_id FROM department_details d RIGHT OUTER JOIN course_details c ON (c.department_id=
H. department_id);

Correct Answer:B

Question 83

You must create a table for a banking application. (Choose the best answer.) One of the columns in the table has these requirements:
1: A column to store the duration of a short team loan
2: The data should be stored in a format supporting DATE arithmetic with DATE datatypes without using conversion functions.
3: The maximum loan period is 30 days.
4: Interest must be calculated based on the number of days for which the loan remains unpaid. Which data type would you use?

A. Date
B. Number
C. Timestamp
D. Interval day to second
E. Interval year to month

Correct Answer:D

Question 84

Examine the structure of the BOOKS_ TRANSACTIONS table:
1Z0-071 dumps exhibit
Examine the SQL statement:

Which statement is true about the outcome?

A. It displays details only for members who have borrowed before today with RM as TRANSACTION_TYPE.
B. It displays details for members who have borrowed before today's date with either RM asTRANSACTION_TYPE or MEMBER_ID as A101 and A102.
C. It displays details for only members A101and A102 who have borrowed before today with RM as TRANSACTION_TYPE.
D. It displays details for members who have borrowed before today with RM as TRANSACTION_TYPE and the details for members A101 or A102.

Correct Answer:A