SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    (Relational) DB tennis

    DB tennis

    Is anyone interested in having a little game of DB tennis? Here's how it would work:

    [list=1][*]We have a relational schema (optionally with an entity diagram)[*]Someone asks a question and the person who gets the right answer gets to post a question of his own. Correctness will be verified by everyone who takes part, and also an SQL guru (hopefully I can get one to give advice and teach us useful stuff)[*]Throughout the thread, questions are asked pertaining only to that schema[*]Questions in the vein of 'What is the SQL statement to do X?' and 'Why must column X reference column Y in table Z?' are currently the only allowed questions (there are much more possibilities, but we'll stick to this for now)[/list=1]

    This is not so much a competition as it is (hopefully) a learning experience. I've been wanting to learn from the veterans and experts around here and I figured this would be a good way, and get everyone else in on it too. Hopefully, we can get them to pop in now and then with precious tidbits of advice, demonstrate classic examples of newbie mistakes, etc.

    Here's the table for the ACME database, a simple 4-table database for keeping track of ACME's customers (apparently, they're 'badguys' ), it's products and shipping and receipt information for these products.

    Code:
    CREATE TABLE badguy (
      id INTEGER NOT NULL
     ,name VARCHAR(35)
     ,address VARCHAR(100)
     ,PRIMARY KEY (id))
    );
    
    CREATE TABLE product (
      id INTEGER NOT NULL
     ,name VARCHAR(40) NOT NULL
     ,description VARCHAR(255)
     ,price DECIMAL(10,2)
     ,PRIMARY KEY (id)
    );
    
    CREATE TABLE receipt (
      badguy INTEGER NOT NULL
     ,rdate DATE NOT NULL
     ,amount DECIMAL(10,2)		
     ,PRIMARY KEY (badguy, rdate)
     ,FOREIGN KEY (badguy) REFERENCES badguy(id)
    );
    
    CREATE TABLE shipped (
      badguy INTEGER NOT NULL
     ,sdate DATE NOT NULL		
     ,product INTEGER NOT NULL
     ,quantity INTEGER
     ,PRIMARY KEY (badguy, sdate, product)
     ,FOREIGN KEY (badguy) REFERENCES badguy(id)
    );
    (this is a table (slightly modified) taken from http://sqlzoo.net/)

    Here's the first question (let's start out slow):
    Code:
    Print the ids and names of badguys who have been shipped more
    than 5 quantities of the new 'Tasmanian Anvil' product.

  2. #2
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Code:
    Print the ids and names of badguys who have been shipped more
    than 5 quantities of the new 'Tasmanian Anvil' product.
    PHP Code:
    SELECT B.IDB.Name FROM badguy B 
    INNER JOIN Shipped S ON S
    .badguy B.badguy 
    INNER JOIN Product P on P
    .ID S.Product 
    WHERE S
    .Quantity AND P.Name='Tasmanian Anvil'
    ORDER BY B.Name ASC
    You even got an order by out of me!
    Here's my question:
    Code:
    Calculate how much money 'John Smith' has spent on the product 'Super Strength Dynamite' in the last 90 days.

  3. #3
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    Vinnie, what are the benefits of using an explicit JOIN instead of using the WHERE clause?


    I'll give it a shot. I'm not very good at this as you can see.
    Code:
    SELECT (S.quantity * P.price) AS amount_spent
    FROM badguy B, product P, shipped S
    WHERE B.name = 'John Smith'
      AND B.id = S.badguy
      AND TO_DAYS(NOW()) - TO_DAYS(S.sdate) <= 90
      AND S.product = P.id
      AND P.name = 'Super Strength Dynamite'

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    whoa, not so fast, please

    we shouldn't move on to the second question until the first has been satisfactorily answered

    the problem -- and i hasten to point out that this happens all the time in real life -- is very sloppy specs (no offence, joel)

    "badguys who have been shipped more than 5 quantities" can be interpreted three ways

    - more than 5 shipments
    - more than 5 in any one shipment
    - more than 5 overall, in all shipments

    vinnie answered only one of those, and it's not the way i would have interpreted the question


    rudy

    p.s. great idea, joel

    also, a suggestion: let us put the second thread, and each subsequent one, into its own thread, with a subject like "DB Tennis: Question 2"

    otherwise we will have all sorts of overlapping comments and answers and get all confused

  5. #5
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by r937

    we shouldn't move on to the second question until the first has been satisfactorily answered

    the problem -- and i hasten to point out that this happens all the time in real life -- is very sloppy specs (no offence, joel)
    No offence taken I'm basically a newbie. Now I know what it's like to be on the giving end of sloppy specs

    I meant it to be "more than 5 in all shipments", which I think is what Vinnie answered.

    also, a suggestion: let us put the second thread, and each subsequent one, into its own thread, with a subject like "DB Tennis: Question 2"

    otherwise we will have all sorts of overlapping comments and answers and get all confused
    Yeap that's what happens in PHP tennis. Good idea. We'll do that after Vinnie's question.
    Last edited by redemption; Feb 20, 2003 at 07:58.

  6. #6
    Jamison DaDaHost's Avatar
    Join Date
    Sep 2002
    Location
    Florida, USA
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am a little confused about the tennis threads. I posted an answer to Vinnie's question here:

    http://www.sitepointforums.com/showt...threadid=96645

    Jamie

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,336
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    no, vinnie answered "all shipments of more than 5 in that shipment"


  8. #8
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Originally posted by redemption


    Vinnie, what are the benefits of using an explicit JOIN instead of using the WHERE clause?
    In MSSQL at least, it converts the massive WHERE clauses to a JOIN, so I take it that a JOIN is faster anyway (no conversion needed). I've just learned to JOIN tables rather than use the WHERE for that slightly faster execution. If you have SQL Server or MSDE, try it out one day in Enterprise Manager.

    [EDIT:] As jofa said, it's standard too!
    Last edited by vgarcia; Feb 20, 2003 at 10:33.

  9. #9
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by redemption
    Vinnie, what are the benefits of using an explicit JOIN instead of using the WHERE clause?
    Ehm, because it's the way you should do it?...
    Or; it's standard, easier to read, understand, change into left/right joins when needed, etc etc...

    One more time:
    The older Microsoft join syntax lends itself to making mistakes because it is easy to make a mistake without knowing it. On the other hand, the ANSI syntax is very explicit and there is no chance you can make a mistake.

  10. #10
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    BTW, I think this DB Tennis idea is great, but I'm a little confused too, about which question is the one to answer; 1 or 2?

  11. #11
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    select id, name
    from badguy
    where id in
    (select badguy
    from shipped
    where product in
    (select id
    from product
    where name = 'Tasmanian Anvil')
    group by badguy
    having sum(quantity) > 5)
    order by 2



  12. #12
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for explaining the ANSI JOIN syntax.

    Originally posted by jofa
    select id, name
    from badguy
    where id in
    (select badguy
    from shipped
    where product in
    (select id
    from product
    where name = 'Tasmanian Anvil')
    group by badguy
    having sum(quantity) > 5)
    order by 2
    I'm confused too I must say, but I think that answers my question. I'll need a guru to verify though so hold your horses

    In any case, just to make it clear (I hope), the question I meant to ask was
    Code:
    What are the ids and names of badguys who have been shipped a total of more
    than 5 quantities of the new 'Tasmanian Anvil' product in all shipments.
    Is that sloppy specs? Does anyone understand what I'm trying to ask?
    Last edited by redemption; Feb 20, 2003 at 23:00.

  13. #13
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think my query is the answer to rudy's last variant:
    "more than 5 overall, in all shipments"

    And, look, no joins!

  14. #14
    Just Blow It bronze trophy
    DaveMaxwell's Avatar
    Join Date
    Nov 1999
    Location
    Mechanicsburg, PA
    Posts
    7,286
    Mentioned
    121 Post(s)
    Tagged
    1 Thread(s)
    Originally posted by jofa
    I think my query is the answer to rudy's last variant:
    "more than 5 overall, in all shipments"

    And, look, no joins!
    Just don't try to run it in MySQL unless you have the latest and greatest version (which I THINK now finally supports subselects)
    Dave Maxwell - Manage Your Site Team Leader
    My favorite YouTube Video! | Star Wars, Dr Suess Style
    Learn how to be ready for The Forums' Move to Discourse

  15. #15
    ☆★☆★ silver trophy vgarcia's Avatar
    Join Date
    Jan 2002
    Location
    in transition
    Posts
    21,235
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Originally posted by jofa
    And, look, no joins!
    Yes with that many sub-selects who needs 'em?

  16. #16
    ALT.NET - because we need it silver trophybronze trophy dhtmlgod's Avatar
    Join Date
    Jul 2001
    Location
    Scotland
    Posts
    4,836
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by jofa
    I think my query is the answer to rudy's last variant:
    "more than 5 overall, in all shipments"

    And, look, no joins!
    And is it going to be more efficent that joins?

  17. #17
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by dhtmlgod
    And is it going to be more efficent that joins?
    I don't know
    Some expert (rudy?) that could analyze it?

    Anyway, are we ready for question #2 now?

  18. #18
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yup I guess so. Btw, we can safely use anything that's in the SQL:1999 standard and forget about MySQL limitations in any answers (otherwise we'll never learn anything! )

    Fire away (in this thread or another thread, whichever you think is best)

    Still, it would be great to know if it would be more efficient than using ANSI joins like dhtmlgod asked.

  19. #19
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

  20. #20
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sorry I buggered it up when merging on the request of someone. Erm... this is Q No.2

    http://www.sitepointforums.com/showt...threadid=96645
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky

  21. #21
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    Svj
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by weirdbeardmt
    Sorry I buggered it up when merging ...
    For you, my friend...
    http://www.jcu.edu.au/aff/history/so...falc/0757.html

  22. #22
    We like music. weirdbeardmt's Avatar
    Join Date
    May 2001
    Location
    Channel Islands Girth: Footlong
    Posts
    5,882
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by jofa


    For you, my friend...
    http://www.jcu.edu.au/aff/history/so...falc/0757.html
    Oh really? Well doing fine until Friday, February 6, 2054 ... how about you?
    I swear to drunk I'm not God.
    Matt's debating is not a crime
    Hint: Don't buy a stupid dwarf Clicky


Bookmarks

Posting Permissions

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