2.9k 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
and Courses.Instructor = 'Korth'


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

C. Names of the students who have got an A grade in at least one of the courses taught by Korth.
selected
+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?
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.

0
Answer is D there because of NOT EXIST

1
2
3
4
5
6