Chapter 4 Database Management

Document Sample
Chapter 4 Database Management Powered By Docstoc
					Concepts of Database Management, Sixth Edition                                Solutions for chapters 3&4



Chapter 3 Solutions

Answers to Premiere Products Exercises

4.
SELECT CustomerNum, CustomerName
FROM Customer
WHERE RepNum='35'
AND CreditLimit=10000;
6.
????
8.
????
Using Access:
SELECT COUNT(*)
FROM Orders
WHERE OrderDate=#10/20/2010#;
14.
SELECT PartNum, Description, OnHand, Warehouse, Price
INTO SportingGoods
FROM Part
WHERE Class='SG';
15.
????



Answers to Henry Books Case
Note: See the Instructor’s Resource Kit on CD-ROM for a copy of the Henry Books database that contains the solutions
to these exercises. It also includes the SQL script files necessary to create the Henry Books queries in Oracle.
5.
????
7.
????

12.
SELECT BookCode, Title
FROM Book
WHERE Type IN ('SFI', 'HOR', 'ART');
19.
????
21.
DELETE
FROM Fiction
WHERE PublisherCode=′VB′;
Concepts of Database Management, Sixth Edition                                    Solutions for chapters 3&4



Chapter 4 Solutions

Answers to Premiere Products Exercises
The following answers indicate how to perform the specified task in SQL and in Microsoft Access. The process for
other database management systems may be different, although it should be similar. See the Instructor’s Resource Kit
on CD-ROM for a copy of the Premiere Products database that contains the solutions to these exercises. It also includes
the SQL script files necessary to perform these tasks in Oracle.

2.      STANDARD SQL:
        CREATE VIEW PartOrder AS
        SELECT Part.PartNum, Description, Price, OrderLine.OrderNum, OrderDate,
        NumOrdered, QuotedPrice
        FROM Part, OrderLine, Orders
        WHERE Part.PartNum = OrderLine.PartNum
        AND Orders.OrderNum = OrderLine.OrderNum;
        SELECT *
        FROM PartOrder;
        In Access:
        Create a query containing the PartNum, Description, and Price fields from the Part table as well as the
        OrderNum, OrderDate, NumOrdered, and QuotedPrice fields from the OrderLine table. Save the query as
        PartOrder. Run the saved query.
4.      ????
7.      ????
10.     STANDARD SQL:
        DROP TABLE Part;
        In Access:
        Right-click the Part table, and then click Delete on the shortcut menu.



Answers to Henry Books Case
The following answers indicate how to perform the specified task in SQL and in Microsoft Access. The process for
other database management systems may be different, although it should be similar. See the Instructor’s Resource Kit
on CD-ROM for a copy of the Henry Books database that contains the solutions to these exercises. It also includes the
SQL script files necessary to perform these tasks in Oracle.

2.      ????


4.      SQL:
        a.
        CREATE INDEX BookIndex1 ON Publisher (PublisherName);
        b.
        CREATE INDEX BookIndex2 ON Book (Type);
        c.
        CREATE INDEX BookIndex3 ON Book (Type, Price DESC);
        Access:
Concepts of Database Management, Sixth Edition                                  Solutions for chapters 3&4


        a.       While viewing the Publisher table in Design view, select Yes (Duplicates OK) as the index option for
        the PublisherName field.
        b.       While viewing the Book table in Design view, select Yes (Duplicates OK) as the index option for the
        Type field.
        c.       While viewing the Book table in Design view, click the Indexes button, enter BookIndex3 as the
        name of the index, and then select the Type and Price fields as the index keys. Select Descending as the order
        for the Price field.
10.     ????

				
DOCUMENT INFO
Description: Chapter 4 Database Management document sample