Could someone help me with sql statements for the following questions please? He
ID: 3569010 • Letter: C
Question
Could someone help me with sql statements for the following questions please?
Here below are the tables/attributes I will be using for the following questions.
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
UnitPrice
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)
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
I NEED HELP WITH SQL STATEMENTS ON THESE QUESTIONS:
A. List all of the products provided by each supplier in alphabetical order with the least expensive
products shown first for each supplier. Hint: Be sure to include the Product ID, Product Description,
Supplier ID, Supplier Name, Vendor Part #, and Cost.
B. Which sales rep has the highest $ in total sales? Hint: Provide the name and the total sales amount for
that rep only. Do not include the information for other people, and do not list details about orders.
C. List all of the sales reps and their total commission amounts in order of highest sales commission
dollar amount first. Hint: Provide all of sales reps
Explanation / Answer
--A. List all of the products provided by each supplier in alphabetical order with the least expensive
--products shown first for each supplier. Hint: Be sure to include the Product ID, Product Description,
--Supplier ID, Supplier Name, Vendor Part #, and Cost.
SELECT p.ProductID, p.ProductDescription, s.SupplierID, s.SupplierName, ps.VendorPartID, ps.ProductCost
FROM PRODUCT_T p, PRODUCT_SUPPLIER_T ps, SUPPLIER_T s
WHERE p.ProductID = ps.ProductID
AND ps.SupplierID = s.SupplierID;
--B. Which sales rep has the highest $ in total sales? Hint: Provide the name and the total sales amount for
--that rep only. Do not include the information for other people, and do not list details about orders.
SELECT e.EmployeeName, MAX(ol.OrderQuantity * p.UnitPrice)
FROM CUSTOMER_T c, ORDER_T o, ORDER_LINE_T ol, PRODUCT_T p, EMPLOYEE_T e
WHERE c.CustomerID = o.CustomerID
AND o.OrderID = o1.OrderID
AND ol.ProductID = p.ProductID
AND c.SalesRepID = e.EmployeeID
GROUP BY e.EmployeeName;
--C. List all of the sales reps and their total commission amounts in order of highest sales commission
--dollar amount first. Hint: Provide all of sales reps