2.6k views

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)

Select distinct Name
from Students, Courses, Grades
and Courses.Instructor = 'Korth'
and Courses.Course_number = Grades.Course_number


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 | 2.6k views

C. Names of the students who have got an A grade in at least one of the courses taught by Korth.
answered by Veteran (384k points)
selected
0

thanx arjun sir, just clear my one more silly question :P :P (just to clarify),

1. if we have 10 tuples each in all the 3 relations , then the above two queries during its computation , produces an intermediate cartesian product, that has 1000 tuples and then applies these conditions right ??

2. is it anyway possible to see that  intermediate cartesian product result(using the db tools like sql fiddle), so that we could understand sql more clearly (because thats how we get confused between ("at least" , "any" and "all " keywords)??

+2
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.
+1
thnx again
0
@Arjun  why not option a)?
0
^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.
0

For example consider this diagram

0
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.
0
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.
0
infact distinct names of students.
0

https://gateoverflow.in/2089/gate2014-3-54 Then in this question also the answer should be A but its D.

1
2