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, age: integer, salary: real)

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

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

In the above, please note that there is an attribute pcttime (of Integer type) in the Works table, as the schema shows. A tuple/row <1, 1, 50> in the Works Table indicates that the Employee 1 works in Department 1 with 50% of time, which is PART TIME. Another tuple/row <2, 2, 90> in the Works Table indicates that the Employee 2 works in Department 2 with 80% of time, which is also PART TIME. Finally, a tuple/row <3, 5, 100> in the Works Table indicates that the Employee 3 works in Department 5 with 100% of time, which is FULL TIME. The number of FULL-TIME EQUIVALENT employees within a department is calculated based on the TOTAL PCTTIME of ALL employees in the department.

Find the managers who also work in another department (or other departments) but not as a manager there.

I need SQL statements!!!

Answer 1

As stated in the problem , we have three tables EMP, DEPT and WORKS. Lets first write the create statements and set in some values or data to the tables. Just to note , SQL Server statements have been used here.

1. Create EMP table :

create table emp(Id int, ename nvarchar(100), age int, salary real, PRIMARY KEY(Id));

Insert values : lets have 3 different employees
insert into emp values(1, 'A', 30, 10000);
insert into emp values(2, 'AB', 30, 13000);
insert into emp values(3, 'ABC', 32, 12000);

2.Create DEPT table :

create table dept(did int, budget real, managerid int, PRIMARY KEY(did));
ALTER TABLE dept
ADD CONSTRAINT FK_emp_dept
FOREIGN KEY(managerid)
REFERENCES emp(Id)

Insert values: lets have 3 departments and managers set respectively.
insert into dept values(1, 1000000, 2);
insert into dept values(2, 281980000, 1);
insert into dept values(3, 290000000, 3);

Foreign key constraint makes sure that the managers are employees. Values used for manager id should be from EMP table.

So, Emp A is the manager of dept 2 and Emp AB is the manager of dept 1. Emp 3 is manger of dept 3 itself.

We can take more examples to proof the SQL query.

3. Create Works table:
create table works(eid int, did int, pct_time int);
ALTER TABLE works
ADD CONSTRAINT FK_emp_works
FOREIGN KEY(eid)
REFERENCES emp(Id)

ALTER TABLE works
ADD CONSTRAINT FK_emp_dept1
FOREIGN KEY(did)
REFERENCES dept(did)

Here eid and did are foreign keys.

Insert Values:
insert into works values(1, 1, 50);
insert into works values(1, 2, 40);
insert into works values(2, 2, 90);
insert into works values(2, 1, 10);
insert into works values(1, 3, 10);
insert into works values(3, 3, 100);

So with the values inserted to tables we can see, empid 1 (A)works for 3 departments 1,2 and 3. while he is manager of dept id 2. Similarly emp id 2 (AB) works for two departments 2 and 1, while he is manager of dept id 1. Empid 3 (ABC) works full time only for dept id 3 where he himself is manger.

Now, lets try the SQL query where we need to find the managers who also work in another department (or other departments) but not as a manager there. below SQL Query would help

select distinct eid Manager_Id, ename Manager_Name, w.did Department from emp e inner join works w on e.id = w.eid inner join dept d on w.did= d.did where eid <> managerid

output :

	Manager_Id	Manager_Name	Department
1	1	A	1
2	1	A	3
3	2	AB	2

This means emp id 1 (A) works for dept 1 and 3 where he isn't manager. Emp id 2 (AB) works for dept id 2 where he isn't manager. This solves our stated problem to find the managers who also work in another department (or other departments) but not as a manager there.

In case we only need the manager names, below SQL query would help :

select distinct ename Manager_Name from emp e inner join works w on e.id = w.eid inner join dept d on w.did= d.did where eid <> managerid

Output :

	Manager_Name
1	A
2	AB

Hope this helps.