ISY34AB Practical Exercise

ISY34AB Practical Exercise 1- 13 April 2016 The Ch05_ConstructCo database stores data for a consulting company that tracks all charges to projects. The charges are based on the hours each employee works on each project. The structure and contents of the Ch05_ConstructCo database are shown in Figure P5.1. Note that the ASSIGNMENT table in Figure P5.1 stores the JOB_CHG_HOUR values as an attribute (ASSIGN_CHG_HR) to maintain historical accuracy of the data. The JOB_CHG_HOUR values are likely to change over time. In fact, a JOB_CHG_HOUR change will be reflected in the ASSIGNMENT table. And, naturally, the employee primary job assignment might change, so the ASSIGN_JOB is also stored. Because those attributes are required to maintain the historical accuracy of the data, they are not redundant. Given the structure and contents of the Ch05_ConstructCo database shown in Figure P5.1, use SQL commands to answer Problems 1–25. 1. 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 the table below. (Note that the JOB_CODE is the FK to JOB.) 2. Having created the table structure in Problem 1, write the SQL code to enter the first two rows for the table shown in Figure P5.2. 3. Assuming the data shown in the EMP_1 table have been entered, write the SQL code that will list all attributes for a job code of 502. 4. Write the SQL code that will save the changes made to the EMP_1 table. 5. Write the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is 105. After you have completed the task, examine the results, and then reset the job code to its original value. 6. Write the SQL code to delete the row for the person named William Smithfield, who was hired on June 22, 2004, and whose job code classification is 500. (Hint: Use logical operators to include all of the information given in this problem.) 7. Write the SQL code that will restore the data to its original status; that is, the table should contain the data that existed before you made the changes in Problems 5 and 6. 8. Write the SQL code to create a copy of EMP_1, naming the copy EMP_2. Then write the SQL code that will add the attributes EMP_PCT and PROJ_NUM to its structure. The EMP_PCT is the bonus percentage to be paid to each employee. The new attribute characteristics are: EMP_PCTNUMBER(4,2) PROJ_NUMCHAR(3) (Note: If your SQL implementation allows it, you may use DECIMAL(4,2) rather than NUMBER(4,2).) 9. Write the SQL code to change the EMP_PCT value to 3.85 for the person whose employee number (EMP_NUM) is 103. Next, write the SQL command sequences to change the EMP_PCT values as shown in Figure P5.9. Figure P5.9 The contents of the EMP_2 table 10. Using a single command sequence, write the SQL code that will change the project number (PROJ_NUM) to 18 for all employees whose job classification (JOB_CODE) is 500. 11. Using a single command sequence, write the SQL code that will change the project number (PROJ_NUM) to 25 for all employees whose job classification (JOB_CODE) is 502 or higher. When you finish Problems 10 and 11, the EMP_2 table will contain the data shown in Figure P5.11. (You may assume that the table has been saved again at this point.) Figure P5.11 The EMP_2 table contents after the modification 12. Write the SQL code that will change the PROJ_NUM to 14 for those employees who were hired before January 1, 1994 and whose job code is at least 501. (You may assume that the table will be restored to its condition preceding this question.) 13. Write the two SQL command sequences required to: There are many ways to accomplish both tasks. We are illustrating the shortest way to do the job next. a. Create a temporary table named TEMP_1 whose structure is composed of the EMP_2 attributes EMP_NUM and EMP_PCT. b. Copy the matching EMP_2 values into the TEMP_1 table. 14. Write the SQL command that will delete the newly created TEMP_1 table from the database. 15. Write the SQL code required to list all employees whose last names start with Smith. In other words, the rows for both Smith and Smithfield should be included in the listing. Assume case sensitivity. 16. Using the EMPLOYEE, JOB, and PROJECT tables in the Ch05_ConstructCo database (see Figure P5.1), write the SQL code that will produce the results shown in Figure P5.16. Figure P5.16 The query results for Problem 16 17. Write the SQL code that will produce a virtual table named REP_1. The virtual table should contain the same information that was shown in Problem 16. 18. Write the SQL code to find the average bonus percentage in the EMP_2 table you created in Problem 8. 19. Write the SQL code that will produce a listing for the data in the EMP_2 table in ascending order by the bonus percentage. 20. Write the SQL code that will list only the distinct project numbers found in the EMP_2 table. 21. Write the SQL code to calculate the ASSIGN_CHARGE values in the ASSIGNMENT table in the Ch05_ConstructCo database. (See Figure P5.1.) Note that ASSIGN_CHARGE is a derived attribute that is calculated by multiplying ASSIGN_CHG_HR by ASSIGN_HOURS. 22. Using the data in the ASSIGNMENT table, write the SQL code that will yield the total number of hours worked for each employee and the total charges stemming from those hours worked. The results of running that query are shown in Figure P5.22. Figure P5.22 Total hours and charges by employee 23. Write a query to produce the total number of hours and charges for each of the projects represented in the ASSIGNMENT table. The output is shown in Figure P5.23. Figure P5.23 Total hour and charges by project 24. Write the SQL code to generate the total hours worked and the total charges made by all employees. The results are shown in Figure P5.24. (Hint: This is a nested query) Figure P5.24 Total hours and charges, all employees 25. Write the SQL code to generate the total hours worked and the total charges made to all projects. The results should be the same as those shown in Figure P5.24. (Hint: This is a nested query. If you use Microsoft Access, you can generate the result by using the query output shown in Figure P5.23 as the basis for this query.) 3