sql script for each question 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) Alter PayRoll table with the following, make sure to write each script separately 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 Add Foreign Key constraint in PayRoll table with job id column referencing to project num column in Project table 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 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

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 25, 2024

sql script for each question 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

sql script for each question

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)

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

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.

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

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

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.