50道SQ练习题(MySQL为例)

50道SQ练习题(MySQL为例)

---

创建表结构并插入数据

use mydb;
-- 学生表 student
create table student
(
	stuid int primary key auto_increment not null,
	sname varchar(10) not null,
	sage datetime,
	ssex varchar(10)
);

insert into student values(null,'赵雷','1990-01-01','男');
insert into student values(null,'钱电','1990-12-21','男');
insert into student values(null,'孙风','1990-05-20','男');
insert into student values(null,'李云','1990-08-06','男');
insert into student values(null,'周梅','1991-12-01','女');
insert into student values(null,'吴兰','1992-03-01','女');
insert into student values(null,'郑竹','1989-07-01','女');
insert into student values(null,'王菊','1990-01-20','女');

-- 教师表 teacher
create table teacher(
	teaid int primary key auto_increment not null ,
	tname varchar(10) not null
);

insert into teacher values(null,'张三');
insert into teacher values(null,'李四');
insert into teacher values(null,'王五');

-- 科目表 course

create table course(
	couid int primary key not null auto_increment,
	cname varchar(10) not null,
	teaid int references teacher(teaid)
);
insert into course values(null,'语文',2);
insert into course values(null,'数学',1);
insert into course values(null,'英语',3);

-- 成绩表 score
create table score(
	stuid int references student(stuid),
	couid int references course(couid),
	scores decimal(18,1) not null
);
insert into score values(1,1,80);
insert into score values(1,2,90);
insert into score values(1,3,99);
insert into score values(2,1,70);
insert into score values(2,2,60);
insert into score values(2,3,80);
insert into score values(3,1,80);
insert into score values(3,2,80);
insert into score values(3,3,80);
insert into score values(4,1,50);
insert into score values(4,2,30);
insert into score values(4,3,20);
insert into score values(5,1,76);
insert into score values(5,2,87);
insert into score values(6,1,31);
insert into score values(6,3,34);
insert into score values(7,2,89);
insert into score values(7,3,98);

需求

-- 1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select s1.*,s2.scores '课程1',s3.scores '课程2' 
	from student s1,score s2,score s3 
	where s1.stuid=s2.stuid and s1.stuid=s3.stuid 
	and s2.couid=1 and s3.couid=2 and s2.scores>s3.scores
-- 1.1 查询同时存在" 01 "课程和" 02 "课程的情况
select s1.*,s2.scores '课程1',s3.scores '课程2' 
	from student s1,score s2,score s3 
	where s1.stuid=s2.stuid and s1.stuid=s3.stuid 
	and s2.couid=1 and s3.couid=2

-- 1.2 查询存在" 01 "课程但可能不存在" 02 "课程的情况(不存在时显示为 null )
select s1.*,s2.scores '课程1',s3.scores '课程2' 
	from student s1 
	left join score s2 on s1.stuid =s2.stuid and s2.couid=1 
	left join score s3 on s1.stuid =s3.stuid and s3.couid=2
	where s2.scores > ifnull(s3.scores,0)
-- 1.3 查询不存在" 01 "课程但存在" 02 "课程的情况
select * from score where score.stuid 
	not in(	select stuid from score where couid=01)
	and score.couid=2
-- 2. 查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
	select score.*,AVG(score.scores) from score GROUP BY score.stuid HAVING AVG(score.scores)>=60
-- 3. 查询在 SC 表存在成绩的学生信息
select s1.* 
	from student s1,(select DISTINCT score.stuid from score) s2 
	where s1.stuid=s2.stuid
-- 4. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )

select s.stuid,s.sname,s2.countcou '选课数',s2.sumscore '总成绩' from student s,
	(select s1.stuid,SUM(s1.scores) as sumscore,COUNT(s1.couid) as countcou  from score s1 GROUP BY s1.stuid) as s2
	where s.stuid=s2.stuid

-- 4.1 查有成绩的学生信息
select * from student where EXISTS(select * from score where student.stuid=score.stuid)

-- 5. 查询「李」姓老师的数量 
select COUNT(*) from teacher t where t.tname like('李%')
-- 6. 查询学过「张三」老师授课的同学的信息 

select s.* from student s ,score s1,(select t.teaid,c.couid from teacher t,course c where t.tname='张三' and t.teaid=c.teaid) s2
	where s.stuid = s1.stuid and s1.couid=s2.couid

-- 7. 查询没有学全所有课程的同学的信息 
select s.* from student s,score s2 
	where s.stuid=s2.stuid 
	GROUP BY s.stuid 
	HAVING COUNT(s.stuid)<(SELECT COUNT(*) from course)
-- 8. 查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息 
select distinct s2.* from score s1, student s2 where s1.couid in  	
	(select s.couid from  score s where s.stuid=1) and s1.stuid=s2.stuid
-- 9. 查询和" 01 "号的同学学习的课程完全相同的其他同学的信息 
	-- 查询出1号学生选修的课程
	select couid from score where stuid=1
	-- 查询出选修了01号学生没有选修课程的学生编号
	select stuid from score where couid not in(select couid from score where stuid=1)
	-- 查询选修的课程是01号学生选修课程的子集的学生编号
	select stuid from score where stuid not in (select stuid from score where couid not in(select couid from score where stuid=1))
	-- 筛选出选修的课程数量与01号学生选修的课程数量相等的其他学生的编号
	select stuid from score where stuid not in (select stuid from score where couid not in(select couid from score where stuid=1))
	GROUP BY stuid HAVING COUNT(*) = (select COUNT(*) from score where stuid=1) and stuid !=1

-- 10. 查询没学过"张三"老师讲授的任一门课程的学生姓名 
select * from student where student.stuid not in
	(select s.stuid from score s where s.couid
	in (select c.couid from teacher t ,course c where t.teaid=c.teaid and t.tname='张三'))
-- 11. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 
select s2.stuid,s2.sname,AVG(s1.scores) FROM student s2, score s1
  where s2.stuid=s1.stuid
	and s1.scores<60 GROUP BY s1.stuid HAVING COUNT(s1.stuid)>=2

-- 12. 检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select s2.* from score s1,student s2 
	where s1.couid=1 
	and s1.stuid=s2.stuid 
	and s1.scores <60 
	ORDER BY s1.scores
-- 13. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select s1.stuid,s1.couid,s1.scores,t1.avgscores 
	from score s1 left join (select s2.stuid,AVG(s2.scores) AS avgscores from score s2 GROUP BY s2.stuid) as t1 
	on s1.stuid =t1.stuid 
	ORDER BY t1.avgscores desc 
-- 14. 查询各科成绩最高分、最低分和平均分：
 --    以如下形式显示：课程 ID，课程 name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
 --    及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90
 --    要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
select s1.couid,c1.cname,COUNT(*)as'选修人数',MAX(s1.scores)as'最高分', MIN(s1.scores) as '最低分', AVG(s1.scores) as '平均分',
	SUM(case when s1.scores>=60 then 1 else 0 end )/count(*) as'及格率',
	SUM(case when s1.scores>=70 and s1.scores<80 then 1 else 0 end )/count(*) as'中等率',
	SUM(case when s1.scores>=80 and s1.scores<90 then 1 else 0 end )/count(*) as'优良率',
	SUM(case when s1.scores>=90 then 1 else 0 end )/count(*) as'优秀率'
  from score s1 ,course c1 
	where s1.couid=c1.couid
	GROUP BY s1.couid

-- 15. 按各科成绩进行排序，并显示排名， Score 重复时保留名次空缺
	-- 要在mysql中声明一个变量，你必须在变量名之前使用@符号。FROM子句中的(@curRank := 0)部分允许我们进行变量初始化
select s1.couid,@curRank:=@curRank+1 as rank,s1.scores 
	from (select @curRank:=0)as t, score s1
	ORDER BY s1.scores desc

-- 15.1 按各科成绩进行排序，并显示排名， Score 重复时合并名次
	-- case WHEN @fontscore=s1.scores then @curRank when @fontscore:=scores then @curRank:=@curRank+1 end 
	-- 如果@fontscore==scores 执行 @curRank   rank与上次一样不变
	-- 如果@fontscore!=scores 执行 @curRank+1 rank+1显示
select s1.couid,case WHEN @fontscore=s1.scores then @curRank when @fontscore:=scores then @curRank:=@curRank+1 end as rank,s1.scores
	from (select @curRank:=0)as t, score s1
	ORDER BY s1.scores desc
-- 16.  查询学生的总成绩，并进行排名，总分重复时保留名次空缺
select t1.*,@curRank:=@curRank+1 as rank
	from 
	(select s1.stuid ,sum(s1.scores) as '总成绩'
		from score s1 
		GROUP BY s1.stuid
		ORDER BY sum(s1.scores) desc) as t1 ,
	(select @curRank:=0) as t
	

-- 16.1 查询学生的总成绩，并进行排名，总分重复时不保留名次空缺
select t1.* ,case when @fontscore=t1.sumscores then @curRank
	when @fontscore:=t1.sumscores then @curRank:=@curRank+1 end as rank
	from 
	(select s1.stuid ,sum(s1.scores) as sumscores
		from score s1 
		GROUP BY s1.stuid
		ORDER BY sum(s1.scores) desc) as t1 ,
	(select @curRank:=0) as t

-- 17. 统计各科成绩各分数段人数：课程编号，课程名称，[100-85]，[85-70]，[70-60]，[60-0] 及所占百分比
-- concat(str1,str2,…)  返回结果为连接参数产生的字符串。如有任何一个参数为NULL ，则返回值为 NULL。
select s1.couid,c1.cname,
	CONCAT(SUM(case when s1.scores>=85 then 1 else 0 end)/COUNT(s1.scores)*100,'%--',SUM(case when s1.scores>=85 then 1 else 0 end),'个') as '[100-85]',
	CONCAT(SUM(case when s1.scores>=70 and s1.scores<85 then 1 else 0 end)/COUNT(s1.scores)*100,'%--',SUM(case when s1.scores>=70 and s1.scores<85 then 1 else 0 end),'个') as '[85-70]',
	CONCAT(SUM(case when s1.scores>=60 and s1.scores<70 then 1 else 0 end)/COUNT(s1.scores)*100,'%--',SUM(case when s1.scores>=60 and s1.scores<70 then 1 else 0 end),'个') as '[70-60]',
	CONCAT(SUM(case when s1.scores<60 then 1 else 0 end)/COUNT(s1.scores)*100,'%--',SUM(case when s1.scores<60 then 1 else 0 end),'个') as '[60-0]'
	from score s1,course c1
	where s1.couid=c1.couid
	GROUP BY s1.couid
-- 18. 查询各科成绩前三名的记录

	
-- 19. 查询每门课程被选修的学生数 
select s1.couid, COUNT(*) from score s1
	GROUP BY s1.couid
-- 20. 查询出只选修两门课程的学生学号和姓名 
select s1.stuid,s1.sname 
	from student s1,score s2
	where s1.stuid=s2.stuid
	GROUP BY s2.stuid
	HAVING COUNT(*)=2
-- 21. 查询男生、女生人数
SELECT s1.ssex ,COUNT(*) AS '人数' from student s1
	GROUP BY s1.ssex
-- 22. 查询名字中含有「风」字的学生信息
select * from student s1
	where s1.sname LIKE('%风%')
-- 23. 查询同名同性学生名单，并统计同名人数
select t1.* from student s2 
	LEFT join 
	(select s1.sname,s1.ssex,COUNT(*) as '同名人数' from student s1 GROUP BY s1.sname) as t1 
	on s2.sname=t1.sname and s2.ssex=t1.ssex 
	where t1.同名人数>1
-- 24. 查询 1990 年出生的学生名单
select * from student s1
	where YEAR(s1.sage)=1990
-- 25. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select s1.couid, AVG(s1.scores) '平均成绩'  from score s1
	GROUP BY s1.couid
	ORDER BY AVG(s1.scores) desc,s1.couid asc
-- 26. 查询平均成绩大于等于 85 的所有学生的学号、姓名和平均成绩 
	select t1.*,s1.sname from 
(select s2.stuid, AVG(s2.scores) '平均成绩' 
	from score s2
	GROUP BY s2.stuid) as t1 LEFT JOIN student s1 ON t1.stuid = s1.stuid
	where t1.平均成绩>=85
-- 27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数 
select * from student s2
		where s2.stuid IN
		(select s1.stuid from score s1,course c1 
		where s1.couid=c1.couid
		and c1.cname='数学'
		and s1.scores<60)
-- 28. 查询所有学生的课程及分数情况（存在学生没成绩，没选课的情况）
select s1.sname,s2.* from student s1 LEFT JOIN score s2 on s1.stuid= s2.stuid
-- 29. 查询任何一门课程成绩在 70 分以上的姓名、课程名称和分数
select s1.sname,c1.cname,s2.scores 
	from student s1,course c1,score s2
	where s1.stuid=s2.stuid and c1.couid=s2.couid
	and s2.scores>70
-- 30. 查询不及格的课程
select distinct s1.couid from score s1 where s1.scores<60
-- 31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
select s1.stuid,s1.sname from student s1,score s2
	where s1.stuid=s2.stuid
	and s2.couid=1
	and s2.scores>=80
-- 32. 求每门课程的学生人数 
select s1.couid,COUNT(*)'学生人数' from score s1
	GROUP BY s1.couid 
-- 33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select s1.*,s2.scores from student s1, score s2, course c1,teacher t1
	where s1.stuid=s2.stuid
	and s2.couid=c1.couid
	and c1.teaid=t1.teaid
	and t1.tname='张三'
	ORDER BY s2.scores desc
	LIMIT 1
-- 34. 成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩
select s.*,t3.scores,t3.rank from student s ,
	(select t2.*,case when @fontscores=t2.scores then @curRank WHEN @fontscores:=t2.scores then @curRank:=@curRank+1 end as rank 
	from
		(select s2.stuid,s2.scores from  score s2, course c1,teacher t1
		where s2.couid=c1.couid
		and c1.teaid=t1.teaid
		and t1.tname='张三'
		ORDER BY s2.scores desc) as t2,
		(select @curRank:=0) as t )as t3
	where s.stuid=t3.stuid
	and t3.rank=1
-- 35. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩 
select * from score s2 where EXISTS
(select * from score s1  where s1.stuid=s2.stuid and s1.couid!=s2.couid and s1.scores=s2.scores )
-- 36. 查询每门功成绩最好的前两名
select * from score s1 where 
	(select COUNT(*) from score s2 where s1.couid=s2.couid and s2.scores>s1.scores) <2 
	ORDER BY s1.couid
-- 37. 统计每门课程的学生选修人数（超过 5 人的课程才统计）。
select s1.couid '课程编号',COUNT(*) '选修人数' from score s1 GROUP BY(s1.couid) HAVING COUNT(*)>5

-- 38. 检索至少选修两门课程的学生学号 
select s1.stuid from score s1 GROUP BY s1.stuid HAVING COUNT(*)>=2
-- 39. 查询选修了全部课程的学生信息
select s2.* from score s1 ,student s2 where s1.stuid=s2.stuid  GROUP BY s1.stuid HAVING COUNT(*)=(select COUNT(*) from course)
-- 40. 查询各学生的年龄，只按年份来算 
select s1.stuid, s1.sname, TIMESTAMPDIFF(YEAR,s1.sage,CURDATE()) '学生年龄' from student s1
-- 41. 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一
select s1.* ,
	case 
		when (EXTRACT(MONTH from (s1.sage)))*30+EXTRACT(DAY from (s1.sage))<(EXTRACT(MONTH from (CURDATE())))*30+EXTRACT(DAY from (CURDATE())) 
			then TIMESTAMPDIFF(YEAR,s1.sage,CURDATE())-1 
		when (EXTRACT(MONTH from (s1.sage)))*30+EXTRACT(DAY from (s1.sage))>(EXTRACT(MONTH from (CURDATE())))*30+EXTRACT(DAY from (CURDATE())) 
			then  TIMESTAMPDIFF(YEAR,s1.sage,CURDATE()) 
	end as '年龄' 
	from student s1
-- 42. 查询本周过生日的学生
select * from student s1 where YEARWEEK(s1.sage)= YEARWEEK(CURDATE())
-- 43. 查询下周过生日的学生
select * from student s1 where YEARWEEK(s1.sage)=(YEARWEEK(CURDATE())+1)

-- 44. 查询本月过生日的学生
select * from student s1 where EXTRACT(YEAR_MONTH FROM s1.sage)=EXTRACT(YEAR_MONTH FROM CURDATE())
-- 45. 查询下月过生日的学生
select * from student s1 where EXTRACT(YEAR_MONTH FROM s1.sage)=(EXTRACT(YEAR_MONTH FROM CURDATE())+1)