SQL笔试题

增：

insert INTO student (s_id, s_name, s_birth, s_sex)
values
	(09, 1, 1, 1)

删：

delete
FROM
	student
WHERE
	s_id = 09

改：

update student
SET s_name = "李海"
WHERE
	s_id = 01

注意：

一、count(*) 和 count(1) 和 count(列名)区别

count(*)：包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL
count(1)：包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL
count(列名)：只包括列名那一列，在统计结果的时候，会忽略列值为空（这里的空不是只空字符串或者0，而是表示null）的计数，即某个字段值为NULL时，不统计。

1、查询课程编号为“01”的课程比“02”的课程成绩高的所有学生的学号。（join联结自身两张表，条件：学生id相等，课程分数比较，课程号为01和02）
要点：联结

SELECT
	a.s_id
FROM
	Score a
JOIN Score b ON a.s_id = b.s_id
AND a.s_score > b.s_score
WHERE
	a.c_id = '01'
AND b.c_id = '02'

2、查询平均成绩大于60分的学生的学号和平均成绩（group by归类学生id的having平均成绩大于60）

SELECT
	s_id,
	avg(s_score)
FROM
	Score
GROUP BY
	s_id
HAVING
	avg(s_score) > 60

3、查询所有学生的学号、姓名、选课数、总成绩（联结成绩表和学生表，条件：学生id相等。group by归类学生id相等的）
要点：内联结

SELECT
	student.s_id AS "学号",
	student.s_name AS "姓名",
	count(score.c_id) AS "选课数",
	SUM(score.s_score) AS "总成绩"
FROM
	score
JOIN student ON score.s_id = student.s_id
GROUP BY
	score.s_id

4、查询姓“张”的老师的个数

SELECT
	count(t_id)
FROM
	teacher
WHERE
	t_name LIKE "张%"

5、查询没学过“张三”老师课的学生的学号、姓名(重点)（三表联结）
（先查学过张三老师的课为子查询；再查询学生表的学生不在学过张三的课的表中）

SELECT
	s_id,
	s_name
FROM
	Student
WHERE
	s_id NOT IN (
		SELECT
			s_id
		FROM
			Score
		JOIN Course ON Score.c_id = Course.c_id
		JOIN Teacher ON Course.t_id = Teacher.t_id
		WHERE
			t_name = '张三'
	)

6、查询学过“张三”老师所教的所有课的同学的学号、姓名

SELECT
	Student.s_id,
	s_name
FROM
	Student
JOIN Score ON Student.s_id = Score.s_id
JOIN Course ON Score.c_id = Course.c_id
JOIN Teacher ON Teacher.t_id = Course.t_id
WHERE
	t_name = '张三'

7、查询学过编号为“01”的课程并且也学过编号为“02”的课程的学生的学号、姓名（同时满足两个条件，使用两个子查询）

SELECT
	s_id,
	s_name
FROM
	student
WHERE
	s_id IN (
		SELECT
			s_id
		FROM
			score
		WHERE
			score.c_id = 01
	)
AND s_id IN (
	SELECT
		s_id
	FROM
		score
	WHERE
		score.c_id = 02
)

8、查询课程编号为“02”的总成绩
要点：聚合

SELECT
	SUM(s_score)
FROM
	score
WHERE
	c_id = 02

9、查询所有课程成绩小于60分的学生的学号、姓名（先查大于等于60分的学生id的表，然后再查学生表不not in这个表）

SELECT
	s_id,
	s_name
FROM
	student
WHERE
	s_id NOT IN (
		SELECT
			s_id
		FROM
			score
		WHERE
			score.s_score >= 60
	)

18、查询各科成绩最高和最低的分： 以如下的形式显示：课程ID，最高分，最低分
（把课程id归类，比各课成绩最高分、最低分）

SELECT
	c_id as "课程ID",
	MAX(s_score) as "最高分",
	MIN(s_score) as "最低分"
FROM
	score
GROUP BY
	c_id

19、按各科平均成绩从低到高和及格率的百分数从高到低排列，以如下形式显示：课程号,课程名,平均成绩,及格百分数

select a.c_id as '课程号',c_name as '课程名',avg(s_score) as '平均成绩',
concat((select count(b.s_score) from Score b where b.s_score>=60 and a.c_id=b.c_id)/
(select count(b.s_score) from Score b where a.c_id=b.c_id)*100,'%') as '及格百分数'
from Score a join Course c
on a.c_id=c.c_id
group by a.c_id,c_name
order by 平均成绩, 及格百分数 DESC;

21、查询不同老师所教不同课程平均分从高到低显示（联结老师和课程表，联结课程和成绩表，归类课程id，order by 平均分 desc）

SELECT
	teacher.t_id,
	teacher.t_name,
	course.c_name,
	avg(score.s_score)
FROM
	teacher
JOIN course ON teacher.t_id = course.t_id
JOIN score ON course.c_id = score.c_id
GROUP BY
	course.c_id
ORDER BY
	AVG(score.s_score) DESC

23、使用分段[100-85],[85-70],[70-60],[<60]来统计各科成绩，分别统计各分数段人数：课程ID和课程名称

select a.c_id AS '课程ID',c_name '课程名称',
sum(case when s_score between 85 and 100 then 1 else 0 end) as '[100-85]',
sum(case when s_score >=70 and s_score<85 then 1 else 0 end) as '[85-70]',
sum(case when s_score>=60 and s_score<70  then 1 else 0 end) as '[70-60]',
sum(case when s_score<60 then 1 else 0 end) as '[<60]'
from Score a join Course b on a.c_id=b.c_id
group by a.c_id,c_name;

26、查询每门课程被选修的学生数

SELECT
	c_id,
	COUNT(s_id)
FROM
	score
GROUP BY
	c_id

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

SELECT
	student.s_id,
	student.s_name
FROM
	student
JOIN score ON student.s_id = score.s_id
GROUP BY
	student.s_id
HAVING
	count(score.c_id) = 2

28、查询男生、女生人数

SELECT
	s_sex,
	COUNT(s_sex)
FROM
	student
GROUP BY
	s_sex

29、查询名字中含有“风”字的学生信息

SELECT
	*
FROM
	Student
WHERE
	s_name LIKE '%风%'

30、查询同名同姓学生名单并统计同名人数

SELECT
	s_name,
	count(*)
FROM
	Student
GROUP BY
	s_name
HAVING
	count(*) > 1

31、1990年出生的学生名单

SELECT
	s_name
FROM
	Student
WHERE
	s_birth LIKE '1990%'

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

SELECT
	student.s_id AS "学号",
	student.s_name AS "姓名",
	avg(score.s_score) AS "平均成绩"
FROM
	student
JOIN score ON student.s_id = score.s_id
GROUP BY
	student.s_id
HAVING
	avg(score.s_score) > 85

34、查询课程名称为“数学”且分数低于60的学生姓名和分数

难度：***

select s_name as 学生姓名, s_score as 分数
from Student a join Score b on a.s_id=b.s_id
join Course c on c.c_id=b.c_id
where c_name='数学'
and s_score <60

35、查询所有学生的选课情况

SELECT
	student.s_name,
	course.c_name,
	course.c_id
FROM
	student
JOIN score ON student.s_id = score.s_id
JOIN course ON score.c_id = course.c_id

37、查询不及格的课程并按课程号从大到小排列

SELECT
	c_id,
	s_score
FROM
	score
WHERE
	s_score < 60
ORDER BY
	c_id