Question
Need help with this SQL question thanks. CUSTOMER_T CustomerID (PK) CustomerName CustomerStreet CustomerCity CustomerState CustomerZip CreditLimit SalesRepID (FK) of EMPLOYEE_T ORDER_T OrderID (PK) CustomerID (FK)
Need help with this SQL question thanks.
CUSTOMER_T
CustomerID (PK)
CustomerName
CustomerStreet
CustomerCity
CustomerState
CustomerZip
CreditLimit
SalesRepID (FK) of EMPLOYEE_T
ORDER_T
OrderID (PK)
CustomerID (FK) of CUSTOMER_T
CustomerPONumber
OrderDate
DueDate
ShipDate
ORDER_LINE_T
OrderID composite (PK), (FK) of ORDER_T
ProductID composite (PK), (FK) of PRODUCT_T
OrderQuantity
PRODUCT_T
ProductID (PK)
ProductDescription
StockQuantity
FABRICATED_T
ProductID composite (PK), (FK) of PRODUCT_T.ProductID
PartID composite(PK), (FK) of PRODUCT_T.ProductID
PartQuantity quantity of PartID that goes into ProductID (example, 2 tires
In one bicycle)
PRICE_HISTORY_T
ProductID(FK) StartPrice(PK)
EndDate
UnitPrice
PRODUCT_SUPPLIER_T
ProducID composite (PK), (FK) of PRODUCT_T
SupplierID composite (PK), (FK) of SUPPLIER_T
VendorPartID this is the ID the Vendor (i.e., Supplier) uses in their system), similar to
CustomerPONumber in the ORDER_t.
ProductCost
PurchasedQuantity
SUPPLIER_T
SupplierID (PK)
SupplierName
SupplierStreet
SupplierCity
SupplierState
SupplierZip
EMPLOYEE_T
EmployeeID (PK)
EmployeeName
EmployeeStreet
EmployeeCity
EmployeeState
EmployeeZip
ManagerID (FK) unary
EMPLOYEE_COURSE_T
EmployeeID combination (PK), (FK) of EMPLOYEE_T
CourseID combination (PK), (FK) of COURSE_T
CompletionDate
COURSE_T
CourseID (PK)
CourseDescription
SALES_COMMISSION_RATE_T
EmployeeID (PK), (FK) of EMPLOYEE_T
CommissionRate
A.Which sales representative has generated the most profit for the company? Hint: Provide the name of the sales rep and the total profit amount only. This one SQL statement will have subqueries. You must determine the profitability of a product by subtracting its cost from its selling price and multiplying the difference by the quantity sold. Regarding the cost, many products are supplied by more than one suppler, and each supplier may provide a different cost, so when this occurs, use the least expensive cost in measuring your profitability.
in this format
SELECT FROM EMPLOYEE, SALESREP,CUSTOMER,ORDER,ORDERLINE, PRODUCT , PRICEHISTORY , PRODUCT SUPPLIER
(/* DETERMINE THE PROFIT FOR EACH PRODUCT
SELECT* FROM PriceHistory PH, )
(/* DETERMINE LOWERST COST OF EACH PRODUC T*/
SELECT ProductID, AS MinCost FROM ProductSupplier ) AS MT
WHERE PH.ProductID = MT
GROUP BY SalesRep. EmployeeID TOP 1
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started