Part A a1. Use CREATE statement to create a table Client (AcctNo, ClientName, phone). Note AcctNo is primary key and you must define a constraint for this primary key in CREATE statement. Use Char for the data type of phone. Show the statement.

a2. Use INSERT statement to add two client records (insert two clients with different account number, make up your own data for clients). Show the INSERT statements and use select statement to show the contents of Client table.

a3. Use ALTER statements to add a foreign key ClientAcct, into the Project table, which has values of the AcctNo of Client . So that table Client has a 1:M relationship with table Project. Note, you need to use TWO ALTER statements, one for adding ClientAcct into Project table; one for adding foreign key constraint named as project_ClientAcct_fk into Project table. Show the ALTER statements.

a4 use an update statement to assign a client account number to the project(s) with the name that has word 'system' . Write another update statement to assign another client account number to the rest of the projects.

a5. Test the foriegn key constraint by inserting a new project with wrong client account number. Show the result.

a6. User ALTER statement to Add an attribute Project_Count into Employee table (data type to be integer, refer to the data type used for Workon table (hours) in loadDB file).

a7. Use UPDATE statement to fill the value of Project_count of each employee record in Employee table. Namely, add the count of total number of projects an employee works on into Project_count in Employee table for each employee. Hint: you need a subquery in Update statement as follows Update _________ Set _____ = (select count (pid) ...... ) Show the contents of Employee after update.

a8. Create a table Promotion_list (EMPID, Name, Salary, DivisionName).

a9. Load Promotion_list with the information of employees who make less than company average and work on at least 2 projects. (Hint use INSERT INTO SELECT statement ). Show the code and result. Part B b1 Create a view named as Promotion_candidates that lists empid, name, salary, did of employees who work on more than 2 projects and salary is below their divisional average.