SQL2012实验报告 - 图文

SELECT Student.* , SC.* FROM Student , SC WHERE Student.Sno= SC.Sno;

2. 查询每一门课的间接先修课（即先修课的先修课）

SELECT FIRST.Cno, SECOND.Cpno

FROM Course FIRST, Course SECOND WHERE FIRST.Cpno = SECOND.Cno;

3. 查询选修2号课程且成绩在80分以上的所有学生姓名、学号、课程名称、

成绩

SELECT Student.Sno, Sname FROM Student, SC

WHERE Student.Sno=SC.Sno AND SC.Cno= ‘2’ AND SC.Grade > 80;

嵌套查询

1. 查询与“刘晨”在同一个系学习的学生

SELECT Sno, Sname, Sdept FROM Student WHERE Sdept IN

(SELECT Sdept FROM Student

WHERE Sname=’刘晨’);

2. 找出每个学生超过他选修课程平均成绩的姓名和课程名称

select Sname,Cname from SC first,Student,Course where student.Sno=first.Sno and course.Cno=first.Cno and Grade>= (

select AVG(Grade) from SC second

where first.Sno=second.Sno );

集合查询

1. 查询计算机科学系的学生及年龄不大于19岁的学生

SELECT * FROM Student WHERE Sdept= 'CS' UNION

SELECT * FROM Student WHERE Sage<=19;

2. 查询选修课程1的学生集合与选修课程2的学生集合的交集

SELECT Sno FROM SC WHERE Cno=' 1 ' Intersect

SELECT Sno FROM SC WHERE Cno= ' 2 ';