Item_ID
FROM (Sale INNER JOIN Line_Item ON
Sale.Invoice_No = Line_Item.Invoice_No)
INNER JOIN Item ON
Line_Item.Item_Id = Item.Item_ID
WHERE Item.Consignment_Seller_ID Is Null
ANSWERS TO REVIEW QUESTIONS 211
212 ANSWERS TO REVIEW QUESTIONS
AND Item.Price_sold Is Not Null
AND Sale.Date Like '*11*2007*';
g Report the profit on all non-consignment items this month.
SELECT SUM(Line_Item_Price) - SUM(Cost) as Profit
FROM ( ( Sale JOIN Line_Item ON Invoice_No )
JOIN Item ON Item_ID )
WHERE Consignment_Seller_ID IS NULL
AND Price_sold IS NOT NULL
AND Date LIKE '%2007-11%';
In Access:
SELECT SUM(Line_Item_Price)-SUM(Cost) AS Profit
FROM (Sale INNER JOIN Line_Item ON
Sale.Invoice_No = Line_Item.Invoice_No)
INNER JOIN Item ON
Line_Item.Item_ID = Item.Item_Id
WHERE Item.Consignment_Seller_ID Is Null
AND Item.Price_sold Is Not Null
AND Date Like '*11*2007*';
8.6 When a customer buys an item (or several) from the store, several changes to database tables will occur.
Explain what those might be.
a If the customer does not yet exist in the database, a customer record must be
added.
b The row in table Item for the antique the customer purchases must have the column Price_sold
updated.
c The sale must be recorded in tables Sale and Line_Item.
Pages:
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541