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
You might also like to view...
In Java, array indexes always begin at ________________
a) -1 b) 0 c) 1 d) 2 e) you can declare an array to have any indexes you choose
Today, audio circuitry is built into the system board and called ____ audio.
A. integrated B. synchronized C. amplified D. digitized
Systems analysts formulate numbers that represent both current and projected ________ for the system so that any hardware obtained will possess the capability to handle current and future needs.
A) inventory B) power requirements C) heating and cooling needs D) workloads
Apple's iPhone series does NOT allow you to add any memory
Indicate whether the statement is true or false