SQL Lab 3
Download the attached Word Document Template named “Lastname – Lab 3.doc”. (change Lastname to your last name)
*** ATTENTION! ***
USE THE ATTACHED WORD DOCUMENT TEMPLATE WHEN YOU TURN IN YOUR ASSIGNMENT
Using the database and tables from Week Three, write queries using the SQL UPDATE statement.
Choose a job_title_code. Increase all employees’ salaries that have that job_title_code by 10%.
Choose a job_title_code. Increase the Minimum_salary and Maximum_salary for this job_title_code in the job_title table.
Increase all employees’ salaries by 5%.
Choose an employee from the employee table; delete this employee.
Choose a job_title_code from the job_title table; delete this job_title_code.
Describe any differences you found between deleting a row from the employee table and deleting a row from the job_title table.
Write SQL statements to:
Calculate the average salary for all employees.
Calculate the maximum salary for exempt employees and the maximum salary for non-exempt employees.
Calculate the maximum salary for all employees.
Calculate the minimum salary for all employees.
Create a stored procedure that does the following:
Accepts one parameter (job_title_code)
Based on the job_title_code passed into the procedure, return the job_title_code, job title, and the maximum salary for that job_title_code.
Create a view named “EmployeeSalary” and include the following fields from both the Employee and Job_title tables: employee first name, employee last name, employee salary, job_title, minimum salary, and maximum salary.
Include all SQL statements and results for each task in your assignment post. Do not include screenshots – rather copy/paste the SQL scripts into the attached Word document.