Exercises

  1. Using the information for the Chapter 9 exercise, implement the schema using Transact SQL (show SQL statements for each table). Implement the constraints as well.
  2. Create the table shown here in SQL Server and show the statements you used.
    Table: Employee
    ATTRIBUTE (FIELD) NAME DATA DECLARATION
    EMP_NUM CHAR(3)
    EMP_LNAME VARCHAR(15)
    EMP_FNAME VARCHAR(15)
    EMP_INITIAL CHAR(1)
    EMP_HIREDATE DATE
    JOB_CODE CHAR(3)
  3. Having created the table structure in question 2, write the SQL code to enter the rows for the table shown in Figure 15.1.

    Figure 15.2. Employee table with data for questions 4-10, by A. Watt.

    Use Figure 15.2 to answer questions 4 to 10.

  4. Write the SQL code to change the job code to 501 for the person whose personnel number is 107. After you have completed the task, examine the results, and then reset the job code to its original value.
  5. Assuming that the data shown in the Employee table have been entered, write the SQL code that lists all attributes for a job code of 502.
  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 the information given in this problem).
  7. Add the attributes EMP_PCT and PROJ_NUM to the Employee table. The EMP_PCT is the bonus percentage to be paid to each employee.
  8. Using a single command, write the SQL code that will enter the project number (PROJ_NUM) = 18 for all employees whose job classification (JOB_CODE) is 500.
  9. Using a single command, 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.
  10. 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 original condition preceding this question).