sql面试

1.查询各个课程总成绩(显示课程号和总成绩)
SELECT cid,SUM(score) AS 总成绩 FROM t_score GROUP BY cid;

2.查询选了课程的学生人数

SELECT COUNT(DISTINCT(sid)) FROM t_score;

3.查询各科成绩的最高分和最低分

SELECT cid,MAX(score) AS 最高分,MIN(score) AS 最低分 FROM t_score GROUP BY cid;

4.查询每门课被选修的学生数
SELECT cid,COUNT(sid) 选学人数 FROM t_score GROUP BY cid;

5.查询平均成绩大于60分的学号和平均成绩　***１
SELECT sid,AVG(score) 平均成绩 FROM t_score GROUP BY sid HAVING 平均成绩 > 60;

6.查询至少选修两门课的学号

insert into t_score values(5,1,88);
insert into t_student values(5,‘John’,‘m’,‘2004-3-9’);

SELECT sid FROM t_score GROUP BY sid HAVING COUNT(cid) >=2;

7.查询不及格的课程并按课程号从大到小排列（distinct）
SELECT DISTINCT(cid) FROM t_score WHERE score <60 ORDER BY cid DESC;

8.查询每门课程的平均成绩，结果按平均成绩升序排序，平均成绩相同时，按课程号降序排列
SELECT cid,AVG(score) 平均成绩 FROM t_score GROUP BY cid ORDER BY 平均成绩,cid DESC;

9.检索课程编号为3且分数小于60的学生学号，结果按按分数降序排列
SELECT sid,score FROM t_score WHERE cid=3 AND score < 60 ORDER BY score DESC;

10.统计每门课程的学生选修人数(超过2人的课程才统计)
-要求输出课程号和选修人数，查询结果按人数降序排序，若人数相同，
按课程号升序排序

SELECT cid,COUNT(sid) 人数 FROM t_score GROUP BY cid HAVING 人数 > 2 ORDER BY 人数 DESC,cid;

11.查询两门以上不及格课程的同学的学号，以及不及格课程的平均成绩 (where要加在group by之前）***２

SELECT sid,AVG(score) as 平均成绩
FROM t_score
WHERE score<60
GROUP BY sid
HAVING COUNT(cid)>2;

12.查询所有课程成绩小于60分学生的学号、姓名 （考察子查询和in的使用）
SELECT id,name FROM t_student WHERE id IN
(SELECT sid FROM t_score GROUP BY sid HAVING MAX(score) < 60);

13.查询出只选修了1门课程的全部学生的学号和姓名（in）
SELECT id,name FROM t_student WHERE id IN
(SELECT sid FROM t_score GROUP BY sid HAVING count(cid) = 1);

14.查询没有学全所有课的学生的学号、姓名
SELECT id,name FROM t_student WHERE ID IN
(SELECT sid FROM t_score GROUP BY sid HAVING count(cid) < (SELECT COUNT(*) FROM t_course)) ;

15.1989年出生的学生名单
SELECT * FROM t_student WHERE YEAR(birthday)=1989;

16.按课程号分组取成绩最大值所在行的数据(关联子查询)

SELECT *
FROM t_score as a
WHERE score =(
SELECT MAX(score)
FROM t_score as b
WHERE a.cid=b.cid
GROUP BY cid);

17.按照课程号取每门课前两名（union查询）
(SELECT * FROM t_score WHERE cid=1 ORDER BY score desc limit 2)
UNION
(SELECT * FROM t_score WHERE cid=2 ORDER BY score desc limit 2)
UNION
(SELECT * FROM t_score WHERE cid=3 ORDER BY score desc limit 2);

18.查询所有学生的学号、姓名、选课数、总成绩(考查查询结果当作表来做join查询)　***３

SELECT sid,name,选课数,总成绩 FROM
(SELECT sid,count(cid) 选课数,sum(score) 总成绩 FROM t_score GROUP BY sid)
c JOIN t_student s ON c.sid = s.id;

或者
select a.id,a.name,count(b.cid) as 选课数,sum(b.score) as 总成绩
from t_student as a left join t_score as b
on a.id = b.sid
group by a.id;

19.查询平均成绩大于70 的所有学生的学号、姓名和平均成绩 (考查查询结果当作表来做join查询)　***４

SELECT sid,name,平均成绩 FROM
(
SELECT sid,AVG(score) 平均成绩 FROM t_score GROUP BY sid HAVING 平均成绩 > 70)
c JOIN t_student s ON c.sid = s.id;

或者

SELECT sid,name,AVG(score)平均成绩 FROM t_score c JOIN t_student s ON c.sid = s.id
GROUP BY sid HAVING AVG(score) > 70;

20.查询学生的选课情况：学号，姓名，课程号，课程名称（三张表的join）
SELECT sid,s.name,cid,r.name FROM t_score c JOIN t_student s ON c.sid = s.id JOIN t_course r ON c.cid = r.id;

21.查询出每门课程的及格人数和不及格人数-- 考察case表达式　***５

SELECT cid,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) 不及格人数,
SUM(CASE WHEN score >= 60 THEN 1 ELSE 0 END) 及格人数 FROM t_score GROUP BY cid;

22.使用分段[100-85],[85-70],[70-60],[60]来统计各科成绩，分别统计：
各分数段人数，课程号和课程名称-- 考察case表达式

SELECT cid,name,
SUM(CASE WHEN score >=85 AND score <=100 THEN 1 ELSE 0 END) ‘[100-85]’,
SUM(CASE WHEN score >=70 AND score <85 THEN 1 ELSE 0 END) ‘[85-70]’,
SUM(CASE WHEN score >=60 AND score <70 THEN 1 ELSE 0 END) ‘[70-60]’,
SUM(CASE WHEN score <60 THEN 1 ELSE 0 END) ‘[<60]’
FROM t_score c JOIN t_course r ON c.cid = r.id GROUP BY cid;

23.查询男生女生人数

SELECT gender,count(*) FROM t_student GROUP BY gender;

SELECT case when gender=‘m’ then ‘男’ else ‘女’ end 性别,count(*) 人数 FROM t_student GROUP BY gender;

24.查询同名同姓名单统计同名人数

先准备点数据：
create table t_student2 as select * from t_student;
insert into t_student2 values(6,‘Tom’,‘f’,‘1998-01-08’);

SELECT name,count(*) 同名人数 FROM t_student2 GROUP BY name HAVING 同名人数 > 1;