Practical Session 2 Answers

Exercise 1

--a.

SELECT DNAME, COUNT (*)
FROM DEPARTMENT, EMPLOYEE
WHERE DNUMBER=DNO
GROUP BY DNAME
HAVING AVG (SALARY) > 30000

-- Result:
-- DNAME DNUMBER COUNT(*)
-- Research 5 4
-- Administration 4 3
-- Headquarters 1 1

--b.

SELECT fname, lname 
FROM employee
WHERE lname like 'S%';

--c.

SELECT DISTINCT fname, lname
FROM employee, works_on, project
WHERE employee.ssn = works_on.essn AND
    works_on.pno = project.pnumber AND
    project.pname LIKE '%Product%';

Exercise 2

--a.
CREATE VIEW ex_2a AS
SELECT dname, fname, lname, SALARY
FROM employee, department
WHERE department.mgr_ssn = employee.ssn;

--b.

CREATE VIEW ex_2b AS
SELECT e1.fname AS e_fname, e1.lname AS e_lname, sup.fname, sup.lname, e1.salary 
FROM employee AS e1, employee AS sup, department as dept  
WHERE e1.superssn = sup.ssn AND
    e1.dno = dept.dnumber AND
    dept.dname = 'Research';   

--c. 
CREATE VIEW ex_2c AS
SELECT COUNT(employee.ssn), sum(works_on.hours), department.dname
FROM project, department, employee, works_on
WHERE works_on.essn = employee.ssn AND
    works_on.pno = project.pnumber AND
    project.dnum = department.dnumber
GROUP BY department.dname;

Exercise 3

Review prac. session 1 solutions for the ‘Library’ database example. Referentally triggered actions may vary between answers.