in Databases recategorized by
3,787 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.

in Databases recategorized by
3.8k views

4 Comments

edited by

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.tname="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...

0
0
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???
0
0
why it is tagged as relational calculus??
1
1

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

4 Comments

The first query might print duplicate values and DISTINCT keyword cannot be used for multiple attributes. Here we need to print roll number and student name both.

I am getting this:

Select S.rollno, S.sname

From Students S

Where exists( Select E.cno from Enroll E, Teach T

 Where E .cno=T.cno and

T.tname='Ramesh' and

E.grade='A' and

E.rollno=S.rollno )

Please check if this gives correct result or is erroneous.

 

2
2

Can someone review is my query is correct?

Select S.rollno,S.sname from Students S

where S.rollno in 

( Select E.rollno from enroll E,Teach T

where E.grade='A' and E.cno=T.cno and T.tname='Ramesh')

0
0
Distinct keyword must be there rt???
0
0
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 comment

The question says:

Attributes for primary key in each relation are marked by ‘*’”.

(and NOT that attributes marked with * are the primary key of relation).

So we get primary key with multiple attributes for teach table as (tno, cao) i.e. composite key and (rollno, cno) as primary key for enroll table.

See this for the correct answer of 24).

0
0
1 vote
1 vote
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