Examice

Exam 1z0-071 All QuestionsBrowse all questions from this exam

Go to Exam

Question 194

Examine the description of the ORDERS table:

Examine the description of the INVOICES table:

Which three statements execute successfully? (Choose three.)

SELECT * FROM orders ORDER BY order_id

INTERSECT -

SELECT * FROM invoices ORDER BY invoice_id;

(SELECT * FROM orders

UNION ALL -

SELECT * FROM invoices) ORDER BY order_id;

SELECT order_id, order_date FROM orders

UNION ALL -

SELECT invoice_id, invoice_date FROM invoices ORDER BY order_id;

SELECT * FROM orders -

MINUS -

SELECT * FROM invoices ORDER BY 1;

SELECT order_id invoice_id, order_date FROM orders

MINUS -

SELECT invoice_id, invoice_date FROM invoices ORDER BY invoice_id;

SELECT * FROM orders ORDER BY order_id

UNION -

SELECT * FROM invoices;

SELECT order_id, order_date FROM orders

INTERSECT -

SELECT invoice_id, invoice_date FROM invoices ORDER BY invoice_id;

Correct Answer: C, D, E

SELECT statements using UNION, INTERSECT, and MINUS operations must have compatible result sets in terms of the number and types of columns. Option C is correct as it combines compatible columns using UNION ALL and orders by a valid column. Option D uses MINUS and orders by a valid column index (ORDER BY 1). Option E works because 'order_id invoice_id' creates an alias for 'order_id', making it compatible with 'invoice_id' in the second SELECT, and ordering by 'invoice_id' from the first table works correctly. The other options have syntax or logical issues, such as attempting to order by columns not present in the combined result set, or invalid subqueries.

Discussion

lucemqyOptions: CDE

CDE is the correct answer

fasolazgrochemOptions: CDE

Tested

WingLOptions: CDE

CDE CORRECT. PLEASE HAVE A TRY: CREATE TABLE ORDERS_01 ( ORDER_ID NUMBER(38), ORDER_DATE DATE ); CREATE TABLE INVOICES_01 ( INVOICE_ID NUMBER(38), INVOICE_DATE DATE ); SELECT * FROM ORDERS_01 ORDER BY order_id INTERSECT SELECT * FROM INVOICES_01 ORDER BY invoice_id; SELECT * FROM ORDERS_01 UNION ALL SELECT * FROM INVOICES_01 ORDER BY ORDER_ID; SELECT order_id, order_date FROM ORDERS_01 UNION ALL SELECT invoice_id, invoice_date FROM INVOICES_01 ORDER BY order_id; SELECT * FROM ORDERS_01 MINUS SELECT * FROM INVOICES_01 ORDER BY 1; SELECT order_id invoice_id, order_date FROM ORDERS_01 MINUS SELECT invoice_id, invoice_date FROM INVOICES_01 ORDER BY invoice_id; SELECT * FROM ORDERS_01 ORDER BY order_id UNION SELECT * FROM INVOICES_01; SELECT order_id, order_date FROM ORDERS_01 INTERSECT SELECT invoice_id, invoice_date FROM INVOICES_01 ORDER BY invoice_id;

WingL

B HAS TWO ERRORS: 1. MUST START TO BEGIN WITH 'SELECT', NOT () BRACKETS 2. CANNOT ORDER BY ORDER_ID ORA-00904: "ORDER_ID": invalid identifier WHY E IS CORRECT: SELECT order_id invoice_id, order_date FROM ORDERS_01 equals SELECT order_id AS invoice_id, order_date FROM ORDERS_01 (a little trap here)

SantiBZ_07032022_1744Options: CDE

CDE: Pruebas: /*A NOOK*/ SELECT * FROM orders ORDER BY order_id INTERSECT SELECT * FROM invoices ORDER BY invoice_id; /*B NOOK*/ (SELECT * FROM orders UNION ALL SELECT * FROM invoices) ORDER BY order_id; /*C OK*/ SELECT order_id, order_date FROM orders UNION ALL SELECT invoice_id, invoice_date FROM invoices ORDER BY order_id; /*D OK*/ SELECT * FROM orders MINUS SELECT * FROM invoices ORDER BY 1; /*E OK*/ SELECT order_id invoice_id, order_date FROM orders MINUS SELECT invoice_id, invoice_date FROM invoices ORDER BY invoice_id; /*F. NOOK */ SELECT * FROM orders ORDER BY order_id UNION SELECT * FROM invoices; /*G. NOOK*/ SELECT order_id, order_date FROM orders INTERSECT SELECT invoice_id, invoice_date FROM invoices ORDER BY invoice_id;

jm9999Options: BCD

BCDE all work as written. E will fail if you put a comma between order_id and invoice_id

ShrimathiOptions: CDE

answer :CDE

Orxan_HOptions: BCD

BCD are correct

MooonLight

I have checked that E is working, but I don't understand why.

Sathitest071

Invoice_id is an alias for order_id in the first select. So it can be used in order by clause and it works. E. SELECT order_id invoice_id, order_date FROM orders MINUS - SELECT invoice_id, invoice_date FROM invoices ORDER BY invoice_id; F. SELECT * FROM orders ORDER BY order_id

WingL

SELECT order_id invoice_id, order_date FROM ORDERS_01 equals SELECT order_id AS invoice_id, order_date FROM ORDERS_01 (a little trap here)

DarnunOptions: CDE

CDE are correct. B is wrong as this is subquery without SELECT * in the beginning.

Sathitest071Options: BCD

Answer is BCD E is wrong because 1) The first select has 3 columns and second select has only 2 columns which is wrong. 2) The first select selects column invoice_id from the second table which is wrong . 3) Order by invoice_id is wrong.

Sathitest071

Sorry, CDE are the correct answer.

Izzicertificacion

en la E se toma como Alias invoice_id