Kudler Fine Food Week 4
Using the database and tables from Week Three, write queries using the SQL SELECT statement.
Note: Select all of the data from both of your tables before you perform the following.
· Select an EEO-1 Classification: Enhance all employees’ salaries that have: the selected EEO-1 classification by 10%.
revise employee set salary = salary * 1.1 where eeo_1_classification = 'sales workers'
· Increase all employees’ salaries by 5%.
revise employee set salary = salary * 1.05
· Select an employee from the employee table; delete this employee. delete from employee where id_num=5
· Print out the SQL statements and the data from both tables to show how the data was affected. Get ready to show your results in class.
Using the database and tables from Week Three, write SQL statements to:
· Calculate the average salary for all employees.
select avg(salary) from employee
· Calculate the maximum salary for exempt employees and the maximum salary for non-exempt employees.
choose max(salary) from employee join job_title on employee.job_title_id = job_title.job_title_id where exempt='yes'
choose max(salary) from employee join job_title on employee.job_title_id = job_title.job_title_id where exempt='no'
· Calculate the maximum salary for all employees.
choose max(salary) from employee
· Calculate the minimum salary for exempt employees and the maximum salary for non-exempt employees.
choose min(salary) from employee join job_title on employee.job_title_id = job_title.job_title_id where exempt='yes'
choose min(salary) from employee join job_title on employee.job_title_id = job_title.job_title_id where exempt='no'
· Calculate the minimum salary for all employees.
choose min(salary) from employee
· Print out the SQL statements and the results. Get ready to show your
Note: Select all of the data from both of your tables before you perform the following.
· Select an EEO-1 Classification: Enhance all employees’ salaries that have: the selected EEO-1 classification by 10%.
revise employee set salary = salary * 1.1 where eeo_1_classification = 'sales workers'
· Increase all employees’ salaries by 5%.
revise employee set salary = salary * 1.05
· Select an employee from the employee table; delete this employee. delete from employee where id_num=5
· Print out the SQL statements and the data from both tables to show how the data was affected. Get ready to show your results in class.
Using the database and tables from Week Three, write SQL statements to:
· Calculate the average salary for all employees.
select avg(salary) from employee
· Calculate the maximum salary for exempt employees and the maximum salary for non-exempt employees.
choose max(salary) from employee join job_title on employee.job_title_id = job_title.job_title_id where exempt='yes'
choose max(salary) from employee join job_title on employee.job_title_id = job_title.job_title_id where exempt='no'
· Calculate the maximum salary for all employees.
choose max(salary) from employee
· Calculate the minimum salary for exempt employees and the maximum salary for non-exempt employees.
choose min(salary) from employee join job_title on employee.job_title_id = job_title.job_title_id where exempt='yes'
choose min(salary) from employee join job_title on employee.job_title_id = job_title.job_title_id where exempt='no'
· Calculate the minimum salary for all employees.
choose min(salary) from employee
· Print out the SQL statements and the results. Get ready to show your