Results 1 to 13 of 13
  1. #1
    Join Date
    Jul 2006
    Posts
    22

    Unanswered: To DB/SQL teachers: A wrong query on Northwind DB discussion

    Good day to all.

    I'm a DB teacher in a University. Planning and developing SQL exercises for my students I found a "tricky" or extrange sql exercise.

    The sql exercise using Northwind DB reads as follows:
    "Wich is the total amount of the freight that corresponds to all orders containing products of seafood category ?"

    The first sql code that comes to my mind was:

    select sum(freight)
    from orders o, "order details" d, products p, categories c
    where o.orderid=d.orderid and d.productid=p.productid and p.categoryid=c.categoryid
    and categoryname='seafood'

    This is equivalent code constructed with Query constructor in Enterprise Manager:
    SELECT SUM(dbo.Orders.Freight) AS Expr1
    FROM dbo.Orders INNER JOIN
    dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID INNER JOIN
    dbo.Products ON dbo.[Order Details].ProductID = dbo.Products.ProductID INNER JOIN
    dbo.Categories ON dbo.Products.CategoryID = dbo.Categories.CategoryID
    WHERE (dbo.Categories.CategoryName = N'seafood')

    These 2 equivalent queries output 27722.9600 as result, but the correct result (total freight) is 23791.1400.

    Did you also think (as I did) that the exposed code is correct?

    Now I know the correct sql code that gives the corect answer and also the explanation why the code exposed fails, but before share it under your posible request, want to know your tech comments about this exercise in order to know if this particular exercise is "tricky" (make people to fail) or is my total fault and need to review my strategies of applying/constructing sql (and teaching).


    Thanks a lot.

    Carlos

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Took me a few minutes to think of the answer. I think the thing that threw me was the fact that you gave me your first pass at the query. Nice puzzle.

    You have several orders that contain multiple items of "seafood"....if you can call that stuff "food" ;-).

    I am not sure if that is too tricky for students, but it is a problem they are going to see in the "Real World" I would say give it to them. If you feel bad, maybe as a class discussion or extra credit.

  3. #3
    Join Date
    Jul 2006
    Posts
    87
    Quote Originally Posted by MCrowley
    You have several orders that contain multiple items of "seafood"....if you can call that stuff "food" ;-).
    'Escargots de Bourgogne'!!!
    "Waiter! There are SNAILS on her plate!"

    I don't consider the code too tricky, if you cover that type of possibility in the lab or lecture. A test is not the first time this concept should come up.

    Code:
    -- A clue as to why the first method fails with too large a number --
    SELECT     dbo.Orders.OrderID, dbo.Products.ProductID, dbo.Categories.CategoryID
    FROM       dbo.Orders 
    INNER JOIN dbo.[Order Details] 
    ON         dbo.Orders.OrderID            =  dbo.[Order Details].OrderID 
    INNER JOIN dbo.Products 
    ON         dbo.[Order Details].ProductID =  dbo.Products.ProductID 
    INNER JOIN dbo.Categories 
    ON         dbo.Products.CategoryID       =  dbo.Categories.CategoryID
    WHERE      dbo.Categories.CategoryName   =  N'seafood'
    AND        dbo.Orders.OrderID            IN (10270,10273)
    
    -- A brute force solution --
    SELECT     SUM(dbo.Orders.Freight) AS TotalFreight
    FROM       dbo.Orders 
    WHERE      dbo.Orders.OrderID            IN (SELECT OrderID 
    FROM       dbo.[Order Details] 
    WHERE      dbo.[Order Details].ProductID IN (SELECT ProductID 
    FROM       dbo.Products 
    WHERE      dbo.Products.CategoryID       IN (SELECT CategoryID 
    FROM       dbo.Categories
    WHERE      dbo.Categories.CategoryName = N'seafood')))
    I could not figure out how to set up "DISTINCT" within the INNER JOIN, so I had to do the whole thing the long way. Good thing the result desired was only the Freight.
    Last edited by Code Carpenter; 07-26-06 at 17:15.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I think its an excellent problem to give your students, because it is one of the more common SQL errors, and all the more so because it does not give an obviously incorrect result.

    But I'd have hoped that if you are teaching SQL to your students the first code that came to mind would use JOINS rather than linking tables in the WHERE clause...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2006
    Posts
    33
    SQL is easier to read using the new JOIN syntax - you also don't have issues with outer join queries. I think some vendors are planning on dropping the old syntax, but this will probably just affect outer join queries. If you are teaching SQL, you might as well use the new syntax.

    Some more variations:

    Code:
    select sum(Freight)
    from Orders o 
    where exists 
    (select * from [Order Details] od join Products p on od.ProductId = p.ProductId
    join Categories c on c.CategoryId = p.CategoryId where CategoryName = 'seafood'
    and o.OrderId = od.OrderId)
    
    select sum(Freight)
    from Orders o 
    where exists (select * from [Order Details] od 
    		where o.OrderId = od.OrderId and 
    		exists (select * from Products p 
    			where p.ProductId = od.ProductID 
    			and exists(select * from categories c
    				 where c.CategoryId = p.CategoryID 
    				and categoryname = 'seafood')))
    SQL is full of gotchyas. Still, you gotta love it!
    David Wiseman
    MCSE, MCSA, MCDBA

    www.wisesoft.co.uk

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by carloco
    I'm a DB teacher in a University.
    You're kidding, right?

    What University?

    My question to you would be, why wouldn't you create your own DB?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2006
    Posts
    22
    Hello folks

    A lot of thanks for your kindly comments and sugestions.

    First, respect to the fact of using the new Join syntax, well I'm used to old one and for me is more understandable, but for my students I'll work with the new one of course.

    This particular problem is discussed right now in class as an example of the "particularities" of some queries.

    Ok, the right code solution I propose is very similar as those outlined by some of you:
    Code:
    select sum(freight) from orders
    where orderid in (select orderid from  "order details" d, products p, 
    categories c 
    where d.productid=p.productid and p.categoryid=c.categoryid and 
    categoryname='seafood')
    or in the Join syntax outfit:
    Code:
    select sum(freight) from orders 
    where orderid in (select o.orderid from orders o inner join "order details" od 
    on o.orderid=od.orderid inner join products p 
    on od.productid=p.productid inner join categories c 
    on p.categoryid=c.categoryid
    where c.categoryname='seafood')
    The problem arises due to the fact that the aggregate function sum() is working on freight column but of a joined tables (no just in orders) in wich case in some events (in next example the order 10340) it appears more than one time:

    Code:
    select o.orderid, freight, od.productid, p.categoryid, c.categoryname
    from orders o inner join "order details" od on o.orderid=od.orderid
    inner join products p on od.productid=p.productid 
    inner join categories c on p.categoryid=c.categoryid
    where o.orderid = 10340
    
    10340	166.3100	18	8	Seafood
    10340	166.3100	41	8	Seafood
    10340	166.3100	43	1	Beverages
    Of course you neither can use distinct within the agregate (sum(distinct freight)) because it eliminates repeated values of the same order but of different orders with the same freight as well.

    Based on this example, could someone say this as a true fact? :
    "When you are using an aggregate function on joined tables you should realize that the number of records participating in the agregate are those from the table having the grater number of records ("order details" in this case), and for work on this issue you should have to use distinct within the agregate function or "break" the join using subqueries"
    Last edited by carloco; 07-31-06 at 19:03.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by carloco
    Based on this example, could someone say this as a true fact? :
    "When you are using an aggregate function on joined tables you should realize that the number of records participating in the agregate [B]are those from the table having the grater number of records..."
    This is true in the particular case where one side of the join is a unique value, but when joining two non-unique columns you actually get a cross-product of the two cardinalities. Two records in table A that match to three records in table B will yield six resulting records.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by carloco
    First, respect to the fact of using the new Join syntax, well I'm used to old one and for me is more understandable, but for my students I'll work with the new one of course.
    You do know that the "new" ones are the ANSI Standard

    EDIT: And I would hope that you would also teach them benefits of formatting code so it's readable...alos, the "old" join syntax is pure equ-joins....unless you use (+) after the table....wait that's Oracle...I think it was just a + after the column...I forget now..which I'm happy about
    Last edited by Brett Kaiser; 08-01-06 at 13:55.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jul 2006
    Posts
    22
    sorry Brett, could not catch exactly what you mean.

    May you can help given me some on-topic thoughts /criteria ?

    TIA

  11. #11
    Join Date
    Jan 2005
    Location
    Green Bay
    Posts
    201
    I think he is saying don't use old syntax to join in where clause. Hard to read for newbies.

    If you had a choice between old(dial_up) and new(cable or DSL) why would you use dial-up except for mentioning it in case it was needed to be supported.

    Plus for readability the where clause will be the restriction criteria. from your resulting recordset (view) of the properly made joins.

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by carloco
    sorry Brett, could not catch exactly what you mean.

    May you can help given me some on-topic thoughts /criteria ?

    TIA

    What's you major?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    "new" not only as in ANSI but as in 1992.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •