SQL scripts for each question

1. Create Database schema called ClassAssignment

Use ClassAssignment database for rest of the exercise.

2. Create a table called Project with the following columns:

project_num INT NOT NULL PRIMARY KEY

project_code CHAR(4)

project_title VARCHAR(45)

first_name VARCHAR(45)

last_name VARCHAR(45)

project_budget DECIMAL(5,2)

3. Modify project_num to auto_increment and also auto_increment starts from 10.

4. Modify project_budget datatype from decimal (5, 2) to (10, 2).

5. Insert following values into the Project table.

DO NOT insert project_num. Auto_increment should start from 10

project_code	project_title	first_name	last_name	project_budget
PC01	DIA	John	Smith	10000.99
PC02	CHF	Tim	Cook	12000.50
PC03	AST	Rhonda	Smith	8000.40

6. Create a table PayRoll with the following info:

employee_num INT PRIMARY KEY AUTO_INCREMENT

job_id INT NOT NULL

job_desc VARCHAR(40)

emp_pay DECIMAL (10,2)

7. Alter PayRoll table with the following, make sure to write each script separately.

1. Add constraint on emp_pay so that only value greater than 10,000 can be inserted.

ii. Add constraint on job_desc so that default value set to Data Analyst.

iii. Add column pay_date (DATE) after job_desc.

8. Add Foreign Key constraint in PayRoll table with job_id column referencing to project_num column in Project table.

9. Insert following values into PayRoll table. DO NOT insert employee_num and job_desc, those should be auto populated using auto_increment and default values, respectively.

job_id	pay_date	emp_pay
10	current date	12000.99
11	current date	14000.99
12	current date	16000.99

10. Update emp_pay in PayRoll table for employee_num = 2 with 10% emp_pay increase i.e. (emp_pay * 0.10).

11. Create Project_backup table from project table you created above using bulk insert statement only for last_name 'Smith'.

12. Create VIEW as PayRoll_View from PayRoll table you created above. However, your VIEW should only contain job_id, job_desc and pay_date for job_id > 10.

13. Create Index for pay_date on PayRoll table.

14. Delete all data from project_backup table but keep the table structure.

15. Write a DELETE script to delete a row from Project table where project_num = 10.

If there is an error, give a short explanation of what/why about error msg?

16. Solve the question 15 above without error, i.e. write a script how you can delete.