SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 27
  1. #1
    Jamison DaDaHost's Avatar
    Join Date
    Sep 2002
    Location
    Florida, USA
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    DB Tennis: Question 2

    Game Start:
    http://www.sitepointforums.com/showt...threadid=96634

    Code:
    Calculate how much money 'John Smith' has spent on the product
     'Super Strength Dynamite' in the last 90 days.
    Using MS SQL Transact-SQL
    Code:
    select total = sum(p.price * s.quantity)
    from badguy b, shipped s, product p
    where b.id = s.badguy
    and   p.id = s.product
    and   p.name = 'Super Strength Dynamite'
    and   b.name = 'John Smith'
    and   s.sdate >= dateadd(d,-90,getdate())
    Question 3 (assuming I'm right )
    Code:
    The database structure does bother me because 
    there is no Order table that allows you to relate 
    a receipt to the products purchased. 
    How about for the next question: 
    Create an Order table(s) with a relational link 
    into Receipt.
    Jamie
    Last edited by DaDaHost; Feb 20, 2003 at 07:46.

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    (Relational) DB Tennis #2

    Calculate how much money 'John Smith' has spent on the product 'Super Strength Dynamite' in the last 90 days.



    The tables:
    http://www.sitepointforums.com/showt...078#post688078

  3. #3
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I still think that using WHERE where you could have used JOIN (or a subselect ) is bad...

  4. #4
    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)
    Hows this (jofa style):

    Code:
    DECLARE @maxDate DateTime
    SELECT @maxDate = DateAdd(d, -90, GETDATE())
    
    SELECT b.name, 
    ((
    SELECT SUM(s.quantity) 
    FROM shipped s 
    WHERE s.badguy = b.ID AND s.sdate > @maxDate
    ) * (
    SELECT p.price 
    FROM product p 
    WHERE p.name = 'Super Strength Dynamite')) 
    [Spent on Super Strength Dynamite] 
    FROM badguy b WHERE b.name = 'John Smith'
    ?

    Last edited by dhtmlgod; Feb 21, 2003 at 09:29.

  5. #5
    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)
    If that's right, do I get to set the next task?

    If so..

    The mission, if you choose to accept it, is to return the FIRST DATE each BADGUY bought a "Stinger Missle" AND "Super Strength Dynamite" and how MANY of EACH they bought

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    dhtmlgod, i'm quite sure "jofa style" does not involve coding the entire query all on one line forcing everyone to scroll horizontally



    nor would jofa likely use a DECLARE statement, which will work only in my grandfather's database

    let me offer an alternate version, using joins rather than your funky derived tables

    here's my solution to "Calculate how much money 'John Smith' has spent on the product 'Super Strength Dynamite' in the last 90 days."

    Code:
    select sum(s.quantity) * p.price as totalspent
      from badguy b
    inner 
      join shipped s
        on b.id = s.badguy
       and current_date - s.date <= interval 90 days
    inner
      join product
        on s.product = p.id
       and p.description = 'Super Strength Dynamite'
     where b.name = 'John Smith'
    notice that this is standard SQL-92 syntax, and thus may not be supported by your database du jour


    rudy

  7. #7
    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)
    I didn't use derived tables...

    (Took it off one line, just a habit, happy?)
    Last edited by dhtmlgod; Feb 21, 2003 at 09:30.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i forgot to mention that my solution is better, in my most humble opinion, than DaDaHost's, because

    1. join conditions are listed in the JOIN clause, which would allow an optimizer to restrict the rows being joined during row retrieval, rather than during application of filter conditions in the WHERE clause

    2. aggregation is of a single column value (quantity), not an expression (quantity * price) -- i.e. the aggregate is multiplied by the same price once, not repetitively on each shipped row

    both of those are performance considerations (and the JOIN syntax also improves legibility, understandability, and therefore ease of future maintenance)

    it occurs to me that we really need to have sample data so that we can actually verify which of our solutions produces correct results

    in my mind, getting the correct result always trumps getting an efficient result, although it does help to get the correct result efficiently, so we cannot just disregard that (see point 2 above)

    rudy

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    dhtmlgod, a subselect in the SELECT list is called a derived table

    the optimizer usually derives it (unless it's correlated) ahead of time

    for example, to get the total number of posts today along with the body of a particular post...
    Code:
    select postbody, postauthor
         , ( select count(*) from posts 
              where postdate = current_date ) as poststoday
      from posts
     where postid = value
    derived tables are also permitted in the FROM clause

    rudy

  10. #10
    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)
    Ok, confused myself

    Your way is fastest tho, yeah? Derived tables carry a far ammount of overhead...


  11. #11
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by r937
    ...aggregation is of a single column value (quantity), not an expression (quantity * price) -- i.e. the aggregate is multiplied by the same price once, not repetitively on each shipped row
    Which suddenly made me aware that the tables are wrong!
    You can't have the price column in the product table only, because the price might change, therefore you need a price column in the shipped table too
    Otherwise the sum(quantity * price) will be incorrect

  12. #12
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ...and changing the column names to, e.g., badguy_id in both badguy and shipped etc, would make it possible to use this funky variant of JOIN clause:

    ... badguy inner join shipped using(badguy_id) ...

    Or is this grandpa's syntax too?












    BTW, why is the table called "badguy"

  13. #13
    Jamison DaDaHost's Avatar
    Join Date
    Sep 2002
    Location
    Florida, USA
    Posts
    244
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Originally posted by jofa

    Which suddenly made me aware that the tables are wrong!
    You can't have the price column in the product table only, because the price might change, therefore you need a price column in the shipped table too
    Otherwise the sum(quantity * price) will be incorrect
    Hey Jofa,

    This is why I posed the Order table as the next question. I think Orders and Shipments should be two different entities, but with the current schema they are mushed together.

    I've been doing Transact-SQL for years, but honestly, really I'm trying to switch over to Ansi.

    Jamie

  14. #14
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    redemption, where are you?
    I think we need a referee in this tennis game

    Should the tables be changed before we move on to the next question, and who is the winner in the current round?

  15. #15
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ...or, if we should use only the 4 original tables, shouldn't the bad guys, products etc be limited to the one found here ?

    I.e. no 'John Smith' or 'Stinger Missile'

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

    Hmmm I see this has gotten a bit messy because of my poor rules and specs (my only excuse is that this happens in all tennis matches ).

    Here's the deal:
    [list=1][*]Nobody gets to post a question until it is certified correct by a guru (rudy/r937 will do the judging). Hopefully people like MattR and DaveMaxwell would come across these threads and offer to judge too (hint! )[*]We'll probably have to start a new question using a new database with sample data so we can easily verify correctness. To that end, we'll need a good sample database which hopefully someone can find.[*]Correctness is the deciding factor. Even if more efficient answers come up, the 1st correct answer is the winner. For our purposes, please do come up with suggestions for improvement in terms of efficiency, clarity, etc.[/list=1]

    So now, we have to figure out who's correct. I can't judge because I'm not good at this so I'll try and get rudy to judge and then we can move on.

    The next question would have to be posted in another thread (post the link to that new thread here). You can (probably should) start off a new set of tables, with sample data. (I understand this is difficult to do so as I said before, anyone who has a good set of tables with sample data could please come forward )

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i am honoured, but really, i was hoping i would get to enter the tennis matches myself, rather than judge

    if correctness is the criterion, it shouldn't need a judge anyway, right?



    so, anybody want to make suggestions about a sample database?

    please don't suggest northwind, ok?

    the database should be a script, consisting of DDL (create table statements) and SQL (insert statements)

    that way we could all run it on our own systems

    other comments?

  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)
    You can enter too, only we do need someone of sufficient expertise to fall back on when there are any disputes or uncertainty about correctness.

    When we get a sample database with which to verify the answers, we can do without a human judge. But at least we need you to judge the answers to this question. Could you please?

  19. #19
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I do have a sample database from my coursework at school. I'll post it so that everyone can see if it's OK to use it.

    It's a bank database.
    Code:
    create table branch
       (branch_name varchar(15) not null unique,
        branch_city varchar(15) not null,
        assets numeric not null,
        primary key(branch_name));
    
    create table account
       (account_number varchar(15) not null unique,
        branch_name varchar(15) not null,
        balance numeric not null,
        primary key(account_number),
        foreign key(branch_name) references branch(branch_name));
    
    create table customer
       (customer_name varchar(15) not null unique,
        customer_street varchar(12) not null,
        customer_city varchar(15) not null,
        primary key(customer_name));
    
    create table loan
       (loan_number varchar(15) not null unique,
        branch_name varchar(15) not null,
        amount numeric not null,
        primary key(loan_number),
        foreign key(branch_name) references branch(branch_name));
    
    create table depositor
       (customer_name varchar(15) not null,
        account_number varchar(15) not null,
        primary key(customer_name, account_number),
        foreign key(account_number) references account(account_number),
        foreign key(customer_name) references customer(customer_name));
    
    create table borrower
       (customer_name varchar(15) not null,
        loan_number varchar(15) not null,
        primary key(customer_name, loan_number),
        foreign key(customer_name) references customer(customer_name),
        foreign key(loan_number) references loan(loan_number));
    E-R diagram:
    Attached Images Attached Images
    Last edited by redemption; Feb 22, 2003 at 03:55.

  20. #20
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I agree, the referee can participate in the game
    (If John McEnroe could be both player and referee, then you can...)


    redemption; any insert statements for the db?

  21. #21
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes I've got them and will post them if this set of tables is OK with everyone.

    In any case, once we get an acceptable set of tables, I'll start a new thread stating all the rules once more so it's clearer.

  22. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i'll help out judging if i can, but it shouldn't be necessary with the right sample data

  23. #23
    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
    i'll help out judging if i can, but it shouldn't be necessary with the right sample data
    OK can we use the schema I posted above?

    If so we'll forget about the correctness of the answers in this thread and start afresh with a new thread.

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    suggest we hold off on the questions till we see some actual data

    the two many-to-many relationships to customer should be fun...

    e.g. customers who have loans at branches other than their deposit accounts, customers whose loan balances are larger than their deposit balances, and so on

    rudy

  25. #25
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh so you want to see the actual data.

    Code:
    insert into customer	values ('Jones',	'Main',		'Harrison');
    insert into customer	values ('Smith',	'Main',		'Rye');
    insert into customer	values ('Hayes',	'Main',		'Harrison');
    insert into customer	values ('Curry',	'North',	'Rye');
    insert into customer	values ('Lindsay',	'Park',		'Pittsfield');
    insert into customer	values ('Turner',	'Putnam',	'Stamford');
    insert into customer	values ('Williams',	'Nassau',	'Princeton');
    insert into customer	values ('Adams',	'Spring',	'Pittsfield');
    insert into customer	values ('Johnson',	'Alma',		'Palo Alto');
    insert into customer	values ('Glenn',	'Sand Hill',	'Woodside');
    insert into customer	values ('Brooks',	'Senator',	'Brooklyn');
    insert into customer	values ('Green',	'Walnut',	'Stamford');
    insert into customer	values ('Jackson',	'University',	'Salt Lake');
    insert into customer	values ('Majeris',	'First',	'Rye');
    insert into customer	values ('McBride',	'Safety',	'Rye');
    
    insert into branch	values ('Downtown',	'Brooklyn',	 900000);
    insert into branch	values ('Redwood',	'Palo Alto',	2100000);
    insert into branch	values ('Perryridge',	'Horseneck',	1700000);
    insert into branch	values ('Mianus',	'Horseneck',	 400200);
    insert into branch	values ('Round Hill',	'Horseneck',	8000000);
    insert into branch	values ('Pownal',	'Bennington',	 400000);
    insert into branch	values ('North Town',	'Rye',		3700000);
    insert into branch	values ('Brighton',	'Brooklyn',	7000000);
    insert into branch	values ('Central',	'Rye',		 400280);
    
    insert into loan	values ('L-17',		'Downtown',	1000);
    insert into loan	values ('L-23',		'Redwood',	2000);
    insert into loan	values ('L-15',		'Perryridge',	1500);
    insert into loan	values ('L-14',		'Downtown',	1500);
    insert into loan	values ('L-93',		'Mianus',	500);
    insert into loan	values ('L-11',		'Round Hill',	900);
    insert into loan	values ('L-16',		'Perryridge',	1300);
    insert into loan	values ('L-20',		'North Town',	7500);
    insert into loan	values ('L-21',		'Central',	570);
    
    insert into account	values ('A-101',	'Downtown',	500);
    insert into account	values ('A-215',	'Mianus',	700);
    insert into account	values ('A-102',	'Perryridge',	400);
    insert into account	values ('A-305',	'Round Hill',	350);
    insert into account	values ('A-201',	'Perryridge',	900);
    insert into account	values ('A-222',	'Redwood',	700);
    insert into account	values ('A-217',	'Brighton',	750);
    insert into account	values ('A-333',	'Central',	850);
    insert into account	values ('A-444',	'North Town',	625);
    
    insert into depositor values ('Johnson','A-101');
    insert into depositor values ('Smith',	'A-215');
    insert into depositor values ('Hayes',	'A-102');
    insert into depositor values ('Hayes',	'A-101');
    insert into depositor values ('Turner',	'A-305');
    insert into depositor values ('Johnson','A-201');
    insert into depositor values ('Jones',	'A-217');
    insert into depositor values ('Lindsay','A-222');
    insert into depositor values ('Majeris','A-333');
    insert into depositor values ('Smith',	'A-444');
    
    insert into borrower values ('Jones',	'L-17');
    insert into borrower values ('Smith',	'L-23');
    insert into borrower values ('Hayes',	'L-15');
    insert into borrower values ('Jackson',	'L-14');
    insert into borrower values ('Curry',	'L-93');
    insert into borrower values ('Smith',	'L-11');
    insert into borrower values ('Williams','L-17');
    insert into borrower values ('Adams',	'L-16');
    insert into borrower values ('McBride',	'L-20');
    insert into borrower values ('Smith',	'L-21');


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
  •