![](https://i-blog.csdnimg.cn/blog_migrate/4570618219fdc9c2892e7f942aca7698.png)
1. 创建学生表 S(命名格式“姓名拼音_三位学号_s”,如 LBJ_023_s)并插入数据
create table LYL_116_s (S# varchar(4) primary key,
Sname varchar(9), Sage int, Ssex varchar(2))
insert into LYL_116_s values('S1','张无忌','22','男')
insert into LYL_116_s values('S2','郭靖','55','男')
insert into LYL_116_s values('S3','杨过','39','男')
insert into LYL_116_s values('S4','小龙女','42','女')
insert into LYL_116_s values('S5','令狐冲','28','男')
2. 创建课程表 C(命名格式“姓名拼音_三位学号_c”,如 LBJ_023_c) 并插入数据
![](https://i-blog.csdnimg.cn/blog_migrate/8b9b43ad2752c219bc33a1a47f582fb5.png)
3.
创建选课表
SC(
命名格式
“
姓名拼音
_
三位学号
_sc”
,如 LBJ_023_sc) 并插入数据
![](https://i-blog.csdnimg.cn/blog_migrate/3e1000ee5ef98f1b758f6f38e5f91fed.png)
4.
在实验报告中贴出查询语句及其执行结果
1
)列出所有学生的姓名,选课名称,及其成绩
select * s.Sname, c.Cname, sc.Grade
from LYL_116_s s, LYL_116_c c, LYL_116_sc sc
where s.S# = sc.S# and c.C# = sc.C#
![](https://i-blog.csdnimg.cn/blog_migrate/2f6687369facd9841a990217709e6d1b.png)
2
)列出所有学生的姓名,及其平均成绩
select cs.Sname'名字', avg(cs.Grade)'平均成绩'
from (select s.Sname, c.Cname, sc.Grade
from LYL_116_s s, LYL_116_c, LYL_116_sc
where s.S# = sc.S# and c.C# = sc.C#) cs group by cs.Sname
3
)检索选修课程号为
C5
的学生学号和成绩
select c.C#, sc.Grade from LYL_116_c c, LYL_116_sc sc
where c.C# = sc.C# and c.C# = 'C5'
4
)检索选修课程名称为
“
九阴真经
”
的学生学号和姓名
select sc.S#, S.Sname from
(select sc.S# from (select C# from LYL_116_c where Cname = '九阴真经')
s, LYL_116_sc sc where s.C# = sc.C#) cs, LYL_116_s s
where cs.S# = s.S#
5
)检索选修课程号为
C1
或
C5
的学生学号
select sc.S# from LYL_116_sc sc where sc.C# = 'C1' or sc.C# = 'C5'
6
)检索选修课程号为
C1
和
C8
的学生学号
select LYL_116_s.S# from LYL_116_s where S# IN(select S# from(
select S# from LYL_116_sc where LYL_116_sc.C#='C1' union all
select S# from LYL_116_sc where LYL_116_sc.C#='C8')
group by S# having COUNT(*)=2)
7
)检索不选修课程号
C8
的学生姓名和年龄
select s.Sname, s.Sage from LYL_116_s s,
(select sc.S# from LYL_116_sc sc where sc.C#='C8') cs
where s.S# != cs.S#