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)
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));```
2. 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...
+
3. 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
) )