Assume the following tables, describing employees and the departments in which they work, for all the parts of this question.

Emp(eid: integer, ename: string, salary: real)

Works(eid: integer, did: integer)

Dept(did: integer, dname:string)

(a) Write a SELECT statement that outputs the employee id (eid) of all employees that work
in the department with department id (did) equal to 5.

(b) Write a SELECT statement that outputs the names (ename) and salaries (salary) of all
employees that work in the department with department name (dname) ?accounting?.

(c) Write a SELECT statement that for each department outputs the department name (dname)
and the number of employees that work in that department.

(d) Write the declaration of a view that gives the name of each employee who works in more
than one department.

(a) Write a SELECT statement that outputs the employee id (eid) of all employees that work
in the department with department id (did) equal to 5.
Solution:

SELECT W.eid

FROM Works W

WHERE did = 5

(b) Write a SELECT statement that outputs the names (ename) and salaries (salary) of all
employees that work in the department with department name (dname) ?accounting?.
Solution:

SELECT E.ename, E.salary

FROM Emp E, Works W, Dept D

WHERE E.eid = W.eid AND W.did = D.did AND

D.dname = ``accounting''

(c) Write a SELECT statement that for each department outputs the department name (dname)
and the number of employees that work in that department.
Solution:

SELECT D.dname, COUNT(*)

FROM Work W, Dept D

WHERE W.did = D.did

GROUP BY D.dname, D.did

(d) Write the declaration of a view that gives the name of each employee who works in more
than one department.
Solution:

CREATE VIEW multiple-depts AS

SELECT E.name

FROM Emp E, Works W

WHERE E.eid = W.eid

GROUP BY E.eid, E.name

HAVING COUNT (*) > 1

or

CREATE VIEW multiple-depts AS

SELECT E.name

FROM Emp E

WHERE (SELECT COUNT (*)

FROM Works W

WHERE W.eid = E.eid) >1