Question

Department (deptID, deptName, location) Employee (empID, empName, deptID, slevel, age) Manager (mgrID, mgrName, deptID) Meeting (mtName, time, room, mgrID) Accepted (empID, mtName)

1. Provide CREATE TABLE queries for Employee and Manager with necessary constraints.

2. Get the names of manager conducting a meeting in room ‘CodeBlue’.

3. Get the employee IDs and names of employees who have never taken a part in meeting with a manager named ‘Smith’.

4. Get the department names and the total number of employees in that department, sorted in decreasing order of the number of employees.

5. Get the ID of the employee whose name comes first among all employees (i.e. first in alphabetical order). (hint: you can use ALL keyword for this query)

Answer 1

CREATE TABLE DEPARTMENT(deptId varchar2(6) PRIMARY KEY,deptName varchar2(20), location varChar2(20))

CREATE TABLE EMPLOYEE(empId varchar2(6) PRIMARY KEY,empName varchar2(20),deptId varchar(6) references DEPARTMENT(deptId),slevel INT(2), age INT(3))

CREATE TABLE MANAGER(mgrId varchar2(6) PRIMARY KEY,mgrName varchar2(20),deptId varchar2(6) references DEPARTMENT(deptId))

CREATE TABLE MEETING(mtName varchar2(20) PRIMARY KEY,time DATE,room varchar2(20),mgrId varchar2(6) references MANAGER(mgrId))

CREATE TABLE ACCEPTED(empId varchar2(6) references EMPLOYEE(empId),mtName varchar2(20) references MANAGER(mgrId), PRIMARY KEY(empId,mtName))

Q1. Name of the manager conducting a meeting in the room 'CODEBLUE'

SELECT mgrName from MANAGER where mgrId in (SELECT mgrId from MANAGER where room = 'CODEBLUE')

Q2. Id and Name of the employee from EMPLOYEE who has never taken part in meeting with a manage named SMITH

SELECT empId,empName from Employee where empId NOT IN

(SELECT empId from Accepted where mtName IN

(SELECT mtName from MEETING where mgrId IN

(SELECT mgrId from MANAGER where mgrName = 'SMITH')))

Q3. Department names and total number of employees in that department sorted in decreasing order of the number of the employees.

SELECT deptName as Department_name,COUNT(*) as Total_no_of_employee from DEPARTMENT INNER JOIN EMPLOYEE ON

EMPLOYEE.deptId = DEPARTMENT.deptId

GROUP BY DEPARTMENT.deptId,deptName

ORDER BY Total_no_of_employee DESC

Q4. Get Id of the employee whose name comes first among all employees.

SELECT empId from EMPLOYEE order by empName LIMIT 1