#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Posts
    2
    Rep Power
    0

    Lightbulb Try this from a past degree exam paper....!


    Hi, a work collegue gave me this to try from a final year degree exam paper as a challenge. If you think you can get your head round it go for it....Its a hard one. Here goes:

    Write a query that lists the names of those suppliers who have an entry on the suppliers spreadsheet for cd items who have never been asked to satisfy a customer order item through the back order route.

    The tables for this are as follows:


    Table 1= Item


    ItemCode (primary key)

    Description
    Format
    Unit price
    Stock Level

    ----------------------------------
    Table 2 = Customer

    CustomerId (pk)

    CustName
    CustAdd
    CustTel

    ---------------------------------
    Table 3= CustOrderHead

    CustOrderNo(pk)

    CustomerId (FOREIGN KEY)
    OrderDate
    CcDetails
    CcStatus
    PostandPack

    --------------------------------
    Table 4 = CustOrderItem

    CustomerOrderNo (pk)(fk)
    ItemCode(pk)(fk)

    CustOrderQuantity
    CustOrderUnitPrice
    Comments
    DateActioned

    --------------------------------
    Table 4 =BackOrderDetail

    CustOrderNo(pk)(fk) | this character is inserted to show that CustOrderNo and itemCode
    ItemCode (pk) |is related to Table 4 = CustOrderItem, CustomerOrderNo and ItemCode
    ItemCode(pk)(fk
    SupplierId (pk)(fk)

    DateRaised
    Comments
    DateActioned

    --------------------------------
    Table 5 = Supplier

    SupplierId (primary key)

    SuppName
    SuppAddress
    SuppTel

    ----------------------
    Table6 = SupplierItem

    SupplierId (composite key)
    ItemCode(composite key)

    SuppCatalogueNo
    SuppFormat
    SuppDescription
    SuppUnitPrice
    Delivery

    Well im stuck with it anyway....good luck!
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2001
    Location
    High above the mucky-muck (Columbus, OH)
    Posts
    266
    Rep Power
    14
    Mmmm.. SQL!!

    I'm not sure I completely understand the requested query, but here's my attempt (I assume the goal is to achive the result with one query and no temp tables or the like in the way?).

    Assumptions:
    "CD" item is Item.Format = "CD"
    "Suppliers Spreadsheet" =
    "Customer Order" = Line item in CustOrderItem
    "Back Order Route" = Item in BackOrderDetail

    p.s. BackOrderDetail isn't designed very well, since you have SupplierID stuck in there even though there's a supplierID in the ItemCode table. Plus item code is there twice?

    Code:
    SELECT s.SuppName
      FROM Supplier S
     INNER JOIN SupplierItem SI
             ON SI.SupplierID = S.SupplierID
     INNER JOIN Item I
             ON I.ItemCode = SI.ItemCode
            AND I.Format = 'CD'
     INNER JOIN CustOrderItem COI
             ON COI.ItemCode = I.ItemCode
     WHERE s.SupplierID NOT IN ( SELECT DISTINCT SupplierID
                                   FROM BackOrderDetail BOD
                                  WHERE BOD.ItemCode = COI.ItemCode )
    So if I wrote that correctly we'd want to get Supplier Names who stock items which have been purchased (COI table) and are Format 'CD', then disregard all Supplier IDs which exist in the BOD table for any items found in the first query.

    Although I can't remember if you can reference COI in the WHERE clause...
  4. #3
  5. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Posts
    2
    Rep Power
    0
    Hi, thanks for having a go, I definately think your on the right track although I'm a novice....

    ....I'll try to get the answer and post it....!

IMN logo majestic logo threadwatch logo seochat tools logo