Could someone help me with more sql statements for the following questions pleas
ID: 3557667 • Letter: C
Question
Could someone help me with more 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:
G. List the suppliers that provide hack saws, and sort the list with the lowest prices first. Hint: Include
the Supplier IDs, Supplier Names, Vender Part #s, and cost.
H. Show the components and cost of materials for fabricating a hack saw.
I. Which customer purchased the overall largest dollar amount? Hint: One name and the total dollar
amount only
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