POS 410 Week 4 Lab Three

Download the attached Word Document Template named “Lastname – Lab 3.doc”. (change Lastname to your last name)

• 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.