Exam 1z0-082 topic 1 question 95 discussion

Actual exam question from Oracle's 1z0-082

Question #: 95
Topic #: 1

Table ORDER_ITEMS contains columns ORDER_ID, UNIT_PRICE and QUANTITY, of data type NUMBER.
Examine these SQL statements:
Statement 1:
SELECT MAX(unit_price * quantity) `Maximum Order`
FROM order_items;
Statement 2:
SELECT MAX(unit_price * quantity) `Maximum Order`

FROM order_items -
GROUP BY order_id;
Which two statements are true?

A. Statement 1 returns only one row of output.
B. Statement 2 returns only one row of output.
C. Both statements will return NULL if either UNIT_PRICE or QUANTITY contains NULL.
D. Both the statements give the same output.
E. Statement 2 may return multiple rows of output.

Show Suggested Answer

Suggested Answer: AE 🗳️

by danito at May 29, 2020, 11:52 a.m.

Comments

Submit Cancel

danito

Highly Voted 3 years, 7 months ago

A E because https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj27781.html The Expression can contain multiple column references or expressions, but it cannot contain another aggregate or subquery. It must evaluate to a built-in data type. You can therefore call methods that evaluate to built-in data types. (For example, a method that returns a java.lang.Integer or int evaluates to an INTEGER.) If an expression evaluates to NULL, the aggregate skips that value.

upvoted 15 times

Ekos

3 years ago

i agree

upvoted 2 times

...

[Removed]

Most Recent 2 years, 3 months ago

A and E (max function gives only one output in st.1 / when group by order_id is used, max function is used for every order_id

upvoted 1 times

...

flaviogcmelo

2 years, 8 months ago

A , E are the correct statements. I've tested on this same sample schema.

upvoted 2 times

...

guestart

2 years, 9 months ago

Yes, A & E are correct answers. The followding demo is what I have tested on LiveSQL. CREATE TABLE order_items (order_id number, unit_price number, quantity number); INSERT INTO order_items VALUES (1, 234.35, 300); INSERT INTO order_items VALUES (2, 95.10, 20); INSERT INTO order_items VALUES (3, 150.75, 400); INSERT INTO order_items VALUES (4, 50.65, NULL); INSERT INTO order_items VALUES (5, 20.15, 150); INSERT INTO order_items VALUES (6, 80.50, 70); COMMIT; SELECT MAX(unit_price * quantity) "Maximum Order" FROM order_items; Maximum Order ------------------------- 70305 SELECT order_id, MAX(unit_price * quantity) "Maximum Order" FROM order_items GROUP BY order_id; ORDER_ID Maximum Order ---------------- ------------------------- 6 5635 1 70305 2 1902 4 - 5 3022.5 3 60300

upvoted 4 times

...

Mandar79

3 years, 3 months ago

A - Correct in all scenarios E - Correct if there is more than 1 row C - Correct if there is only one row which has either UNIT_PRICE or QUANTITY contains NULL

upvoted 3 times

Yanal98

1 year ago

C is incorrect. Statement one will not give null although if there are null values within either the "unit_price" or the "quantity".

upvoted 1 times

Yanal98

1 year ago

You can try this using this statement on HR Schema: select max(commission_pct*employee_id) from employees group by first_name ; And this manipulates Statement 2 execution in C.

upvoted 1 times

...