given this database: Department (dcode, dname, chair) Course (dcode, cno, title,

Question

given this database:

Department (dcode, dname, chair)
Course (dcode, cno, title, units)
Prereq (dcode, cno, pcode, pno)
Class (class, dcode, cno, instr)
Faculty (ssn, name, dcode, rank)
Student (ssn, name, major, status)
Enrollment (class, ssn)
Transcript (ssn, dcode, cno, grade)

I cant seem to resolve the diffences between these 3 questions:

9. Find classes (class, dcode, cno) in which at least two students are enrolled
10. Find classes (class, dcode, cno) in which at most two students are enrolled
11. Find classes (class, dcode, cno) in which exactly two students are enrolled

Explanation / Answer

Hi, Lets forst start to understand the difference between at least, at most and exactly. We will take the reference of above question.

At least 2 students : This mean we need to find out the classes which have a minimum of 2 students and there is no limit to maximum students. So we can say that the classes which have 2 students or more than 2 students should come in this list.

At most 2 students: This means that we need to find all the classes that do not have more than 2 students. Here the max limit is 2. So all the classes that have 2 or less than 2 students will come in this list.

Exactly 2 students: All the classes that have exactly 2 students will come in this list.

Hope the difference is clear now.

Lets proceed with solving the query. To solve we need class from Emrolment table that have the count of students as required, which can be derviced using the ssn column of Enrolment table and then use use the result to get the details from class table (Since type of database is not mentioned, I am assuming it ot Oracle DB)

1. At lease 2 students.

select class, dcode, cno from class where class in (
select class from enrolment group by class having count(ssn)>=2);

2. At most 2 students

select class, dcode, cno from class where class in (
select class from enrolment group by class having count(ssn)<=2);

3. Exactly 2 students

select class, dcode, cno from class where class in (
select class from enrolment group by class having count(ssn)=2);

Hope your doubts are cleared. Please feel free to connect if required. Thanks.

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

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