Given the following schema. Primary keys are in italics/underlined and foreign k

Question

Given the following schema. Primary keys are in italics/underlined and foreign keys are bold. The meaning of the relations is straightforward. Student(snum, sname, major, level, age) Class(cname , time, room, facid) Enrolled(snum, cname, grade) Faculty(facid, fname, deptid) Department(deptid, deptname, numFaculty) Write SQL queries to answer the following questions: Find the names of all juniors (level = JR) who are enrolled in a class taught by faculty 'Sam Eric'. 5.2). Find the name of the oldest student. 3 5. 3 3) For each class, list the class name and the number of students enrolled in the class. ) For each class list tl

Explanation / Answer

Student(snum,sname,major,level,age)

Class(cname , time , room , facid)

Enrolled(snum , cname , grade )

Faculty(facid , fname , deptid)

Department(deptid , deptname , numFaculty)

1.

select distinct s.sname

from Student s , Class c , Enrolled e , Faculty f

where s.snum = e.snum and c.cname = e.cname and c.facid = f.facid and s.level = 'JR' and f.fname = 'Sam Eric';

Above query performs joins among 4 tables mentioned based on where condition and returns the result.

2.

select sname

from Student

where age = (select max(age) from Student );

First the inner query is executed which gives maximum age of student then outer query is executed which returns sname of the student having max age.

3.

select cname , count(snum)

from Enrolled

group by cname;

It returns class name and number of students enrolled in that class.

group by is used to group the result by cname.

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

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