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
... I have done the frist 49 of the queries ... and they all made sense ... but this one is just bugging me ... PLEASE HELPDon't feel bad. This is not a simple task.
Some questions:
Does this have to be done in a single SELECT statement, or can it be done using a stored procedure or user-defined function that contains multiple SQL statements?
You need to list items supplied by companies, but all I see in your structure are departments and divisions. Please clarify.
Your DIVISION and DEPT tables are kind of a goofy schema, but I suppose you have no control over that?
What is the purpose of the SELL table vs the SUPPLY table? Can we ignore the SELL table?
Is a company considered to be a supplier of all type "A" items if its departments and divisions collectively supply all the items, or does it have to supply the items itself directly?
Can we ignore the EMP table? It appears to have nothing to do with the problem.
blindman|||Originally posted by blindman
Don't feel bad. This is not a simple task.
Some questions:
Does this have to be done in a single SELECT statement, or can it be done using a stored procedure or user-defined function that contains multiple SQL statements?
You need to list items supplied by companies, but all I see in your structure are departments and divisions. Please clarify.
Your DIVISION and DEPT tables are kind of a goofy schema, but I suppose you have no control over that?
What is the purpose of the SELL table vs the SUPPLY table? Can we ignore the SELL table?
Is a company considered to be a supplier of all type "A" items if its departments and divisions collectively supply all the items, or does it have to supply the items itself directly?
Can we ignore the EMP table? It appears to have nothing to do with the problem.
blindman
The assignment had 50 queries ... of all different kinds.
We have to do it in a single SELECT statement (with subqueries of course)
I do not think that an table except the SUPPLY and ITEM are needed but I dont know exactly how they interact ...|||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.
blindman
samirayes:
Actually the aspnet_regsql could be better: if it retrieve database information from 'master..sysdatabases' not just 'sysdatabases', we do not have to change the default database for the login used in the wizard.