2017 May 20

sql练习1705

有以下4张表：

1.查询课程编号为001的课程比002的课程成绩高的所有学生学号

select A.sid from 
  (select sid, mark from score where cid='001') A,
  (select sid, mark from score where cid='002') B,
where A.sid = B.sid and A.mark > B.mark;

2.查询平均成绩大于60分的学生的学号和平均成绩

select sid, avg(mark) from score group by sid having avg(mark) > 60;

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

select sid, name, count(cid), sum(mark) from score
left join student on student.id = score.sid
group by sid;

4.查询李姓老师的个数

select count(*) from teacher where name like '李%';

5.查询没学过李美玲老师课程的学号和姓名

select id, name from student where id not in
  (
    select distinct sid from score
      left join course on course.id = score.cid
      left join teacher on teacher.id = course.tid
      where teacher.name = '李美玲'
  );

6.查询同时学过编号为001和002课程的学生的学号、姓名

select id, name from student,
  (select sid from score where cid = '001') A,
  (select sid from score where cid = '002') B,
where student.id = A.sid and A.sid = B.sid;

7.查询学过李多多老师所教的所有课的学生的学号、姓名

8.查询课程编号为002的总成绩

select sum(mark) from score where cid = '002';

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

select id, name from student where id not in
  (select distinct sid from score where mark >= 60);

10.查询没有学全所有课程学生的学号、姓名

select sid, name from score
  left join student on student.id = score.sid
  group by sid having count(cid) < (select count(*) from course);

11.查询至少有一门课与学号为1001的学生所学课程相同的学生的学号、姓名

12.查询所学课程和学号为001的学生所有课程一样的其他学生的学号、姓名

13.把成绩表中李多多老师教的课的成绩都更改为此课程的平均成绩

14.查询没有学过1002号课程的学生的学号和姓名

15.删除学习李多多老师课的成绩记录

16.向成绩表插入一些记录，这些记录满足：没有上过编号为003课程的学生的学号，编号为002的课程的平均成绩

17.按平均成绩从高到低显示所有学生的数据库、企业管理、英语三门课程成绩，该三门课的编号分别为004、001、006，按”学生ID 数据库 企业管理 英语 有效课程数 有效平均成绩”的格式显示

18.查询各科成绩最高和最低的分，按”课程ID 最高分 最低分”格式显示

select cid, max(mark), min(mark) from score group by cid;

19.按各科平均成绩从低到高和及格率的百分数从高到低排列，按”课程号 课程名 平均成绩 及格百分数”格式显示

20.查询以下课程增均成绩和及格率的百分数：企业管理001，马克思002，UML003，数据库004

21.查询不同老师所教不同课程平均分，从高到低显示

22.查询以下课程成绩第3名到第6名的学生成绩单：企业管理001，马克思002，UML003，数据库004，按”学生ID 学生姓名 企业管理 马克思 UML 数据库 平均成绩”格式显示

23.使用分段[100-85],[84-70],[69-60],[0-60]统计各科成绩分数段的人数

24.查询学生平均成绩及其名次

25.查询各科成绩前三名的记录（不考虑成绩并列情况）

26.查询每门课程被选修的学生数

select course.id, course.name, count(score.sid) from course
left join score on score.cid = course.id
group by course.id;

27.查询只选修了一门课程的学生学号和姓名

select sid, name from score
  left join student on student.id = score.sid
  group by sid having count(cid) = 1;

28.查询男生、女生人数

select sum(case sex when '男' then 1 else 0 end) as boys,
       sum(case sex when '女' then 1 else 0 end) as girls
from student group by sex;

select sex, count(*) from student group by sex;

29.查询张姓学生名单

select name from student where name like '张%';

30.查询姓名相同的学生名单并统计同名人数

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

42.查询有2门不同课程成绩相同的学生学号、课程号和成绩

select sid, cid, mark from score A, score B
where A.sid = B.sid and A.mark = B.mark and A.cid != B.cid;

43.查询每门课程成绩的前两名

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

select cid, count(sid) from score group by cid having count(sid) > 10 order by 2 desc, 1;

45.查询至少选修两门课程的学生学号

select sid from score group by sid having count(cid) > 1;

46.查询全部学生都选修的课程号和课程名

select cid, course.name from score
left join course on course.id = score.cid
group by cid having count(sid) = 
  (select count(*) from student);

47.查询没学过李多多老师讲授的作何课程的学生姓名

select name from student where id not in
  (select distinct sid from score
    left join teacher on teacher.id = score.tid
    where teacher.name = '李多多';)

48.查询两门以上不及格课程的学生学号及平均成绩

select sid, avg(mark) from score where sid in
  (select sid from score where mark < 60 group by sid having count(*) > 2);

49.查询课程号为004且分数小于60的学生学号，按分数降序排列

select sid from score where cid = '004' and mark < 60 order by mark desc;

50.删除学号为002的课程号为001的课程的成绩

delete from score where sid = '002' and cid = '001';
Table of Contents