Problem: The Ch07_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 Ch07_ConstructCo database are shown in Figure P7.1.
Figure P7.1 Structure and contents of the Ch07_ConstructCo database
Note that the ASSIGNMENT table in Figure P7.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 Ch07_ConstructCo database shown in Figure P7.1, use SQL commands to answer Problems 1-25. Save SQL commands and snapshot of created tables into a file named ???Assign3.doc.
Write the SQL code that will create the table structure of JOB table (Determine the data type).
Having created the table structure for the JOB table, write SQL code to enter the first 6 rows for the table shown in Figure. P7.1. Note that in mySQL, date is formatted as ‘yyyy-mm-dd'. Use this format to enter the values for JOB_LAST_UPDATE column.
Write the SQL code that will create the table structure for EMPLOYEE table (Note that the JOB_CODE is the FK to JOB.)
Having created the table structure for the EMPLOYEE table, write SQL code to enter the first 6 rows for the table shown in Figure. P7.1.
Write the SQL code that will list all attributes in EMPLOYEE table for a job code of 502.
Write the SQL code to change the job code to 503 for the person whose employee number (EMP_NUM) is 102 in the EMPLOYEE table. After you have completed the task, examine the results, and then reset the job code to its original value (use
ROLLBACK).
Write the SQL code to delete the row in the EMPLOYEE table for the person whose job code classification is 502.
Write the SQL code to create a copy of EMPLOYEE table, naming the copy EMP_1.
Using a single command sequence, write the SQL code that will change EMP_YEARS in EMP_1 table to 20 for all employees whose job classification (JOB_CODE) is 501 or higher.
Create an index (name it as EMP_NAMEX) using EMP_LNAME and EMP_FNAME for EMPLOYEE table.
Note: 1) to see the attribute names in a table, use the command describe table_name; e.g., describe director;
2) to view all the table names you have created, use the command show tables;
3) to view all the databases, use the command show databases, then choose the second database use database_name, which is your MySQL login name.