MS Access Exercise

Directions: Read the case description below. Download the Assignment6.mdb Access database file and review the tables it contains. Write SQL SELECT statements for each of the problems below and execute them in the Access database. Each SELECT statement and its resulting output should be copied into your Word document .

CASE DESCRIPTION

Bootup Computers assembles and sells several different computer-system packages. Bootup purchases a variety of parts from different manufacturers and then assembles them into complete computer systems. Due to the rapid growth of its business, Bootup is concerned about the rising costs of its parts inventory. Bootup's owners have asked you to create a simple inventory database to track the companys parts inventory.

Problems

1. display all of the data in the SYSTEM table.

2. display all of the data in the PART table.

3. display all of the data in the COMPONENT table.

4. display the PART_DESC and PART_PRICE for all of the parts in the PART table.

5. display the PART_ID and total value of each part (PART_PRICE * PART_ONHAND) in the PART table. NOTE: Use the AS keyword to name the expression VALUE.

6. display the total combined value(PART_PRICE * PART_ONHAND) of all of the parts in the PART table. NOTE: The query should return a single value.

7. display the PART_ID and QTY_USED from the COMPONENT table for all rows where SYSTEM_ID is STU3419

8. display the SYSTEM_DESC and SYSTEM_PRICE for all of the systems in the SYSTEM table that have a price less than $1,000

9. display the SYSTEM_ID, SYSTEM_DESC, and SYSTEM_PRICE for all of the systems in the SYSTEM table that have a price between $1,000 and $2,000.

10. display the PART_ID, PART_DESC and PART_PRICE for all of the parts in the PARTS table where PART_TYPE is CPU and PART_PRICE is greater than $75 and order the output in ascending order by PART_PRICE.

11. display the PART_TYPE and total number of parts on hand for each part type in the PART table. NOTE: Each part type should appear on a separate line.

12. display the PART_ID, PART_DESC and QTY_USED from the COMPONENT and PART tables for all of the parts used for SYSTEM_ID = 'STU3419'.

13. write a subquery to display the SYSTEM_DESC of all systems for which any necessary components have a zero quantity on hand.