聚齐三只SQL圣兽，玩转增删改查创建表，横扫sql补充练习题

在这篇文章了，小伙伴们将看到全C站关于这份sql补充训练最详细的解答~

🍊第一只 在数据库 school 中建立student , sc, course 表。

需求描述

学生表、课程表、选课表属于数据库 School ，其各自的数据结构如下：

参考代码

create table student
(
	sno char(6) primary key,
	sname varchar(8),
	ssex char(2),
	sage smallint,
	sdept varchar(15)
)

create table course
(
	cno char(4) primary key,
	cname varchar(20),
	cpno char(4),
	ccredit tinyint
)

create table sc
(
	sno char(6),
	cno char(4),
	grade decimal(12,2),
	primary key(sno,cno)
)

🍏第二只 主码设定以及增删改练习

需求描述

练习①

1、 设定Student表的主码：sno
2 、设定Course表的主码：cno
3 、设定Sc表的主码：sno，cno

---

练习②
写出使用 Create Table 语句创建表 student , sc, course 的SQL语句

---

练习③
在student表中插入信息

---

练习④
删除student表中的元组

---

练习⑤
在数据库school中删除关系student

---

练习⑥
在student表添加属性sbirthdate 类型 datetime

---

练习⑦
1 、删除所有 JSJ 系的男生

2 、删除“数据库原理”的课的选课纪录

---

练习⑧
1、修改 0001 学生的系科为: JSJ

2、把陈小明的年龄加1岁，性别改为女。

3、修改李文庆的1001课程的成绩为 93 分

4、把“数据库原理”课的成绩减去1分

参考代码

--2在student表中插入信息
insert into student values('4001','赵茵','男','20','sx'),
						('4002','杨华','女','21','jsj')

--3 删除student表中的元组
delete from student

--4在数据库school中删除关系student
drop table student

--5在student表添加属性sbirthdate  类型 datetime
alter table student add sbirthday datetime

--练习
--1 删除所有 JSJ 系的男生  
delete from student 
where sdept='jsj' and ssex='男'

--2 删除“数据库原理”的课的选课纪录 
delete from sc 
where cno in
(
	select cno from course 
	where cname='数据库原理'
)

--update
--1 修改 0001 学生的系科为:  JSJ
update student set sdept='jsj'
where sno='0001'

--2 把陈小明的年龄加1岁，性别改为女。
update student set sage=sage+1
where sname='陈小明'
update student set ssex='女'
where sname='陈小明'

--3	修改李文庆的1001课程的成绩为 93 分
update sc set grade='93'
where sno in
(
	select sno from student
	where sname='李文庆'
)

--4 把“数据库原理”课的成绩减去1分
update sc set grade=grade-1
where cno in
(
	select cno from course
	where cname='数据库原理'
)

🍇第三只 欺负 Select 查询语句

🎒① 单表查询

需求描述

1、查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。

2、查询姓名中第2个字为“明”字的学生学号、性别。（_代表1个字符，%代表多个字符）

3、查询 1001课程没有成绩的学生学号、课程号

4、查询JSJ 、SX、WL 系的年龄大于25岁的学生学号,姓名，结果按系排列

5、按10分制查询学生的sno,cno,10分制成绩 （1-10分 为1 ，11-20分为2 ，30-39分为3，。。。90-100为10）

6、查询 student 表中的学生共分布在那几个系中。（distinct去掉重复系名）

7、查询0001号学生1001，1002课程的成绩。

参考代码

--1查询年龄在19至21岁之间的女生的学号,姓名,年龄,按年龄从大到小排列。
select sno,sname,sage
from student
where ssex='女'and sage between 19 and 21
order by sage desc

--2查询姓名中第2个字为“明”字的学生学号、性别。（_代表1个字符，%代表多个字符）
select sno,sname,ssex
from student
where sname like'%明%'

--3查询 1001课程没有成绩的学生学号、课程号
select sno,cno
from sc
where grade is null and cno='1001'

--4查询JSJ 、SX、WL 系的年龄大于25岁的学生学号,姓名，结果按系排列
select sno,sname 
from student
where sdept in('jsj','sx','wl')and sage>25
order by sdept

--5按10分制查询学生的sno,cno,10分制成绩 （1-10分 为1 ，11-20分为2 ，30-39分为3，。。。90-100为10） 
select sno,cno,grade*0.1+1 as A
from sc

--6查询 student 表中的学生共分布在那几个系中。（distinct去掉重复系名） 
select distinct sdept from student

--7查询0001号学生1001，1002课程的成绩。
select grade 
from sc
where sno='0001'and cno in('1001','1002')

🎀② 统计

需求描述

1、查询姓名中有“明”字的学生人数。

2、计算‘JSJ’系的平均年龄及最大年龄。

3、查询学生中姓名为张明、赵英的人数

4、计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列

5 、计算 1001,1002 课程的平均分。

6 、查询平均分大于80分的学生学号及平均分

7 、统计选修课程超过 2 门的学生学号

8 、统计有10位成绩大于85分以上的课程号。

9 、统计平均分不及格的学生学号

10 、统计有大于两门课不及格的学生学号

参考代码

--1查询姓名中有“明”字的学生人数。
select count(*)
from student
where sname like'%明%'

--2计算‘JSJ’系的平均年龄及最大年龄。
select avg(sage)AS A,max(sage)AS B
from student
where sdept='jsj'

--3查询学生中姓名为张明、赵英的人数
select count(*)
from student
where sname in('张明','赵英')

--4计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列
select cno,sum(grade)as 'sum',avg(grade)as 'avg',max(grade)as 'max',min(grade)as 'min'
from sc
group by cno 
order by avg(grade) desc

--5 计算 1001,1002 课程的平均分。
select cno,avg(grade)
from sc
where cno in('1001','1002')
group by cno

--6 查询平均分大于80分的学生学号及平均分 
select sno,avg(grade)
from sc
group by sno 
having avg(grade)>80

--7 统计选修课程超过 2 门的学生学号
select sno
from sc
group by sno
having count(*)>'2'

--8 统计有10位成绩大于85分以上的课程号。
select cno
from sc
where grade>85
group by cno
having count(*)='10'

--9 统计平均分不及格的学生学号
select sno
from sc
group by sno 
having avg(grade)<60

--10 统计有大于两门课不及格的学生学号
select sno
from sc
where grade<60
group by sno 
having count(*)>'2'

🔖③ 连接

需求描述

1查询 JSJ 系的学生选修的课程号

2查询选修1002 课程的学生的学生姓名 (不用嵌套及嵌套2种方法）

3查询数据库原理不及格的学生学号及成绩

4查询选修“数据库原理”课且成绩 80 以上的学生姓名(不用嵌套及嵌套2种方法）

5查询平均分不及格的学生的学号，姓名,平均分。

6查询女学生平均分高于75分的学生姓名。

7查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出，不能遗漏)

参考代码

--1查询 JSJ 系的学生选修的课程号
select cno 
from student,sc
where student.sno=sc.sno and sdept='jsj'

--2查询选修1002 课程的学生的学生姓名 (不用嵌套及嵌套2种方法）
a:select sname 
from student,sc
where student.sno=sc.sno and cno='1002'

b:select sname 
from student 
where sno in 
(
	select sno 
	from sc 
	where cno='1002'
)

--3查询数据库原理不及格的学生学号及成绩
select sno,grade 
from sc,course
where sc.cno=course.cno and cname='数据库原理' and grade<60

--4查询选修“数据库原理”课且成绩 80 以上的学生姓名(不用嵌套及嵌套2种方法）
a:select sname
from student,sc,course
where student.sno=sc.sno and sc.cno=course.cno and grade>80 and cname='数据库原理'

b:select sname 
from student 
where sno in 
(
	select sno 
	from sc 
	where grade>80 and cno in
	(
	select cno 
	from course
	where cname='数据库原理'
	)
)

--5查询平均分不及格的学生的学号，姓名,平均分。
select sc.sno,min(sname)as name,avg(grade)as avg
from student join sc on student.sno=sc.sno
group by sc.sno 
having avg(grade)<60

--6查询女学生平均分高于75分的学生姓名。
select sname 
from student 
where ssex='女' and sno in
(
	select sno 
	from sc 
	group by sno
	having avg(grade)>75
)

--7查询男学生学号、姓名、课程号、成绩。(一门课程也没有选修的男学生也要列出，不能遗漏)
select student.sno,sname,cno,grade 
from student left join sc on student.sno=sc.sno and ssex='男'

📌④ 嵌套、相关及其他

需求描述

1、 查询平均分不及格的学生人数

2、 查询没有选修1002 课程的学生的学生姓名

3 、查询平均分最高的学生学号及平均分 （2种方法 TOP , any , all）

4 、查询1002课程第一名的学生学号（2种方法）

5 、查询平均分前三名的学生学号

6、 查询 JSJ 系的学生与年龄不大于19岁的学生的差集

7 、查询1001号课程大于90分的学生学号、姓名及平均分大于85分的学生学号、姓名

8、查询每门课程成绩都高于该门课程平均分的学生学号

9 、查询大于本系科平均年龄的学生姓名

参考代码

--1 查询平均分不及格的学生人数
select count(*)
from student
where sno in
(
	select sno 
	from sc
	group by sno 
	having avg(grade)<60
)

--2 查询没有选修1002 课程的学生的学生姓名  
select sname
from student 
where not exists
(
	select*
	from sc 
	where cno='1002'and sc.sno=student.sno
)

--3 查询平均分最高的学生学号及平均分 （2种方法 TOP , any , all）
a:select top 1 sno,avg(grade) 
from sc 
group by sno 
order by avg(grade) desc

b:select sno, avg(grade) 
from sc  
group by sno 
having avg(grade)=any
(
	select top 1 avg(grade) 
	from sc
	group by sno 
	order by avg(grade) desc
)

c:select sno, avg(grade) 
from sc 
group by sno
having avg(grade)>=all 
( 
	select avg(grade)  
	from sc 
	group by sno
)

--4 查询1002课程第一名的学生学号（2种方法）
a:select top 1 sno 
from sc
where cno='1002' 
order by grade desc

b:select sno 
from sc 
where cno='1002' and grade >=all 
(
	select grade 
	from sc 
	where cno='1002'
)

--5 查询平均分前三名的学生学号
select top 3 sno 
from sc  
group by sno 
order by avg(grade)desc

--6 查询 JSJ 系的学生与年龄不大于19岁的学生的差集
a:select*
from student 
where sdept='jsj' and sage>19

b:select*
from student 
where sdept='jsj' 
except select* 
from student 
where sage<19

--7 查询1001号课程大于90分的学生学号、姓名及平均分大于85分的学生学号、姓名
select student.sno,sname 
from student,sc 
where cno='1001' and grade>90
union  select sno,sname
from student 
where sno in 
(
	select sno 
	from sc 
	group by sno
	having avg(grade)>85
)

--8 查询每门课程成绩都高于该门课程平均分的学生学号
select sno 
from student 
where sno not in
(
	select sno 
	from sc x 
	where grade< 
	(
		select avg(grade) 
		from sc 
		where cno=x.cno
	) 
)

--9 查询大于本系科平均年龄的学生姓名
select sname 
from student x
where sage>
(
	select avg(sage)
	from student
	where sdept=x.sdept
)

完美解决所有问题