SQL练习题

一、创建测试数据

(1)、创建数据库
mysql>create database school;
mysql>use school;
(2)、创建表（可以尝试用powerdesigner工具设计数据库，导出SQL）

/*==============================================================*/
/* DBMS name:      MySQL 5.0                                    */
/* Created on:     2019/4/19 10:34:32                           */
/*==============================================================*/


drop table if exists class;

drop table if exists course;

drop table if exists score;

drop table if exists student;

drop table if exists teacher;

/*==============================================================*/
/* Table: class                                                 */
/*==============================================================*/
create table class
(
   cid                  varchar(10) not null,
   caption              varchar(20) not null,
   primary key (cid)
);

/*==============================================================*/
/* Table: course                                                */
/*==============================================================*/
create table course
(
   cid                  varchar(10) not null,
   cname                varchar(20) not null,
   tid                  varchar(10) not null,
   primary key (cid)
);

/*==============================================================*/
/* Table: score                                                 */
/*==============================================================*/
create table score
(
   sid                  varchar(10) not null,
   cid                  varchar(10) not null,
   score                decimal not null,
   编号                   varchar(10) not null,
   primary key (编号)
);

/*==============================================================*/
/* Table: student                                               */
/*==============================================================*/
create table student
(
   sid                  varchar(10) not null,
   sname                varchar(20) not null,
   gender               varchar(2) not null,
   cid                  varchar(10),
   birth                datetime,
   primary key (sid)
);

/*==============================================================*/
/* Table: teacher                                               */
/*==============================================================*/
create table teacher
(
   tid                  varchar(10) not null,
   tname                varchar(20) not null,
   primary key (tid)
);

(2)、插入数据

insert into class values('101','一年级一班');
insert into class values('102','一年级二班');
insert into class values('103','一年级三班');
insert into class values('104','一年级四班');
insert into class values('201','二年级一班');
insert into class values('202','二年级二班');
insert into class values('203','二年级三班');

insert into Student values('01' , '赵雷' ,'男','101', '1990-01-01');
insert into Student values('02' , '彭万里' ,'男','101', '1990-12-21');
insert into Student values('03' , '高大山' ,'男','102', '1990-12-20');
insert into Student values('04' , '谢大海' ,'男','104', '1990-12-06');
insert into Student values('05' , '林莽' ,'女','104', '1991-12-01');
insert into Student values('06' , '章汉夫' ,'女','203', '1992-01-01');
insert into Student values('07' , '范长江' ,'女','203', '1989-01-01');
insert into Student values('09' , '林君雄' ,'女','102', '2017-12-20');
insert into Student values('10' , '谭平山' ,'女','201', '2017-12-25');
insert into Student values('11' , '李四光' ,'女','203', '2012-06-06');
insert into Student values('12' , '甘铁生' ,'女','104', '2013-06-13');
insert into Student values('13' , '程孝先' ,'女','102', '2014-06-01');

insert into teacher values('01' , '朱希亮');
insert into teacher values('02' , '黄强辉');
insert into teacher values('03' , '马宏宇');
insert into teacher values('04' , '马继祖');

insert into course values('01' , '语文','01');
insert into course values('02' , '数学','02');
insert into course values('03' , '英语','03');
insert into course values('04' , '历史','04');
insert into course values('05' , '物理','01');

insert into score values('01' , '01', '01',78);
insert into score values('02' , '01', '02',72);
insert into score values('03' , '02', '01',87);
insert into score values('04' , '02', '03',90);
insert into score values('05' , '03', '04',100);
insert into score values('06' , '05', '04',97);
insert into score values('07' , '07', '03',89);
insert into score values('08' , '07', '02',72);
insert into score values('09' , '08', '04',79);
insert into score values('10' , '08', '01',57);
insert into score values('11' , '09', '02',52);
insert into score values('12' , '09', '01',69);
insert into score values('13' , '11', '04',87);
insert into score values('14' , '12', '01',60);
insert into score values('15' , '13', '01',79);

 二、练习题

1、查询“语文”课程比“物理”课程成绩高的所有学生的学号；
select A.sid,yw,wl from
(select sid,score as yw from score left join course on score.cid=course.cid where cname='语文') as A
left join (select sid,score as wl from score left join course on score.cid=course.cid where cname='物理') as B
on A.sid=B.sid
where yw>if(isnull(wl),0,wl);
2、查询平均成绩大于60分的同学的学号和平均成绩；
select sid,avg(score) as mean from score group by sid
having mean>60;
3、查询所有同学的学号、姓名、选课数、总成绩；
select score.sid,student.sname,count(score.cid) as 选课数,sum(score.score) as total from score
left join student on score.sid=student.sid
group by sid
4、查询姓“马”的老师的个数；
select count(tname) from teacher where tname like '马%';
5、查询没学过“马宏宇”老师课的同学的学号、姓名；
(1)查询马宏宇老师教的课
select cid from course left join teacher on course.tid=teacher.tid where tname='马宏宇';
(2)查询学过马宏宇老师课程的学生
select score.sid from score
where cid in (select cid from course left join teacher on course.tid=teacher.tid where tname='马宏宇');
(3)查询没有学过马宏宇老师课程的学生
select * from student
where sid not in
(select score.sid from score where cid in (select cid from course left join teacher on course.tid=teacher.tid where tname='马宏宇'));
6、查询学过“01”并且也学过编号“02”课程的同学的学号、姓名；
(1)选出既选01又选02课程的学生学号
select A.sid from (select sid from score where cid='01') as A inner join (select sid from score where cid='02') as B on A.sid=B.sid
(2)查询学生的姓名
select sid ,sname from student where sid in
(select A.sid from (select sid from score where cid='01') as A inner join (select sid from score where cid='02') as B on A.sid=B.sid);

7、查询学过“马宏宇”老师所教的所有课的同学的学号、姓名；
(1)查询马宏宇老师教的课程
select cid from course left join teacher on course.tid=teacher.tid where tname='马宏宇';
(2)查询学过马宏宇老师课程的学生
select student.sid ,sname from student
left join score on student.sid=score.sid
where score.cid in (select cid from course left join teacher on course.tid=teacher.tid where tname='马宏宇');

8、查询课程编号“02”的成绩比课程编号“01”课程低的所有同学的学号、姓名；
(1)查询02比01低的同学的学号
select A.sid,two,one from
(select sid,score as two from score where cid='02') as A
left join
(select sid,score as one from score where cid='01') as B
on A.sid=B.sid
where two<if(isnull(one),0,one)
(2)连表查询学号和姓名
select student.sid ,student.sname,one,two from student right join
(select A.sid,two,one from
(select sid,score as two from score where cid='02') as A
left join
(select sid,score as one from score where cid='01') as B
on A.sid=B.sid
where two<if(isnull(one),0,one)) as C
on student.sid=C.sid;
9、查询有课程成绩小于60分的同学的学号、姓名；
select distinct student.sid,sname from student
right join (select sid ,score from score where score < 60) as A
on student.sid=A.sid;
10、查询没有学全所有课的同学的学号、姓名；
(1)学全五门课程的学生学号
select sid,count(cid) as num from score group by sid having num=5;
(2)查询没有学全五门课程的学生名单
select sid,sname from student
where sid not in
(select sid from score group by sid having count(cid)=5);
11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名；
select distinct score.sid,sname from score left join student on score.sid=student.sid
where score.cid in (select cid from score where sid = '01')
12、查询至少学过学号为“01”同学所选课程中任意一门课的其他同学学号和姓名；
select distinct score.sid,sname from score left join student on score.sid=student.sid
where score.cid in (select cid from score where sid = '01') and score.sid !='01';
13、查询和“02”号的同学学习的课程完全相同的其他同学学号和姓名；（暂时没有实现，待续……）
select score.sid,sname from score left join student on score.sid=student.sid
where score.cid in (select cid from score where sid='02') and score.sid !='02';

14、删除学习“马继祖”老师课的score表记录；
delete from score where cid in (select cid from teacher left join course on teacher.tid=course.tid where tname='马继祖');
15、向SC表中插入一些记录，这些记录要求符合以下条件：①没有上过编号“02”课程的同学学号；②插入“02”号课程的平均成绩；

（由于涉及到主键，暂时没有实现，待续……）
16、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩，按如下形式显示： 学生ID,语文,数学,英语,有效课程数,有效平均分； 方法一：
select sid,(select score from score where A.sid=score.sid and cid='01')as yw,
(select score from score where A.sid=score.sid and cid='02')as sx,
(select score from score where A.sid=score.sid and cid='03')as yy, count(A.cid) as num,avg(A.score) as mean
from score as A group by sid
order by mean;
方法二：
select sid,(select score from score left join course on score.cid=course.cid where A.sid=score.sid and cname='语文')as yw,
(select score from score left join course on score.cid=course.cid where A.sid=score.sid and cname='数学')as sx,
(select score from score left join course on score.cid=course.cid where A.sid=score.sid and cname='英语')as yy,
count(A.cid) as num,avg(A.score) as mean
from score as A group by sid
order by mean;

17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分；
select cid,(select max(score) from score)as max,
(select min(score) from score)as min
from score as A group by cid;
18、按各科平均成绩从低到高和及格率的百分数从高到低顺序；
方法一：
select cid,(select avg(score) from score where A.cid=score.cid)as mean,
((select count(1) from score where score>60)/(count(1))*100) as jgl
from score as A group by cid
order by mean,jgl desc;
方法二：
select cid,(select avg(score) from score where A.cid=score.cid)as mean,
(sum(case when A.score>60 then 1 else 0 end)/(count(1))*100) as jgl
from score as A group by cid
order by mean,jgl desc;
19、课程平均分从高到低显示（显示任课老师）；
select cid,cname,tname,mean from teacher right join
(select score.cid,cname,tid,avg(score) as mean from score left join course on score.cid=course.cid group by cid)as A
on teacher.tid=A.tid order by mean desc;
20、查询各科成绩前三名的记录:(不考虑成绩并列情况)? (select sid ,cid ,score from score where cid='01' order by score limit 3)
union
(select sid ,cid ,score from score where cid='02' order by score limit 3)
union
(select sid ,cid ,score from score where cid='03' order by score limit 3)
union
(select sid ,cid ,score from score where cid='04' order by score limit 3)
union
(select sid ,cid ,score from score where cid='05' order by score limit 3);
21、查询每门课程被选修的学生数；
select cid,count(sid) from score group by cid;
22、查询出只选修了一门课程的全部学生的学号和姓名；
select student.sid,sname from student right join
(select sid,count(cid) as num from score group by sid having num=1) as A
on student.sid=A.sid;
23、查询男生、女生的人数；
select gender,count(1) from student group by gender;
24、查询姓“赵”的学生名单；
select * from student where sname like '赵%';
25、查询同名同姓学生名单，并统计同名人数；
select sname,count(1) as num from student group by sname
having num>1;
26、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列；
select cid,avg(score) as mean from score group by cid
order by mean,cid desc;
27、查询平均成绩大于60的所有学生的学号、姓名和平均成绩；
select score.sid,sname,avg(score) as mean from score left join student on score.sid=student.sid
having mean>60;
28、查询课程名称为“数学”，且分数低于60的学生姓名和分数；
select sid,cname,score from score left join course on score.cid=course.cid
where score<60 and cname='数学';
29、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名；
select score.sid,sname,score from score left join student on score.sid=student.sid
where score>80 and score.cid='03'
30、求选了课程的学生人数
select count(sid) as num from (select distinct sid from score) as A;
31、查询选修“马宏宇”老师所授课程的学生中，成绩最高的学生姓名及其成绩；
select score.sid,sname,score from score left join student on score.sid=student.sid
where score.cid in (select cid from course left join teacher on course.tid=teacher.tid where tname='马宏宇') order by score desc
limit 1;
32、查询各个课程及相应的选修人数；
select cid ,count(1) from score group by cid;
33、查询不同课程但成绩相同的学生的学号、课程号、学生成绩；
select s1.sid,s1.cid,s1.score from score as s1,score as s2 where s1.score=s2.score and s1.cid!=s2.cid;
34、查询每门课程成绩最好的前两名；
(select cid,sid,score from score where cid='01' order by score desc limit 2)
union
(select cid,sid,score from score where cid='02' order by score desc limit 2)
union
(select cid,sid,score from score where cid='03' order by score desc limit 2)
union
(select cid,sid,score from score where cid='04' order by score desc limit 2)
union
(select cid,sid,score from score where cid='05' order by score desc limit 2);

35、检索至少选修两门课程的学生学号；
select sid ,count(cid) as num from score group by sid having num>1;
36、查询全部学生都选修的课程的课程号和课程名；
select score.cid,cname,count(sid) as num from score left join course on score.cid=course.cid
group by cid having num=(select count(1) from student);

37、查询没学过“马宏宇”老师讲授的任一门课程的学生姓名；
select score.sid,sname from score left join student on score.sid=student.sid
where score.sid not in
(select cid from course left join teacher on course.tid=teacher.tid where tname='马宏宇');

38、查询两门以上不及格课程的同学的学号及其平均成绩；
select score.sid,sname,avg(score) as mean from score left join student on score.sid=student.sid
group by score.sid
having sid in
(select sid from (select sid from score where score<60) as A group by sid having count(1) >2);

39、检索“04”课程分数小于60，按分数降序排列的同学学号；
select sid,score from score where cid='01' and score <60 order by score desc;
40、删除“02”同学的“01”课程的成绩； delete from score where sid='02' and cid='01';