recategorized by
3,818 views
20 votes
20 votes

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.

recategorized by

5 Answers

18 votes
18 votes
select student.rollno, student.sname

From student natural join enroll on student.rollno=enroll.rollno

Where enroll.grade='A' AND enroll.cno in (select cno from teach where tname='Ramesh')
edited by
7 votes
7 votes

Select student.rollno,student.sname
From Student Natural Join Enroll
where enroll.grade='A' AND Enroll.cno in (Select from teach where tname='Ramesh')


Select student.rollno,student.sname
From Student , Enroll , Teach
where  Student. rollno. = Enroll.rollno. AND Enroll.grade='A' AND tname='Ramesh')


Students (rollno*, sname, saddr)  with rollno →sname, saddr is in BCNF
courses (cno*, cname) with cno→ cname is in BCNF
enroll(rollno*, cno*, grade) with rollno →  grade,cno and cno →  grade, rollno. is in BCNF
teach(tno*, tname, cao*) here Cao tno → tname, cao and  cao → tname,tno is in BCNF
edited by
1 votes
1 votes
select rollno. , sname

from student as s courses as c , enroll as e , teach as t

where t.tname="Ramesh" AND c.cno.=e.cno. AND e.rollno.=s.rollno. AND e.grade="A"
0 votes
0 votes
SELECT Students.rollno, sname

FROM Students

WHERE Students.rollno IN (SELECT enroll.rollno FROM enroll, teach WHERE tname = 'Ramesh' AND grade = 'A');

Related questions

30 votes
30 votes
4 answers
4
Kathleen asked Sep 29, 2014
4,524 views
Let $\left(\{ p,q \},*\right)$ be a semigroup where $p*p=q$. Show that:$p*q=q*p$ and$q*q=q$