Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2003
    Posts
    3

    Unanswered: 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

  2. #2
    Join Date
    Oct 2003
    Location
    Dallas
    Posts
    76
    --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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •