图片 1

SQL基础测试题目

Posted by

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

MySQL练习题【二】参考答案

学生编号 学生姓名 课程名称 成绩

1.导入表结构和数据

 图片 1

 sql脚本

图片 2 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” 
成绩高的所有学生的学号;

图片 3

#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;    

图片 4

 2、查询平均成绩大于60分的同学的学号和平均成绩;

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

3、查询所有同学的学号、姓名、选课数、总成绩;

图片 5

#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

图片 6

4、查询含有”子”的老师的个数;

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

5、查询没学过“老子”老师课的同学的学号、姓名;

图片 7

#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 ='老子') 
)

图片 8

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;

图片 9

#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;

图片 10

  7、查询学过“老子”老师所教的所有课的同学的学号、姓名;

图片 11

#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 =’老子’)
 )

图片 12

  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”的同学所学相同的同学的学号和姓名;

图片 13

#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

图片 14

    12、查询学过 学号为“002”同学全部课程
的其他同学的学号和姓名;

图片 15

# 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')

图片 16

  13、查询和“002”号的同学学习的课程完全相同的,其他同学学号和姓名;

图片 17

#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')

图片 18

  14、把“score”表中“老子”老师教的课的成绩都更改为此课程的平均成绩;

图片 19

#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 ='老子')

图片 20

  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”三门的课程成绩,按如下形式显示:
学生ID,python,java,linux,有效课程数,有效平均分

图片 21

#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;

图片 22

  17、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分

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

  18、查询”学生ID”,”各科平均成绩”,”及格率”,并按各科平均成绩从低到高和及格率的百分数从高到低顺序

图片 23

#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;

图片 24

  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] 

图片 25

#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

图片 26

  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分以上的姓名、课程名称和分数;

图片 27

#方式一
#学生编号分组,获得最小分数
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)

图片 28

  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、查询选修“老子”老师所授课程的学生中,成绩最高的学生姓名及其成

图片 29

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

图片 30

  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、查询没学过“老子”老师讲授的任一门课程的学生姓名

图片 31

#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 ='老子') 
)

图片 32

  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;

用到case表达式

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');

相关文章

Leave a Reply

电子邮件地址不会被公开。 必填项已用*标注