Could someone help me with sql statements for the following questions please? He
ID: 3557666 • 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
g)
--------------------------------------
SELECT Supplier.SupplierId.Supplier.SupplierName,PRODUCT_SUPPLIER_T.VendorPartID SUM(Product.StockQuantity*Product_Supplier.ProductCost) AS Cost
FROM Supplier, Product, Product_Supplier
WHERE Product.ProductID = Product_Supplier.ProductID AND (Product.ProductID = '4905' OR Product.ProductID = 'IMT-SAW')
AND Product_Supplier.SupplierID = Supplier.SupplierID
GROUP BY Supplier.SupplierName
SELECT Supplier.Supplier.SupplierName, SUM(Product.StockQuantity*Product_Supplier.ProductCost) AS Cost
FROM Supplier, Product, Product_Supplier
WHERE Product.ProductID = Product_Supplier.ProductID AND (Product.ProductID = '4905' OR Product.ProductID = 'IMT-SAW')
AND Product_Supplier.SupplierID = Supplier.SupplierID
GROUP BY Supplier.SupplierName
--------------------------------------------
h)
---------------------------------------
SELECT Product.ProductID, Product.ProductDescription, Manufactured.ComponentQuantity, Product_Supplier.ProductCost
FROM Product, Manufactured, Product_Supplier
WHERE Product.ProductID = Product_Supplier.ProductID AND Product.ProductID = Manufactured.CompnentID
AND (Product.ProductID = '4905' or Product.ProductID = 'IMT-SAW')
------------------------------------------------
i)
----------------------------------------------
SELECT TOP 1 SupplierName, sum(ProductCost * StockQuantity) AS TotalPurchased
FROM SUPPLIER S, PRODUCT_SUPPLIER PS, PRODUCT P
WHERE S.SupplierID = PS.SupplierID
AND PS.ProductID = P.ProductID
GROUP BY SupplierName
ORDER BY TotalPurchased DESC
----------------------------------------------------
j)
--------------------------------------------------------
SELECT ProductDescription, Supplier.SupplierName, SUM(Product.UnitPrice * Product.StockQuantity) AS Cost
FROM Product, Supplier, Product_Supplier
WHERE Product.ProductID = Product_Supplier.ProductID AND Product_Supplier.SupplierID = Supplier.SupplierID
GROUP BY ProductDescription, Supplier.SupplierName
ORDER BY Cost DESC
-----------------------------------------------------
k)
--------------------------------------------------
select PRODUCT_T.UnitPrice,PRODUCT_SUPPLIER_T.ProductCost,PRODUCT_SUPPLIER_T.PurchasedQuantity,Supplier.SupplierId,Supplier.SupplierName,SUM(PRODUCT_T.UnitPrice-PRODUCT_SUPPLIER_T.ProductCost) AS PROFIT
FROM Supplier, Product, Product_Supplier
ORDER BY Cost DESC
SELECT Supplier.SupplierId,Supplier.SupplierName, SUM(Product.StockQuantity*Product_Supplier.ProductCost) AS Cost
FROM Supplier, Product, Product_Supplier
WHERE Product.ProductID = Product_Supplier.ProductID AND (Product.ProductID = '4905' OR Product.ProductID = 'IMT-SAW')
AND Product_Supplier.SupplierID = Supplier.SupplierID
GROUP BY Supplier.SupplierName
---------------------------------------------------
l)
--------------------------------------------------
SELECT DISTINCT A1.EmployeeName Name, A2.CompletionDate, A3.CourseDescription
FROM Employee A1, Employee_Course A2, Course A3
WHERE A2.CourseID = A3.CourseID AND A2.EmployeeID = A1.EmployeeID
ORDER BY A1.EmployeeName, A2.CompletionDate