Monday, March 19, 2012

Help ... new to SQL

Hi,

I am working on a school project ...

I have the following schema:
<b>
DIVISION (dvname, manager)
DEPT (dname, parent-dname/parent-dvname, manager, floor#)
EMP (ename, salary, dname/dvname)
ITEM (iname, color, price, type)
SELL (dname, iname)
SUPPLY (sname, iname, dname)
</b>
Each of the first fields is the PKey.

I have to figure out the SQL for the following statement:

<b>List the items supplied by all companies that supply all items of type A.</b>

I have gotten this far, but do not understand division in SQL well enough .. I have a relational algebra solution that works ... but am having a hell of a time with a SQL solution ... Please help,

This is what I have in SQL:
SELECT SE.iname FROM SUPPLY SE WHERE NOT EXISTS
(SELECT I.iname FROM ITEM I WHERE I.type='A' AND NOT EXISTS
(SELECT S.iname FROM SUPPLY S WHERE S.iname=I.iname AND S.dname=SE.dname))

I have not work with SQL much so please help out ... I am using SQL Server if that matters ... Thanks--should give you the supply name where type = 'A'

select s.sname, i.iname, i.color, i.price, i.type
from supply s, item, i
where s.iname = i.iname and i.type = 'A'

if there is more to it let me know, but that should give you the items by supplier, if you want just distinct iname then just put

select distinct i.iname
from supply s, item, i
where s.iname = i.iname and i.type = 'A'

have fun

No comments:

Post a Comment