GATE CSE 1993 | Question: 23

Question

The following relations are used to store data about students, courses, enrollment of students in courses and teachers of courses. Attributes for primary key in each relation are marked by ‘*’.

Students (rollno*, sname, saddr)
courses (cno*, cname)
enroll(rollno*, cno*, grade)
teach(tno*, tname, cao*)

(cno is course number cname is course name, tno is teacher number, tname is teacher name, sname is student name, etc.)

Write a SQL query for retrieving roll number and name of students who got A grade in at least one course taught by teacher names Ramesh for the above relational database.

Comments

I can not understand my own statement after 1 month :D

Yes you r right meghna...
This is my 2nd attempt of your Query Satyajeet.. Forgive me if i m unable to correct this time also :p

Select s.rollno, s.sname

from( students as s JOIN Enroll as e ON s.rollno=e.rollno)

where e.grade='A' AND NOT EXIST ( select t.cno from teach as t   where t.tid="gdev-id-Ramesh"

EXCEPT

select A.cno from enroll as A   where e.rollno=A.rollno);

you can refer to https://www.geeksforgeeks.org/sql-division/ for better understanding...

---

For this type of sql query can we write by joining everything, I mean -

select roll no, sname from students as s, enroll as e, teach as t, where s.roll no= e.roll no AND grade = A AND e.cno=t.cno AND tname=ramesh.

Is it correct???

---

why it is tagged as relational calculus??

Answer 1

SELECT s.rollno, s.sname FROM students s natural join enroll e 
WHERE e.grade='A' AND e.cno in (SELECT cno from teach where tname='Ramesh')