-----------------------------------------------------------------------2022SqlServer笔试基础篇---------## 标题

试用SQL查询语句表达下列对教学数据库中三个基本表 S、SC 、C 的查询：
S(sno,sname,SAGE,SSEX) 各字段表示学号，姓名，年龄，性别
Sc(sno,cno,grade) 各字段表示学号，课程号，成绩、
C(cno,cname, TEACHER) 各字段表示课程号，课程名和教师名 其 中 SAGE， grade 是数值型，其他均为字符型。

-----drop table s
 create table s (
 sno varchar(10) primary key not null,
 sname varchar(10),
 sage int ,
 ssex varchar(5)
 )
 insert into s values (‘03’,‘石头满’,28,‘女’)
 insert into s values (‘02’,‘徐志勇’,16,‘男’)
 insert into s values (‘01’,‘田丹’,36,‘男’)insert into s values (‘04’,‘许新宇’,20,‘女’)
 insert into s values (‘05’,‘王测验’,16,‘男’)
 insert into s values (‘06’,‘餐含混’,36,‘男’)
 ----drop table sc
 create table sc (
 sno varchar(10) not null,
 cno varchar(10),
 grade int ,
 )insert into sc values (‘03’,‘001’,60)
 insert into sc values (‘02’,‘002’,75)
 insert into sc values (‘01’,‘003’,86)insert into sc values (‘04’,‘007’,99)
 insert into sc values (‘05’,‘008’,98)
 insert into sc values (‘06’,‘009’,88)insert into sc values (‘03’,‘001’,70)
 insert into sc values (‘02’,‘001’,85)
 insert into sc values (‘01’,‘002’,76)
 ----drop table c
 create table c (
 cno varchar(10) primary key not null,
 cname varchar(10),
 techer varchar(10)
 )
 insert into c values (‘001’,‘语文’,‘翟东发’)
 insert into c values (‘002’,‘数学’,‘孙晓飞’)
 insert into c values (‘003’,‘英语’,‘网经书’)insert into c values (‘007’,‘物理’,‘个连锁’)
 insert into c values (‘008’,‘化学’,‘蔡建数’)
 insert into c values (‘009’,‘生物’,‘们仿若’)

1.求年龄大于所有女同学年龄的男学生姓名和年龄。

SELECT sname ,sage from s where ssex='男’and sage > all (select sage from s where ssex=‘女’)

2.求年龄大于女同学平均年龄的男学生姓名和年龄。

select sname sage from s where ssex=‘男’ and sage > (select avg(sage) from s where ssex=‘女’)

3.在 SC 中检索成绩为空值的学生学号和课程号。

select sno,cno from sc where grade is null

4.检索姓名以 WANG 打头的所有学生的姓名和年龄
select sname,sage from s where sname like ‘wang%’

5.检索学号比 WANG 同学大，而年龄比他小的学生姓名。
select sname from s where sno >(select sno from s where sname like ‘wang%’) and sage >(select sage from s where sname like ‘wang%’)

6.统计每门课程的学生选修人数 （超过 2 人的课程才统计） 。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
select a.cno 课程号,count(* ) 选修人数 from sc a inner join c b on a.cno = b.cno group by a.cno having count()>2 order by count() desc ,a.cno asc

7.求翟老师所授课程的每门课程的学生平均成绩。
select distinct b.cno 课程号, avg(b.grade) 平均成绩 from c a inner join sc b on a.cno =b.cno where a.cno in(select cno from c where techer like ‘%翟%’)group by b.cno

8.求选修 001 课程的学生的平均年龄。

select avg(sage) 平均年龄 from sc a inner join s b on a .sno=b.sno where cno =‘001’

9．统计学生姓名和各自选修的课程门数。

select b.sname 学生姓名 ,count(*) 课程门数 from sc a inner join s b on a.sno=b.sno group by b.sname

试用 SQL 更新语句表达对教学数据库中三个基本表 S、
SC 、C的各个更新操作：

10.在基本表 SC 中修改 007 号课程的成绩，若成绩小于等于 75 分时提高 5% ， 若成绩大于 75 分时提高 4% （用两个 UPDATE 语句实现）。

update sc set grade = 99+(990.005) where cno ='007’and grade<=75
update sc set grade = 99+(990.004) where cno ='007’and grade>=75

11 .把低于总平均成绩的女同学成绩提高 5%

update a set a.grade = a.grade*1.05 from sc a inner join s b on a.sno=b.sno where b.ssex =‘女’ and a.grade< (select avg(grade) 总平均成绩 from sc )

12 .把选修英语课80分一下为不及格的成绩全改为空值。

select a.cno 课程编号, b.cname 课程名,a.grade 成绩 from sc a inner join c b on a.cno=b.cno where a.cno =‘003’ and a.grade<80
update a set a.grade = null from sc a inner join c b on a.cno=b.cno where a.cno ='003’and a.grade<80

13．把田丹同学的学习选课和成绩全部删去。
select a.sname 姓名,a.sno 学生号,b.cno 课程号,b.grade 成绩 from s a inner join sc b on a.sno=b.sno where a.sno=‘01’
delete from a from s a inner join sc b on a.sno=b.sno where a.sno=‘01’

14 ．在基本表 SC 中删除尚无成绩的选课元组。
select * from sc where grade is null
delete from sc where grade is null

15 ．往基本表 S 中插入一个学生元组 ‘ 12’，‘赵东海 ’，48 ，女)

insert into s values (‘12’,‘赵东海’,48,‘女’)