面试常见sql

面试常见sql

---

---

一、查询学生表信息

学生表(student)基本信息如下

科目和分数表(grade)

1、统计每个学生的总成绩(由于学生可能有重复名字),显示字段:学生 id，姓 名，总成绩

GROUP BY 和 ORDER BY 要在一起使用的时候可以像下面这样操作

SELECT a.id, a.name, c.sum_score
from student a,
(SELECT b.id, sum(b.score) as sum_score FROM grade b
GROUP BY id
)c
WHERE a.id = c.id ORDER BY sum_score DESC

2、列出各门课程成绩最好的学生， 要求显示字段: 学号，姓名,科目，成绩

SELECT c.id , a.name, c.kemu, c.score
FROM grade c, student a,
(SELECT b.kemu, MAX(b.score) as max_score FROM grade b
GROUP BY kemu) t
WHERE c.kemu = t.kemu
AND c.score = t.max_score
AND a.id = c.id

3、计算每个人的平均成绩， 要求显示字段: 学号，姓名，平均成

select a.id, a.name, c.avg_score
from student a,
(select b.id, avg(b.score) as avg_score from grade b
group by b.id
)c
where a.id = c.id

4、列出各门课程的平均成绩，要求显示字段:课程，平均成绩

select b.kemu, avg(b.score) from grade b
group by b.kemu

5、用一条 SQL 语句查询 学生表每门课都大于 80 分的学生姓名

SELECT name
FROM xuesheng
GROUP BY name
HAVING MIN(score)> 80 AND COUNT(kecheng)>=3

6、用 sql 查询出“张”姓学生中平均成绩大于 75 分的学生信息

select * from student
where name in
(select name from student
where name like '张%' group by name having avg(score) > 75);