关系代数练习题
- 格式：doc
- 大小：46.50 KB
- 文档页数：5
关系模式：
S(sno,sname,ssex,sdep,sage)
C(cno,cname,credit,cpno)
SC(sno,cno,grade)
(1) 查询选修了C2课程的学生学号和成绩。
πsno,grade（σcno=‘c2’（SC））SELECT sno,grade
FROM sc
WHERE Cno= 'c2'
(2) 查询选修了C2课程的学生学号和姓名.
πsno,sname（σcno=‘c2’（SC C））SELECT sno,sname
FROM student
WHERE EXISTS
( SELECT *
FROM SC
WHERE Sno = Student.Sno AND Cno= 'c2') SELECT student.sno,sname
FROM student,sc
WHERE student.sno=sc.sno and Cno= 'c2'
(3) 查询选修了数据库课程的学生学号、姓名和成绩。
πsno,sname,grade（σcname=‘数据库’
（S C SC ））
SELECT Sno, Sname
FROM Student
WHERE Sno IN
( SELECT Sno
FROM SC
WHERE Cno ＝
( SELECT Cno
FROM Course
WHERE Cname= '数据库' ) ) SELECT student.sno,sname,grade
FROM student,sc,course
WHERE student.sno=sc.sno and o=o and Cname= '数据库'
(4) 查询选修了C2或C4课程的学生学号。
πsno（σcno=‘c2’V cno=‘c4’（SC））SELECT distinct sno
FROM sc
WHERE cno='c1' or cno='c4'
查询选修了C2和C4课程的学生学号。
SELECT distinct scx.sno
FROM sc scx ,sc scy
WHERE scx.sno=scy.sno and o='c2' and o='c4' (5) 查询选修全部课程的学生学号。
πsno,cno(SC)÷πcno(C)
解法1:
SELECT sno
FROM SC
group by Sno
having count(*) = (
select count(*)
from course )
解法2:
SELECT Sno
FROM Student
WHERE NOT EXISTS
( SELECT *
FROM Course
WHERE NOT EXISTS
( SELECT *
FROM SC
WHERE Sno = Student.Sno
AND Cno = o ))
%%%%%%%%%查询选修全部课程的学生姓名
SELECT Sname
FROM Student
WHERE NOT EXISTS
( SELECT *
FROM Course
WHERE NOT EXISTS
( SELECT *
FROM SC
WHERE Sno = Student.Sno
AND Cno = o )) %%%%%%%%%%%%%%%%%%查询选修全部课程的学生学号
SELECT sno
FROM SC
group by Sno
having count(*) = (
select count(*)
from course )
%%%%%%%%%%%%%%%查询选修学号为“2”的学生所选全部课程的学生学号SELECT distinct Sno
FROM SC SCX
WHERE NOT EXISTS
( SELECT *
FROM SC SCY
WHERE SCY.Sno = '2 ' AND NOT EXISTS
( SELECT *
FROM SC SCZ
WHERE SCZ.Sno=SCX.Sno AND o=o))
%%%%%%%%%%%%%%%%查询选修学号为“2”的学生所选全部课程的学生学号和姓名SELECT sno,Sname
FROM Student
WHERE sno in (SELECT distinct Sno
FROM SC SCX
WHERE NOT EXISTS
( SELECT *
FROM SC SCY
WHERE SCY.Sno = '2' AND NOT EXISTS
( SELECT *
FROM SC SCZ
WHERE SCZ.Sno=SCX.Sno AND o=o)))
%%%%%%%%%%%%%%%%%查询选修了1课程的学生学号和姓名
SELECT sno,sname
FROM student
WHERE EXISTS
( SELECT *
FROM SC
WHERE Sno = Student.Sno AND Cno= '1')
%%%%%%%%%%%%查询选修了C2课程的学生学号和成绩
SELECT sno,grade
FROM sc
WHERE Cno= '1'
%%%%%%%%%%%%%查询选修了C2课程的学生学号和姓名
SELECT student.sno,sname
FROM student,sc
WHERE student.sno=sc.sno and Cno= '1'
%%%%%%%%%%%%%%%查询选修了数据库课程的学生学号，姓名和成绩SELECT student.sno,sname,grade
FROM student,sc,course
WHERE student.sno=sc.sno and o=o and Cname= '数据库'
%%%%%%%%%%%%%%%查询选修了C2或C4课程的学生学号SELECT distinct sno
FROM sc
WHERE cno='1' or cno='3'
%%%%%%%%%%%%%%%查询选修了C2和C4课程的学生学号SELECT distinct scx.sno
FROM sc scx ,sc scy
WHERE scx.sno=scy.sno and o='3' and o='2'。
