关系代数和SQL练习.doc

对下列关系模式分别用关系代数、和SQL实现下列查询理解下面几句话：1. SQL语言是具有很坚实数学基础的语言2. SQL语言是介于关系代数和关系演算之间的结构化查询语言3. 一个查询只要能用关系代数或关系演算实现，必能用SQL实现4. 一个查询即能用关系代数、关系演算、SQL实现5. 在SQL语言中，能用非EXISTS谓词实现的查询，均能用EXISTS谓词实现，反之不一定。1. 查询学生95001的所有信息。 关系代数：SQL语言：SELECT * FROM Student WHERE Sno=950012. 查询学生95001的姓名和所在系。 关系代数：SQL语言：方法一：SELECT Sname,Sdept FROM Student WHERE Sno=95001方法二：SELECT Sname,Sdept FROM StudentWHERE EXISTS ( SELECT * FROM Student SX WHERE Student.Sno=SX.Sno AND SX.Sno=95001)方法三：SELECT Sname,Sdept FROM StudentWHERE Sno IN ( SELECT Sno FROM Student WHERE Sno=95001)3. 查询选修了1号课的学生的学号。 关系代数：SQL语言：方法一：SELECT Sno FROM SC WHERE Cno=1方法二：SELECT Sno FROM SCWHERE EXISTS ( SELECT * FROM SC SCX WHERE SC.Sno=SCX.Sno AND SCX.Cno=1)方法三：SELECT Sno FROM SCWHERE Sno IN ( SELECT Sno FROM SC WHERE Cno=1)4. 查询选修了1号课的学生的姓名。 关系代数：SQL语言：方法一：SELECT Sname FROM Student,SC WHERE SC.Sno=Student.Sno AND Cno=1方法二：SELECT Sname FROM StudentWHERE EXISTS ( SELECT * FROM SC WHERE SC.Sno=Student.Sno AND Cno=1)方法三：SELECT Sname FROM StudentWHERE Sno IN ( SELECT Sno FROM SC WHERE Cno=1)5. 查询至少选修了1号课和3号课的学生的学号。 关系代数：或或：首先建立一个临时关系K：cno13然后求：Sno.Cno(SC)KSQL语言：方法一：SELECT SCX.Sno FROM SC, SC SCXWHERE SC.Cno=1 AND SCX.Sno=SC.Sno AND SCX.Cno=3方法：SELECT Sno FROM SCWHERE EXISTS (SELECT * FROM SC SCXWHERE SC.Cno=1 AND SCX.Sno=SC.Sno AND SCX.Cno=3)方法二：SELECT Sno FROM SCWHERE Cno=1 AND Sno IN (SELECT Sno FROM SC WHERE Cno=3)6. 查询至少选修了一门其直接先行课为5号课的学生学号。 关系代数：SQL语言：方法一：SELECT Sno FROM SC,CourseWHERE SC.Cno=Course.Cno AND Course.Cpno=5方法二：SELECT Sno FROM SCWHERE EXISTS ( SELECT * FROM Course WHERE SC.Cno=Course.Cno AND Course.Cpno=5) 方法三：SELECT Sno FROM SCWHERE Cno IN ( SELECT Cno FROM Course WHERE Cpno=5) 7. 查询至少选修了一门其直接先行课为5号课的学生姓名。 关系代数：SQL语言：方法一：SELECT Sname FROM Student,SC,CourseWHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno AND Course.Cpno=5方法二：SELECT Sname FROM StudentWHERE EXISTS ( SELECT * FROM SC WHERE Student.Sno=SC.Sno AND EXISTS (SELECT * FROM CourseWHERE SC.Cno=Course.Cno AND Course.Cpno=5)方法三：SELECT Sname FROM StudentWHERE Sno IN ( SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM CourseWHERE Cpno=5)8. 查询没有选修1号课程的学生姓名。 关系代数：SQL语言：SELECT Sname FROM StudentWHERE NOT EXISTS (SELECT * FROM SCWHERE Student.Sno=SC.Sno AND SC.Cno=1)9. 查询选修了全部课程的学生的学号和姓名。 关系代数：SQL语言：SELECT Sno,Sname FROM StudentWHERE NOT EXISTS (SELECT * FROM Course WHERE NOT EXISTS ( SELECT * FROM SCWHERE Student.Sno=SC.Sno AND SC.Cno=Course.Cno)10.查询最少选修了95002学生所选课程的学生学号。 关系代数：SQL语言：方法一：SELECT Sno FROM SCWHERE NOT EXISTS (SELECT * FROM Course WHERE EXISTS ( SELECT * FROM SC SCXWHERE SCX.Sno=95002 AND SCX.Cno=Course.Cno AND NOT EXISTS ( SELECT * FROM SC SCY WHERE SCY.Sno=SC.Sno AND SCY.Cno=Course.Cno )方法二：SELECT Sno FROM SCWHERE NOT EXISTS (SELECT * FROM SC SCX WHERE SCX.Sno=95002 AND NOT EXISTS ( SELECT * FROM SC SCY WHERE SCY.Sno=SC.Sno AND SCY.Cno=SCX.Cno)方法三：SELECT Sno FROM St