WebCT-chapter04sols

# WebCT-chapter04sols - Answers to Chapter 4 Problems Unless...

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

Answers to Chapter 4 Problems Unless noted, all statements execute in both Access (97 to 2003 versions) and Oracle (versions 9i and beyond). 1. SELECT CustNo, CustFirstName, CustLastName, CustBal FROM Customer 2. SELECT CustNo, CustFirstName, CustLastName, CustBal FROM Customer WHERE CustState = 'CO' 3. SELECT * FROM Product WHERE ProdPrice > 50 ORDER BY ProdMfg, ProdName 4. The first solution uses the IN comparison operator and the second solution uses the Boolean OR connector. SELECT OrdNo, OrdDate, OrdName FROM OrderTbl WHERE OrdCity IN ('Denver', 'Englewood') SELECT OrdNo, OrdDate, OrdName FROM OrderTbl WHERE OrdCity = 'Denver' OR OrdCity = 'Englewood' 5. The parentheses are necessary when mixing the logical AND and OR connectors. SELECT CustNo, CustFirstName, CustLastName, CustCity, CustBal FROM Customer WHERE (CustCity = 'Denver' AND CustBal > 150) OR (CustCity = 'Seattle' AND CustBal > 200) 6. The DISTINCT keyword eliminates duplicate rows. SELECT DISTINCT OrdCity, OrdState FROM OrderTbl

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

View Full Document
2/23/2011 Answers to Chapter 4 Problems 7. SELECT * FROM OrderTbl WHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND EmpNo IS NULL 8. SELECT * FROM OrderTbl WHERE OrdDate BETWEEN #2/1/2007# AND #2/28/2007# AND EmpNo IS NOT NULL 9. SELECT * FROM Product WHERE ProdName LIKE '*Ink Jet*' 10. SELECT OrdNo, OrdDate, CustNo FROM OrderTbl WHERE OrdState = 'WA' AND OrdDate > #1/23/2007# 11. SELECT OrdNo, OrdDate, Customer.CustNo, CustFirstName, CustLastName FROM OrderTbl, Customer WHERE OrdState = 'CO' AND OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND OrderTbl.CustNo = Customer.CustNo Access solution with an INNER JOIN operation: SELECT OrdNo, OrdDate, Customer.CustNo, CustFirstName, CustLastName FROM OrderTbl INNER JOIN Customer ON OrderTbl.CustNo = Customer.CustNo WHERE OrdState = 'CO' AND OrdDate BETWEEN #1/1/2007# AND #1/31/2007# 12. SELECT OrdNo, OrdDate, Customer.CustNo, CustFirstName, CustLastName FROM OrderTbl, Customer WHERE OrdState = 'WA' AND OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND OrderTbl.CustNo = Customer.CustNo AND CustState = 'CO' 13. SELECT DISTINCT Customer.CustNo, CustFirstName, CustLastName, CustBal FROM OrderTbl, Customer WHERE CustState = 'WA' AND OrdDate BETWEEN #2/1/2007# AND #2/28/2007# AND OrderTbl.CustNo = Customer.CustNo 14. SELECT OrdNo, OrdDate, Customer.CustNo, CustFirstName, CustLastName, Employee.EmpNo, EmpFirstName, EmpLastName 2
2/23/2011 Answers to Chapter 4 Problems FROM OrderTbl, Customer, Employee WHERE CustState = 'CO' AND OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND OrderTbl.CustNo = Customer.CustNo AND OrderTbl.EmpNo = Employee.EmpNo Access SQL solution with INNER JOIN operations: SELECT OrdNo, OrdDate, Customer.CustNo, CustFirstName, CustLastName, Employee.EmpNo, EmpFirstName, EmpLastName FROM ( OrderTbl INNER JOIN Customer ON OrderTbl.CustNo = Customer.CustNo ) INNER JOIN Employee ON OrderTbl.EmpNo = Employee.EmpNo WHERE CustState = 'CO' AND OrdDate BETWEEN #1/1/2007# AND #1/31/2007# 15 SELECT DISTINCT Employee.EmpNo, EmpFirstName, EmpLastName, EmpPhone

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

View Full Document
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 / 10

WebCT-chapter04sols - Answers to Chapter 4 Problems Unless...

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

View Full Document
Ask a homework question - tutors are online