CSC 455 Fall 2009 Lab 14 – Solutions

1. Show all employee data for employees who earn more than $50,000. (14 rows in result)

SELECT *
FROM EMPLOYEE
WHERE salary >50000;

1. Retrieve the name and address of all employees who work for the ‘Research’ department. (6 rows in result)

SELECT Fname, Lname, Address
FROM EMPLOYEE
JOIN DEPARTMENT ON Dno = Dnumber
WHERE Dname = 'Research';

2. List the names of all employees in department 5 who work more than 10 hours per week on the ProductX project. (2 rows in result)

SELECT Fname, Lname
FROM EMPLOYEE
JOIN PROJECT
JOIN WORKS_ON ON Dno = Dnum
AND Pnumber = Pno
AND Ssn = Essn
WHERE Dno =5
AND Pname = 'ProductX'
AND hours >10;

3. List the names of employees and their supervisor names rename the columns for the supervisor name to be SuperF and SuperL. (33 rows in result)

SELECT E.Fname, E.Lname, S.Fname AS SuperF, S.Lname AS SuperL
FROM EMPLOYEE AS E, EMPLOYEE AS S
WHERE E.Super_ssn = S.Ssn;