CODEWITHSUNDEEP
mysqlsqldatabase
Gabby S
Gabby S

Reputation: 47

What is this query doing?

I have the following tables:

PRODUCT ( ProdId, Name, Price, Weight, Category, Description, … )
DETAIL ( OrdId, ProdId, Qty, … )
ORDER ( OrdId, ClientId, Amount, OrdDate, DeliveryDate, ChosenStoreId, … )

and I must describe in simple words (i.e. to a non programmer), what the following query is doing:

select * from Order O
where Amount < 0.95 * ( select sum( Qty * Price ) from Product natural join Detail
where OrderId = O.OrderId )

My initial response was that it was showing the orders with the lowest amounts, which is only 5% of them, but it was marked as incorrect.

Another thought I had was perhaps it represented a discount, however the exercise description does not mention anything about discounts so I'm guessing it's also incorrect.

Can anyone tell me what the query is doing? Thanks in advance.

Note: apart from the tables, this is all the relevant information I have for the query.

Upvotes: 1

Views: 170

Answers (2)

Alexus Wong
Alexus Wong

Reputation: 365

Re Gordon I also don't approve of natural join. The query seems intentionally cryptic, especially since there's no example data. A good strategy is to write out the SQL in more general syntax and infer, see below:

select o.OrdId,
       sum(o.Amount) Amount,
       sum(d.Qty * p.Price) QtyPrice

from Order as o
  left join Detail as d on d.OrdId = o.OrdId
  left join Product as p on p.ProdId = d.ProdId

group by o.OrdId

having sum(o.Amount) < (0.95 * sum(d.Qty * p.Price))

To me, this query returns

  1. All unique orders made in a system
  2. Where the amount charged on the order was LESS than 95% of the expected amount.

Like you said, this could be a discount tracker, or maybe the o.Amount field is populated independently of the Detail and Product tables and this shows any errors.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I don't approve of natural join, but that is a separate issue.

This is returning rows where the amount of the order is less than 95% of the sum of the prices of the items on the order.

The cause for the difference is unclear -- it could be due to discounts at the order level; it could be due to extra charges, such as taxes or shipping; or there could be other causes as well.

Upvotes: 1

Related Questions