Aspire's Library

A Place for Latest Exam wise Questions, Videos, Previous Year Papers,
Study Stuff for MCA Examinations - NIMCET

Previous Year Question (PYQs)



Use the following schema of the academic institution relational database:

student (rollno, name, degree, year, sex, deptno, advisor)

department (deptid, name, hod, phone)

professor (empid, name, sex, startyear, deptno, phone)

course (courseid cname, credits, deptno)

enrollment (rollno, courseid, sem, year, grade)

teaching (empid, courseid, sem, year, classroom)

prerequisite (precourseid, courseid)

deptno is foreign key in the student, professor and course relations referring to deptid of department relation; advisor is a foreign key in the student relation referring to empid of professor relation; hod is a foreign key in the department relation referring to empid of professor relation; rollno is a foreign key in the enrollment relation referring to rollno of student relation; courseid is a foreign key in the enrollment, teaching relations referring to courseid of course relation; empid is a foreign key of the teaching relation referring to empid of professor relation; precourseid and courseid are foreign keys in the prerequisite relation referring to courseid of the course relation;

Which of the following queries would retrieve the roll number and names of students who have enrolled for all the prerequisite courses of the course number 324 ?







Solution

We need students who have enrolled in all prerequisite courses of course 324.

This is a “for all” type query.

In SQL, “for all” is usually written using double NOT EXISTS.

Meaning of option (c):

For a student s, there should not exist any prerequisite course p of course 324 such that the student has not enrolled in that prerequisite course.

So, option (c) checks:

No prerequisite course of 324 is missing from the student's enrollment.

Option (a) uses ANY, so it checks only at least one prerequisite course.

Option (b) uses ALL incorrectly, because one enrollment course cannot be equal to all different prerequisite course IDs at the same time.

Option (d) uses EXISTS, so it checks only at least one matching prerequisite enrollment, not all.


Online Test Series,
Information About Examination,
Syllabus, Notification
and More.

Click Here to
View More


Online Test Series,
Information About Examination,
Syllabus, Notification
and More.

Click Here to
View More

Ask Your Question or Put Your Review.

loading...