 # SQL基础测试题目 Posted by

1、完成查询如下表显示，显示全部学生的信息，按照学生编号的升序排列，对同一学号按照课程名称的字母顺序显示。

# MySQL练习题【二】参考答案

### 1.导入表结构和数据 sql脚本 View Code

``````select s.STUDNO "学生编号",s.STUDNAME "学生姓名",c.COURSENAME "课程名称",g.GRADE "成绩"from student s left join score g on(s.STUDNO=g.STUDNO) left join course c on (c.COURSEID=g.COURSEID)order by s.STUDNO,c.COURSENAME;
``````

### 2.sql语句

1、查询课程编号“001”比课程编号“002” ``````#1.先查询001课程和"002"课程的学生成绩,临时表
#2.让两个临时表进行比较
select a.s_id from
(select * from score where c_id ='1') a,
(select * from score where c_id ='2') b
where a.s_id = b.s_id and a.s_score > b.s_score;
`````` 2、查询平均成绩大于60分的同学的学号和平均成绩;

``````#1.先查询学生的学号和平均成绩
#2.再进行条件过滤
select s_id, avg(s_score) as sc from score GROUP BY s_id having sc>60;
``````

3、查询所有同学的学号、姓名、选课数、总成绩； ``````#1.先查学生表中的字段
#2.然后再连表查询成绩表中的字段
select s.s_id,s.s_name,COUNT(sc.c_id)AS'选课数',sum(sc.s_score)
from student s
LEFT JOIN score sc
on s.s_id = sc.s_id GROUP BY s.s_id
`````` 4、查询含有”子”的老师的个数；

``````select count(t_id) from teacher where t_name like'%子%'
``````

5、查询没学过“老子”老师课的同学的学号、姓名； ``````#1.先查询"老子"老师教什么课程
#2.再查询学过该老师课程的学生有哪些
#3.排除学过该老师课的学生,剩下的就是没有学过的学生

select s_id,s_name from student where s_id not in(
select s_id FROM score where c_id =
(select c_id from teacher,course where teacher.t_id = course.t_id and t_name ='老子')
)
`````` 6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名； ``````#1.注意:是即学过001也学过002课程的学生
#2.思路:先查询有哪些学生学了'001'或者'002'课程
#3.然后进行分组,学科数 = 2 表示学了两门学科

select student.s_id,student.s_name FROM

(select s_id from score se where se.c_id='1' or se.c_id ='2'  GROUP BY se.s_id  HAVING  count(c_id)>1) as B

LEFT JOIN student on student.s_id = B.s_id;
`````` 7、查询学过“老子”老师所教的所有课的同学的学号、姓名； ``````#1.先查询"老子"老师教哪些课程
#2.再查询哪些学生学习了这些课程
#3.再根据学生编号分组,如果分组后的个数 ="老子"老师所教授课程的个数,则表示学过该老师所有课程.
``````

select s_id,s_name from student where
s_id in(

select s_id FROM score where c_id
in(
select c_id from
teacher,course where teacher.t_id = course.t_id and t_name =’老子’
) group by s_id having count(s_id)
=( select count(c_id) from teacher,course where teacher.t_id =
course.t_id and t_name =’老子’)
) 8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；

``````#同第一题

select a.s_id from
(select * from score where c_id ='1') a,
(select * from score where c_id ='2') b
where a.s_id = b.s_id and a.s_score < b.s_score;
``````

9、查询有课程成绩小于60分的同学的学号、姓名；

``````#1.查询所有成绩分数小于60分的同学
#2.关联学生表,去重复

select DISTINCT student.s_id,student.s_name from score,student where score.s_id=student.s_id and s_score < 60
``````

10、查询没有学全所有课的同学；

``````#1.分数表分组得到学生选课数量
#2.选课数量 = 课程表总课程
select student.* from score LEFT JOIN student
on score.s_id = student.s_id
GROUP BY score.s_id HAVING count(score.s_id) = (select count(c_id) from course);
``````

11、查询至少有一门课与学号为“002”的同学所学相同的同学的学号和姓名； ``````#1 002学生学了什么课程
#2.其他学过002学生其中一门课程的学生
#3.关联学生表

select student.s_id,student.s_name from score LEFT JOIN student
on score.s_id = student.s_id
where score.c_id in(select c_id from score where s_id = '2') and score.s_id !='2' GROUP BY score.s_id
`````` 12、查询学过 学号为“002”同学全部课程 ``````# 1先找到学过002同学课程的人
# 2.课程个数 = 002学生课程个数
# 3.关联学生表,如果不显示自身就去掉

select student.s_id,student.s_name from score LEFT JOIN student on
score.s_id = student.s_id
where score.c_id in(select c_id from score where score.s_id ='2')
and score.s_id !='2'
GROUP BY score.s_id having count(score.s_id) =(select count(c_id) from score where score.s_id ='2')
`````` 13、查询和“002”号的同学学习的课程完全相同的,其他同学学号和姓名； ``````#1.找出与002学生学习课程数相同的学生(你学两门,我也学两门)
#2.然后再找出学过'002'学生课程的学生,剩下的一定是至少学过一门002课程的学生
#3.再根据学生ID进行分组,剩下学生数count(1) = 002学生所学课程数

SELECT * FROM score where score.s_id in(

select score.s_id from score GROUP BY s_id

HAVING count(1) =(select count(1) from score where score.s_id = '2')
)
and score.c_id in (select c_id from score where score.s_id = '2') and score.s_id!='2'

GROUP BY score.s_id HAVING count(1) = (select count(1) from score where score.s_id = '2')
`````` 14、把“score”表中“老子”老师教的课的成绩都更改为此课程的平均成绩； ``````#1.获得"老子"老师所教的课程号
-- select c_id from course LEFT JOIN teacher on teacher.t_id = course.t_id and teacher.t_name ='老子';

#2. 获得"老子"老师课程的平均成绩
-- select AVG(score.s_score) s_score from score where score.c_id
--     in(select c_id from course,teacher where teacher.t_id = course.t_id and teacher.t_name ='老子')
--
#3.注意:如果直接把上面的查询结果作为更新字段,则会报错(不能先select出同一表中的某些值，再update这个表(在同一语句中))
#所以 需要将查询结果集包装(加一层查询)变为临时表.则可以作为更新字段

update score SET s_score = (
select bb.s_score from (
select AVG(s_score) s_score from score where score.c_id
in(select c_id from course,teacher where teacher.t_id = course.t_id and teacher.t_name ='老子')
)as bb)
where score.c_id in (select c_id from course,teacher where teacher.t_id = course.t_id and teacher.t_name ='老子')
`````` 15、删除学习“墨子”老师课的score表记录；

``````#1.找到墨子老师教的课程
#2.根据课程号直接删除

DELETE from score where c_id in(select c_id from course INNER JOIN teacher  on teacher.t_id = course.t_id and teacher.t_name = '墨子')
``````

16、按平均成绩从高到低显示所有学生的“python”、“java”、“linux”三门的课程成绩，按如下形式显示： #1.学生python课程的平均成绩是多少?
select sc1.s_score from course c,score sc1 where c.c_id = sc1.c_id
and c.c_name = ‘python’ and sc1.s_id = 1 ORDER BY sc1.s_score desc;
select sc.s_score from course c,score sc where c.c_id = sc.c_id and
c.c_name = ‘java’ and sc.s_id = 1 ORDER BY sc.s_score desc;
select sc.s_score from course c,score sc where c.c_id = sc.c_id and
c.c_name = ‘linux’ and sc.s_id = 1 ORDER BY sc.s_score desc

#2.学生id,有效课程数,有效平均分如何查询?
select sc.s_id,
count(*),
AVG(sc.s_score)
from score sc,course c where sc.c_id = c.c_id GROUP BY sc.s_id;

#3.组合SQL:按平均分排序

select sc.s_id,
(select sc1.s_score from course c,score sc1 where c.c_id = sc1.c_id
and c.c_name = ‘python’ and sc1.s_id = sc.s_id ORDER BY sc1.s_score
desc)as ‘python’,
(select sc1.s_score from course c,score sc1 where c.c_id = sc1.c_id
and c.c_name = ‘java’ and sc1.s_id = sc.s_id ORDER BY sc1.s_score
desc)as ‘java’,
(select sc1.s_score from course c,score sc1 where c.c_id = sc1.c_id
and c.c_name = ‘linux’ and sc1.s_id = sc.s_id ORDER BY sc1.s_score
desc)as ‘linux’,
count(*) as ‘课程数’,
AVG(sc.s_score) as ‘平均分’
from score sc,course c where sc.c_id = c.c_id GROUP BY sc.s_id order
by AVG(sc.s_score) desc; 17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

``````　　select c_id,MAX(s_score),MIN(s_score) from score GROUP BY c_id
``````

18、查询”学生ID”,”各科平均成绩”,”及格率”,并按各科平均成绩从低到高和及格率的百分数从高到低顺序 ``````#1. case when .... then ...else ... end
#2.先获得学生ID,各科平均成绩
#3.计算及格率. 规则:及格课数/总科数 *100
``````

select sc.c_id as ‘学生号’,
avg(sc.s_score) as ‘平均成绩’,
sum(case when sc.s_score >=60 then 1 ELSE 0 end)/count(1) * 100
as ‘及格率’
from score sc GROUP BY sc.c_id order by avg(sc.s_score) asc ,
sum(case when sc.s_score >=60 then 1 ELSE 0 end)/count(1) * 100
desc; 19、查询老师所教课程平均分从高到低显示,并显示老师的名称及课程名称

select teacher.t_name,avg(score.s_score),course.c_name from teacher

LEFT JOIN course on course.t_id = teacher.t_id

LEFT join score on score.c_id = course.c_id

GROUP BY score.c_id

20、统计列印各科成绩,各分数段人数,显示:课程ID,课程名称,[100-85],[85-70],[70-60],[
<60] #1.先统计出课程ID和课程名称,可以利用分组
#2.通过判断语句计算和的方式获得分数段人数

``````select score.c_id, course.c_name,
sum(case when score.s_score between 85 and 100 THEN 1 ELSE 0 END) as '[100-85]',
sum(case when score.s_score between 70 and 85 THEN 1 ELSE 0 END) as '[85-70]',
sum(case when score.s_score between 60 and 70 THEN 1 ELSE 0 END) as '[70-60]',
sum(case when score.s_score < 60 THEN 1 ELSE 0 END) as '[<60]'
from score,course
where score.c_id = course.c_id group by score.c_id
`````` 21、查询每门课程被选修的学生数.

``````select c_id,count(s_id) from score GROUP BY c_id
``````

22、查询出只选修了一门课程的学生的学号和姓名

``````select student.s_id,student.s_name from score
LEFT JOIN student on score.s_id = student.s_id

group by s_id HAVING count(1)='1';
``````

23、查询学生表中男生、女生人数

``````select
sum(case when s_sex ='男' then 1 ELSE 0 end )as '男',
sum(case when s_sex ='女' then 1 ELSE 0 end )as '女'
from student
``````

24、查询姓“张”的学生名单

``````select * from student where student.s_name like '张%'
``````

25、查询同名学生名单，并统计同名人数

``````select s_name,count(1) from student group by s_name;
``````

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

``````select AVG(IFNULL(s1.s_score,0)) from score s1 GROUP BY s1.c_id ORDER BY AVG(IFNULL(s1.s_score,0)) asc,s1.c_id DESC
``````

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

``````select st1.*,avg(sc2.s_score) from student st1,score sc2 where st1.s_id = sc2.s_id  GROUP BY sc2.s_id HAVING avg(sc2.s_score)>65
``````

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

``````select student.s_name,score.s_score from score
LEFT JOIN course on score.c_id = course.c_id
left join student on student.s_id = score.s_id
where course.c_name  ='python' AND score.s_score <60
``````

29、查询所有学生的选课情况,显示

``````select score.s_id,student.s_name,c_name from score LEFT JOIN student
on student.s_id = score.s_id
LEFT join course on course.c_id = score.c_id
``````

30、查询任何一门课程成绩在70分以上的姓名、课程名称和分数； ``````#方式一
#学生编号分组,获得最小分数
select st.s_name,c.c_name,sc.s_score from score sc,student st,course c
where  sc.s_id = st.s_id and sc.c_id = c.c_id
GROUP BY sc.s_id HAVING MIN(sc.s_score) > 70

#方式二
select s2.s_name,c3.c_name,s1.s_score
from score s1,student s2,course c3
where  s1.s_id = s2.s_id and s1.c_id = c3.c_id
GROUP BY s2.s_id
HAVING  sum(case when s1.s_score>60 THEN 1 ELSE 0 end) =
(select count(c_id) from score where s2.s_id= score.s_id GROUP BY score.s_id)
`````` 31、查询不及格的课程，并按课程号从大到小排列

``````select c_id from score where score.s_score < 60 ORDER BY c_id DESC
``````

32、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名

``````select student.s_id,student.s_name from score LEFT JOIN student
on score.s_id = student.s_id
where score.s_score>60 AND score.c_id = 2
``````

33、求 已选课程的学生人数

``````select count( DISTINCT s_id) as '人数' from score
``````

34、查询选修“老子”老师所授课程的学生中，成绩最高的学生姓名及其成 ``````select student.s_name,score.s_score from teacher LEFT JOIN course
on teacher.t_id = course.t_id
LEFT JOIN score
on score.c_id = course.c_id
LEFT JOIN student
on score.s_id = student.s_id
where teacher.t_name = '老子' order BY score.s_score desc LIMIT 1
`````` 35、查询各个课程及相应的选修人数

``````select score.c_id,course.c_name,count(score.s_id) as'选修人数'
from score LEFT JOIN course on course.c_id = score.c_id
GROUP BY score.c_id
``````

36、查询不同课程但成绩相同的学生的学号、课程号、学生成绩

``````select  s1.s_id, s1.c_id, s1.s_score from score s1,score s2
where s1.s_score = s2.s_score and s1.c_id != s2.c_id
``````

37、检索至少选修两门课程的学生学号

``````select s_id from score GROUP BY s_id HAVING COUNT(s_id)>1
``````

38、查询全部学生都选修的课程的课程号和课程名

``````#1.学生数量 = 分组的课程数量

select score.c_id,course.c_name from score
LEFT JOIN course ON score.c_id = course.c_id
GROUP BY score.c_id HAVING count(score.c_id) = (select count(1) from student)
``````

39、查询没学过“老子”老师讲授的任一门课程的学生姓名 ``````#1.先查询"老子"老师教什么课程
#2.再查询学过该老师课程的学生有哪些
#3.排除学过该老师课的学生,剩下的就是没有学过的学生

select s_id,s_name from student where s_id not in(
select s_id FROM score where c_id =
(select c_id from teacher,course where teacher.t_id = course.t_id and t_name ='老子')
)
`````` 40、查询两门以上不及格课程的同学的学号及其平均成绩

``````select score.s_id,avg(score.s_score),COUNT(1) from score where score.s_score <60 GROUP BY score.s_id HAVING COUNT(1)>1
``````

41、检索“003”课程分数小于60，按分数降序排列的同学学号

``````select s_id from score where score.c_id='1' and score.s_score < 160 ORDER BY score.s_score desc
``````

42、删除“002”同学的“001”课程的成绩

``````DELETE from score where score.s_id = '2' and score.c_id ='1'
``````

score,student,course三表之间的等值连接即可

2、查询显示单科最高成绩

zhansan c 98

temp一个临时表，里面存着每一科的最高分，然后再和student，score，course做四张表的连接
(select courseid,max maxgrade from score group by coursed)
temp也可以用分析函数实现

``````create table temp("学生编号","学生姓名","课程名称","单科最高成绩") asselect s.STUDNO,s.STUDNAME,c.COURSENAME,g.GRADEfrom student s,course c,(select studno,courseid,grade from score where (grade,courseid) in (select max,courseid from score group by courseid)) gwhere s.STUDNO=g.STUDNO and c.COURSEID=g.COURSEID;
``````

3、查询显示学生课程及格还是不及格

``````select s.STUDNO "学生编号",s.STUDNAME "学生姓名",c.COURSENAME "课程名称",case when grade>=60 then '及格'else '不及格' end "考试通过状态"from student s,course c,score g where s.STUDNO=g.STUDNO and g.COURSEID = c.COURSEID;
``````

4、统计学生选科的数量

``````select s.STUDNO "学生编号",s.STUDNAME "学生姓名",temp.c "选课数量"from student s,(select count c,studno from courseplan group by studno) tempwhere s.STUDNO=temp.STUDNO;
``````

5、查询单科成绩超过课程平均成绩的学生的信息，列出学生编号，学生姓名，课程名称和课程成绩。

``````select s.STUDNO "学生编号",s.STUDNAME "学生姓名",c.COURSENAME "课程名称",g.grade "成绩" from student s,course c,score g,(select courseid,avg avggrade from score group by courseid) ggwhere s.STUDNO = g.STUDNOand c.COURSEID = g.COURSEID and gg.courseid=c.COURSEID and g.GRADE>gg.avggrade;
``````

6、查询显示需要补考的学生的学生编号，学生姓名和课程名称

``````SELECT s.STUDNO "学生编号",s.STUDNAME "学生姓名",c.COURSENAME "课程名称",g.grade "成绩" FROM student s,course c,score gwhere g.COURSEID = c.COURSEIDAND s.STUDNO = g.STUDNOand g.GRADE<60;
``````

7、统计各科成绩平均分，显示课程编号，课程名称，平均分。

``````select c.COURSEID "课程编号",c.COURSENAME "课程名称",temp.avggrade "平均分"from course c ,(select courseid,avg avggrade from score group by courseid) temp where c.COURSEID=temp.courseid;
``````

8、查询选修了java课程的学生信息

``````select s.STUDNO "学生编号",s.STUDNAME "学生姓名", s.BATCHCODE "班级编号"from student s,score g,course cwhere g.COURSEID = c.COURSEIDand s.STUDNO = g.STUDNOand c.COURSENAME='JAVA';
``````

9、查询没有选修JAVA课程的学生信息

``````select s.STUDNO "学生编号",s.STUDNAME "学生姓名", s.BATCHCODE "班级编号"from student s,score g,course cwhere g.COURSEID = c.COURSEIDand s.STUDNO = g.STUDNOand c.COURSENAME<>'JAVA';
``````

10、查询选修了教师李可课程的学生信息

``````select s.STUDNO "学生编号",s.STUDNAME "学生姓名"from student s,(select tt.studno from courseplan tt where tt.TEACHID=(select tea.TEACHID from TEACHERINFO tea where tea.TEACHNAME='李可')) bbwhere s.STUDNO=bb.STUDNO;
``````

11、查询同时选修了A01和A02这两门课的学生的上课安排，显示学生编号，学生姓名、班级编号、课程编号、授课教师、上课日期

``````select s.STUDNO "学生编号",s.STUDNAME "学生姓名",s.BATCHCODE "班级编号",c.COURSEID "课程编号",t.TEACHNAME "授课教师",co.COURSEDT "上课日期"from student s,course c,courseplan co,teacherinfo twhere s.STUDNO=co.STUDNO and c.COURSEID=co.COURSEID and t.TEACHID=co.TEACHID and (c.COURSEID='A01' or c.COURSEID='A02');
``````