use mysql

drop table if exists WorksFor ; drop table if exists Manages ; drop table if exists Employee ; drop table if exists Department ;

create table Employee ( eid int, name varchar(20), age int, salary float, residenceState char(2), startDate date, Primary Key (eid) ) ;

create table Department ( did int, name varchar(20), floor int, supplyBudget float, stateLocated char(2), PRIMARY KEY (did) ) ;

create table WorksFor ( eid int, did int, startDate date, PRIMARY KEY (did,eid), Foreign Key (did) references Department(did), Foreign Key (eid) references Employee(eid) ) ;

create table Manages ( eid int, did int, dateStartedManaging date, PRIMARY KEY (did,eid), Foreign Key (did) references Department(did), Foreign Key (eid) references Employee(eid) ) ;

Now, write an SQL procedure that:

• prints out the salary of all managers
• finds the maximum salary of employees who are NOT managers. Call this salary MaxNonManager
• updates all employees who are managers so that their salary is (MaxNonManager + 1) if there salary is lower than this number.
• prints out the salary of all managers again, showing that most have been updated (a few already had higher salaries)

Hints:

First write some SQL queries to understand what is going on and then to put some version of them into the procedure. Useful queries, although not an exhaustive list, would be:

• a query that returns the max salary of non-managers
• a query that returns the salaries of the managers
• an update command that updates the salaries of managers to the value in a variable @foo

Once you have a set of queries/updates working, then create the needed procedure. Because you are doing updates, you likely will need to reload the data after updates so you can test again.