MySQL经典练习题，看看你会几道？

SQL准备：

共四个表：course课程表、student学生表、score学生表、teacher表

----建表语句如下----

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `course_id` varchar(20) DEFAULT NULL COMMENT '课程id',
  `course_name` varchar(20) DEFAULT NULL COMMENT '课程名',
  `tea_id` varchar(20) DEFAULT NULL COMMENT '任课老师id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `course` */

insert  into `course`(`course_id`,`course_name`,`tea_id`) values 

('01','语文','1003'),

('02','数学','1001'),

('03','英语','1004'),

('04','体育','1002'),

('05','音乐','1002');

/*Table structure for table `score` */

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (
  `stu_id` varchar(20) DEFAULT NULL COMMENT '学生id',
  `course_id` varchar(20) DEFAULT NULL COMMENT '课程id',
  `course` int(11) DEFAULT NULL COMMENT '成绩'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `score` */

insert  into `score`(`stu_id`,`course_id`,`course`) values 

('001','01',94),

('002','01',74),

('004','01',85),

('005','01',64),

('006','01',71),

('007','01',48),

('008','01',56),

('009','01',75),

('010','01',84),

('011','01',61),

('012','01',44),

('013','01',47),

('014','01',81),

('015','01',90),

('016','01',71),

('017','01',58),

('018','01',38),

('019','01',46),

('020','01',89),

('001','02',63),

('002','02',84),

('004','02',93),

('005','02',44),

('006','02',90),

('007','02',55),

('008','02',34),

('009','02',78),

('010','02',68),

('011','02',49),

('012','02',74),

('013','02',35),

('014','02',39),

('015','02',48),

('016','02',89),

('017','02',34),

('018','02',58),

('019','02',39),

('020','02',59),

('001','03',79),

('002','03',87),

('004','03',89),

('005','03',99),

('006','03',59),

('007','03',70),

('008','03',39),

('009','03',60),

('010','03',47),

('011','03',70),

('012','03',62),

('013','03',93),

('014','03',32),

('015','03',84),

('016','03',71),

('017','03',55),

('018','03',49),

('019','03',93),

('020','03',81),

('001','04',54),

('002','04',100),

('004','04',59),

('005','04',85),

('007','04',63),

('009','04',79),

('010','04',34),

('013','04',69),

('014','04',40),

('016','04',94),

('017','04',34),

('020','04',50),

('005','05',85),

('007','05',63),

('009','05',79),

('015','05',59),

('018','05',87);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `stu_id` varchar(20) DEFAULT NULL COMMENT '学生id',
  `stu_name` varchar(20) DEFAULT NULL COMMENT '学生姓名',
  `birthday` date DEFAULT NULL COMMENT '出生日期',
  `sex` varchar(20) DEFAULT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`stu_id`,`stu_name`,`birthday`,`sex`) values 

('001','彭于晏','1995-05-16','男'),

('002','胡歌','1994-03-20','男'),

('003','周杰伦','1995-04-30','男'),

('004','刘德华','1998-08-28','男'),

('005','唐国强','1993-09-10','男'),

('006','陈道明','1992-11-12','男'),

('007','陈坤','1999-04-09','男'),

('008','吴京','1994-02-06','男'),

('009','郭德纲','1992-12-05','男'),

('010','于谦','1998-08-23','男'),

('011','潘长江','1995-05-27','男'),

('012','杨紫','1996-12-21','女'),

('013','蒋欣','1997-11-08','女'),

('014','赵丽颖','1990-01-09','女'),

('015','刘亦菲','1993-01-14','女'),

('016','周冬雨','1990-06-18','女'),

('017','范冰冰','1992-07-04','女'),

('018','李冰冰','1993-09-24','女'),

('019','邓紫棋','1994-08-31','女'),

('020','宋丹丹','1991-03-01','女');

/*Table structure for table `teacher` */

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (
  `tea_id` varchar(20) DEFAULT NULL COMMENT '老师id',
  `tea_name` varchar(20) DEFAULT NULL COMMENT '学生姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `teacher` */

insert  into `teacher`(`tea_id`,`tea_name`) values 

('1001','张高数'),

('1002','李体音'),

('1003','王子文'),

('1004','刘丽英');

1. 查询姓名中带"冰"的学生名单

select stu_name from student where stu_name like '%冰%'

2. 查询姓"王"的老师的个数

select count(0) from teacher where tea_name like '%王%'

3. 检索课程编号为"04"且分数小于60的学生学号，结果按分数降序排列

select s.stu_id from course c 
    left join score s
    on c.course_id = s.course_id
where s.course < 60 and c.course_id = 4 order by course desc

4. 查询数学成绩不及格的学生和其对应的成绩

select st.stu_name from score sc 
      left join student st 
      on st.stu_id = sc.stu_id
where course < 60 and course_id = (select course_id from course where course_name = '数学')

5. 查询课程编号为"02"的总成绩

select sum(course) '数学总成绩' from score where course_id = '02'

6. 查询参加考试的学生个数

explain select count(distinct a.stu_id) from score a;

7. 查询各科成绩最高和最低的分数，以如下的形式显示：课程号，最高分，最低分

select course_name '课程号', max(course) '最高分',min(course) '最低分' from course c
		left join score sc
		on c.course_id = sc.course_id
group by c.course_id

8. 查询每门课程有多少学生参加了考试(有考试成绩)

select c.course_id,c.course_name,count(sc.course) from course c 
			join score sc 
			on c.course_id = sc.course_id
group by c.course_id

9. 查询男生、女生人数

select sum(case when sex = '男' then 1 else 0 end) 男生人数,
			 sum(case when sex = '女' then 1 else 0 end) 女生人数
			 from student

10. 查询平均成绩大于60分学生的学号和平均成绩

select stu_id,avg(course) avgscore
			from score
group by stu_id
having avgscore > 60

11. 查询至少选修两门课程的学生学号

select stu_id,count(course_id)  count_two
    from score 
    group by stu_id
    having count_two > 1

12. 查询同姓(假设每个学生姓名的第一个字为姓)的学生名单并统计同姓人数

select group_concat(stu_name),
     left(st.stu_name,1) stunm,
     count(left(st.stu_name,1)) stucount 
  from student st 
  group by stunm 
  having stucount > 1

13. 查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列

select course_id,avg(course) avgscore
			 from score 
			 group by course_id
			 order by avgscore asc,course_id desc

14. 统计参加考试人数大于等于15的学科

select course_id,count(*) countnum 
   from score
   group by course_id
	 having countnum > 14

15. 查询学生的总成绩并进行排名

select a.*,rank() over(order by sumscore desc)
    from
(select stu_id,sum(course) sumscore
    from score 
    group by stu_id
    order by sumscore desc) a

16. 查询平均成绩大于60分的学生的学号和平均成绩

select stu_id,avg(course) avgscore
        from score
        group by stu_id
      	having a.avgscore > 60

17. 查询一共参加两门课程且其一门为语文课程的学生的id和姓名

select st.stu_id,st.stu_name,count(sc.course) countscore from student st
        left join score sc
        on st.stu_id = sc.stu_id
        right join 
         (select * from score where course_id = (select course_id from course where course_name = '语文')) b
        on sc.stu_id = b.stu_id
        group by st.stu_id
        having countscore = 2

18. 查询所有课程成绩小于60分的学生的学号、姓名

select a.stu_id,st.stu_name 
		from (select stu_id,max(course) maxcourse
				from score 
				group by stu_id
				having maxcourse < 60) a 
left join student st
on a.stu_id = st.stu_id

19. 查询出只选修了两门课程的全部学生的学号和姓名

select st.stu_id,st.stu_name,count(course_id) countcourse
      from student st
      left join score sc
      on st.stu_id = sc.stu_id
      group by st.stu_id
    	having countcourse = 2

20. 查询两门以上不及格课程的同学的学号 及其平均成绩

select b.stu_id,c.avgcourse 
				from (select a.stu_id,count(course_id) countcourse 
						from (select stu_id,course_id,course from score where course < 60) a
				group by stu_id
				having countcourse > 2) b
				left join (select stu_id,avg(course) avgcourse from score
									 group by stu_id) c
			  on b.stu_id = c.stu_id

21. 查询所有学生的学号、姓名、选课数、总成绩

select st.stu_id,st.stu_name,count(sc.course_id),sum(sc.course)
				from student st
				left join score sc
				on sc.stu_id = st.stu_id
				group by st.stu_id

22. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select st.stu_id,st.stu_name,avg(sc.course) avgscore
		from student st
		left join score sc
		on st.stu_id = sc.stu_id
		group by st.stu_id
		having avgscore > 85

23. 查询学生的选课情况:学号，姓名，课程号，课程名称

select st.stu_id,st.stu_name,c.course_id,c.course_name
      from student st 
      left join score sc
      on st.stu_id = sc.stu_id
      left join course c
      on sc.course_id = c.course_id
      order by st.stu_id,c.course_id

24. 查询出每门课程的及格人数和不及格人数

select c.course_id,
			 c.course_name,
			 sum(case when course >= 60 then 1 else 0 end) 及格人数,
			 sum(case when course < 60 then 1 else 0 end) 不及格人数
		from  score sc
    left join course c
    on sc.course_id = c.course_id
		group by c.course_id
		

25. 检索"01"课程分数小于60，按分数降序排列的学生信息

select sc.course_id,st.stu_id,st.stu_name,sc.course
      from student st
      join score sc
			on st.stu_id = sc.stu_id
			join course c
			on sc.course_id = c.course_id 
      where c.course_id = '01' and sc.course < 60
      order by sc.course desc

26. 查询任何一门课程成绩在70分以上的学生的姓名、课程名称和分数

select st.stu_name,group_concat(c.course_name),group_concat(sc.course)
				from student st
				left join score sc
				on st.stu_id = sc.stu_id
				left join course c
				on sc.course_id = c.course_id
				group by st.stu_id
				having min(sc.course) > 70

27. 查询两门及以上不及格课程的同学的学号，姓名及其平均成绩

-- 第一种
select st.stu_id,st.stu_name,round(avg(course),2) from student st
			join (select stu_id from score where course < 60 group by stu_id having count(*) >= 2) a
			on st.stu_id = a.stu_id
			join score sc
			on st.stu_id = sc.stu_id
			group by st.stu_id
-- 第二种
select d.stu_id,d.stu_name,d.countcourse,e.avgcourse
				from (select b.stu_id,b.stu_name,b.countcourse 
									from (select a.stu_id,st.stu_name,count(a.course_id) countcourse
														from student st
														right join (select stu_id,course_id,course
																	from score
																	where course < 60) a
														on a.stu_id = st.stu_id
														group by a.stu_id) b
									where b.countcourse >= 2) d
				left join (select stu_id,avg(course) avgcourse
									 from score
									 group by stu_id) e
				on d.stu_id = e.stu_id

28. 查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

-- 同一学生不同课程成绩相同
select sc1.stu_id,sc1.course_id,sc1.course,sc2.stu_id,sc2.course_id,sc2.course from score sc1
			  join score sc2
				on sc1.stu_id = sc2.stu_id
				where sc1.course = sc2.course
				and sc1.course_id != sc2.course_id
-- 不同学生不同课程成绩相同
select sc1.stu_id,sc1.course_id,sc1.course,sc2.stu_id,sc2.course_id,sc2.course from score sc1
			  join score sc2
				on sc1.course = sc2.course
				where sc1.course = sc2.course
				and sc1.stu_id != sc2.stu_id
				and sc1.course_id != sc2.course_id