Case Problem

Bootup Computers assembles and sells several different computer system packages. Bootup purchases a variety of parts frofm 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 company's parts inventory. For this assignment, you will download an SQL script file (attached) and use it to create the necessary database tables and populate them with data. Copy the SQL commands from the attached file and paste them into SQL*Plus. Each command should be entered automatically. Use the DESCRIBE command to view the definition of the table (SYSTEM, PART, and COMPONENT). You will need to know the table names, field names, and field datatypes for the case problems below. ## Deliverables Case Problems: Use the SQL SELECT commands to complete each of the following queries. 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 value (PART_PRICE*PART_ONHAND) of all of the parts in the PART table. Note: Use a function to return a single total. 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 PART table where PART_TYPE is CPU and PART_PRICE is greater than $100; 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 parttype 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. Display the SYSTEM_DESC of all systems for which any necessary components have a zero quantity on hand. Note: Use a subquery. When done, stop the SPOOL file using the SPOOL OFF command. Put in it Word and zip it up! ## Platform Oracle8i Personal