CREATING SCHEMAS AND QUERIES

Q1

Assume following Schemas exist:

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

Works (eid: integer, did: integer, pct time: integer)

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

Write following queries in SQL

1. Print the names and ages of each employee who works in both the Hardware department and
the Software department.

SELECT E.ENAME, E.AGE FROM EMP E, DEPT D1, DEPT D2, WORKS W1, WORKS W2
WHERE
E.EID = W1.EID AND W1.DID = D1.DID AND D1.DNAME ='HARDWARE' AND
E.EID = W2.EID AND W2.DID = D2.DID AND D2.DNAME ='SOFTWARE'

OR

SELECT E.ENAME, E.AGE FROM EMP E, DEPT D, WORKS W

WHERE
E.EID = W.EID AND W.DID = D.DID AND D.DNAME ='HARDWARE'
INTERSECT
SELECT E1.ENAME, E1.AGE FROM EMP E1, DEPT D1, WORKS W1
WHERE
E1.EID = W1.EID AND W1.DID = D1.DID AND D1.DNAME ='SOFTWARE'

2. For each department with more than 20 full-time-equivalent employees (i.e., where the part-
time and full-time employees add up to at least that many full-time employees), print the did
together with the number of employees that work in that department.

SELECT W.DID, COUNT(W.EID) AS EMPCOUNT FROM WORKS W

GROUP BY W.DID
HAVING 2000 <(SELECT SUM(W1.PCT) FROM WORKS W1 WHERE W.DID = W1.DID)

3. Find the managerids of managers who manage only departments with budgets greater than
$1 million.

SELECT D.MGRID FROM DEPT D WHERE D.BUDGET > 1000000

EXCEPT
 SELECT D1.MGRID FROM DEPT D1 WHERE D1.BUDGET <= 100000

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

SELECT E.ENAME FROM EMP E, DEPT D WHERE

D.MGRID = E.EID AND D.BUDGET >= (
SELECT MAX(D1.BUDGET) FROM DEPT D1)

OR

SELECT E.ENAME FROM EMP E, DEPT D WHERE

D.MGRID = E.EID AND D.BUDGET >= ALL(
SELECT D1.BUDGET FROM DEPT D1 )

5. Find the enames of managers who manage only departments with budgets larger than $1
million, but at least one department with budget less than $5 million.

SELECT E.ENAME FROM EMP E WHERE E.EID IN

((SELECT D.MGRID FROM DEPT D WHERE D.BUDGET > 1000000

EXCEPT
SELECT D1.MGRID FROM DEPT D1 WHERE D1.BUDGET <= 1000000)
INTERSECT
SELECT D2.MGRID FROM DEPT D2 WHERE D2.BUDGET <5000000)

Q2

Given Schemas

Student (snum: integer, sname: string, major: string, level: string, age: integer)

Class (name: string, meets_at: time, room: string, fid: integer)

Enrolled (snum: integer, cname: string)

Faculty (fid: integer, fname: string, deptid: integer)

Write SQL for Creating tables and for queries

Schema creation with Primary Key and Foreign Key

CREATE TABLE Students (

 snum integer,
sname char (30),
major char (10),
level char (10),
age integer,
PRIMARY KEY (snum)
)

CREATE TABLE Class(

name char(20),
meets_at time,
room char (10),
fid integer,
PRIMARY KEY (name),
FOREIGN KEY (fid) REFEFENCES Faculty(fid)
ON DELETE SET TO NULL //do not want to delete course another faculty may be assigned later
ON UPDATE CASCADE
)

CREATE TABLE Enrolled(

snum integer,
cname char(20),
PRIMARY KEY (snum,cname),
FOREIGN KEY (snum) REFEFENCES Student(snum)
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY (cname) REFERENCES Class(name)
ON DELETE CASCADE
ON UPDATE CASCADE
)

CREATE TABLE Facutly(

fid integer,
fname char(30),
deptid integer,
PRIMARY KEY (fid)
 )

(a) Every class has a minimum enrollment of 5 students and a maximum enrollment of 30
students.

Modify the Enrolled schema as follows :

CREATE TABLE Enrolled(

snum integer,
cname char(20),
PRIMARY KEY (snum,cname),
FOREIGN KEY (snum) REFEFENCES Student(snum),
FOREIGN KEY (cname) REFERENCES Class(name)
CHECK (( SELECT COUNT (E.snum) FROM ENROLLED E GROUP BY E.cname) >=5 ),
CHECK (( SELECT COUNT (E.snum) FROM ENROLLED E GROUP BY E.cname) <=30)
)
Operations to be monitored :

Insert and delete on STUDENTS needs to be monitored so that the no of students enrolled for a
course remain between 5 and 30. Also Insert and Delete on ENROLLMENT needs to be
monitored

(b) At least one class meets in each room.

Cannot be done in SQL

(c) Every faculty member must teach two courses

Since this involves two tables so we need to create an ASSERTION as follows:

CREATE ASSERTION CoursesTaught

CHECK (( SELECT COUNT(*) FROM FACULTY F CLASS C WHERE F.fid = C.fid
GROUP BY C.fid HAVING COUNT(*) < 2) =0)

Operations to be monitored :

Delete operation on CLASS needs to be monitored, if a class/course is deleted, and if a particular

faculty is teaching only two courses, this constraint will be violated.
(d) Every student must be enrolled in the course called Math101.

CREATE ASSERTION EnrolledMath101

CHECK ( SELECT COUNT(*) FROM STUDENTS S WHERE S.snum NOT IN
(SELECT E.snum FROM ENROLLED E WHERE E.cname = ‘Math101’) = 0)

Operations to be monitored :

Delete on Enrollment needs to be monitored, so as the enrollment for Math101 is not removed

(e) No department can have more than 10 faculty members.

Modify the Faculty Schema to add Check constraint as follows :

CREATE TABLE Facutly(

fid integer,
fname char(30),
deptid integer,
PRIMARY KEY (fid),
CHECK ( (SELECT COUNT(*) FROM FACULTY F
GROUP BY deptid HAVING COUNT (*) > 10) =0)
)

Operations to be monitored :

Insert on faculty needs to be monitored, if there are 10 faculty members in a deptid, addition of
another faculty in the same deptid will violate this constraint.

(f) The number of CS majors must be more than the number of Math majors.

The STUDENTS TABLE needs to be modified as below :

CREATE TABLE Students (

snum integer,
sname char (30),
major char (10),
level char (10),
age integer,
PRIMARY KEY (snum),
CHECK ( ( SELECT COUNT(*) FROM STUDENTS S WHERE MAJOR = ‘CS’) >
(SELECT COUNT(*) FROM STUDENTS WHERE MAJOR =’Math’))
)

Operations to be monitored :
Insert and delete on ENROLLMENT needs to be to maintain the above constraint on majors