Consider the following relational schema: Employee(eid, name, office) Manager(ei

Question

Consider the following relational schema:

Employee(eid, name, office)
Manager(eid, mid)

Each employee has a unique key, eid. An employee may have several managers, who are, in turn, employee: both attributes eid and mid in Manager(eid, mid) are foreign keys to Employee.

For each of the queries below, write it as a SQL query and show an equivalent relational algebra plan.

Write a query that retrieves all employees that have two or more managers. Your query should return the eid's and the names.

An independent employee is an employee without a manager. (For example, the CEO is independent.) Write a query that retrieves all independent employees; you should return their eid and their names.

Retrieve the office of all managers of the employee called 'Alice'. If there are multiple employees called Alice, or if one of them has several managers, you have to return all their offices.

Explanation / Answer

Table:1

create table Employee(
    eid number(5) not null,
    name varchar(15) not null,
    office varchar(15) not null,
    constraint eid_pk prinmary key(eid));          

Name                                      Null?           Type
-----------------------------------------    --------        -------------

EID                                       NOT NULL        NUMBER(5)
ENAME                                     NOT NULL   VARCHAR2(15)
OFFICE                                    NOT NULL    VARCHAR2(15)

Table: 2

create table Manager(
    eid number(5) not null,
    mid number(5) not null,
    constraint eid_fk foreign key(eid) references Employee(eid),
    constraint mid_fk foreign key(mid) references Employee(eid));

Name                                      Null?            Type
-----------------------------------------    --------       ----------

EID                                       NOT NULL        NUMBER(5)
MID                                       NOT NULL        NUMBER(5)

select * from Manager;

   EID        MID
------ ----------
10002      10001
10003      10001
10012      10011
10002      10011

select * from Employee;

   EID ENAME           OFFICE
------ --------------- ---------
10000 Alice           San
10001 Bob             San
10002 Alice           Chi
10003 Catri           Chi
10011 David           Was
10012 Edwin           Can

select e.ename,m.eid,count(e.ename) x# from Employee e cross join Manager m where m.mid in(10001) having x#=2;

no rows selected

select e.ename,m.eid from Employee e cross join Manager m where m.eid in(10001,10011);

EID ENAME        
------ ---------------
10002 Alice           Chi

select e.ename,e.office from Employee e cross join Manager m where e.eid in(10001,10011);

ENAME           OFFICE
--------------- -------
Bob             San
Bob             San
Bob             San
Bob             San
David           Was
David           Was
David           Was

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.