|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
Lose your application development headaches. Start developing and deploying applications with Advantage Database Server today. Download a 30-day trial for Free!
|
|
#1
April 20th, 2002, 10:38 AM
|
|||
|
|||
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
April 20th, 2002, 01:09 PM
|
|||
|
|||
|
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...
__________________
Matt - matt@fanhome.com FanHome.com - Where Sports Fans Connect (our SYBASE-backed vB Forums!) Sybase DBA / PHP fanatic  Sybase v. MySQL v. Oracle | Why I don't like MySQL  | Download Sybase TODAY! | Visit DBForums.com for all your RDBMS talk!
|
|
#3
April 20th, 2002, 04:48 PM
|
|||
|
|||
|
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....!
|
|
| Viewing: Dev Shed Forums > Databases > Database Management > Try this from a past degree exam paper....! |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
