2.3k 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
0
can someone explain why it is not A???
0
option a is said if person tuaght by all couses teach by korth then only slect but query slect if a person there who join course taught by korth need not all course.

In short ur case fail when studnt attend all couse taught by korth expect one... ( if korth taught n course then student join n-1 ).
+1
how do we evaluate the predicate??all conditions are neccessary right??there is "and" between the conditions so all should be true.According to me the above query must select all tuples where korth is the instructor of the course and grade is A.May be i am not able to understand how predicate can be solved.Plz help.
0
yes, you are correct. And that is option C rt?
0
i am sorry bt i just can't understand how it is option c.
0
make any table with random input and for one student assign all korth subject and for remaining student assign one only korth ..u get the ans why not a is ans..

ans will be sid of studnt who register for any course of korth (one or more)
+7
Also as distinct is used, whether student take single course of 10000, only one result will come out. So C is correct !
+20

option a is telling us to select a student only if he gets an A grade in all the subjects taught by KORTH.

so student need to get A grade in all subjects taught by korth.

for eg.

suppose Korth teaches 3 subjects Dbms, OS and Algo and a student1 gets A grade in DBMS and OS but not in Algo. so student1 won't be selected if u choose option A.
but this is not correct.

If a student gets A grade in any sub. taught by Korth should be selected.
so the option is C

+2
i have one question . please give a query for students who will be selected for getting grade 'A' in all the subjects taught by korth (so that it'd make sense)?

anyone plz ??

0
i think then you need to apply group by course instructor =korth having grades.grade=A
+6

@khush No. Never use having unless we need a group property like MAX or a property of attribute used in group by.

Select distinct Name
from Students
where Students.Roll_number NOT IN
and Courses.Instructor = 'Korth'
and Grades.Grade != 'A');
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?
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.

1
2