The 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 ConstructCo

database are shown in Figure $1.$

Figure $1$: ConstructCo Database

Given the structure and contents of the ConstructCo database shown in Figure $1,$ use

SQL commands to answer following exercises

$2$

a$.$ Download the following file from Moodle: Week $8$: $08\_$ConstructCo$\_$MySQL$.$txt

b$.$ Import the file $08\_$ConstructCo$\_$MySQL$.$txt into XAMPP.

c$.$ Write the SQL code that will create only the table structure for a table named EMP$\_1.$

This table will be a subset of the EMPLOYEE table. The basic EMP$\_1$ table structure is

summarized in the following table. Use EMP$\_$NUM as the primary key. Note that the

JOB$\_$CODE is the FK to JOB so be certain to enforce referential integrity. Your code should

also prevent null entries in EMP$\_$LNAME and EMP$\_$FNAME.

d$.$ Having created the table structure in $4($c$),$ write the SQL code to enter the first two rows

for the table shown in Figure $2.$ Each row should be inserted individually, without using a

subquery. Insert the rows in the order that they are listed in the figure.

Figure $2$

The EMP$\_1$ Table

e$.$ 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 sub$-$ query should

only retrieve the columns needed for the EMP$\_1$ table and only the employees shown in

the figure.

f$.$ Write the SQL code to change the job code to $501$ for the person whose employee

number $($EMP$\_$NUM$)$ is $107.$

$3$

g$.$ 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.$)$

h$.$ Write the SQL code to create a copy of EMP$\_1,$ including all of its data, and naming the

copy EMP$\_2.$

i$.$ 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).$

j$.$ Using the EMP$\_2$ table, write the SQL code to change the EMP$\_$PCT value to $3\mathrm{.}85$ for the

person whose employee number $($EMP$\_$NUM$)$ is $103.$