共计 4291 个字符，预计需要花费 11 分钟才能阅读完成。
SELECTE.EmployeeID, E.EmployeeName, M.EmployeeName AS Manager FROM Employee_T E, Employee_T M WHERE E.EmployeeSupervisor = M.EmployeeID; [self join, find out all the managing relationships,]
SELECTCOUNT(OrderID) as N_APP FROM OrderLine_T WHERE ProductID = 4 AND OrderID IN (SELECTOrderID FROM OrderLine_T WHERE ProductID = 7); [Frequent Item-Pair Calculate the number of times the pair of products with ProductID=4 AND ProductID=7 appear together on the same order.]
SELECTCOUNT(*) AS N_APP FROM OrderLine_T L1, OrderLine_T L2 WHERE L1.OrderID = L2.OrderID AND L1.ProductID = 4 AND L2.ProductID = 7; [same as previous one]
SELECTL1.ProductID, L2.ProductID, COUNT() n_app FROM OrderLine_T L1, OrderLine_T L2 WHERE L1.Orderid = L2.Orderid AND L1.ProductID < L2.ProductID GROUP BY L1.ProductID, L2.ProductID HAVING COUNT() >=2 ORDER BYn_app DESC; [Frequent Item-Pair; find ALL pairs of products that appear together on the same order more that 2 times?]
SELECTC1.CustomerName, C2.CustomerName, C1.CustomerState FROM Customer_T C1, Customer_T C2 WHERE C1.CustomerID < C2.CustomerID AND C1.CustomerState = C2.CustomerState ORDER BY C1.CustomerState; [find all pairs of customers in the same state]
SELECTCustomerName, COUNT(DISTINCT ProductID) AS Number_Of_Products FROM Customer_T, Order_T, OrderLine_T WHERE Customer_T.CustomerID = Order_T.CustomerID AND Order_T.OrderID = OrderLine_T.OrderID GROUP BY CustomerName HAVING COUNT(DISTINCT ProductID) > 1 ORDER BY Number_Of_Products DESC, CustomerName; [Find the names of all of the customers that have purchased more than one product. Also, provide the number of unique products that such customers have purchased]
SELECT CustomerName, ProductDescription, ProductFinish FROM Customer_T, Order_T, OrderLine_T, Product_T WHERE Customer_T.CustomerID = Order_T.CustomerID AND Order_T.OrderID = OrderLine_T.OrderID AND OrderLine_T.ProductID = Product_T.ProductID ORDER BY CustomerName; [Find the names of all of the customers along with the description AND finish of the products that they have purchased.]
SELECTDISTINCT S.StudentID, StudentName FROM Student S, Registration R, Course C WHERE S.StudnetID = R.StudentID AND R.CourseID = C.CourseID AND Building =‘PBB’[Find ID AND names of students who take courses in the Pappajohn building (PBB)]
SELECT Dept, COUNT(DISTINCT Student.StudentID) FROM Student S, Registration R, Course C WHERE S.StudentID = R.StudentID AND R.CourseID = Course.CourseID AND Building =‘PBB’GROUP BY Dept; [For all students that take classes in the PBB, find the number of students in each department.]
SELECTCustomerName FROM Customer_T WHERE CustomerID NOT IN (SELECTDISTINCT CustomerID FROM Order_T);[Show the names of the customers who have NOT placed any orders.]
SELECT CustomerName FROM Customer_T WHERE CustomerID NOT IN (SELECT DISTINCT CustomerID FROM Order_T O, OrderLine_T L, Product_T P WHERE O.OrderID = L.OrderID AND L.ProductID = P.ProductID AND ProductDescription = ‘Computer Desk’); [Show the names of the customers who have NOT placed orders for“Computer Desk”.]
SELECT ProductDescription, ProductStANDardPrice FROM Product_T WHERE ProductStANDardPrice >= ALL (SELECTProductStANDardPrice FROM Product_T); [Find out the most expensive product AND its price.]
SELECT DISTINCT OrderID FROM OrderLine_T WHERE EXISTS (SELECT* FROM Product_T WHERE ProductID = OrderLine_T.ProductID AND ProductFinish = ‘Natural Ash’); [Show the order IDs for all orders that included furniture finished in natural ash? When use in , there is a attribute before in to compare, EXISTS just check whether it’s empty or not]
SELECT ProductDescription, ProductStANDardPrice, AvgPrice FROM Product_T, (SELECT AVG(ProductStANDardPrice) AS AvgPrice FROM Product_T) WHERE ProductStandardPrice > AvgPrice; [Show all the products that have a standard price higher than the average standard price as well as the average standard price.]
SELECT FROM (SELECT FROM Product_T ORDER BY ProductStANDardPrice DESC), (SELECTCOUNT() as Total FROM Product_T) WHERE ROWNUM <= 0.3 Total; [Find the top 30% products in price.]
SELECT* FROM Product_T WHERE ProductLineID = (SELECT ProductLineID FROM Product_T WHERE ProductID = 2) AND ProductFinish = (SELECT ProductFinish FROM Product_T WHERE ProductID = 2); [Find the product(s) that has the same ProductLineID AND ProductFinish as the product whose ProductID is 2.]
SELECT Customer_T.CustomerID, CustomerName, COUNT(OrderID) as Total_Orders FROM Customer_T, Order_T WHERE Customer_T.CustomerID = Order_T.CustomerID GROUP BY Customer_T.CustomerID, CustomerName UNION SELECT CustomerID, CustomerName, 0 FROM Customer_T WHERE NOT EXISTS (SELECT * FROM Order_T WHERE Order_T.CustomerID = Customer_T.CustomerID); [For ALL customers, display the customer ID/name and the number of orders (label this number Total_Orders) that the customer placed. For those customers who do not have any orders, include them in the display once, with a 0 value for Total_Orders.]
