WebCT-chapter10sols

WebCT-chapter10sols - Answers to Chapter 10 Problems In...

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

View Full Document Right Arrow Icon
Answers to Chapter 10 Problems In Microsoft Access, it is easier to use stored queries rather than views. For stored queries, you should save the SELECT statement (the view definition) with the indicated query name. After saving a stored query, you can use it in other queries. You can only use the CREATE VIEW statement in SQL-92 query mode in Access. To use a view in Access, you must execute the CREATE VIEW statement before executing queries that use the view. 1. In SQL-89 mode in Access, you should save the stored query as CHPT10_01. SELECT * FROM Product WHERE SuppNo = 'S3399214' In SQL-92 mode in Access, you can create the view with the following statement. You need to create the view as a data definition query. You should save the view definition before executing the view definition. You must execute the view before it can be used in queries. CREATE VIEW Chpt10_01 AS SELECT * FROM Product WHERE SuppNo = 'S3399214' 2. In SQL-89 query mode in Access, you should save the SELECT statement as “Chpt10_02”: SELECT OrderTbl.*, OrdLine.Qty, ProdName FROM OrderTbl, OrdLine, Product WHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND Product.ProdNo = OrdLine.ProdNo AND OrderTbl.OrdNo = OrdLine.OrdNo In SQL-92 mode in Access, you can create the view with the following statement. You need to create the view as a data definition query. You should save the view definition before executing the view definition. You must execute the view before it can be used in queries.s CREATE VIEW Chpt10_02 AS SELECT OrderTbl.*, OrdLine.Qty, ProdName FROM OrderTbl, OrdLine, Product WHERE OrdDate BETWEEN #1/1/2007# AND #1/31/2007# AND Product.ProdNo = OrdLine.ProdNo AND OrderTbl.OrdNo = OrdLine.OrdNo 3. In SQL-89 query mode in Access, you should save the SELECT statement as “Chpt10_03”:
Background image of page 1

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

View Full DocumentRight Arrow Icon
2/23/2011 Answers to Chapter 10 Problems SELECT Product.ProdNo, ProdName, ProdPrice, ProdQOH, COUNT(*) AS NumOrders FROM Product, OrdLine WHERE Product.ProdNo = OrdLine.ProdNo GROUP BY Product.ProdNo, ProdName, ProdPrice, ProdQOH In SQL-92 mode in Access, you can create the view with the following statement. You need to create the view as a data definition query. You should save the view definition before executing the view definition. You must execute the view before it can be used in queries. CREATE VIEW Chpt10_02 AS SELECT Product.ProdNo, ProdName, ProdPrice, ProdQOH, COUNT(*) AS NumOrders FROM Product, OrdLine WHERE Product.ProdNo = OrdLine.ProdNo GROUP BY Product.ProdNo, ProdName, ProdPrice, ProdQOH 4. SELECT * FROM Chpt10_01 WHERE ProdPrice > 300 In Access SQL-92 query mode, you must execute the view definition statement before you can use a view. If you are using a stored query instead of a view, you do not need to execute the stored query before using it. 5.
Background image of page 2
Image of page 3
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-chapter10sols - Answers to Chapter 10 Problems In...

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

View Full Document Right Arrow Icon
Ask a homework question - tutors are online