(37 points) Q1 A. Consider the following ER diagram. Write SQL statements to cre

Question

(37 points) Q1 A. Consider the following ER diagram. Write SQL statements to create the corresponding tables and capture as many of the constraints as possible (create 2 tables only). name date ssn pid sponsor age mployee Work Project B. Assume we have the following instances for two tables Employee and Project: Project Employee pio 100 102 sponsor Computer Science Biolo Ssn name 5380 Guldu 5381Ali 5382 Jil age 30 32 27 Assume Jill and Ali work on project 100. What will be the new instances of the tables Employee and Project after running the following query? DELETE FROM Project P WHERE P.pid-'100 (35 points) Q2. Consider the following relations Student(sid: integer, sname: string, major: string, level: string, age: integer) Class(cname: string, meets at: string, room: string, fid: integer) Enrolled(sid: integer, cname: string) Faculty(fid: integer, fname: string, deptid: integer)

Explanation / Answer

If you have any doubt, give me comment...

A)
CREATE TABLE Employee(
ssn INTEGER NOT NULL PRIMARY KEY,
name VARCHAR(255),
age INTEGER
pid INTEGER,
FOREIGN KEY(pid) REFERENCES Project(pid)
);

CREATE TABLE Project(
pid INTEGER NOT NULL PRIMARY KEY,
sponser VARCHAR(255),
date DATE
);

B)
Project
pid Sponser
102 Biology

Employee
Ssn name age
5380 Guldu 30
5381 Ali 32
5382 Jill 27

Q2)

1)
SELECT COUNT(*)
FROM Class C, Enrolled E, Facutly F
WHERE E.cname=C.cname AND C.fid=F.fid AND F.fname='I.Teach';

2)
SELECT C.cname
FROM Class C, Enrolled E
WHERE C.cname=E.cname
GROUP BY C.cname
HAVING COUNT(*)>6;

3)
SELECT C.cname
FROM Class C
WHETE C.room = 'R543'
OR C.cname IN(
SELECT E.cname
FROM Enrolled E
GROUP BY E.cname
HAVING COUNT(*)>6;
);

4)
SELECT level, COUNT(*)
FROM Student
WHERE level <> 'JR'
GROUP BY level

5)
SELECT AVG(age)
FROM Student S
WHERE S.sid NOT IN(
SELECT E.sid
FROM Enrolled E
);

Q3)

1)
SELECT S.sname
FROM Suppliers S
WHERE NOT EXISTS(
SELECT P.pid
FROM Parts P
WHERE P.color = 'red' AND NOT EXISTS(
SELECT C.sid
FROM Catalog C1
WHERE C.sid = S.sid AND
C.pid = P.pid)
);

2)
SELECT DISTINCT C.sid
FROM Catalog C
WHETE NOT EXISTS(
SELECT P.pid
FROM Parts P
WHERE P.pid = C.pid AND P.color<>'red'
);

3)
SELECT DISTINCT C.sid
FROM Catalog C, Parts P
WHERE C.pid = P.pid AND P.color='red' AND C.sid IN(
SELECT C1.sid
FROM Catalog C1, Parts P1
WHERE C1.pid = P1.pid AND P1.color='green'
)

4)
SELECT DISTINCT C.sid
FROM Catalog C
WHERE C.cost > (
SELECT AVG(C1.cost)
FROM Catalog C1
WHERE C1.pid = C.pid
)

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

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