What could possibly go wrong? correct answers only

Databases
1

this has to be the loneliest SQL-related forum i am still subscribed to

    SELECT ordernumber
         , SUM(quantity)
      FROM order
    LEFT OUTER
      JOIN orderitem
        ON orderid = order.id
     WHERE product = 'widget'   
       AND quantity < '10'
2

Maybe only a typo because otherwise it would be too easy but

SUM(quantity)

let me guess that a quantity is a number and in that case you should not compare it with a string

quantity < ‘10’

3

yes!

in the universe of answers, you have found a correct one

5

anyone else?

i can’t believe no one noticed the half dozen other problems with that query…

6

There might be orderitems which do not have an order. In that Case you would have a oedernumber NULL and therefor a useless sum of quantity

7

in a sane world, you could not have an orphan orderitem row like that, but you would only see it using RIGHT OUTER JOIN, not LEFT OUTER JOIN

but why would ordernumber be NULL? what table is it in?

8
  • it’s an aggregate query without GROUP BY clause. Better specify what you want there (probably GROUP BY ordernumber). IIRC in strict mode MySQL won’t execute any aggregate query without a GROUP BY clause.
  • you can’t actually call a table order as it’s a reserved keyword. You’d have to quote it in backticks
  • ordernumber, quantity and product might be ambiguous. Better to specify which table you want them from (orderid possibly also, though less nasty because both orderid and order.id appear in the ON clause)
  • maybe the intent of the query was to get all orders with less than 10 items total, in which case it should be HAVING SUM(quantity) < 10 rather than WHERE quantity < 10
9

it certainly will if the SELECT clause contains only aggregates

for example, a table of student marks –

SELECT MIN(mark) AS lo_mark
     , MAX(mark) AS hi_mark
     , AVG(mark) AS avg_mark
     , COUNT(*) AS nbr_students
  FROM studentmarks

or doublequotes – "order"

or square brackets – [order]

yes, but unlikely

you saw the hint in order.id – this is the PK, so orderid has to be in orderitems as the FK

still, unqualified column names increase uncertainty for someone trying to understand the query

what table is product in?

10

My guess would be orderitem, but it’s not clear. That’s why I said

:slight_smile:

11

a product name like ‘widget’ would presumably be in the products table

orderitem is the many-to-many table between orders and products

so orderitem would have a FK to the product’s PK, which presumably isn’t the name

i didn’t say the query would actually run

;o)

1 Like
12

Why would you have more than 1 instance of a product in an order? Why are we summing quantities less than 10, but not all quantities?

No, it’s not strictly what-could-go-wrong-with-my-query, but more what-went-wrong-to-make-you-make-this-query.

13

well spotted – orderitem would have only one row per product in an order

summing product quantities within an order might make sense, if only it wasn’t for that pesky ‘widget’ filter, which negates that need

as for the “less than 10” criterion, that’s fine – suppose i wanted to see orders for low quantities, when people usually order my products by the hundreds

15

Then you wouldnt sum them… (so again, its that abiguous nature of what was written - is it meant to be a sum of orders (which doesnt fit the brief), or is it meant to be a sum within the order (which doesnt make sense given the nature of an order)…

16

indeed, so that’s actually the main thing that could go wrong

i mean, other than the fact that the query won’t run

17

“Your database engineers were so preoccupied with whether they could, they didn’t stop to think if they should.” :wink:

2 Likes
19

The code in post 1 won’t work because the indentation on each line is not uniform. :grimacing:

1 Like
20

sorry, that is incorrect… we wanted correct answers only

2 Likes
21

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.