数据库：增、删、改、查操作

数据库表详情：
学生表（STU）：

课程表（COU）：

学生选修课程表（SC）：

专业表（MAJOR）：

一、查询操作
1．查询全学院所有学生的信息

select * from stu

2．查询所有学生的学号与姓名以及性别。
要求：性别用“男”或“女”来显示，提示：使用T-SQL的case…when语句（参看联机帮助）

select sno,sname,case sex 
    when 1 then '男'
    when 0 then '女'
end
    AS 'sex'
from stu

3．查询女生的学号与姓名

select sno '学号',sname '姓名' from stu where(sex = 0) 

4．查询女生且年龄19以上学生信息

select sno '学号',sname '姓名',sex '性别',mno '专业',2020-year(birdate) '年龄',memo '备注'from stu where(sex = 0 and 2020-year(birdate)>19)

5．查询年龄18-20的学生信息

select sno '学号',sname '姓名',sex '性别',mno '专业',2020-year(birdate) '年龄',memo '备注'from stu where (2020-year(birdate)>=18 and 2020-year(birdate) <=20)

6．查询所有姓陈的学生信息

select sno '学号',sname '姓名',sex '性别',mno '专业',birdate '出生日期',memo '备注'from stu where (sname like '陈%')

7．查询计算机、软件专业的学生信息

select sno '学号',sname '姓名',sex '性别',mname '专业' from stu,major where(stu.mno = major.mno and (mname = '计算机工程'or mname = '软件工程')) order by sno 

8．查询全学院的学生成绩平均分（注：有的学生可能没有选修课程,平均分为0）

select stu.sno 学号,ifnull(avg(grade),0) 平均成绩
from stu left join sc on stu.sno = sc.sno
group by stu.sno

9．查询各专业的学生成绩平均分（注：有的学生可能没有选修课程）

select mname 专业,ifnull(avg(grade),0) 平均成绩
from major left join stu on major.mno = stu.mno left join sc on stu.sno = sc.sno 
group by mname

10．查询平均分多于75分的学生学号

select sno 学号,avg(grade) 平均成绩
from sc
group by sno
having avg(grade)>75

11．查询‘C001’课程未登记成绩的学生学号

select sno
from sc
where cno = 'C001' and grade is NULL

12．查询选修‘C语言’课程的学生的学号
1）采用连接查询

select sname 姓名,grade 成绩
from stu,cou,sc
where stu.sno = sc.sno and sc.cno = cou.cno and cname = 'C语言'
order by grade desc

2）采用嵌套查询

select SNO from dbo.SC where CNO in (select CNO from dbo.COU where (CNAME = 'C语言') )

3）采用EXIST查询

select sname 姓名,grade 成绩 from stu,sc where exists(select * from sc where sc.sno = stu.sno and sc.cno in (select cno from cou where cname = 'C语言')) order by grade,sname desc

13．查询未选修‘C语言’课程的学生的学号（not exist实现）

select sno 学号,sname 姓名
from stu
where not exists
(select * from sc,cou where stu.sno = sc.sno and sc.cno = cou.cno and cname = 'C语言')
order by stu.sno

15．查询与‘张三’在同一个专业的学生信息

select sno 学号, sname 姓名
from stu 
where mno in
(select mno from stu where sname = '张三')
and sname != '张三'

16.按各位同学自已的姓名查询选修了哪几门课程，已获得的学分。
注：至少两种命令，并且需截图。

select * from(
select cou.cno 课程号,cname 课程,grade 成绩
from cou,sc,stu
where sname = '张三' and stu.sno = sc.sno and sc.cno = cou.cno
union 
select '张三','总学分',
(select sum(credit) 
from cou,stu,sc 
where sname = '张三' and stu.sno = sc.sno and sc.cno = cou.cno and grade >= 60)
) a
order by a.课程号

17．查询至少选修的’C001’与’C002’课程的学生学号
1）使用SC表的自连接完成

select a.sno '学号' 
from (select * from sc where (cno = 'C001')) a 
inner join 
(select * from sc where (cno = 'C002')) b 
on a.sno = b.sno

2）使用INTERSECT（交）完成

select sno '学号' from sc where (cno = 'C001')
intersect
select sno '学号' from sc where (cno = 'C002')

18．查询S001学号选修而S003学号未选修的课程号
（提示：使用EXCEPT）

select a.cno '课程号'
from 
(select cno from sc where sno = 'S001') a
left outer join
(select cno from sc where sno = 'S003') b
on a.cno = b.cno
where b.cno is NULL

select cno '课程号' from sc where sno = 'S001'
except
select cno '课程号' from sc where sno = 'S003'

19. 查询S001学号、S003学号都选修了哪些课程
    （试验：UNION）

select cou.cno 课程号,cname 课程
from cou,sc
where cou.cno = sc.cno and sc.sno = 'S001'
union
select cou.cno 课程号,cname 课程
from cou,sc
where cou.cno = sc.cno and sc.sno = 'S003'
order by 1

20．查询每个同学超过他选修的平均成绩的课程名。
1）用相关子查询实现

select sno 学号,cname 课程名,grade 成绩
from cou,sc a 
where cou.cno = a.cno and 
(a.grade > (select avg(b.grade) from sc b where a.sno = b.sno))

2）使用派生表实现。

select a.sno 学号,cname 课程名,grade 成绩
from cou,sc a,(select cno,avg(grade) x from sc group by sc.cno) as b
where a.cno = b.cno and a.grade > b.x and cou.cno = a.cno

21．查询平均分高于80分的学生姓名

select sname,avg(grade) 
from stu,sc
where sti.sno = sc.sno and sno in(select sno from sc group by (sno) 
having (avg(grade)>80))

22．查询平均分高于60分的课程的课程名。

select cno '课程号',cname '课程名' 
from cou 
where cno in (select cno from sc group by cno having (avg(grade)>60))

23.查询‘C语言’课程成绩最高的前三名同学

select sname '姓名',grade '成绩'
from stu,sc,cou 
where(sc.cno = cou.cno and cname = 'C语言' and sc.sno = stu.sno) 
order by grade desc 
limit 3

24.查询平均成绩最高的前3名同学的学号，姓名，性别及年龄。

Select top 3 stu.sno as 学号,sname as 姓名,sex as 性别,2020-year(birdate) as 年龄,平均成绩 
from stu,(select sno,avg(grade) as 平均成绩 from sc group by sno ) x 
where stu.sno=x.sno order by 平均成绩 desc

25．检索C003课程成绩最高二人学号，姓名与成绩。并将结果保存于max_C003临时表中
注：结果截图

Select top 2 stu.sno,sname,grade
from stu,sc
where cno = 'C002' and stu.sno = sc.sno
order by grade desc

26．查询选修了张老师所讲授的所有课程的学生。

select sname 
from stu 
where not exists (select * from cou where teacher = '张老师' and not exists (select * from sc where sc.sno = stu.sno and sc.cno = cou.cno))

（二）添加
1.对学生表添加一条记录，记录(SO12,周强，女)等

insert into stu values('S012','周强',0,NULL,NULL,NULL)

2.为上述学生添加二条选课记录。

insert into sc values('S012','C001',90);
insert into sc values('S012','C002',NULL);

3．为软件专业创建一个学生简表，用于点名。

insert into softstu (sno,sname) 
select sno,sname 
from stu 
where mno = '02'

4．检索所授每门课程平均成绩均大于70分的教师姓名，并将检索的值送往另一个已在的表faculty(tname)

insert into faculty
select teacher
from cou c1
where cno in(
select sc.cno
from sc
group by sc.cno
having avg(grade)>70
)
group by teacher
having count(c1.teacher)=(select count(teacher) from cou c2 where c1.teacher = c2.teacher)

5.创建表totalcredit(sno,totalcredit),为该表插入各同学当前获得总学分

CREATE TABLE totalcredit ( sno char(4) NOT NULL, totalcredit smallint DEFAULT 0 )
insert into totalcredit(sno,totalcredit) 
select distinct stu.sno,ifnull(sum(credit),0) 
from stu left join 
(select sc.sno,credit from sc left join cou on sc.cno = cou .cno and grade>60)a on stu.sno = a.sno
group by stu.sno 
order by stu.sno

（三）删除
(1)在SC中删除尚无成绩的选课元组

delete from sc where grade is null

(2)把选修’C语言’课程的女同学选课元组全部删除

delete from sc where cno in (select cno from cou where cname = 'C语言') and sno in (select sno from stu where sex = 0)

(3)删除周强的所有信息

delete from sc where sno in (select sno from stu where stu.sno = sc.sno and stu.sname = '周强');
delete from stu where sname = '周强'

（四）修改
1.将高数课不及格的成绩全改为60分

update sc set grade = 60 where grade < 60 and cno in (select cno from cou where cname = '高等数学')

2.把低于所有课程总平均成绩的女同学成绩提高5%;

update sc set grade = grade + grade * 0.05
where grade in
(select sno 学号,cname 课程名,grade 成绩
from cou,sc a 
where cou.cno = a.cno and 
(a.grade < (select avg(b.grade) from sc b where a.sno = b.sno)))

3.在SC中修改C004课程的成绩，若成绩小于70分则提高5%，若成绩大于70分则提高4%(要求用两种方法实现，一种方法是用两个UPDATE语句实现。另一种方法是用CASE 操作的一条UPDATE语句实现)

update sc set grade = grade + grade * 0.04 where cno = 'C001' and grade >= 70;
update sc set grade = grade + grade * 0.05 where cno = 'C001' and grade < 70;

update sc set grade = case
when grade >= 70 then grade + grade * 0.04
when grade < 70 then grade + grade * 0.05
end 
where cno = 'C001'

4.为SC表添加一个字段RANK。将各同学按60分以下为E,60-69为D,70-79为C,80-89为B,90及以上为A

update sc set rank = case
when grade >= 90 then 'A'
when grade >= 80 and grade < 90 then 'B'
when grade >= 70 and grade < 80 then 'C'
when grade >= 60 and grade < 70 then 'D'
when grade < 60 then 'E'
end