SQL练习2

Author: 张浩森 Date: Apr 29, 2018 Updated On: Apr 29, 2018
Categories:
Tags:

一、实验目的

​ 掌握复杂查询的使用方法。

​ 掌握视图的创建、视图的查询以及通过视图更新(插入、修改、删除)基本表中的数据。

二、实验任务

综合查询:

1、求不选修C语言课程的学生学号。

1
2
3
select sno from S where not exists(
select * from SC where sno = S.sno and cno = (
select cno from C where cname = 'C语言'));

2、查询平均成绩在60分以上的学生姓名。

1
2
select sname from S x where 60 <= (
select avg(grade) from SC y where x.sno = y.sno);

3、求这样的学生姓名:该学生选修了全部课程并且其中一门课在90分以上。

1
2
3
4
5
6
7
8
9
select sname from S where sno in (
select sno from SC group by sno having count(*) = (select count(*) from C) in(
select sno from SC group by sno having max(grade) > 90));

select sname from S where sno in(
select sno from SC where sno in (
select sno from S where not exists(
select * from C where not exists(
select * from SC where sno = S.sno and cno = C.cno))) group by sno having max(grade) > 90);

4、查询选修课程数在两门以上的学生姓名。(用连接查询和嵌套查询两种方法实现)

1
2
3
4
5
6
#嵌套查询
select sname from S where sno in (
select sno from SC group by sno having count(*) > 2);

#连接查询
select S.sname from S inner join SC on S.sno = SC.sno group by sname having count(*) > 2;

5、查询所有成绩都及格的学生人数。

1
2
select count(distinct sno) from SC where sno in(
select sno from SC group by sno having min(grade) >= 60);

6、查询成绩及格的学生人次。

1
2
select count(sname) from S where sname in(select sname from S x where 60 <= (
select avg(grade) from SC y where x.sno = y.sno));

7、查询所有学生都没有选修的课程名称。

1
2
select cname from C where cno not in (
select distinct cno from SC);

8、查询每个学生的平均成绩,并按平均成绩的降序排序。

1
select sname, avg(grade) from S,SC where S.sno = SC.sno group by SC.sno order by avg(grade) DESC;

9、查询每个同学的最高分,要求显示学号、姓名和分数等信息。

1
select S.sno, sname, max(grade) from SC,S where S.sno = SC.sno group by SC.sno;

10、查询选修同一门课程的学生学号。

1
select distinct sno from SC,C where SC.cno = C.cno;

11、查询不及格学生的学号、课程名、开课学期的信息。

1
2
select SC.sno, C.cname, SC.term from SC,C where SC.cno = C.cno and sno in (
select sno from SC group by sno having avg(grade) < 60);

12、按学号分组汇总总分高于100的学生记录,并按总分的降序排列。

1
select S.* from S,SC where S.sno = SC.sno group by SC.sno having sum(grade) > 100 order by sum(grade) DESC;

13、求恰好有两门课程不及格的学生信息。

1
2
select * from S where sno in (
select sno from sc where grade < 60 group by sno having count(cno)=2)

14、查询每门课程的最高分的学生记录。

1
2
select S.*, SC.cno from S,SC,C where S.sno = SC.sno and SC.cno = C.cno and grade in (
select max(grade) from SC where SC.cno = C.cno);

15、查询每个学生的最低分的课程记录。

1
2
select C.*, SC.sno from S,SC,C where S.sno = SC.sno and SC.cno = C.cno and grade in (
select min(grade) from SC where SC.sno = S.sno);

视图管理:

1、为学生表创建一个视图V_XSQK,以显示学生的基本信息,如学号、姓名、性别和出生年月。

1
create view V_XSQK as select sno,sname,ssex,sbirth from S;

2、创建视图V_SC,要求显示学生的姓名、选修的课程名和成绩。

1
create view V_SC as select S.sname,C.cname,SC.grade from S,C,SC where S.sno=SC.sno and C.cno=SC.cno;

3、创建视图V_ST,显示在1981-1-1以后出生的学生信息,包括学号,姓名,性别和出生年月。

1
create view V_ST as select sno,sname,ssex,sbirth from S where sbirth>'1981-1-1';

4、创建视图v_HG,显示成绩合格的学生信息,包括姓名、课程名和成绩。

1
create view v_HG as select S.sname,C.cname,SC.grade from S,C,SC, where S.sno=SC.sno and C.cno=SC.cno and SC.grade>=60;

5、创建分组视图V_GP,要求查询每个学生的平均成绩,包括姓名,平均成绩。

1
create view v_GP as select S.sname, avg(grade) from S,SC where S.sno = SC.sno group by SC.sno;

6、向视图V_XSQK中插入一条记录:‘2020206’,‘李红’,‘1983-12-3’。

1
insert into V_XSQK values ('2020206','李红','女','1983-12-3');

7、修改视图V_XSQK,将李红同学是出生年月改为1982-12-3。

1
update V_XSQK set sbirth='1982-12-3' where sno='2020206';

8、删除视图V_XSQK中李红同学的信息。

1
delete from V_XSQK where sno='2020206';

9、删除视图V_XSQK。

1
drop view v_XSQK;

10、创建视图V_PJ,查询平均成绩在60分以上的学生信息,包括姓名和平均成绩。

1
create or replace view V_PJ as select S.sname, avg(grade) from S,SC where S.sno = SC.sno group by SC.sno having avg(grade) > 60;

注意:用到的表结构及信息如下:

S:学生基本信息表;C:课程基本信息表;SC:学生选课信息表。

各表的结构以及数据如下所示:

表2.1 学生基本信息表(表名:S)

sno sname ssex sbirth Sdept
95001 李勇 1986/1/1 CS
95002 刘晨 1985/2/1 IS
95003 王敏 1886/10/4 MA
95004 张立 1985/6/8 IS

表2.2 课程基本信息表(表名:C)

cno cname cpno Ccredit
1 数据库 2 4
2 数学 3
3 信息系统 1 4
4 操作系统 5 3
5 数据结构 6 3
6 C语言 2

表2.3 学生选课信息表(表名:SC)

sno cno grade term
95001 1 92 1
95001 2 85 2
95001 3 87 3
95002 2 78 2
95002 3 84 3
95003 1 30 1
95004 2 58 2
95004 4 70 3
95002 5 60 4
95003 4 70 3