Students(sid:decimal(9,0), sname:char(20), rno:int,major:char(20)) Courses(cid:d
ID: 3609959 • Letter: S
Question
Students(sid:decimal(9,0), sname:char(20), rno:int,major:char(20))
Courses(cid:decimal(9,0), cname:char(20), fid:decimal(9,0),color:char(10), room:char(4))
Enrolled(sid:decimal(9,0), cid:decimal(9,0))
Faculty(fid:decimal(9,0), fname:char(20))
) Select the names of students with rno > 7
SELECT Students.sname
FROM Students
WHERE Students.rno>7;
Translating this into relational algebra would be:
PROJECTsname (SELECTrno>7(students))
2) Select the names of students enrolled in a red course
SELECT Students.sname
FROM Students, Courses, Enrolled
Where Students.sid = Enrolled.sid AND Couses.cid = Enrolled.cid ANDCourses.color=’red’;
So translating this into relational algebra would be:
PROJECTsname (( SELECTstudents.sid JOIN ( Students, Enrolled )) JOIN Courses [SELECTcname = 'red' (Enrolled JOIN Cources)])
3) Select the names of students enrolled in a green course
SELECT Students.sname
FROM Students, Courses, Enrolled
Where Students.sid = Enrolled.sid AND Couses.cid = Enrolled.cid ANDCourses.color=’green’;
Relational algebra:
Explanation / Answer
Students(sid:decimal(9,0), sname:char(20), rno:int,major:char(20))
Courses(cid:decimal(9,0), cname:char(20), fid:decimal(9,0),color:char(10), room:char(4))
Enrolled(sid:decimal(9,0), cid:decimal(9,0))
Faculty(fid:decimal(9,0), fname:char(20))
1) Select the names of students with rno > 7
SELECT Students.sname
FROM Students
WHERE Students.rno>7;
2) Select the names of students enrolled in a red course
SELECT Students.sname
FROM Students, Courses, Enrolled
Where Students.sid = Enrolled.sid AND Couses.cid =Enrolled.cid AND Courses.color=’red’;
3) Select the names of students enrolled in a green course
SELECT Students.sname
FROM Students, Courses, Enrolled