Select distinct prodname prodprice from ordline inner

This preview shows page 7 - 10 out of 10 pages.

SELECT DISTINCT ProdName, ProdPriceFROM ( ( OrdLine INNER JOIN OrderTbl ON OrdLine.OrdNo = OrderTbl.OrdNo ) INNER JOIN Customer ON Customer.CustNo = OrderTbl.CustNo )INNER JOIN Product ON OrdLine.ProdNo = Product.ProdNoWHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007#AND CustFirstName = 'Beth' AND CustLastName = 'Taylor'35.SELECT Customer.CustNo, CustLastName, COUNT(*) AS NumOrdersFROM OrderTbl, CustomerWHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007#AND Customer.CustNo = OrderTbl.CustNoGROUP BY Customer.CustNo, CustLastName36.Access SQL SolutionSELECT Customer.CustNo, CustLastName, COUNT(*) AS NumOrdersFROM OrderTbl, Customer, OrdLine, ProductWHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007#AND OrderTbl.OrdNo = OrdLine.OrdNoAND OrdLine.ProdNo = Product.ProdNoAND Customer.CustNo = OrderTbl.CustNoAND ProdMfg = 'Connex'GROUP BY Customer.CustNo, CustLastName37.Access SQL SolutionSELECT Employee.EmpNo, EmpLastName, COUNT(*) AS NumOrdersFROM OrderTbl, EmployeeWHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007#AND Employee.EmpNo = Employee.EmpNoAND EmpCommRate < 0.04GROUP BY Employee.EmpNo, EmpLastName7
2/23/2011Answers to Chapter 4 Problems38.SELECT Employee.EmpNo, EmpLastName, SUM(EmpCommRate * Qty * ProdPrice) AS TotCommEarnedFROM OrderTbl, Employee, OrdLine, ProductWHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007#AND Employee.EmpNo = Employee.EmpNoAND OrderTbl.OrdNo = OrdLine.OrdNoAND OrdLine.ProdNo = Product.ProdNoAND EmpCommRate > 0.03GROUP BY Employee.EmpNo, EmpLastName39.SELECT MonthName(Month(OrdDate)), SUM(Qty * ProdPrice) AS TotOrdAmountFROM OrderTbl, OrdLine, ProductWHERE OrdDate BETWEEN #1/1/2007# AND #12/31/2007#AND OrderTbl.OrdNo = OrdLine.OrdNoAND OrdLine.ProdNo = Product.ProdNoGROUP BY Month(OrdDate)8
2/23/2011Answers to Chapter 4 Problems40.SELECT MonthName(Month(OrdDate)), Employee.EmpNo, EmpLastName, SUM(EmpCommRate * Qty * ProdPrice) AS TotCommEarnedFROM OrderTbl, Employee, OrdLine, ProductWHERE OrdDate BETWEEN #1/1/2007# AND #12/31/2007#AND Employee.EmpNo = Employee.EmpNoAND OrderTbl.OrdNo = OrdLine.OrdNoAND OrdLine.ProdNo = Product.ProdNoGROUP BY Month(OrdDate), Employee.EmpNo, EmpLastNameORDER BY Month(OrdDate), SUM(EmpCommRate * Qty * ProdPrice)Part2: Manipulation Problems1.INSERT INTO Customer (CustNo, CustFirstName, CustLastName, CustStreet, CustCity, CustState,CustZip, CustBal)VALUES ('C9999999', 'Michael', 'Mannino', '123 Any Street', 'MyTown', 'CO','80217-0211', 500)2.INSERT INTO Employee ( EmpNo, EmpFirstName, EmpLastName, EmpPhone, EmpCommRate, EmpEmail)VALUES ('E9999999', 'Mary', 'Mannino', '(720)543-1234', 0.04, '[email protected]')3.Access SQL:INSERT INTO OrderTbl ( OrdNo, OrdDate, CustNo, EmpNo, OrdName, OrdStreet, OrdCity, OrdState, OrdZip)VALUES ('O9999999', #1/27/2007#, 'C9999999', 'E9999999', 'Mary Mannino', '123 Any Street', 'MyTown', 'CO', '80217-1121')9
2/23/2011Answers to Chapter 4 Problems4.INSERT INTO OrdLine (OrdNo, ProdNo, Qty)VALUES ('O9999999', 'P6677900', 2)INSERT INTO OrdLine (OrdNo, ProdNo, Qty)VALUES ('O9999999', 'P1556678', 2)5.

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture