Question 1:

Exercise 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 Fig. 1.1. (Note that the JOB CODE is the FK to JOB.)

Exercise 2. Having created the table structure in previous exercise, write the SQL code to enter the following rows for the table shown in Fig. 1.2:

Figure 1.1: The EMP_1 Table Structure

Figure 1.2: The Contents of the EMP_1 Table 

Exercise 3. Assuming that 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.

Exercise 4. Write the SQL code to change the job code to 501 for the person whose personnel number is 106. After you have completed the task, examine the results, and then reset the job code to its original value.

Exercise 5. Write the SQL code to delete the row for the person named William Smithfield, who was hired on June 22, 2002 and whose job code classification is 500.

Exercise 6. 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 shown next:

EMP_PCT NUMBER(4, 2)

PROJ_NUM CHAR(3)

Exercise 7.Write the SQL code to enter an EMP_PCT value of 3.85 for the person whose employee number (EMP_NUM) is 103. Next, enter the remaining EMP_PCT values shown in Fig. 1.3:

Figure 1.3: The Contents of the EMP_2 Table

Exercise 8. Using a single command sequence, write the SQL code that will enter the project number (PROJ NUM) = 18 for all employees whose job classification (JOB CODE) is 500.

Exercise 9. Using a single command sequence, write the SQL code that will enter the project number (PROJ NUM) = 25 for all employees whose job classification (JOB CODE) is 502 or higher.

Exercise 10. Write the SQL code that will enter a PROJ NUM of 14 for those employees who were hired before January 1, 1992 and whose job code is at least 501

Question 2:

Exercise 11. You are given the EMPLOYEE, JOB, PROJECT and ASSIGNMENT tables in a database as in Fig. 1.4.

The table schemes are:

EMPLOYEE (EMP NUM, EMP LNAME, EMP FNAME, EMP INITIAL, EMP HIREDATE, JOB CODE, EMP YEARS)

JOB (JOB CODE, JOB DESCRIPTION, JOB CHG HOUR, JOB LAST UPDATE)

PROJECT (PROJ NUM, PROJ NAME, PROJ VALUE, PROJ BALANCE, EMP NUM)

ASSIGNMENT (ASSIGN NUM, ASSIGN DATE, PROJ NUM, EMP NUM,

ASSIGN JOB, ASSIGN CHG HR, ASSIGN HOURS, ASSIGN CHARGE)

Using the EMPLOYEE, JOB, and PROJECT tables in the database (see above), write the SQL code that will produce the results shown in Fig. 1.5.

1.4:The Database and The Tables

Exercise 12. 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 the previous exercise.

Exercise 13.Write the SQL code to find the average project value in the table PROJECT.

Figure 1.5: The Expected Query Results

Exercise 14.Write the SQL code that will produce a listing for the data in the PROJECT table in ascending order by the project value.

Exercise 15. Write the SQL code that will list only the different assignment jobs found in the ASSIGNMENT table.

Exercise 16.Write the SQL code to calculate the ASSIGN CHARGE values in the ASSIGNMENT table. Note that ASSIGN CHARGE is a derived attribute that is calculated by multiplying the ASSIGN CHG HR and the ASSIGN HOURS.

Exercise 17. Using the data in the ASSIGNMENT table, write the SQL code that will, for each employee, yield the total number of hours worked and the total charges stemming from those hours worked. The results of running that query are shown in Fig. 1.6.

Figure 1.6: Total Hours and Charges by Employee

Exercise 18.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 Fig. 1.7.

Figure 1.7: Total Hours and Charges by Project