Discuss Oracle 1Z0-071 Exam Topic 9 Question 112

Examine the description of the ORDER_ITEMS table:

Examine this incomplete query:

SELECT DISTINCT quantity * unit_price total_paid FROM order_items ORDER BY ;

Which two can replace so the query completes successfully?

Suggested Answer: B, C

In Oracle SQL, when you subtract a number from a date, the result is a date. When you subtract one date from another, the result is the number of days between the two dates.

B: PROMO_BEGIN_DATE - 5 will subtract 5 days from the PROMO_BEGIN_DATE, resulting in a new date that is 5 days earlier than PROMO_BEGIN_DATE.

C: PROMO_BEGIN_DATE - SYSDATE will return the number of days between the PROMO_BEGIN_DATE and the current date (SYSDATE).

The incorrect options are:

A: TONUMBER(PROMO_BEGIN_DATE) - 5 will not return a number because PROMO_BEGIN_DATE is a date, and TONUMBER is not a valid function to convert dates to numbers in Oracle.

D: PROMO_BEGIN_DATE - SYSDATE will not return an error; it will return the number of days between the two dates as explained above.

E: TODATE(PROMO_BEGIN_DATE * 5) will not return a date because PROMO_BEGIN_DATE * 5 is not a valid operation in Oracle SQL as you cannot multiply a date by a number, and TODATE is not a valid function. The correct function name is TO_DATE.

Oracle Documentation on Date Arithmetic: Database SQL Language Reference - Datetime Functions

Get Premium 1Z0-071 Questions as Interactive Web-Based Practice Test or PDF Real Exam Format Detailed Explanations Instant Access Get Your Practice Test

33 comments · newest first

Santos 11 months ago

Yeah, I thought it had to be a column name, not an expression.

Leonora 11 months ago

I think quantity * unit_price is a no-go for ORDER BY.

Tammy 11 months ago

Wait, can you really use total_paid in the ORDER BY?

Gretchen 12 months ago

Totally agree, product_id makes sense here!

Brandon 12 months ago

You can use quantity or product_id to complete the query.

Alline 1 year ago

I definitely remember that we can order by columns directly from the SELECT statement, so I think quantity and product_id could work here.

Boris 1 year ago

I’m a bit confused about whether we can use expressions like quantity * unit_price in the ORDER BY. I feel like we might have done that before, but I can't recall clearly.

Kanisha 1 year ago

I think we practiced a similar question where we had to order by a calculated field. I feel like quantity and unit_price might be valid options here.

Eun 1 year ago

I remember we talked about using aliases in queries, but I'm not sure if total_paid can be used directly in the ORDER BY clause.

Ayesha 1 year ago

Alright, let me think this through. The query is selecting the distinct total paid values, so the clause should be either quantity * unit_price or the calculated total_paid column. I'll need to carefully consider the options to determine the best answer.

Lino 1 year ago

This is straightforward. The query is calculating the total paid for each order, and the clause should be the calculated total_paid column. I'm confident that's the right answer.

Teddy 1 year ago

I'm a bit confused here. The question says the query is incomplete, but it looks like a complete SELECT statement to me. I'll need to double-check the table structure and the expected output to make sure I understand what the query is trying to achieve.

Maile 1 year ago

Okay, I think I've got this. The query is asking for the total paid, which is calculated as quantity * unit_price. So the clause should be either quantity, unit_price, or the calculated total_paid.

Tracey 1 year ago

Hmm, this looks like a tricky one. I'll need to carefully examine the table structure and the query to figure out the right clause to use.

Theron 1 year ago

I'm just wondering if the 'ORDER_ITEMS' table is where they keep all the order items that are out of order.

Roselle 1 year ago

↳ replying to Theron

B) quantity, unit_price

Kristin 1 year ago

↳ replying to Theron

A) quantity

Tammara 1 year ago

A) quantity? Really? That's like ordering a meal by the number of forks you used. E) is the only logical choice here.

Dorothea 1 year ago

Hmm, I was leaning towards C) total_paid, but I guess that would be circular reasoning. E) is the way to go!

Norah 1 year ago

↳ replying to Dorothea

Hmm, I was leaning towards C) total_paid, but I guess that would be circular reasoning. E) is the way to go!

Matthew 1 year ago

↳ replying to Dorothea

I agree, E) quantity * unit_price makes the most sense.

Kiley 1 year ago

↳ replying to Dorothea

I think E) quantity * unit_price is the correct choice.

Dustin 1 year ago

B) quantity, unit_price looks good to me. Ordering by the individual columns that make up the total_paid calculation should also work.

Martina 1 year ago

↳ replying to Dustin

Let's go with B) quantity, unit_price then.

Clarinda 1 year ago

↳ replying to Dustin

Agreed, ordering by the individual columns that make up the total_paid calculation should work.

Michell 1 year ago

↳ replying to Dustin

I think B) quantity, unit_price is the correct choice.

Major 1 year ago

I think the correct answer is E) quantity * unit_price. It makes sense to order the results by the computed total_paid column.

Arlyne 1 year ago

↳ replying to Major

Yes, that would give us the total amount paid for each order item.

Teddy 1 year ago

↳ replying to Major

I agree, ordering by the computed total_paid column would make sense.

Emiko 1 year ago

↳ replying to Major

I think the correct answer is E) quantity * unit_price.

Gene 2 years ago

But the query is looking for a column to order by, so it should be B) quantity, unit_price.

Sarah 2 years ago

I disagree, I believe the answer is E) quantity * unit_price.

Gene 2 years ago

I think the answer is B) quantity, unit_price.

Oracle 1Z0-071 Exam - Topic 9 Question 112 Discussion