美文网首页
【SQL】18.test3练习题及答案(1)

【SQL】18.test3练习题及答案(1)

作者: 一曈 | 来源:发表于2017-06-07 10:47 被阅读452次

    1、查询“c001”课程比“c002”课程成绩高的所有学生的学号

    select a.* from
    (select * from sc a where a.cno='c001') a,
    (select * from sc b where b.cno='c002') b
    where a.sno=b.sno and a.score > b.score;
    
    1

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

    select sno,avg(score) from sc
    group by sno
    having avg(score) >60;
    
    2

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

    解法一
    select a.sno,a.sname,count(b.cno),sum(b.score) from student a
    join sc b on a.sno=b.sno
    group by sno;
    
    解法二
    select a.*,s.sname from
    (select sno,sum(score),count(cno) from sc group by sno) a ,student s where a.sno=s.sno
    
    3

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

    select count(*) from teacher
    where tname like '刘%';
    
    4

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

    #学过谌燕老师课的同学
    select a.sno,a.sname from student a
    join sc b on a.sno=b.sno
    join(select a.cno from course a 
    join teacher b on 
    a.tno = b.tno
    where tname='谌燕') c on 
    b.cno = c.cno;
    #没学过谌燕老师课的同学
    select a.sno,a.sname from student a
    where a.sno not in
    (select distinct a.sno from sc a 
    join course b on a.cno=b.cno
    join teacher c on c.tno=b.tno
    where tname='谌燕');
    
    5

    6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名

    select a.sno,a.sname from student a
    join sc b on a.sno=b.sno
    join sc c on b.sno=c.sno
    where b.cno='c001' and c.cno='c002' and a.sno=c.sno;
    
    6

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

    select a.sno,a.sname from student a
    join sc b on a.sno=b.sno
    join(select a.cno from course a 
    join teacher b on 
    a.tno = b.tno
    where tname='谌燕') c on 
    b.cno = c.cno;
    
    7

    8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名

    解法一
    select sno,sname from student 
    where sno in (select a.sno from
    (select * from sc a where a.cno='c001') a,
    (select * from sc b where b.cno='c002') b
    where a.sno=b.sno and a.score > b.score);
    
    8解法一
    解法二
    select * from student st
    join sc a on st.sno=a.sno
    join sc b on st.sno=b.sno
    where a.cno='c002' and b.cno='c001' and a.score < b.score
    
    8解法二

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

    select a.sno,a.sname from student a 
    join sc b on a.sno=b.sno
    join course c on c.cno=b.cno
    where b.score <60;
    
    9

    10、查询没有学全所有课的同学的学号、姓名

    select a.sno,a.sname,count(b.cno) from student a 
    left join sc b on a.sno=b.sno 
    group by a.sno,a.sname 
    having count(b.cno)<(select count(distinct cno) from course);
    
    10

    11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名

    解法一
    select distinct a.sno,a.sname from student a
    join sc b on a.sno=b.sno
    where b.cno in
    (select cno from sc a
    join student b on a.sno=b.sno
    where a.sno='s001')
    and a.sno<>'s001';
    
    11解法一
    解法二
    select st.* from student st,
    (select distinct a.sno from
    (select * from sc) a,
    (select * from sc where sc.sno='s001') b
    where a.cno=b.cno) h
    where st.sno=h.sno and st.sno<>'s001'
    
    11解法二

    12、查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名
    此题解法我认为与11题相同,但网上解法如下

      select * from sc
    left join student st
    on st.sno=sc.sno
    where sc.sno<>'s001'
    and sc.cno in
    (select cno from sc
    where sno='s001')
    
    12

    13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩

    #替换数据应使用
    update sc set score =
    #“谌燕”老师教的课的平均成绩
    (select avg(score) from sc
    join (course a,teacher c) on a.tno=c.tno and sc.cno=a.cno 
    and c.tname='谌燕'
    group by sc.cno )
    #where cno in 
    #'谌燕'老师教的课程
    (select  cno from course a
    join teacher b on a.tno=b.tno
    where b.tname='谌燕');
    

    14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名

    select sno from sc 
    where cno in (select cno from sc where sno='s001')
    #join sc b on a.cno= b.cno and a.sno='s001'
    group by sno 
    having count(*)=
    (select count(*) from sc where sno='s001');
    
    14

    15、删除学习“谌燕”老师课的SC 表记录

    #删除数据应使用delete from sc where sc.cno in 
    (select  cno from course a
    join teacher b on a.tno=b.tno
    where b.tname='谌燕');
    

    16、向SC 表中插入一些记录,这些记录要求符合以下条件:没有上过编号“c002”课程的同学学号、“c002”号课的平均成绩

    #插入数据应使用insert into sc(sno,cno,score)
    #平均成绩
    select distinct st.sno,sc.cno,(select avg(score)from sc where cno='c002')
    from student st,sc
    where not exists
    #没有上过编号“c002”课程的同学学号
    (select * from sc where cno='c002' and sc.sno=st.sno) and sc.cno='c002';
    

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

    select cno,max(score),min(score) from sc
    group by cno
    
    17

    18、按各科平均成绩从低到高和及格率的百分数从高到低顺序

    select cno,avg(score),
    sum(case when score>=60 then 1 else 0 end)/count(*)
    as 及格率
    from sc
    group by cno
    order by avg(score) ,及格率 desc;
    
    18

    19、查询不同老师所教不同课程平均分从高到低显示

    select a.tno,a.tname,b.cname,c.cno,avg(c.score) from teacher a
    join (course b,sc c) on a.tno=b.tno and b.cno=c.cno
    group by c.cno
    order by avg(score) desc
    
    19

    20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

    select sc.cno,c.cname,
    sum(case  when score between 85 and 100 then 1 else 0 end) as "[100-85]",
    sum(case  when score between 70 and 85 then 1 else 0 end) as "[85-70]",
    sum(case  when score between 60 and 70 then 1 else 0 end) as "[70-60]",
    sum(case  when score <60 then 1 else 0 end) as "[<60]"
    from sc, course c
    where  sc.cno=c.cno
    group by sc.cno ,c.cname;
    
    20

    <b> 未解答 21、查询各科成绩前三名的记录:(不考虑成绩并列情况)</b>

    select b.cno,a.sno,a.sname,b.score from student a
    join sc b on a.sno=b.sno
    group by b.cno
    order by b.score desc;
    

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

    select a.cno,b.cname,count(a.sno) from sc a,course b
    where a.cno=b.cno
    group by cno;
    
    22

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

    select a.sno,a.sname from student a 
    join sc b on a.sno=b.sno
    group by b.sno 
    having count(b.cno) =1;
    
    23

    24、查询男生、女生人数

    select ssex,count(sno) from student
    group by ssex;
    
    24

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

    select * from student
    where sname like '张%';
    
    25

    26、查询同名同性学生名单,并统计同名人数

    select sname,count(*)from student 
    group by sname 
    having count(*)>1;
    
    26

    相关文章

      网友评论

          本文标题:【SQL】18.test3练习题及答案(1)

          本文链接:https://www.haomeiwen.com/subject/omyofxtx.html