SQL基础50题

SQL 基础50题

表结构和数据比较简单清晰, 主要是在过程中体会多表查询的手段, 和学会基本函数的用法.

数据库文件

/*
SQLyog Ultimate v8.32 
MySQL - 5.7.28-log : Database - school
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`school` /*!40100 DEFAULT CHARACTER SET utf8 */;

USE `school`;

/*Table structure for table `course` */

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (
  `c_no` varchar(255) NOT NULL,
  `c_name` varchar(50) NOT NULL,
  `t_no` varchar(255) NOT NULL,
  PRIMARY KEY (`c_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `course` */

insert  into `course`(`c_no`,`c_name`,`t_no`) values ('0001','语文','0002'),('0002','数学','0001'),('0003','英语','0003');

/*Table structure for table `score` */

DROP TABLE IF EXISTS `score`;

CREATE TABLE `score` (
  `s_no` varchar(255) NOT NULL,
  `c_no` varchar(255) NOT NULL,
  `s_score` float NOT NULL,
  PRIMARY KEY (`s_no`,`c_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `score` */

insert  into `score`(`s_no`,`c_no`,`s_score`) values ('0001','0001',50),('0001','0002',90),('0001','0003',99),('0002','0002',60),('0002','0003',80),('0003','0001',85),('0003','0002',50),('0003','0003',49);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `s_no` varchar(255) NOT NULL,
  `s_name` varchar(255) NOT NULL,
  `s_birth` date NOT NULL,
  `s_gender` varchar(10) NOT NULL,
  PRIMARY KEY (`s_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `student` */

insert  into `student`(`s_no`,`s_name`,`s_birth`,`s_gender`) values ('0001','猴子','1989-01-01','男'),('0002','猴子','1990-12-21','女'),('0003','马云','1991-12-21','男'),('0004','王思聪','1990-05-20','男');

/*Table structure for table `teacher` */

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (
  `t_no` varchar(255) NOT NULL,
  `t_name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`t_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

/*Data for the table `teacher` */

insert  into `teacher`(`t_no`,`t_name`) values ('0001','张三'),('0002','马化腾'),('0003','李四'),('0004','');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

题目及解答

#参考资料:https://zhuanlan.zhihu.com/p/43289968
#MySQL8.0以上支持窗口函数
#当前MySQL版本为5.7.28,因此所有涉及窗口函数的SQL均没有实际运行
#超级重点 18和23、 22和25 、 41、46

#1.查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号（重点）
SELECT
 stu.s_no,
 stu.s_name,
 sc_1.s_score AS "课程0001",
 sc_2.s_score AS "课程0002"
FROM student stu
LEFT JOIN score sc_1 ON sc_1.s_no=stu.s_no AND sc_1.c_no='0001'
LEFT JOIN score sc_2 ON sc_2.s_no=stu.s_no AND sc_2.c_no='0002'
WHERE sc_1.s_score > sc_2.s_score;

#2.查询平均成绩大于60分的学生的学号和平均成绩
SELECT sc.s_no,AVG(sc.s_score)
FROM score sc
GROUP BY sc.s_no
HAVING AVG(sc.s_score)>60;
#假如同时还要查询姓名
SELECT sc.s_no,stu.s_name,AVG(sc.s_score)
FROM score sc
LEFT JOIN student stu ON stu.s_no=sc.s_no
GROUP BY sc.s_no,stu.s_name
HAVING AVG(sc.s_score)>60;


#3.查询所有学生的学号，姓名，选课数，总成绩
SELECT
	sc.s_no AS '学号',
	stu.s_name AS '姓名',
	COUNT(*) AS '选课数',
	SUM(sc.s_score) AS '总成绩'
FROM score sc
LEFT JOIN student stu ON stu.s_no=sc.s_no
GROUP BY sc.s_no;

#4.查询姓猴的老师的个数
SELECT COUNT(*)
FROM teacher t
WHERE t.t_name LIKE "马%";

#5.查询没学过张三(0001)老师所教的课程的学生学号及姓名(重要)
#通过张三的名字查出对应的教师编号,与课程表联查得出课程编号
#再与成绩表联查,得出有这几门课程的学生
#最后使用not in
SELECT *
FROM student st
WHERE st.s_no NOT IN
(
SELECT
stu.s_no
FROM teacher t
RIGHT JOIN course c ON c.t_no=t.t_no AND t.t_no='0001'
RIGHT JOIN score sc ON sc.c_no=c.c_no
LEFT JOIN student stu ON sc.s_no=stu.s_no
WHERE t.t_name='张三'
);

SELECT
stu.s_no AS '学号',
stu.s_name AS '姓名',
sc.c_no AS '课程编号',
c.c_name AS '课程名'
FROM teacher t
RIGHT JOIN course c ON c.t_no=t.t_no AND t.t_no='0001'
RIGHT JOIN score sc ON sc.c_no=c.c_no
LEFT JOIN student stu ON sc.s_no=stu.s_no
WHERE t.t_name='张三';

#6.查询学过张三老师所教的所有课的学生学号及姓名
SELECT
stu.s_no AS '学号',
stu.s_name AS '姓名',
sc.c_no AS '课程编号',
c.c_name AS '课程名'
FROM teacher t
#RIGHT JOIN course c ON c.t_no=t.t_no AND t.t_no='0001'
RIGHT JOIN course c ON c.t_no=t.t_no
RIGHT JOIN score sc ON sc.c_no=c.c_no
LEFT JOIN student stu ON sc.s_no=stu.s_no
WHERE t.t_name='张三';

#7.查询学过编号为01的课程并且学过课程02的学生学号和姓名
SELECT 
stu.s_no AS '学号',
stu.s_name AS '姓名'
FROM score sc1
INNER JOIN score sc2 ON sc2.s_no=sc1.s_no
LEFT JOIN student stu ON stu.s_no=sc2.s_no
WHERE sc1.c_no='0001' AND sc2.c_no='0002';

#8.查询课程编号为02的总成绩
SELECT SUM(s_score)
FROM score
WHERE c_no='0002'
GROUP BY c_no;

#9.查询所有课程成绩小于60分的学生学号和姓名
#所有成绩小于60分,有大于60分则不用查出来,先查出大于60分的,然后使用not in
#所有成绩小于60分,则使用group by进行分组,然后having进行筛选(结合min函数)(尝试一下)
#这样是根据student表进行排除,即使score表中没有成绩,也视为小于
SELECT
st.s_no '学号',
st.s_name '姓名'
FROM student st
WHERE st.s_no NOT IN(
SELECT DISTINCT sc.s_no
FROM score sc
WHERE sc.c_no='0002' AND sc.s_score>=60
);

#这样是根据score表中进行查找,只查询拥有成绩的学生,没有成绩的学生则查不出来
SELECT 
stu.s_no '学号',
stu.s_name '姓名'
FROM score sc
LEFT JOIN student stu ON stu.s_no=sc.s_no
GROUP BY sc.s_no
HAVING MAX(sc.s_score)<60;


#10.查询没有学全所有课的学生的学号和姓名(重点)
#思路:所学课程数小于总课程数
SELECT DISTINCT sc.s_no,
stu.s_name
FROM student stu
LEFT JOIN score sc ON sc.s_no=stu.s_no
GROUP BY sc.s_no,stu.s_name
HAVING COUNT(sc.c_no)<(
SELECT COUNT(*)
FROM course
);

#11.查询至少有一门课与学号为0001的学生相同的学生学号与姓名(重点)
#思路:按照课程分组,再将0001学生不在的组,所有学生进行去重
SELECT
DISTINCT sc.s_no
FROM score sc
GROUP BY sc.c_no
HAVING sc.s_no<>'0001'

SELECT
DISTINCT a.c_no
FROM score AS a
INNER JOIN
(
SELECT sc.c_no
FROM score sc
WHERE sc.s_no='0001'
)AS b ON b.c_no=a.c_no AND a.s_no!='0001';

#B站答案版
SELECT a.s_no,a.s_name
FROM student AS a
INNER JOIN(
	SELECT DISTINCT s_no
	FROM score WHERE s_no IN(
		SELECT c_no
		FROM score
		WHERE s_no='0001'
	)AND s_no!='0001'
)AS b ON a.s_no=b.s_no;



#最终优化版
SELECT DISTINCT stu.s_no
FROM score sc1
INNER JOIN score sc2 ON sc2.c_no=sc1.c_no AND sc2.s_no='0001'
INNER JOIN student stu ON stu.s_no=sc1.s_no AND sc1.s_no!='0001';



#12.查询和0001同学所学课程完全一样的学生(重点)
#思路:先查询1同学所学课程,然后自连接进行筛选,得出01同学选过的课程,排除01,再统计课程总数与01同学相等的
SELECT sc.c_no FROM score sc WHERE sc.s_no='0001';
SELECT * FROM score sc WHERE sc.c_no IN (
	SELECT sc.c_no FROM score sc WHERE sc.s_no='0001'
) AND sc.s_no!='0001';
SELECT COUNT(*) FROM score sc GROUP BY sc.s_no HAVING sc.s_no='0001';

SELECT * FROM score sc WHERE sc.c_no IN (
	SELECT sc.c_no FROM score sc WHERE sc.s_no='0001'
) AND sc.s_no!='0001' AND sc.c_no;
SELECT COUNT(c_no) FROM score GROUP BY s_no;

SELECT COUNT(c_no)
FROM score
GROUP BY s_no
HAVING s_no='0001';

SELECT s_no
FROM score
GROUP BY s_no
HAVING COUNT(c_no)=(
	SELECT COUNT(c_no)
	FROM score
	GROUP BY s_no
	HAVING s_no='0001'
) AND s_no!='0001';

SELECT DISTINCT c_no
FROM score
WHERE s_no='0001';

#基本结果
SELECT DISTINCT sc2.s_no
FROM score sc1
INNER JOIN score sc2 ON sc2.c_no=sc1.c_no AND sc1.s_no='0001'
WHERE sc2.s_no IN (
	SELECT s_no
	FROM score GROUP BY s_no HAVING COUNT(*)=(
	SELECT COUNT(*) FROM score GROUP BY s_no HAVING s_no='0001'
	) AND s_no !='0001'
);

#15.查询有两门及以上课程不及格的同学,及其平均成绩(重点)
#思路:查询所有不及格的成绩,然后根据s_no进行分组,统计条数>=2的学生
#解答1,不能这样算,这样只算了不合格成绩的平均值
SELECT
s_no '学号',
AVG(s_score) AS '平均成绩'
FROM score WHERE s_score<60
GROUP BY s_no
HAVING COUNT(*)>=2;

#解答2:先查学号,再从成绩表中取平均分
SELECT s_no
FROM score WHERE s_score<60
GROUP BY s_no
HAVING COUNT(*)>=2;

SELECT AVG(s_score)
FROM score
GROUP BY s_no
HAVING s_no IN (
	SELECT s_no
	FROM score WHERE s_score<60
	GROUP BY s_no
	HAVING COUNT(*)>=2
);

#16.查询01课程小于60分并按分数降序排列显示学生信息及成绩
#思路:查询01课程,并小于60分的学生学号,再关联学生表显示学生信息
SELECT
	stu.s_no,
	stu.s_name,
	sc.s_score
FROM student stu
INNER JOIN score sc ON sc.s_no=stu.s_no
WHERE sc.c_no='0001' AND sc.s_score<60
ORDER BY sc.s_score DESC;



#17.按平均成绩从高到低显示所有学生的所有成绩和平均成绩(case when 重点)
#知识点:case简单函数,case搜索函数
#思路:先查出所有的平均成绩,然后order by排序,然后$&#*(^乱七八糟
#这里主要是函数结合case when用法,重排select查询结果
SELECT
	s_no '学号',
	MAX(CASE WHEN c_no='0001' THEN s_score ELSE NULL END) '语文',
	MAX(CASE WHEN c_no='0002' THEN s_score ELSE NULL END) '数学',
	MAX(CASE WHEN c_no='0003' THEN s_score ELSE NULL END) '英语',
	AVG(s_score) '平均分'
FROM score
GROUP BY s_no
ORDER BY AVG(s_score) DESC;

#18.查询各科成绩最高分,最低分,平均分,以如下形式显示:
#课程id,课程name,最高分,最低分,平均分,及格率,中等率,优良率,优秀率
#及格分为>=60,中等为:70-80,优良为:80-90,优秀为:>=90
SELECT
	sc1.c_no,
	MAX(c.c_name),
	MAX(sc1.s_score)'最高分',
	MIN(sc1.s_score)'最低分',
	AVG(sc1.s_score) '平均分',
	SUM(CASE WHEN sc1.s_score>=60 THEN 1 ELSE 0 END)/COUNT(sc1.s_no) '及格率',
	SUM(CASE WHEN sc1.s_score>=60 AND sc1.s_score<80 THEN 1 ELSE 0 END)/COUNT(sc1.s_no) '中等率',
	SUM(CASE WHEN sc1.s_score>=80 AND sc1.s_score<90 THEN 1 ELSE 0 END)/COUNT(sc1.s_no) '优良率',
	SUM(CASE WHEN sc1.s_score>=90 THEN 1 ELSE 0 END)/COUNT(sc1.s_no) '优秀率'
FROM course c
INNER JOIN score sc1
GROUP BY sc1.c_no;
#总结:case when结合sum,count函数计算合格率的实际应用(重点)

#19.按各科成绩进行排序,并显示排名(重点row_number)
#SELECT VERSION();查看MySQL版本5.7.28,MYSQL8.0以上支持窗口函数
#row_number() over (order by 列)
#窗口函数:参考资料 https://developer.aliyun.com/article/593698
#select
#	sc1.*,
#	row_number() over(partition by sc1.c_no order by sc1.s_score desc)
#from score sc1;



#20.查询学生的总成绩并进行排名(不重点)
#查询学生的总成绩并进行排名,显示总成绩及学生信息
SELECT
	stu.s_no,
	stu.s_name,
	SUM(sc1.s_score)
FROM score sc1
INNER JOIN student stu ON stu.s_no=sc1.s_no
GROUP BY sc1.s_no
ORDER BY SUM(sc1.s_score) DESC;

#21.查询不同老师所教不同课程平均分从高到低显示
#思路:
#先查询各个老师所教的课程
#解答1:以课程分组平均,再进行查询(desc降序,asc升序)
SELECT
	c.t_no,
	t.t_name,
	AVG(sc.s_score)
FROM score sc
 INNER JOIN course c ON c.c_no=sc.c_no
 INNER JOIN teacher t ON t.t_no=c.t_no
GROUP BY c.c_no ORDER BY AVG(sc.s_score) ASC;

#解答2:以老师为主体,老师所教课程的所有成绩/人次所得的平均分
SELECT
	c.t_no '教师编号',
	t.t_name '姓名',
	AVG(sc.s_score) '课程均分'
FROM course c
LEFT JOIN score sc ON sc.c_no=c.c_no
LEFT JOIN teacher t ON t.t_no=c.t_no
GROUP BY c.t_no;

#22.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩(重点)
#思路:先以课程为主体,先将所有成绩进行组内排序,然后连表查询学生信息
SELECT * 
FROM (SELECT 
	stu.*,
	sc1.c_no,
	sc1.s_score,
	row_number() over(PARTITION BY sc1.c_no ORDER BY sc1.s_score DESC) AS m
	FROM score sc1
	INNER JOIN student stu ON stu.s_no=sc1.s_no)AS a
WHERE m IN(2,3);

#(select 
#	row_number() over(partition by sc1.c_no order by sc1.s_score desc) as m
#from score sc1
#inner join student stu on stu.s_no=sc1.s_no)as a


#23.使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩,分别统计各分段人数:课程id和课程名称(重点)
#思路:查询score,以课程为主体进行group by,使用case when?
#关于使用sum和count区别,sum计算值,count计算数量,使用count时else后值为null,否则count会记录这条数据
SELECT
	sc1.c_no AS '课程id',
	c.c_name '课程名称',
	SUM(CASE WHEN sc1.s_score<60 THEN 1 ELSE 0 END) '<60分',
	SUM(CASE WHEN sc1.s_score<70 AND sc1.s_score>=60 THEN 1 ELSE 0 END) '60-70分',
	SUM(CASE WHEN sc1.s_score<85 AND sc1.s_score>=70 THEN 1 ELSE 0 END) '70-85分',
	SUM(CASE WHEN sc1.s_score<100 AND sc1.s_score>=80 THEN 1 ELSE 0 END) '85-100分'
FROM score sc1
INNER JOIN course c ON c.c_no=sc1.c_no
GROUP BY c.c_no,c.c_name;

#24.查询学生平均成绩及其名次(同19题-row_number,重点)
#思路:group by s_no -> select avg(s_score),order by avg(s_score)
#第一种理解:使用order by对平均分排序,这种方法没办法显示名次,会过滤部分数据
SELECT
	sc1.s_no,
	AVG(sc1.s_score)
FROM score sc1
GROUP BY sc1.s_no
ORDER BY AVG(sc1.s_score) DESC;
#第二种理解:使用窗口函数
#思路:partition by s_no
#ps:思路基本正确,函数用法没掌握,写的乱七八糟
SELECT
	sc1.*,
	row_number() over(ORDER BY AVG(sc1.s_score))
FROM score sc1
GROUP BY sc1.s_no

#B站答案版,供参考
SELECT
	sc1.s_no,
	AVG(sc1.s_score),
	row_number() over(ORDER BY AVG(sc1.s_score) DESC)
FROM score sc1
GROUP BY sc1.s_no;

#25.查询各科成绩前三名的记录(不考虑成绩并列的情况)(重点 与22题类似)
#思路:以科目为主体,分组排序,使用窗口函数(显示所有记录)
#大致:partition by c_no order by s_score desc
#参考知乎答案,注意case when优化返回结果,以及group by,省略了子查询连接student
SELECT
	c_no,
	MAX(CASE WHEN m=1 THEN s_score ELSE 0 END) '第一名',
	MAX(CASE WHEN m=2 THEN s_score ELSE 0 END) '第二名',
	MAX(CASE WHEN m=3 THEN s_score ELSE 0 END) '第三名'
FROM (
SELECT
	sc1.*,
	row_number() over(PARTITION BY sc1.c_no ORDER BY sc1.s_score DESC) AS m
FROM score sc1
)AS a
WHERE m<=3
GROUP BY sc1.c_no;

#26.查询每门课程被选修的学生数
#思路:score查课程选修人,course查询课程信息
SELECT
	c.c_no,
	c.c_name,
	COUNT(sc.s_no) '选修人数'
FROM score sc
INNER JOIN course c ON c.c_no=sc.c_no
GROUP BY c.c_no,c.c_name;

#第二种做法,使用case when
SELECT
	c.c_no,
	c.c_name,
	SUM(CASE WHEN c.c_no=0001 THEN 1 ELSE 0 END)'语文',
	SUM(CASE WHEN c.c_no=0002 THEN 1 ELSE 0 END)'数学',
	SUM(CASE WHEN c.c_no=0003 THEN 1 ELSE 0 END)'英语'
FROM score sc
INNER JOIN course c ON c.c_no=sc.c_no
GROUP BY c.c_no,c.c_name;

SELECT
	SUM(CASE WHEN c.c_no=0001 THEN 1 ELSE 0 END)'语文人数',
	SUM(CASE WHEN c.c_no=0002 THEN 1 ELSE 0 END)'数学人数',
	SUM(CASE WHEN c.c_no=0003 THEN 1 ELSE 0 END)'英语人数'
FROM score sc
INNER JOIN course c ON c.c_no=sc.c_no
GROUP BY NULL;

#27.查询出只有两门课程的全部学生的学号和姓名
#思路:查score,以s_no为group by,count(*)<=2,再连表查学生信息
SELECT 
	stu.s_no,
	stu.s_name
FROM score sc
INNER JOIN student stu ON stu.s_no=sc.s_no
GROUP BY stu.s_no,stu.s_name
HAVING COUNT(sc.c_no)>=2;

#28.查询男生,女生人数
#思路:如果是0代表女生,1代表男生,则用case when替代为'男','女'
SELECT
	stu.s_gender,
	COUNT(*)'人数'
FROM student stu
GROUP BY stu.s_gender;


#29.查询名字中含有'风'的学生信息
SELECT *
FROM student stu
WHERE stu.s_name LIKE '%猴%';


#30.略,出处无

#31.查询1990年出生的学生名单(重点year)
SELECT *
FROM student stu
WHERE YEAR(stu.s_birth)=1990;

#32.查询平均成绩>=70的所有学生的学号,姓名和平均成绩(不重要)
SELECT
	stu.s_no,
	stu.s_name,
	AVG(sc.s_score)
FROM score sc
INNER JOIN student stu ON stu.s_no=sc.s_no
GROUP BY stu.s_no,stu.s_name
HAVING AVG(sc.s_score)>=70;


#33.查询每门课程的平均成绩,结果按平均成绩升序排序,平均成绩相同时,按课程号降序排列(不重要)
SELECT
	sc.c_no,
	AVG(sc.s_score)
FROM score sc
GROUP BY sc.c_no
ORDER BY AVG(sc.s_score),sc.c_no DESC;

#34.查询课程名称为'数学',且分数低于60的学生姓名和分数(不重点)
#关于on条件过滤和and https://blog.csdn.net/qq_45138120/article/details/123932581
SELECT
	stu.s_no,
	stu.s_name,
	c.c_name,
	sc.s_score
FROM course c
INNER JOIN score sc ON sc.c_no=c.c_no AND sc.s_score<60
INNER JOIN student stu ON stu.s_no=sc.s_no
WHERE c.c_name='数学';


#35.查询所有学生的课程及分数情况(重点)
#1.因为要选出需要的字段 用case when当name=数学 then score
#2.因为group by要与select一致,所以max修饰case when
#3.因为要展现出每个同学的各科成绩为一行,所以用case
SELECT
	stu.s_no '学号',
	stu.s_name '姓名',
	MAX(CASE WHEN sc.c_no=0001 THEN sc.s_score ELSE NULL END)'语文',
	MAX(CASE WHEN sc.c_no=0002 THEN sc.s_score ELSE NULL END)'数学',
	MAX(CASE WHEN sc.c_no=0003 THEN sc.s_score ELSE NULL END)'英语'
FROM student stu
LEFT JOIN score sc ON sc.s_no=stu.s_no
#inner join course c on c.c_no=sc.c_no
WHERE 1=1
GROUP BY stu.s_no,stu.s_name;

#36.查询任何一门课程成绩在70分以上的姓名,课程名称和分数(重点)
#ps:不用group by
#思路:查询score>=70,再展示出来
#解答1
SELECT 
	stu.s_name,
	stu.s_gender,
	(CASE WHEN sc.c_no=0001 THEN sc.s_score ELSE NULL END)'语文',
	(CASE WHEN sc.c_no=0002 THEN sc.s_score ELSE NULL END)'数学',
	(CASE WHEN sc.c_no=0003 THEN sc.s_score ELSE NULL END)'英语'
FROM student stu
INNER JOIN score sc ON sc.s_no=stu.s_no AND sc.s_score>=70
INNER JOIN course c ON c.c_no=sc.c_no;

#解答2
SELECT 
	stu.s_no,
	stu.s_name,
	c.c_name,
	sc.s_score
FROM student stu
INNER JOIN score sc ON sc.s_no=stu.s_no AND sc.s_score>=70
INNER JOIN course c ON c.c_no=sc.c_no;

#37.查询不及格的课程并按课程号从大到小排列(不重点)
SELECT
	sc.*
FROM score sc
WHERE sc.s_score<60
ORDER BY sc.c_no DESC;
#查询不及格的课程,并显示学生和课程信息(课程号相同,则按学号降序排列)
SELECT
	stu.s_no,
	stu.s_name,
	c.c_name,
	sc.s_score
FROM student stu
INNER JOIN score sc ON sc.s_no=stu.s_no
INNER JOIN course c ON c.c_no=sc.c_no
WHERE sc.s_score<60
ORDER BY sc.c_no,sc.s_no DESC;

#38.查询课程编号为03且课程成绩在80分以上的学生的学号和姓名(不重要)
SELECT 
	stu.s_no,
	stu.s_name,
	sc.c_no,
	sc.s_score
FROM score sc
INNER JOIN student stu ON stu.s_no=sc.s_no
WHERE sc.c_no=0003 AND sc.s_score>=80;

#39.求每门课程的学生人数(不重要)
#解答1
SELECT 
	COUNT(CASE WHEN sc.c_no=0001 THEN 1 ELSE NULL END) '语文人数',
	COUNT(CASE WHEN sc.c_no=0002 THEN 1 ELSE NULL END) '数学人数',
	COUNT(CASE WHEN sc.c_no=0003 THEN 1 ELSE NULL END) '英语人数'
FROM score sc;
#解答2
SELECT
	c.c_no,
	c.c_name,
	COUNT(1) '人数'
FROM score sc
INNER JOIN course c ON c.c_no=sc.c_no
GROUP BY c.c_no,c.c_name;

#40.查询选修'张三'老师所教课程的学生中成绩最高的学生姓名及其成绩(重要top)
#SQL server中用top,MySQL中用limit,select筛选的是order by后的数
#limit用法:
#1个参数:limit a,显示a条记录
#2个参数:limit a,b, 从a+1条记录开始,显示b条记录
SELECT
	stu.s_no,
	stu.s_name,
	c.c_name,
	sc.s_score
FROM student stu
INNER JOIN score sc ON sc.s_no=stu.s_no
INNER JOIN course c ON c.c_no=sc.c_no
INNER JOIN teacher t ON t.t_no=c.t_no
WHERE t.t_name='张三'
ORDER BY sc.s_score DESC
LIMIT 1;

SELECT
	stu.s_no,
	stu.s_name,
	c.c_name,
	sc.s_score
FROM student stu
INNER JOIN score sc ON sc.s_no=stu.s_no
INNER JOIN course c ON c.c_no=sc.c_no
INNER JOIN teacher t ON t.t_no=c.t_no
WHERE t.t_name='张三'
ORDER BY sc.s_score DESC
LIMIT 0,1;

#41.查询不同课程成绩相同的学生的学生编号,课程编号,学生成绩(重点)
SELECT
	sc1.s_no,
	sc1.c_no,
	sc2.c_no,
	sc1.s_score
FROM score sc1
INNER JOIN score sc2 ON sc2.s_no=sc1.s_no
WHERE sc2.c_no!=sc1.c_no AND sc2.s_score=sc1.s_score;


#42.查询每门功课成绩最好的前两名(同22和25题)
#窗口函数未运行,解答仅供参考
SELECT
	stu.s_no,
	stu.s_name,
	row_number() over(PARTITION BY sc1.c_no ORDER BY sc1.s_score DESC) AS m
FROM score sc1
INNER JOIN student stu ON stu.s_no=sc1.s_no
WHERE m<=2;

#43.统计每门课程选修人数(超过5人的课程才统计),要求输出课程号和选修人数,
#查询结果按人数降序排列,若人数相同,按课程号升序排列(不重要)
SELECT
	sc.c_no,
	COUNT(1)
FROM score sc
GROUP BY sc.c_no
HAVING COUNT(1)>2
ORDER BY COUNT(1) DESC,sc.c_no;

#44.检索至少选修两门课程的学生学号(不重要)
SELECT 
	sc.s_no,
	COUNT(1) '选修课程数'
FROM score sc
GROUP BY sc.s_no
HAVING COUNT(1)>=2;

#45.查询选修了全部课程的学生信息(重点划红线的地方)
#思路:选修课程数与总课程数相等,即选修了全部课程
#假如总课程数为m,查选修课程数为m的学生信息,如下
SELECT
	stu.s_no,
	stu.s_name
FROM score sc
INNER JOIN student stu ON stu.s_no=sc.s_no
GROUP BY stu.s_no,stu.s_name
HAVING COUNT(1)=m
#总课程数m如下:
SELECT COUNT(1) FROM course c
#(与题干无关)如果考虑一门课有几个不同的老师教,则如下
SELECT COUNT(*) FROM
(
	SELECT
	c.c_no
	FROM course c
	GROUP BY c.c_no
)AS m;

SELECT
	stu.s_no,
	stu.s_name
FROM score sc
INNER JOIN student stu ON stu.s_no=sc.s_no
GROUP BY stu.s_no,stu.s_name
HAVING COUNT(1)=(SELECT COUNT(*) FROM
(
	SELECT
	c.c_no
	FROM course c
	GROUP BY c.c_no
)AS m);

#46.查询各学生的年龄(精确到月份)
#ps:年份转换为月份,比如结果是1.9, datediff最后取1年
#该函数在sql server和mysql中不同
SELECT
	stu.s_no,
	stu.s_name,
	stu.s_birth,
	DATEDIFF(CURDATE(),stu.s_birth)/12 '年龄'
FROM student stu;

#参考答案(SQL server中)(MySQL5.7不支持3参数datediff函数)
SELECT
	stu.s_no,
	stu.s_name,
	stu.s_birth,
	DATEDIFF(MONTH,s_birth,CURDATE())/12 '年龄'
FROM student stu;

#47.查询本月过生日的学生(无法使用week, date(now))
#一些函数无法在SQL server中使用,所以取标准答案(MySQL)
SELECT
	stu.s_no,
	stu.s_name
FROM student stu
WHERE MONTH(stu.s_birth)=MONTH(CURDATE());

#47.查询本周过生日的学生
SELECT
	stu.s_no,
	stu.s_name,
	stu.s_birth
FROM student stu
WHERE WEEK(stu.s_birth)=WEEK(CURDATE());


#48.查询下周过生日的学生
SELECT
	stu.s_no,
	stu.s_name,
	stu.s_birth
FROM student stu
WHERE WEEK(stu.s_birth)=WEEK(DATE_ADD(CURDATE(),INTERVAL 7 DAY));

#49.查询下月过生日的学生
SELECT
	stu.s_no,
	stu.s_name,
	stu.s_birth
FROM student stu
WHERE MONTH(stu.s_birth)=MONTH(DATE_ADD(CURDATE(),INTERVAL 1 MONTH));


#49.查询没学过'张三'老师所教的任一门课程的学生姓名
SELECT
	DISTINCT stu.s_no,
	stu.s_name
FROM student stu
LEFT JOIN score sc ON sc.s_no=stu.s_no
LEFT JOIN course c ON c.c_no=sc.c_no
LEFT JOIN teacher t ON t.t_no=c.t_no
WHERE t.t_name!='张三';

#50.查询两门以上不及格课程的同学的学号及其(总)平均成绩
SELECT
	s_no
FROM score
WHERE s_score<60
GROUP BY s_no
HAVING COUNT(s_score)>=2;

#题目的其中一种意思,平均成绩为总平均成绩,而不是未合格的平均成绩
#因此SQL如下,我认为这个SQL写的很好
SELECT
	s_no,
	AVG(s_score)
FROM score
GROUP BY s_no
HAVING COUNT(CASE WHEN s_score<60 THEN 1 ELSE NULL END)>=2;