SQL语句练习02

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) 并插入数据

3. 创建选课表 SC( 命名格式 “ 姓名拼音 _ 三位学号 _sc” ，如 LBJ_023_sc) 并插入数据

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#

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#