#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2009
    Posts
    8
    Rep Power
    0

    Simple SQL Query Questions


    CARS (cid, mid, cyear)
    BUYERS (bid, bname, bcity, age)
    MANUFACTURERS (maname, location)
    MODELS (mid, maname, model)
    SALESPEOPLE (sid, sname, years_employed)
    TRANSACTIONS (bid, cid, sid, amount, month, day, year)

    Source: http://sandbox.mc.edu/~gwiggins/syllabi/csc415/SQL-HMWK-F99.html

    Print the sid of all salespeople who have sold at least one car of every manufacturer.

    I do not know, i did it as - Wrong

    Code:
    select t.sid
    from salespeople s
    join transactions t on t.sid = s.sid
    join cars c on c.cid = t.cid
    join models m on m.mid = c.mid
    join manufacturers mn on mn.maname = m.maname
    group by t.sid 
    having t.sid >= 1 
    and count (distinct m.maname) = count(distinct mn.maname);
    Print the sname and total sales amount of the salesperson who had the highest total sales (in dollars) for 1997.

    Doesn't work:

    Code:
    select s.sname, to_char(sum(t.amount), 'fm$999999.00')
    from transactions t, salespeople s
    where t.sid = s.sid
    group by s.sname, t.year
    having (t.year = 1997) and max(sum(t.amount));
    Please help i tried my best...
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    857
    Rep Power
    388

    Talking Select the count()


    For your first query, try something like this (not tested):
    Code:
    SELECT   t.SID, COUNT (DISTINCT m.maname) sid_cnt
        FROM salespeople s JOIN transactions t ON t.SID = s.SID
             JOIN cars c ON c.cid = t.cid
             JOIN models m ON m.mid = c.mid
             JOIN manufacturers mn ON mn.maname = m.maname
       WHERE t.SID > 1
    GROUP BY t.SID
      HAVING COUNT (DISTINCT m.maname) = (SELECT COUNT (DISTINCT maname)
                                            FROM manufacturers);

    The other query should be similar to this...
    +
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2011
    Posts
    1
    Rep Power
    0

    try this, may be too late but see


    select distinct t.said
    From trasaction t, cars c
    where t.cid=c.cid and c.cid= all (select c1.cid
    from cars c1,models m
    where c1.mid= m.mid and m.maname = all (select distinct mn.manname
    from manufacturer mn
    ) )

IMN logo majestic logo threadwatch logo seochat tools logo