目录
一、第1关:子查询一
1.1 任务描述
1、查询CS系学生选择的课程,列出学号,课程号,成绩
2、查询没有选C06(课程号)课程的同学的学号,姓名,性别
3、查询成绩最高的选课信息,列出学号,课程号和成绩。
1.2 SQL语句
SELECT sno,cno,grade FROM SC
WHERE sno IN (SELECT Sno FROM Student WHERE Sdept="CS");
SELECT sno,sname,ssex FROM Student
WHERE sno NOT IN (SELECT sno FROM SC WHERE Cno = "C06");
SELECT sno,cno,grade FROM SC
WHERE grade = (SELECT MAX(Grade) FROM SC);
二、第2关:子查询二
2.1 任务描述
1、查询CS系没有选择'DB'课程学生的姓名,列出学生姓名。
2、查询‘DB’课程考最高分的选课信息。列出学号,课程号,成绩
2.2 SQL语句
SELECT sname FROM Student WHERE sno NOT IN
(SELECT Sno FROM SC WHERE Cno IN (SELECT Cno FROM Course WHERE Cname='DB'))
AND Sdept='CS';
SELECT sno,cno,grade FROM SC WHERE grade =
(SELECT MAX(grade) FROM SC WHERE Cno IN (SELECT cno FROM Course WHERE Cname='DB'));
三、第3关:子查询三
3.1 任务描述
查询选修了先行课为'DB'的课程的学生,列出学生学号,姓名,性别,所在系。
3.2 SQL语句
SELECT sno,sname,ssex,sdept FROM Student WHERE Sno IN
(SELECT Sno FROM SC WHERE Cno =
(SELECT Cno FROM Course WHERE Cpno =
(SELECT Cno FROM Course WHERE Cname='DB')));
四、第4关:带子查询的增删改
4.1 任务描述
1、将'DB'课程不及格的成绩加5分。
2、删除'English'(课程名)课程CS系学生的选课记录。
3、为CS系添加必修课'c02'。(即为CS系没有选c02课程的学生选c02课程)
4.2 SQL语句
UPDATE SC SET Grade=Grade+5 WHERE Cno=
(SELECT Cno FROM Course WHERE Cname='DB')
AND Grade<60;
DELETE FROM SC WHERE Sno IN
(SELECT Sno FROM Student WHERE Sdept='CS')
AND Cno=(SELECT Cno FROM Course WHERE Cname='English');
INSERT INTO SC SELECT Sno,'C02',NULL FROM Student WHERE Sdept='CS'
AND NOT EXISTS(SELECT * FROM SC WHERE SC.sno=Student.Sno AND Cno='C02');