1 / 13

Optimizing Payments and Orders Data Query with Customer Relationships and Inventory Links

This document outlines a comprehensive approach to query optimization within our database system that manages Customer, Orders, and Payments data. Key relationships need to be carefully scrutinized, with a focus on orphan records identified by CustID and StockNum within the Orders and Payments tables. Utilizing inner joins, we will analyze the total items sold, including specifics such as the "Silverware Set," while applying considerations for discounts and rebates. This document serves as a guide for SQL query creation and data integrity checks in relational database management.

pierce
Télécharger la présentation

Optimizing Payments and Orders Data Query with Customer Relationships and Inventory Links

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. FK: CustID on Payments Customers Payments FK: CustID on Orders 1 PK: CustID 8 PK: PaymentID 1 FK: poNum on Orders 8 Orders 8 OrderDates 1 8 FK: Type on Payments PK: None 1 PK: poNum PaymentType PK: Type 8 FK: StockNum on Orders 1 Inventory PK: StockNum Relationship Diagram

  2. YES. CustID 5 in Orders table is an orphan YES. CustID 5 in Payments table is an orphan YES. StockNum7 in Orders table is an orphan NO YES. poNum 5 on Orders table is an orphan

  3. Query Name: Query 3Tables Required: Customers/Payments Join on: CustIDJoin Type: Inner

  4. Table Name: Query 4 Tables Used: Inventory/Orders Join on: StockNumJoin Type: Inner

  5. SELECT Inventory.[Item Description], Sum(Orders.Qty) AS SumOfQty FROM Inventory INNER JOIN Orders ON Inventory.StockNum = Orders.StockNum GROUP BY Inventory.[Item Description] HAVING (((Inventory.[Item Description])="Silverware Set"));

  6. SELECT Inventory.[Item Description], Sum(Orders.Qty) AS SumOfQty FROM Inventory INNER JOIN Orders ON Inventory.StockNum = Orders.StockNum WHERE (((Inventory.[Item Description])="Silverware Set")) GROUP BY Inventory.[Item Description];

  7. Rebate: [SumOfPayments] * 0.20 Adjusted Payment: [SumOfPayments] – [Rebate] Table Name: Query 5 Tables Used: Customers/Payments Join on: CustIDJoin Type: Inner

  8. Query Name: Query 6Tables Used: Payments Join On: None Join Type: None

  9. Query 7

  10. Order Total: Sum( [Cost] * [Qty] ) Discount: [Order Total] * 0.15 Discount Price:[Order Total] - [Discount] Query Name: Query 7 Tables Required: Customers/Orders/Inventory Join on: CustID/StockNumJoin Type: Inner/Inner

  11. Query Name: Query 8Tables Required: Query 7 Join On: None Join Type: None

  12. Query Name: Query 9Tables Required: Customers/Orders/ OrderDates/Payments Join On: CustID/CustID/poNumJoin Type: Inner/Inner/Inner

  13. Query Name: Query 10Tables Required: Customers/Payments Join On: CustIDJoin Type: Inner

More Related