SQL练习题（2）

数据库开发

设教务管理系统中有3个基本表：

学生信息表 S(SNO, SNAME, AGE, SEX) ，其属性分别表示学号、学生姓名、年龄和性别；

课程信息表 C(CNO, CNAME, CTEACHER) ，其属性分别表示课程号、课程名称、任课老师姓名；

选课信息表 SC(SNO, CNO, SCGRADE)，其属性分别表示学号、课程号、课程成绩。

根据要求写出合适的SQL语句（MSSQL环境）：

（1）分别列出男女性别人数

select sum(case SEX when '男' then 1 else 0 end) 男性人数,
       sum(case SEX when '女' then 1 else 0 end) 女性人数
from S

select SEX 性别,count(*) 人数
from S
group by SEX

（2）将SC表中每门课的平均成绩插入到另外一个已经存在的表SC_C(CNO,CNAME,AVG_GRADE)中，其中AVG_GRADE表示的是每门课程的平均成绩。

insert into SC_C
select SC.CNO, CNAME, avg(SCGRADE)
from SC, C
where SC.CNO = C.CNO
group by SC.CNO,CNAME

注：如果SC_C表不存在，则语句为：

select SC.CNO CNO, CNAME CNAME, avg(SCGRADE) AVG_GRADE
into SC_C
from SC, C
where SC.CNO = C.CNO
group by SC.CNO,CNAME

—- select into 会自动复制表结构，into后跟的表一定是当前不存在的，而且select 后除 * 外，必须有命名。

（3）找出选修课程数超过三门的学生学号与姓名

select S.SNO, SNAME
from S, SC
where S.SNO = SC.SNO
group by S.SNO,SNAME
having count(S.SNO) > 3

select SNO,SNAME
from S
where SNO in(select SNO
             from SC
             group by SNO
             having count(SNO) > 3)

（4）从SC表中把"江林"老师的女学生选课记录删除。

delete from SC
where SNO in(select SNO
             from S
             where SEX = '女')
and   CNO in(select CNO
             from C
             where CTEACHER = '江林')

（5）规定女同学选修"兰才"老师的课程成绩都应该在80分以上（包含80分）

用assertion写一个check语句最方便，可惜MSSQL不支持，所以用触发器来实现：

create trigger tgr_8 on SC
for insert, update
as
if @@rowcount = 0
	return
if exists(select SC.SNO
          from S,SC,C
          where S.SNO = SC.SNO and SC.CNO = C.CNO and
                S.SEX = '女' and C.CTEACHER = '兰才' and
                SCGRADE < 80)
begin
	rollback transaction
	print '此操作违反了的约束'
    print '系统已回滚了相关操作'
end

（6）找出没有选修过"兰寿"老师课程的所有学生姓名

select SNAME
from S
where SNAME not in(select SNAME
                   from (S inner join SC on S.SNO = SC.SNO)
                   inner join C on SC.CNO = C.CNO
                   where CTEACHER = '兰寿')

select SNAME
from S
where not exists(select 1
                 from SC,C
                 where SC.CNO = C.CNO and SC.SNO = S.SNO and
                 CTEACHER = '兰寿')

（7）找出有两门以上 （含两门） 成绩不及格的学生的姓名及平均成绩

select SNAME,AVG_SGRADE = avg(SCGRADE)
from S inner join SC on S.SNO = SC.SNO
where S.SNO in (select SNO from SC
                where SCGRADE < 60
                group by SNO
                having count(*) >= 2)
group by SNAME

（8）找出既选修过"高等数学"又选修过"数据结构"的学生姓名

select SNAME
from S
where sno in(select sno
             from (select SNO, CNAME
                   from SC, C
                   where SC.CNO = C.CNO)A
             group by sno
             having sum(case CNAME when '高等数学' then 1 when '数据结构' then 1 else 0 end) > 1)

或

select SNAME
from S,(select SNO
        from SC inner join C on SC.CNO = C.CNO
        where CNAME in('高等数学','数据结构')
        group by SNO
        having count(*) > 1)A
where S.SNO = A.SNO

（9）找出"高等数学"成绩比"大学语文"成绩高的同学，及这两门课的成绩

select SNAME, 高等数学,大学语文
from S,(select  sc1.SNO, sc1.SCGRADE 高等数学, sc2.SCGRADE 大学语文
        from SC sc1, C c1, SC sc2, C c2
        where sc1.CNO = c1.CNO and sc2.CNO = c2.CNO and
             sc1.SNO = sc2.SNO and
             c1.CNAME = '高等数学' and c2.CNAME = '大学语文' and
             sc1.SCGRADE >= sc2.SCGRADE)A
where S.SNO = A.SNO

（10）列出所选课程均及格（大于等于60分）的同学的姓名、课程、成绩

select SNAME, CNAME, SCGRADE
from S, C,(select *
           from SC
           where SNO not in(select distinct SNO
                            from SC
                            where SCGRADE < 60))a
where S.SNO = a.SNO and C.CNO = a.CNO

注：一般的思路都是先写出嵌套查询句，再补充完全，思考由近及远。 4 ~ 10 都有体现。8的技巧最强，有多个自连接的运用，将纵向比较转化为SQL更为擅长的横向比较，不过要注意命名和连接条件。
​ 如果觉得上述嵌套SQL语句可读性不好的话，可以用CTE语法

声明：以上答案，仅供参考，远远不是最佳的写法。