Thomas Small MIS409 Spring 2012 DB Project Queries

Thomas Small | MIS409 | Spring 2012 | DB Project | Queries

1. Find the name of the department manager(s) responsible for all the products ordered most recently by the company 'Crucial.'

SELECT EmpFname, EmpMname, EmpLname

FROM Employees E, Departments D, Products P, Customers C, OrderDetails OD, Orders O, (SELECT CustID, MAX(OrderDate) as LatestOrder

FROM Orders

GROUP BY CustID) vw_Ord

WHERE O.CustID = vw_Ord.CustID

AND C.CustCompany = 'Crucial'

AND C.CustID = O.CustID

AND OD.OrderID = O.OrderID

AND P.ProdID = OD.ProdID

AND D.DeptID = P.ControllingDept

AND E.EmpID = D.DeptMgr;

2. Find the average cost of all products, and list those that cost more than average along with their prices.

SELECT ProdName, ProdCost, AverageProdCost

FROM Products, (SELECT AVG(ProdCost) AS AverageProdCost

FROM Products)

WHERE ProdCost > AverageProdCost;

3. Find all department managers who manage more than one department, and the names of the departments they manage.

SELECT EmpFname, EmpMname, EmpLname, DeptName

FROM Employees E, Departments D, (SELECT DeptMgr, COUNT(DeptID)

FROM Departments

GROUP BY DeptMgr

HAVING COUNT(DeptID) > 1) vw_Dep

WHERE E.EmpID = vw_Dep.DeptMgr

AND E.EmpID = D.DeptMgr;

4. List all the companies who have ordered more than $1500 of products in a single order, and the amounts of those orders.

SELECT CustCompany, OrderAmount

FROM Customers C, Orders O,

(SELECT OrderID, to_char(SUM(SubTotal), '$9,999,999.99') as OrderAmount

FROM OrderDetails

GROUP BY OrderID

HAVING SUM(SubTotal) > 1500) vw_OD

WHERE O.OrderID = vw_OD.OrderID

AND C.CustID = O.CustID;

5. List the amount of sales per product of orders made in 2012.

SELECT ProdName, TotalSales

FROM Products P,

(SELECT ProdID, to_char(SUM(SubTotal), '$9,999,999.99') as TotalSales

FROM Orders O, OrderDetails OD

WHERE O.OrderID = OD.OrderID

AND O.OrderDate >= TO_DATE('01/01/2012', 'mm/dd/yyyy')

GROUP BY ProdID) vw_P

WHERE P.ProdID = vw_P.ProdID

ORDER BY P.ProdID;

6. List the material name and the number of additional units of materials needed to make 100 Product Sixes, and who to contact to get the materials.

SELECT MatName, ((PM.QtyMatNeeded * 100) - M.MatOnHand) AS AdditionalMats,

SupName, SupPhone

FROM Products P, ProdMats PM, Materials M, Suppliers S

WHERE P.ProdName = 'Product Six'

AND PM.ProdID = P.ProdID

AND M.MatID = PM.MatID

AND S.SupID = M.MatSupplier;

7. Find the names of all department managers, the names of the departments they manage, their pay rates, and the combined average pay rate of all the department managers.

SELECT UNIQUE EmpFname, EmpMname, EmpLname, DeptName, EmpPayRate, AveragePay

FROM Employees E, EmpDeptPay EDP, Departments D,

(SELECT AVG(EmpPayRate) AS AveragePay

FROM EmpDeptPay, Departments

WHERE EmpID = DeptMgr)

WHERE E.EmpID = D.DeptMgr

AND E.EmpID = EDP.EmpID

ORDER BY EmpPayRate;

8. Find any companies who have not placed an order in 2012.

SELECT CustCompany

FROM Customers

WHERE CustID NOT IN (SELECT CustID

FROM Orders

WHERE OrderDate >= TO_DATE('01/01/2012', 'mm/dd/yyyy'));

9. Find any orders that had products shipped by both FedEx and UPS, and the companies that placed the orders.

SELECT OrderID, CustCompany

FROM Customers C, Orders O

WHERE NOT EXISTS ((SELECT CarrierID

FROM ShippingCarriers

WHERE CarrierName = 'FedEx'

OR CarrierName = 'UPS')

MINUS

(SELECT CarrierID

FROM OrderDetails OD, ShippingDetails SD

WHERE OD.ShipID = SD.ShipID

AND O.OrderID = OD.OrderID))

AND C.CustID = O.CustID;

10. Find any orders that included all the products from the departments Zed Moe Grey managed, as well as the companies that placed the orders.

SELECT OrderID, CustCompany

FROM Customers C, Orders O

WHERE NOT EXISTS ((SELECT ProdID

FROM Products, Departments, Employees

WHERE EmpFname = 'Zed'

AND EmpMname = 'Moe'

AND EmpLname = 'Grey'

AND DeptMgr = EmpID

AND ControllingDept = DeptID)

MINUS

(SELECT ProdID

FROM OrderDetails OD

WHERE OD.OrderID = O.OrderID))

AND C.CustID = O.CustID;