-------------------------------------------------------------------------

Homework 1 solution:


Statements used to create the tables:

create table if not exists Employee ( Name varchar(50), Salary int, Manager varchar(50), Department varchar(50));
create table if not exists Course (Student varchar(50), Subj varchar(50), Prof varchar(50), Grade int);

The "not exists" part is optional.

--------------------------------------------------------------------------

1. select Name from Employee where Department like 'Software';
+--------+
| Name   |
+--------+
| Eliot  |
| Warren |
+--------+
2 rows in set (0.00 sec)

-------------------------------------------------------------------------

2. select E.Name from Employee as E, Employee as M where E.Manager = M.Name and (E.Salary - M.Salary) >= 5000;
Empty set (0.03 sec)

-------------------------------------------------------------------------

3.  select E.Name from Employee as E, Course as EC, Course as MC 
    where E.name = EC.student and E.manager = MC.student 
    and EC.grade > MC.grade and EC.subj = MC.subj;
Empty set (0.00 sec)

-------------------------------------------------------------------------


4. select avg(Salary) from Employee;
+-------------+
| avg(Salary) |
+-------------+
|  32800.0000 |
+-------------+
1 row in set (0.08 sec)

-------------------------------------------------------------------------

5. select avg(Salary) from Employee as E, Course as C where E.name = C.student and C.prof = "Roe";
+-------------+
| avg(Salary) |
+-------------+
|  32000.0000 |
+-------------+
1 row in set (0.00 sec)

-------------------------------------------------------------------------

6.  select emp.department, avg(emp.salary) "Avg Salary" from Employee as emp     
    where (select count(distinct E.name) from Employee as E, Course C      
    where E.name =  C.student and E.department = emp.department) > 1    
    group by emp.department;
+------------+------------+
| department | Avg Salary |
+------------+------------+
| Switch     | 32000.0000 |
+------------+------------+
1 row in set (0.03 sec)

There is also a faster way to do this. See question 10.

-------------------------------------------------------------------------

7-9 See pdf doc
-------------------------------------------------------------------------

10. The slow way:

select emp.department, avg(emp.salary) "Avg Salary" from Employee as emp     
    where (select count(distinct E.name) from Employee as E, Course C      
    where E.name =  C.student and E.department = emp.department) > 1    
    group by emp.department;
+------------+------------+
| department | Avg Salary |
+------------+------------+
| Finance    | 63863.2479 |
| Hardware   | 66308.3700 |
| HR         | 66402.6549 |
| QA         | 66012.7119 |
| Software   | 61928.2700 |
| Switch     | 66413.2231 |
+------------+------------+
6 rows in set (3 min 23.01 sec)

The fast way requires selecting into a temporary table. So we use 2 queries:

create temporary table temp select department, count(distinct name) as 
"NumOfStudents" from Employee, Course where name = student group by 
department;
Query OK, 6 rows affected (0.84 sec)

select emp.department, avg(emp.salary) "Avg Salary" from Employee as emp
    where emp.department in (select department from temp where 
    numOfStudents > 1) group by emp.department;
+------------+------------+
| department | Avg Salary |
+------------+------------+
| Finance    | 63863.2479 |
| Hardware   | 66308.3700 |
| HR         | 66402.6549 |
| QA         | 66012.7119 |
| Software   | 61928.2700 |
| Switch     | 66413.2231 |
+------------+------------+
6 rows in set (0.04 sec)

Total time for the fast way: 0.88 sec

Also possible to use the join version after creating the temp table:

select emp.department, avg(emp.salary) "Avg Salary" from Employee as emp, temp
    where emp.department= temp.department 
    and temp.numOfStudents > 1 
    group by emp.department;
+------------+------------+
| department | Avg Salary |
+------------+------------+
| Finance    | 63863.2479 |
| Hardware   | 66308.3700 |
| HR         | 66402.6549 |
| QA         | 66012.7119 |
| Software   | 61928.2700 |
| Switch     | 66413.2231 |
+------------+------------+
6 rows in set (0.04 sec)



-------------------------------------------------------------------------

11. select avg(Salary) from Employee as E, Course as C where E.name = C.student and C.prof = "Roe";
+-------------+
| avg(Salary) |
+-------------+
|  64554.8780 |
+-------------+
1 row in set (0.21 sec)