sql 数据查询练习实验题_(6)按照“学号、已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已-CSDN博客

本文链接：https://blog.csdn.net/ndzyh/article/details/127437546

表：
在这里插入图片描述

查询同时选修“01”号课程和“02”号课程的所有学生的学号；

select s1.sno 
from sc s1, sc s2 
where s1.sno = s2.sno and s1.cno = '01' and s2.cno = '02';

按照院系降序显示所有学生的 “院系，学号、姓名、性别、年龄”等信息，其中院系按照以下规定显示：院系为CS显示为计算机系，院系为IS显示为信息系，院系为MA显示为数学系，院系为EN显示为外语系，院系为CM显示为中医系，院系为WM显示为西医系，其他显示为院系不明；

select 
	case sdept
		when 'CS' then '计算机系'
		when 'IS' then '信息系'
		when 'MA' then '数学系'
		when 'EN' then '外语系'
		when 'CM' then '中医系'
		when 'WM' then '西医系'
		else '院系不明'
	end 院系, sno 学号, sname 姓名, ssex 性别, sage 年龄
from student
order by sdept desc;

查询所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数>=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”，若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”；

select sdept 院系,
	case 
		when count(*) >= 5 then '规模很大'
		when count(*) >= 4 then '规模一般'
		when count(*) >= 2 then '规模稍小'
		else '规模很小'
	end 院系规模
from student
where sdept is not null
group by sdept;

查询学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；

select count(*) 学生总人数, avg(sage) 平均年龄
from student;

查询选修的课程数大于3的各个学生的选修课程数；

select sno 学号, count(*) 选修课程数
from sc
group by sno
having 选修课程数 > 3;

查询平均成绩大于“赵菁菁”平均成绩的各个学生的学号、平均成绩；

select sno 学号, avg(grade) 平均成绩
from sc
group by sno
having avg(grade) >
(
	select avg(grade)
	from student, sc
	where student.sno = sc.sno and student.sname = '赵菁菁'
)

查询每个院系的学生前两条记录，并组成新表ceshi；

create table ceshi
	select * 
	from student s
	where sdept is not null 
	and 2 >
	(
		select count(*) from student
		where sdept = s.sdept and sno < s.sno
	)
	order by s.sdept;

在这里插入图片描述

查询选修了‘数学’或者‘大学英语’的学生学号、姓名、所在院系、选修课程号及成绩；

select s.sno, s.sname, s.sdept, sc.cno, sc.grade
from student s, sc
where sc.sno = s.sno and s.sno in
(
	select s.sno 
	from course c, student s, sc 
	where sc.sno = s.sno and sc.cno = c.cno 
		and (c.cname = '数学' or c.cname = '大学英语')
);

查询与‘张力’(假设姓名唯一)年龄不同的所有学生的信息；

select * 
from student
where sage <>
(
	select sage
	from student
	where sname = '张力'
);

按照“学号，姓名，所在院系，已修学分”的顺序列出学生学分的获得情况。其中已修学分为考试已经及格的课程学分之和；

select s.sno 学号, s.sname 姓名, s.sdept 所在院系, sum(c.ccredit) 已修学分
from student s, course c, sc
where s.sno = sc.sno and c.cno = sc.cno and sc.grade >= 60
group by s.sno;

查找选修了至少一门和张力选修课程一样的学生的学号、姓名及课程号；

select s.sno 学号, s.sname 姓名, sc.cno 课程号
from student s, sc
where s.sno = sc.sno and s.sno in
(
	select s.sno
	from student s, sc
	where s.sno = sc.sno and sc.cno in
	(
		select sc.cno
		from sc, student s
		where s.sno = sc.sno and s.sname = '张力'
	)
);

查询只被一名学生选修的课程的课程号、课程名；

select cno, cname
from course
where cno in
(
	select cno
	from sc
	group by cno
	having count(*) = 1
);

使用嵌套查询出选修了“数据结构”课程的学生学号和姓名；

select sno, sname
from student
where sno in
(
	select s.sno
	from student s, sc, course c
	where sc.sno = s.sno and sc.cno = c.cno and c.cname = '数据结构'
);

使用嵌套查询查询其它系中年龄小于CS系的某个学生的学生姓名、年龄和院系；

select sno, sage, sdept
from student
where sdept <> 'CS' and sage < any(
	select sage 
	from student 
	where sdept = 'CS'
);

显示选修各个课程的及格的人数，及格比率；

select cno, sum(case when grade >= 60 then 1 else 0 end) 及格人数,
	sum(case when grade >= 60 then 1 else 0 end) / count(*) 及格比率
from sc
group by cno;

使用ANY、ALL 查询，列出其他院系中比WM系所有学生年龄小的学生的姓名；

select sname 
from student
where sage < all(
	select sage
	from student
	where sdept = 'WM'
);

显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；

select sdept 院系名称,
	sum(case ssex when '男' then 1 else 0 end) 男生人数,
	sum(case ssex when '女' then 1 else 0 end) 女生人数
from student
group by sdept;

列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；

select sno, avg(grade)
from sc 
group by sno
having sum(case when grade < 60 then 1 else 0 end) >= 2;

显示选修课程数最多的学号及选修课程数最少的学号；

select sno, count(*)
from sc
group by sno
having count(*) >= all(select count(*) from sc group by sno)
	or count(*) <= all(select count(*) from sc group by sno);

修改student表，将cs系姓名为“李咏”的学生姓名为“李勇”；

update student
set sname = '李勇'
where sname = '李咏' and sdept = 'CS';

将学号为“200515010”的学生信息重新设置为“王丹丹、女、20、MA”；

update student
set sname = '王丹丹', ssex = '女', sage = 20, sdept = 'MA'
where sno = '200515010';

修改course表,将“数据处理”的学分改为3学分；

update course
set ccredit = 3
where cname = '数据处理';

修改sc表，将选修课程“01”的同学成绩加5分；

update sc
set grade = grade + 5
where cno = '01';

删除数据表student中无专业的学生记录；

delete
from student
where sdept is null;

sql 数据查询练习 实验题

sql 数据查询练习实验题