Problem 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 P8.2. Each row should be inserted individually, without using a subquery. Insert the rows in the order that they are listed in the figure.

Problem 3

Using the EMPLOYEE table that already exists, use a subquery to insert the remaining rows from the EMPLOYEE table into the EMP_1 table. Remember, your subquery should only retrieve the columns needed for the EMP_1 table and only the employees shown in the figure.

Problem 4

Write the SQL code that will save the changes made to the EMP_1 table (if supported by your DBMS).

Problem 5

Write the SQL code to change the job code to 501 for the person whose employee number (EMP_NUM) is 107.

Problem 6

Write the SQL code to delete the row for William Smithfield, who was hired on June 22, 2004, and whose job code is 500.

Hint: Use logical operators to include all of the information given in this problem. Remember, if you are using MySQL, you will have to first disable safe mode.

Problem 7

Write the SQL code to create a copy of EMP_1, including all of its data, and naming the copy EMP_2.

Problem 8

Using the EMP_2 table, write the SQL code that will add the attributes EMP_PCT and PROJ_NUM to EMP_2. The EMP_PCT is the bonus percentage to be paid to each employee. The new attribute characteristics are:

EMP_PCT NUMBER(4,2) PROJ_NUM CHAR(3)

Note: If your SQL implementation requires it, you may use DECIMAL(4,2) or NUMERIC(4,2) rather than NUMBER(4,2).

Problem 9

Using the EMP_2 table, write the SQL code to change the EMP_PCT value to 3.85 for the person whose employee number (EMP_NUM) is 103.

Problem 10

Using the EMP_2 table, write a single SQL command to change the EMP_PCT value to 5.00 for the people with employee numbers 101, 105, and 107.

Problem 11

Using the EMP_2 table, write a single SQL command to change the EMP_PCT value to 10.00 for all employees who do not currently have a value for EMP_PCT.

Problem 12

Using the EMP_2 table, write the SQL command to add .15 to the EMP_PCT of the employee whose name is Maria D. Alonzo. (Use the employee name in your command to determine the correct employee.)

Problem 13

Using a single command sequence with the EMP_2 table, write the SQL code that will change the project number (PROJ_NUM) to 18 for all employees whose job classification (JOB_CODE) is 500.

Problem 14

Using a single command sequence with the EMP_2 table, 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.

Problem 15

Write the SQL code that will change the PROJ_NUM to 14 for employees who were hired before January 1, 1994, and whose job code is at least 501. When you finish Problems 715, the EMP_2 table will contain the data shown in Figure P8.15.