Answers to "Additional Exercises" on Comp 353 midterm study guide


1. Describe in English what is being asked for in example 8.
Query 8 is:
select e.lname from employee2 e
where e.salary in (select e.salary from employee2 e where e.dno = 5);

This asks for the last names of all employees whose salary matches that of an employee in department 5.

2. 2. Give an alternative solution for example 8 that does not involve an inner query.

    select e.lname from employee2 e, employee2 e2 where e.salary = e2.salary and e2.dno = 5

(that is, e2 is an employee in department 5 with the same salary as e)

3. In English, who is getting the raise in example 5? Is this fair?
The query is
    update employee2 set salary = 1.10 * salary where salary >= 50000;
This gives everyone with salary >= 50,000 a 10% raise; in our usual COMPANY database (with table employee instead of employee2), that is Mr Borg only.

As to fairness, you might think that this raise is the highly compensated employees' just reward for leading the company through difficult times or you might think that, once again, the 99% are being ignored. Either way, the point is to realize only the highly paid are getting a raise.

4. Write query 7a using an inner query instead of a join.
Here is the original:
select e.lname from employee2 e, department2 d
where e.dno = d.dnumber and d.dname="maintenance";

Here it is with an inner query.
select e.lname from employee2 e
where e.dno = (select d.dnumber from department2 d where d.dname = "maintenance");

The inner query looks up the department number for the maintenance department.

5. Write insert statements so the following query returns (2, 30000, 40000):

select count(*), avg(e.salary), max(e.salary) from employee e
where e.dno = 5;

This should have been employee2.
We have to add two employees to department 5. The max salary has to be 40,000, which means that for the average to be 30,000 the other salary has to be 20,000.

insert into employee2 values ('bob', 'smith', '234567890', 40000, null, 5);
insert into employee2 values ('james', 'johnson', '345678901', 20000, null, 5);

6. Write a query to print the number of employees, average salary and max salary for all departments with an average salary >= 30000.
While you can do this without including the department number, the information without it is not very useful. I should have said to add this.

Select e.dno, count(*), avg(e.salary), max(e.salary)
from employee2 e
group by e.dno
having avg(e.salary) >= 31000;