I have the following schema:
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)
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
... I have done the frist 49 of the queries ... and they all made sense ... but this one is just bugging me ... PLEASE HELP
If your answer must be a single select statement, then that eliminates any possibility of searching the departmental hierarchy, and so actually makes the problem easier.
Break your problem into parts.
If you had a list of companies (departments?) then it would be trivial to list all the products they sell, right?
So your task is to create a subquery that lists all the departments that sell all type "A" products. How do you determine if a department sells all of the type "A" products?
The most direct method would be to find Departments where the number of distinct type "A" products they sell equals the number of type "A" products that exist.
The more subtle method (which your code seems to be attempting) is to create a list of departments that DON'T sell all the type "A" items, and then find all the departments that AREN'T on that list. This is certainly a more confusing method than the product count technique, but it may have some efficient advantages for extremely large tables. I don't know. But I do know you can get a list of departments that don't sell a full line of type "A" products by using a FULL OUTER JOIN between DEPARTMENT and a list of type "A" suppliers.