WebCT-chapter09sols

# WebCT-chapter09sols - Answers to Chapter 9 Problems 1...

This preview shows pages 1–3. Sign up to view the full content.

Answers to Chapter 9 Problems 1. SELECT CustNo, CustFirstName, CustLastName, CustCity FROM Customer WHERE CustBal > 150 AND CustNo IN ( SELECT CustNo FROM OrderTbl WHERE OrdDate BETWEEN #2/1/2007# AND #2/28/2007# ) 2. The nested query references the outer query in the condition involving the CustNo columns. SELECT CustNo, CustFirstName, CustLastName, CustCity FROM Customer WHERE CustBal > 150 AND EXISTS ( SELECT CustNo FROM OrderTbl WHERE OrdDate BETWEEN #2/1/2007# AND #2/28/2007# AND Customer.CustNo = OrderTbl.CustNo ) 3. Type I nested queries work for this problem because the result table contains columns from only one table. SELECT ProdNo, ProdName, ProdPrice FROM Product WHERE ProdPrice > 150 AND ProdNo IN ( SELECT ProdNo FROM OrdLine WHERE OrdNo IN ( SELECT OrdNo FROM OrderTbl WHERE OrdDate = #1/23/2007# ) ) 4. Type I nested queries work for this problem because the result table contains columns from only one table. SELECT ProdNo, ProdName, ProdPrice FROM Product WHERE ProdPrice > 150 AND ProdNo IN ( SELECT ProdNo FROM OrdLine WHERE OrdNo IN ( SELECT OrderTbl.OrdNo FROM OrderTbl, Customer WHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND OrderTbl.CustNo = Customer.CustNo AND CustBal > 400 ) )

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
Answers to Chapter 9 Problems 5. SELECT OrdNo, OrdDate, Employee.EmpNo, EmpFirstName, EmpLastName FROM OrderTbl LEFT JOIN Employee ON OrderTbl.EmpNo = Employee.EmpNo WHERE OrdDate = #1/23/2007# 6. SELECT OrdNo, OrdDate, Employee.EmpNo, EmpFirstName, EmpLastName, Customer.CustNo, CustFirstName, CustLastName FROM ( OrderTbl LEFT JOIN Employee ON OrderTbl.EmpNo = Employee.EmpNo ) INNER JOIN Customer ON Customer.CustNo = OrderTbl.CustNo WHERE OrdDate = #1/23/2007# 7. This problem requires a full outer join as shown in the SQL:2003 solution. Since Access does not support the full outer join, two one-sided outer joins and a union are required. In the Access solution, the columns in the FROM clause must be qualified with the table name. Otherwise, Access will not execute the statement. Access SQL: SELECT Customer.*, Employee.* FROM Customer LEFT JOIN Employee ON Customer.CustFirstName = Employee.EmpFirstName AND Customer.CustLastName = Employee.EmpLastName UNION SELECT Customer.*, Employee.* FROM Customer RIGHT JOIN Employee ON Customer.CustFirstName = Employee.EmpFirstName AND Customer.CustLastName = Employee.EmpLastName 8. In the Access solution, the LEFT JOIN must be nested inside the INNER JOIN operations. Access does not support inner joins nested inside one-sided outer joins (LEFT or RIGHT) joins. Access SQL: SELECT OrderTbl.OrdNo, OrdDate, Employee.EmpNo, EmpFirstName, EmpLastName, Customer.CustNo, CustFirstName, CustLastName, OrdLine.Qty, Product.ProdNo, ProdName FROM ( ( ( OrderTbl LEFT JOIN Employee ON OrderTbl.EmpNo = Employee.EmpNo) INNER JOIN Customer ON Customer.CustNo = OrderTbl.CustNo ) INNER JOIN OrdLine ON OrderTbl.OrdNo = OrdLine.OrdNo ) INNER JOIN Product ON OrdLine.ProdNo = Product.ProdNo WHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007#
This is the end of the preview. Sign up to access the rest of the document.

## This note was uploaded on 02/21/2011 for the course MIS 310 taught by Professor Staff during the Spring '08 term at George Mason.

### Page1 / 11

WebCT-chapter09sols - Answers to Chapter 9 Problems 1...

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online