mysql 经典45道题（19-32题）

mysql 经典45道题（第二部分）

19. 查询每门课程被选修的学生数

SELECT
	CId,COUNT( 1 ) 
FROM
	sc 
GROUP BY
	cid;

20. 查询出只选修两门课程的学生学号和姓名

SELECT
	a.SId,a.Sname,
	COUNT( CId ) 
FROM
	student a
	JOIN sc b ON a.SId = b.SId 
GROUP BY
	a.SId 
HAVING
	COUNT( b.CId ) = 2;

21. 查询男生、女生人数

SELECT
	Ssex,COUNT( 1 ) 
FROM
	student 
GROUP BY
	Ssex

22. 查询名字中含有「风」字的学生信息

SELECT
	* 
FROM
	student 
WHERE
	Sname LIKE '%风%'

23. 查询同名同性学生名单，并统计同名人数

-- 解题思路：按照姓名分组，姓名形同的情况下按照性别分组统计人数，
-- 如果统计人数大于等于1，那说明这个人就是同名同性的
SELECT
	*,COUNT( 1 ) 
FROM
	student 
GROUP BY
	Sname,Ssex 
HAVING
	COUNT( 1 )> 1;

24. 查询 1990 年出生的学生名单

SELECT
	* 
FROM
	student 
WHERE
	YEAR ( Sage ) = 1990

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

SELECT
	cid,AVG( score ) AS avgScore 
FROM
	sc 
GROUP BY
	CId 
ORDER BY avgScore DESC,CId ASC

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

SELECT
	a.SId,b.Sname,AVG( a.score ) AS avgScore 
FROM
	sc a
	LEFT JOIN student b ON a.SId = b.SId 
GROUP BY
	a.SId 
HAVING
	AVG( a.score )>= 85

27. 查询课程名称为「数学」，且分数低于 60 的学生姓名和分数

SELECT
	b.Sname,
	a.score 
FROM
	sc a
	INNER JOIN student b ON a.SId = b.SId
	INNER JOIN course c ON a.CId = c.CId 
WHERE
	c.Cname = '数学' 
	AND a.score < 60;

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

SELECT
	a.sname,
  c.Cname,
	b.score 
FROM
	student a
	INNER JOIN sc b ON a.SId = b.SId
  INNER JOIN course c on  b.CId =c.CId
WHERE b.score >70

30. 查询不及格的课程

SELECT
	a.sname,
	c.Cname,
	b.score 
FROM
	student a
	INNER JOIN sc b ON a.SId = b.SId
	INNER JOIN course c ON b.CId = c.CId 
WHERE
	b.score < 60

31. 查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名

SELECT
	* 
FROM
	student 
WHERE
	sid IN ( SELECT SId FROM sc WHERE CId = '01' AND score >= 80 );

32. 求每门课程的学生人数

SELECT
	cid,count( 1 ) AS con 
FROM SC 
GROUP BY cid

33. 成绩不重复，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT
	a.*,
	b.score 
FROM
	Student a
	INNER JOIN SC b ON a.sid = b.sid
	INNER JOIN Course c ON b.cid = c.cid
	INNER JOIN Teacher d ON c.tid = d.tid 
WHERE
	d.Tname = '张三' 
ORDER BY
	b.score DESC 
	LIMIT 1