CSCI-GA.2433-011-Homework 2 Solved

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. 

file.sql 

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;