The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
+24 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

asked in Databases by Veteran (52k points) | 2.9k views

1 Answer

+26 votes
Best answer
C. Names of the students who have got an A grade in at least one of the courses taught by Korth.
answered by Veteran (407k points)
selected by
1. yes, or do for 100 first apply conditions and then do next join. Query plan selection is a difficult problem and I do not remember the common strategies.

2. Sorry, no idea. I have seen some DBMS like MSSQL providing some intermediate results but as a novice it wasnt easy to use. May be someone expert in SQL can help.
thnx again
@Arjun  why not option a)?
^Because Korth can instruct more than one course, and nowhere in the query it says which one course. So it will take all the courses by Korth.

For example consider this diagram

If we consider that three students have the same name "RAM" (but different roll number ofcourse) and all the three students are registered to atleast one course by Korth. Now of the three students 2 have got an A grade in some course taken by korth while one student has not got an A grade in any course instructed by Korth. Note that since all student names are same and SELECT DISTINCT Name is used therefore output will have "RAM" only once. So this query does not exactly correspond to option (c). So can option (d) be a valid answer as per my argument?
Let me know your thoughts.
It's nowhere mentioned in the given query that we have to select atleast.

Then why can't I select A. It's also not possible to determine just by looking at the query.
infact distinct names of students.
0 Then in this question also the answer should be A but its D.

Answer is D there because of NOT EXIST

Related questions

Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
49,530 questions
54,139 answers
71,068 users