GATE CSE 2003 | Question: 86

Question

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?

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

• B. Names of students who have got an A grade in all courses

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

• D. None of the above

Comments

Detailed Video Solution: https://youtu.be/zb6vXpZVo1E?t=3035&feature=shared

---

nice explaination sir

---

But in this question if there are two students with same name then it won't work so answer should be d as this is not the correct sql query

Answer 1

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

Comments

@Arjun sir your sql query to the question “students who will be selected for getting grade 'A' in all the subjects taught by korth” is wrong. That query will also select those students who did not enroll in any korth course or if they enroll in some courses then they got grade A. 

The correct query will be :

Select S.Name from Students S where not exists ( Select Course_number from Courses where Instructor=’korth’ EXCEPT ( Select g.Course_no from Grades g where S.Roll_number=g.Roll_number and grade=’A’ ) )

---

@Shukla_ yes, you are correct. I have edited that query.

---

Thanks @srestha mam.

Answer 2

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 :)

Comments

Select distinct name 
From students s
where not exists 
(select course_no from courses where instructor ='Korth'
  Minus
 select course_no from grades g ,courses c where g.grade='A' 
 and g.rollno=s.rollno and c.course_no =g.course_no)

 

This would work fine for A as well, right?

 

---

Yes this works...

For each student 'x', the inner query gives the multi set of course numbers which were taught by korth but 'x' haven't got A grade in that course..

If it is empty, outer query gives that name..

Answer 3

Complete detailed Solution: Detailed Solution

Answer 4

5

Answer 5

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 6

Lets understand by brute force way ...

we can rearrange  Students.roll_no =Grades.Roll_number ∧ Courses.course_number=Grades.Corse_number   since this condition is of JOIN and the rest two conditions of KORTH and Grade 'A' can be considerd together ... Bascially, They together are like T ∧ T then  select DISTNCT name ... Here , Distinct will means there will not be any repitions of tuple .    

Now i have drawn table to make things more clear ⬇️