Task $1(10$ pts$)$: Executing all SQL scripts in $"$hw$4$db$.$sql$"$ to create four tables and have the corresponding data inserted. What to submit:

$-$ Four screenshots of executing the following SQL scripts. You need to execute them one by one, and the results of each execution should be the content of the table as an example shown below. Make sure all rows of each table are displayed in your screenshots.

SELECT $*$ FROM JOB;

SELECT $*$ FROM EMPLOYEE;

SELECT $*$ FROM PROJECT;

SELECT $*$ FROM ASSIGNMENT;

Example Screenshot:

Task $2$ to Task $9-$ What to submit:

$-$ Your SQL statements for each task. Please make sure that we can copy and paste your SQL statements. Do not submit the screenshots of your SQL statements.

$-$ The screenshots of your execution of SQL statements are not required for these tasks, however, you can still add them to help explain your SQL statements better if you prefer.

Before working on Task $2,$ please run the statement below to disable the safe update mode by executing the SQL statement below in your SQL client.

SET SQL$\_$SAFE$\_$UPDATES $=0$;

Task $2(10$ pts$)$: Write the SQL code to change the job code to $501$ for the person whose employee number $($EMP$\_$NUM$)$ is $107.$

Task$-3(10$ pts$)$: Write the SQL code to delete the row for William Smithfield, who was hired on June $22,2004.$

Task$-4(10$ pts$)$: Write the SQL code that will 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. The new attribute characteristics are

Task$-5(10$ pts$)$: Using the EMPLOYEE table, write a single SQL statement to change the EMP$\_$PCT value to $10\mathrm{.}00$ for all employees.

Task$-6(10$ pts$)$: Write the SQL code that will change the PROJ$\_$NUM to $14$ for employees who were hired after January $1,1995,$ and whose job code is greater than $501.$

Task$-7(10$ pts$)$: Write the SQL code that will delete the column of "JOB$\_$LAST$\_$UPDATE" from the JOB table.

Task$-8(10$ pts$)$: Write the SQL code that will add a constraint to the PROJ$\_$BALANCE of the PROJECT table, which will make sure the value of PROJ$\_$BALANCE is not negative.

Task$-9(10$ pts$)$: Write the SQL code that creates a new table named DEPENDENT, which is used to store the dependent information of employees. The DEPENDENT has the following information,

$-$ DEP$\_$NUM

$-$ DEP$\_$LNAME

$-$ DEP$\_$FNAME

$-$ RELATIONSHIP

$-$ DOB

$-$ EMP$\_$NUM

The DEP$\_$NUM should be an auto$-$increment field and used as the primary key of the table. EMP$\_$NUM is used as the foreign key of the table that links to the EMPLOYEE table. The value of EMP$\_$NUM in the DEPENDENT will be set to NULL if the corresponding employee is removed from the EMPLOYEE table.

Task$-10(10$ pts$)$: Write the SQL code to insert the following two dependents into the DEPENDENT table you created in Task$-9.$

Dependent $1$ Dependent $2$

$-$ Last name: Jordan

$-$ First name: Tom

$-$ RELATIONSHIP: child

$-$ DOB: $2002-08-08$

$-$ EMP$\_$NUM: $101$

$-$ Last name: Jordan

$-$ First name: Alex

$-$ RELATIONSHIP: spouse

$-$ DOB: $1977-01-03$

$-$ EMP$\_$NUM: $101$