Free 1Z0-071 Exam Dumps

Question 41

View the Exhibit and examine the structure of the ORDERS table.
1Z0-071 dumps exhibit
Which UPDATE statement is valid?

A. UPDATE ordersSET order_date = ’12-mar-2007’,order_total IS NULLWHERE order_id = 2455;
B. UPDATE ordersSET order_date = ’12-mar-2007’,AND order_total = TO_NUMBER(NULL)WHERE order_id = 2455;
C. UPDATE ordersSET order_date = ’12-mar-2007’,order_total = NULLWHERE order_id = 2455;
D. UPDATE ordersSET order_date = TO_DATE(’12-mar-2007’,’dd-mon-yyyy’),SET order_total = TO_NUMBER (NULL)WHERE order_id = 2455;

Correct Answer:C

Question 42

View the exhibit and examine the data in the PROJ_TASK_DETAILS table. (Choose the best answer.)
1Z0-071 dumps exhibit
The PROJ_TASK_DETAILS table stores information about project tasks and the relation between them. The BASED_ON column indicates dependencies between tasks.
Some tasks do not depend on the completion of other tasks.
You must generate a report listing all task IDs, the task ID of any task upon which it depends and the name of the employee in charge of the task upon which it depends.
Which query would give the required result?

A. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p JOIN proj_task_details dON (p.task_id = d.task_id);
B. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p FULL OUTER JOIN proj_task_details dON (p.based_on = d.task_id);
C. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p JOIN proj_task_details dON (p.based_on = d.task_id);
D. SELECT p.task_id, p.based_on, d.task_in_chargeFROM proj_task_details p LEFT OUTER JOIN proj_task_details dON (p.based_on = d.task_id);

Correct Answer:D

Question 43

Which two statements are true regarding the execution of the correlated subqueries? (Choose two.)

A. The nested query executes after the outer query returns the row.
B. The nested query executes first and then the outer query executes.
C. The outer query executes only once for the result returned by the inner query.
D. Each row returned by the outer query is evaluated for the results returned by the inner query.

Correct Answer:AD

Question 44

Examine the structure of the PROGRAMS table:
1Z0-071 dumps exhibit
Which two SQL statements would execute successfully?

A. SELECT NVL (ADD_MONTHS (END_DATE,1) SYSDATE) FROM programs;
B. SELECT TO_DATE (NVL (SYSDATE-END_DATE, SYSDATE)) FROM programs;
C. SELECT NVL (MONTHS_BETWEEN (start_date, end_date), ‘Ongoing’) FROM programs;
D. SELECT NVL (TO_CHAR (MONTHS_BETWEEN (start-date, end_date)), ‘Ongoing’) FROMprograms

Correct Answer:AD

Question 45

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