Forums: » Register « |  Free Tools |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support |

New Free Tools on Dev Shed!

#1
July 29th, 2009, 05:37 PM
 peace2009
Registered User

Join Date: Jul 2009
Posts: 8
Time spent in forums: 56 m 9 sec
Reputation 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
July 30th, 2009, 01:56 PM
 LKBrwn_DBA
Contributing User

Join Date: Sep 2006
Posts: 812
Time spent in forums: 1 Week 17 h 29 m 48 sec
Reputation Power: 388
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
September 10th, 2011, 10:19 PM
 nanupiya
Registered User

Join Date: Sep 2011
Posts: 1
Time spent in forums: 5 m 51 sec
Reputation 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
) )

 Viewing: Dev Shed Forums > Databases > Oracle Development > Simple SQL Query Questions