Given the following relational database schema: Employee=(ID, name, position, of

Question

Given the following relational database schema:

Employee=(ID, name, position, officeN, phoneN,age)// assume the name is unique.

Committee =( title, meetingDate, startTime, endTime, location)// You may use <,>,=,!= between dates and times.

Membership= ( ID, title, task) // task = ‘member ‘or ‘chair’.

Use a minimum number of operations and tables to express the following queries by SQL statements:

1. List the name of every employee who is a chair of at least two committees.

2. List the name of every employee who is not serving on any committee.

Explanation / Answer

1. List the name of every employee who is a chair of at least two committees.

Answer:

Select name from Employee where ID in (Select ID from Membership where task='chair' group by ID having count(ID) > 1);

Explanation:

The inner SQL statement retrieves the Employee ID from Membership table for Employee who is chair for more than two committees. The outer Query will retrieve the name of those employee ID given by inner sql query.

--------------------------------------------------------------------------------------------------------------------------------------------------------

2. List the name of every employee who is not serving on any committee.

Answer:

Select name from Employee where NOT ID in ( Select ID from Membership INNER JOIN Committee on Membership.title=Committee.title);

Explanation:

The inner SQL query retrieves the Employee ID who is serving some commitees. The outer SQL keyword retrieves the name of the Employee ID which is not in the List given by Inner SQL query. NOT keyword is used for this purpose.

---------------------------------------------------------------------------------------------------------------------------------------------------------

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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