国开学习网电大数据库应用技术第四次形考作业实验答案

实验目的：通过使用SSMS工具，练习对数据库中的数据表进行单表查询、多表连接查询、子查询。对数据表中的数据进行更改和删除等操作。

实验要求：将相关的SQL 语句和运行结果的截屏保存在文件中，或填写在下面的实验报告中，并通过网络提交。

实验内容：

使用SSMS工具，在“可用数据库”中选中Students数据库，完成如下实验。

1. 单表查询。写出实现如下查询的SQL语句。

(1)  查询学生选课表中的全部数据。

SELECT * FROM SC

(2)  查询计算机系的学生的姓名、年龄。

SELECT Sname,Sex FROM Student WHERE Sdept='计算机系'

(3)  查询成绩在70～80分的学生的学号、课程号和成绩。

SELECT Sno,Cno,Grade FROM SC where Grade BETWEEN 70 AND 80;

(4)  查询计算机系年龄在18～20岁男生的姓名、年龄。

SELECT Sname,Sage from Student where  Sex='男' AND Sage>18 AND Sage<20

(5)  查询C001课程的考试成绩最高分。

SELECT max(Grade) from SC where Cno='C001'

(6)  查询计算机系学生的最大年龄和最小年龄。

SELECT max(Sage) '最大年龄',min(Sage) '最小年龄' from Student where Sdept='计算机系'

(7)  统计各系的学生人数。

SELECT Sdept,count(Sno)'人数' from Student GROUP BY Sdept;

(8)  统计每门课程的选课人数和考试成绩最高分。

SELECT cno,count(Sno) '选课人数',max(Grade)'最高成绩' FROM SC GROUP BY Cno;

(9)  统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果。

SELECT sno,count(Cno) '选课门数', sum(Grade) '总成绩' FROM SC GROUP BY Sno ORDER BY 2 ASC;

(10)查询总成绩超过200分的学生，要求列出其学号和总成绩。

SELECT sno, sum(Grade) '总成绩' FROM SC GROUP BY Sno HAVING sum(Grade)>200;

(11)查询选课门数超过2门的学生的学号、平均成绩和选课门数。

SELECT top 2 Sno,count(cno) '选课门数' from SC GROUP BY Sno ORDER BY 2 DESC;

2. 多表连接查询。写出实现如下查询的SQL语句。

(12)  查询选了C002课程的学生的姓名和所在系。

SELECT Sname,Sdept from SC,Student where SC.Sno=Student.Sno AND Cno='C002'

(13)  查询成绩80分以上的学生的姓名、课程号和成绩，并按成绩降序排列结果。

SELECT Sname,Cno,Grade FROM SC,Student WHERE Grade>80 AND Student.Sno = SC.Sno ORDER BY Grade DESC

(14)  查询计算机系男生选修了“数据库基础”的学生的姓名和成绩。

select sname,ssex,grade from student s join sc on s.sno = sc.sno

　　　　join course c on c.cno = sc.cno

　　　　where sdept = '计算机系' and ssex = '男' and cname = '数据库基础'

(15)  查询学生的选课情况，要求列出每位学生的选课情况（包括未选课的学生），并列出学生的学号、姓名、课程号和考试成绩。

SELECT Student.Sno,Sname,Cno from SC,Student WHERE Student.Sno= SC.Sno

(16)  查询哪些课程没有人选修，要求列出课程号和课程名。

SELECT Course.Cno,Cname from SC,Course WHERE SC.Cno= Course.Cno AND Sno IS NULL;

(17)  查询计算机系没有选课的学生，列出学生的姓名。

SELECT Sname FROM Student,SC WHERE Student.Sno=SC.Sno AND Cno is NULL AND Sdept='计算机系'

3. 使用TOP和CASE的查询。写出实现如下查询的SQL语句。

(18)  列出“数据库基础”课程考试成绩前三名的学生的学号、姓名、所在系和考试成绩。

SELECT top 3 Student.Sno,Sname,Sdept,Grade from SC,Course,Student WHERE Student.Sno = SC.Sno AND SC.Cno=Course.Cno  and Cname='数据库基础' ORDER BY Grade DESC;

(19)  查询Java考试成绩最低的学生的姓名、所在系和Java成绩。

SELECT  Sname,Sdept,Grade  from SC,Course,Student WHERE Student.Sno = SC.Sno AND SC.Cno=Course.Cno and Cname='java' order by Grade asc limit 1

(20)  查询选修了Java的学生学号、姓名、所在系和成绩，并对所在系进行如下处理：

当所在系为“计算机系”时，显示“CS”；

当所在系为“信息管理系”时，显示“IS”；

当所在系为“通信工程系”时，显示“CO”；

对其他系，均显示“OTHER”。

select s.sno 学号,sname 姓名, case sdept

　　　　when '计算机系' then 'CS'

　　　　when '信息管理系' then 'IS'

　　　　when '通信工程系' then 'CO'

　　　　else 'OTHER'        

　　　　end as 所在系,grade 成绩

　　　　from student s join sc on s.sno = sc.sno

　　　　join course c on c.cno = sc.cno

　　　　where cname = 'Java'

4. 子查询。写出实现如下查询的SQL语句。

(21)  查询选修了C001课程的学生姓名和所在系。

select sname,sdept from student where sno in(

　　　　　select sno from sc where cno = 'C001')

(22)  查询计算机文化学考试成绩在80分以上的学生的学号和姓名。

select sno,sname from student where sno in((

　　　　　select sno from sc where grade > 80

　　　　　and cno in (select cno from course

　　　　　where cname = '计算机文化学'))

(23)  查询计算机文化学考试成绩最高的学生姓名和所在系。

select sname,sdept from student s join sc on s.sno = sc.sno

　　　　　Join course c on c.cno = sc.cno

　　　　　where grade = (

　　　　　select max(grade) from sc join course c on c.cno = sc.cno

　　　　　where cname = '计算机文化学')

　　　　　and cname = '计算机文化学'

(24)  查询年龄最大的男生的姓名和年龄。

select sname,sage from student

　　　　Where sage = (select max(sage) from student and ssex = '男')

　　　　　　and ssex = '男'

(25)  查询C001课程的考试成绩高于C001课程的平均成绩的学生的学号及其C001课程考试成绩。

select sno,grade from sc where cno = ' C001'

　　　　　And grade > (select avg(grade) from sc where cno = ' C001')

5. 数据更改。写出实现如下操作的SQL语句。

(26)  将C001课程的考试成绩加10分。

update sc set grade = grade + 10 where cno = 'C001'

(27)  将计算机系所有选修了“计算机文化学”课程的学生考试成绩加10分，分别用子查询和多表连接形式实现。

1）子查询

　　　update sc set grade = grade + 10

　　　where sno in(

　　　select sno from student where sdept = '计算机系')

　　　and cno in(

　　　select cno from course where cname = '计算机文化学')

2）多表连接

　　　update sc set grade = grade + 10

　　　from sc join student s on sc.sno = s.sno

　　　join course c on c.cno = sc.cno

　　　where sdept = '计算机系' and canem = '计算机文化学'

6. 数据删除。写出实现如下操作的SQL语句。

(28)  删除考试成绩低于50分的学生的选课记录。

delete from sc where grade < 50

(29)  删除信息管理系考试成绩低于50分的学生的该门课程的选课记录，分别用子查询和多表连接形式实现。

1）用连接查询实现

　　　delete from sc from sc join student s on s.sno=sc.sno

　　　where sdept = '信息管理系' and grade < 50

2）用子查询实现

　　　delete from sc where sno in (

　　　     select sno from student where sdept = '信息管理系' )

(30)  删除Java考试成绩最低的学生的Java选课记录。

delete from sc

　　　where grade = (

　　　     select min(grade) from sc

　　　join course c on c.cno = sc.cno

　　　where cname = 'Java')

　　　and cno in(

　　　     select cno from course where cname = 'Java')