Description
For this assignment, you are to create a database (using MySQL) with the following tables
with the data as follows.
Employee(Name, Salary, Manager, Department)
Smith, 31000, Jones, Switch
Patten, 28000, NULL, Software
Hughes, 33000, Jones, Switch
Jones, 32000, Patten, Switch
Warren, 40000, Patten, Software
Key is Name.
Course(Student, Subj, Prof, Grade)
Smith, Algs, Hackett, 85
Patten, Algs, Hackett, 80
Patten, Comp, Roe, 70
Jones, Comp, Roe, 75
Jones, Dbase, Black, 80
Warren, Dbase, Black, 75
Warren, Comp, Roe, 65
Key is Student, Subj.
Write all your SQL queries in one script (text) file and submit the script file (see above). The
script file (text format) simply lists all the SQL queries you have as answers for the problems
below (separated by semicolon, certainly). Aside from reading your answers, we will run the
script file on several similar but different databases (modified from the example data above.
Remember: a query does not just work on one instance of the database!). Hence your script
file should be runnable from MySQL directly, for example, from mysql prompt as follows:
mysql> source scriptfile.sql
Each query is worth 10 points.
1. Find names of all employees who work in the software department. On above
example data, should be Patten and Warren.
2. Find names of all employees who earn at least 5000 more than their managers. On
above example data, should be Warren.
3. Find names of all employees who received higher grades than their managers in the
same course. On above example data, it should be Jones.
4. Find name of all employees who do not take any class. On above example data,
should be Hughes.
5. Find departments in which all of their employees take (one or more) courses. On
above example data, it should be Software. Hint: it’s fine to use temporary tables.
6. Find departments in which all of their employees take two or more courses. On
above example data, it should be Software. Hint: it’s fine to use temporary tables.
7. Find the average salary earned. On example data should be 32,800.
8. Find the lowest salary earned by people taking Roe’s course. On example data, it
should be 28,000.
9. For those departments whose employees (collectively) take more than three
courses, find the average salary by department. On example data, it should be
34,000. Hint: It’s fine to use two queries and a CREATE TEMPORARY TABLE command
that finds relevant departments first.
10. For each department, find the percentage of the employees who do not take any
course. On example data, it should be two tuples “(Software, 0), (Switch, 33.33)”.
Note that if all the employees of a department D take courses, then the department
should get a result as “(D, 0)”. For example, if your answer on example data is simply
(Switch, 33.33) (without (Software, 0)), then your query is wrong. Hint: you may use
case function like “Case when C is null then 0 else C/D end” in the Select Clause.
General hint: The operation MINUS is not supported by SQL, which can be replaced via “left
join”. For example, the following MINUS operation:
Select A From R1
MINUS
Select A From R2;
may be replaced by:
Select A
From R1 left join R2 on R1.A=R2.A
Where R2.A is NULL;
