CSC 455 Fall 2009 Lab 15 – Solutions

1. Find the names of all employees who do not have a supervisor. (7 rows)

SELECT Fname, Lname
FROM EMPLOYEE
WHERE Super_ssn IS NULL
ORDER BY Lname;

1. Show the average salary by department. (6 rows)

SELECT Dno, AVG( Salary )
FROM `EMPLOYEE`
GROUP BY Dno;

2. Show the average salary by department name instead.

SELECT Dname, AVG( Salary )
FROM EMPLOYEE
JOIN DEPARTMENT ON Dno = Dnumber
GROUP BY Dname;

3. List Essn of employees who work on two or more projects. (7 rows)

SELECT Essn
FROM WORKS_ON
GROUP BY Essn
HAVING count( Pno ) >=2;

4. List the first name, last name, and number of projects of employees who work on two or more projects.

SELECT Fname, Lname, count( Pno )
FROM WORKS_ON
JOIN EMPLOYEE ON Ssn = Essn
GROUP BY Fname, Lname
HAVING count( Pno ) >=2;

5. Use a nested query with IN to show the name of the employee with the highest salary.

SELECT Fname, Lname
FROM EMPLOYEE
WHERE salary
IN (

SELECT Max( salary )
FROM EMPLOYEE;

)