Question

Consider the following relational schema. An employee can work in more than one department; the pct time field of the Works relation shows the percentage of time that a given employee works in a given department.

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

Works(eid: integer, did: integer, pct_time: integer)

Dept(did: integer, dname: string, budget: real, managerid: integer)

Write the following SQL queries.

d) Find the enames of managers who manage the departments with the largest budgets.

e) Find the names and phone numbers of each employee who works in both the Hardware department and the Software department.

f) Write a trigger for the Works table to ensure that an employee’s pcttime is never greater than 100 when a record is inserted into the Works table.  The new pcttime is greater than what is available for that employee then set it to 0.

Answer 1

D. Below query contain joins to get respective columns. Sub query to get max budget and to filter main query on budget.

select e.ename 
from emp e inner join works w e.eid=w.eid 
inner join dept on w.did=d.did where d.budget=(select max(budget) from dept);

E. Below contain and conditions to do 2 filters for software and hardware...

select e.ename, e.age 
from emp e inner join works w on e.eid=w.eid inner join dept on w.did=d.did where d.dname='hardware' and 
      e.eid in(select w2.eid 
                   from works w2 inner join dept d2 
                   On w2.did=d2.did where d2.dname='software');

F. Trigger SQL server.

CREATE TRIGGER pctcheck

ON works

AFTER INSERT, UPDATE

AS

BEGIN

Update works set pcttime=

case when inserted.pcttime > 100 then 0 else inserted.pcttime end;

End