Given the following schema. Primary keys are underlined and highlighted. Foreign

Question

Given the following schema. Primary keys are underlined and highlighted. Foreign keys are bold. The meaning of the relations is straightforward Class(cname.time.room,fid) Enrolled(snum.cname, grade) Faculty(fid.fname.deptid] Write SQL queries to answer the following questions 5.1)(3 Points) Find the names of all juniors (level - JR) who are enrolled in a class taught by faculty 'Sam Eric'. 5.2)(3 Points) Find the name of the youngest student. 3 Points) For each instructor, list the instructor name, and the number of classes each instructor teaches. 5.4(3 Points) Create a view to include a list of all course names along with the name of the faculty who teaches this course. (î.?., view output includes game and mame 5.5)(3 Points) Find names of students who are not enrolled in any class. (Hint: use NOT IN) 5.6) (4 Points) Create a stored procedure that takes two inputs: a faculty identifier (fid) and number of bonus points. The procedure then updates the Enrolled table by adding the bonus points to the grades of all courses that are taught by faculty with the input fid. O5.7) (3 Points) Create a trigger to increment the numFaculty attribute of the Department table when a new row is inserted in the Faculty table

Explanation / Answer

Let me know if you have any doubt.

#5.1)

SELECT DISTINCT S.Sname
FROM Student S, Class C, Enrolled E, Faculty F
WHERE S.snum = E.snum
AND E.cname = C.name
AND C.fid = F.fid AND
F.fname = 'Sam Eric' AND S.level = 'JR';

#5.2)

SELECT sname
FROM Student
WHERE age = (SELECT min(age) FROM Student );

#5.3)

SELECT F.fname , count(C.cname)
FROM Faculty as F join Class as C
ON F.fid = C.fid
GROUP BY F.fname

#5.4)

CREATE VIEW Course_faculty
AS SELECT C.cname, F.fname
FROM Class C, Faculty F
WHERE C.fid=F.fid;

#5.5)

SELECT S.Sname
FROM Students
WHERE S.snum NOT IN ( SELECT * FROM Student S, Class C, Enrolled E where
S.snum=E.snum AND E.cname=C.cname);

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

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