15,218 views
46 votes
46 votes

Consider the set of relations shown below and the SQL query that follows.

Students: (Roll_number, Name, Date_of_birth)

Courses: (Course_number, Course_name, Instructor)

Grades: (Roll_number, Course_number, Grade)

Select distinct Name
from Students, Courses, Grades
where Students.Roll_number=Grades.Roll_number
	and Courses.Instructor = 'Korth'
	and Courses.Course_number = Grades.Course_number
	and Grades.Grade = 'A'

Which of the following sets is computed by the above query?

  1. Names of students who have got an A grade in all courses taught by Korth
  2. Names of students who have got an A grade in all courses

  3. Names of students who have got an A grade in at least one of the courses taught by Korth

  4. None of the above

3 Answers

Best answer
35 votes
35 votes
C. Names of the students who have got an A grade in at least one of the courses taught by Korth.
selected by
7 votes
7 votes

we can rewrite the given query as

select distinct(Name)

from Students 

where Roll_number in

                                    (select Roll_number

                                     from Grades

                                     where Grade='A' and Course_number in

                                                                                                      (select course_number 

                                                                                                        from Courses

                                                                                                        where instructor='Korth'))

Here the inner most query returns the course_number of courses taught by korth.

the 2nd inner query(highlighted in bold) returns the Roll_number of students who got a A grade in at least 1 course taught by korth.(Due to IN operator)

The outermost query returns the unique names of those students.

Hence option c is the answer.

query for option A:

select distinct(Name)

from Students where

Roll_number in

                          (select Roll _number

                           from Grades ,Courses

                           where Grade='A'  and Course.Course_number=Grade.Course_number and Instructor='Korth'

                           group by Roll_number 

                           having count(*)=(select  count(*) from Courses  where instructor='Korth'))

query for option B:

select distinct(Name)

from students

where Roll_number in

                                   (select Roll _number

                                    from Grades 

                                    where Grade='A' 

                                    group by Roll_number 

                                    having count(*)=(select  count(*) from Courses )

Hope this clears :)

0 votes
0 votes
In this question there are 2 conditions to be fulfilled. There is very slight chances that all selected tuples satisfy all test cases of both conditions. so all is not good for any query having 2 or more conditions to be fulfilled. So optimal and more safe answer is to mark option with atleast in it.
Answer:

Related questions

44 votes
44 votes
7 answers
4
Kathleen asked Sep 16, 2014
14,057 views
Which of the following functionality must be implemented by a transport protocol over and above the network protocol?Recovery from packet lossesDetection of duplicate pac...