Reference no: EM13899985
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 company's parts inventory.
Case 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.
14. Write the SQL code that will create the table structure for a table named EMP_1. This Table is a subset of the EMPLOYEE table. The basic EMP_1 table structure is summarized in Table Q7.1. Note that the JOB_CODE is the KF to JOB.
