Views and Permissions
This lab continues using the SELECT command in addition to now incorporating multiple tables and various set operators to produce results.
Getting Started
Your submission will be a single text-based SQL file with appropriate header and commenting.
Tasks
1) Display the names of the employees whose salary is the same as the lowest salaried employee in any department.
2) Display the names of the employee(s) whose salary is the lowest in each department.
3) Give each of the employees in question 2 a $120 bonus.
4) Create a view named vwAllEmps that consists of all employees includes employee_id, last_name, salary, department_id, department_name, city and country (if applicable)
5) Use the vwAllEmps view to:
a. Display the employee_id, last_name, salary and city for all employees
b. Display the total salary of all employees by city
c. Increase the salary of the lowest paid employee(s) in each department by 120
d. What happens if you try to insert an employee by providing values for all columns in this view?
e. Delete the employee named Vargas. Did it work? Show proof.
6) Create a view named vwAllDepts that consists of all departments and includes department_id, department_name, city and country (if applicable)
7) Use the vwAllDepts view to:
a. For all departments display the department_id, name and city
b. For each city that has departments located in it display the number of departments by city
8) Create a view called vwAllDeptSumm that consists of all departments and includes for each department: department_id, department_name, number of employees, number of salaried employees, total salary of all employees. Number of Salaried must be different from number of employees. The difference is some get commission.
9) Use the vwAllDeptSumm view to display department name and number of employees for departments that have more than the average number of employees
10) A) Use the GRANT statement to allow another student (Neptune account) to retrieve data for your employees table and to allow them to retrieve, insert and update data in your departments table. Show proof
B) Use the REVOKE statement to remove permission for that student to insert and update data in your departments table
