详解50道经典SQL练习题(MySQL解法)

数据表说明：

学生表student(sid,sname,sbirth,sgender)参数说明：

    sid：学生编号，sname：学生姓名，sbirth：学生出生日期，sgender：学生性别

课程表course(cid,cname,tid)参数说明：

    cid：课程编号，cname：课程名字，tid：授课老师编号

教师表teacher(tid,tname)参数说明：

    tid：教师编号，tname：教师姓名

成绩表grade(sid,cid,score)参数说明：

    sid：学生编号，cid：课程编号，score：课程成绩

创建上述各表，并向表中插入数据：

# 创建学生表：
create table student (
sid varchar(10),
sname varchar(10),
sbirth datetime,
sgender char(1)
)engine myisam charset utf8;

# 向学生表中插入数据：
insert into student values
('01','赵雷','1990-01-01','男'),
('02','钱枫','1990-12-21','男'),
('03','孙思邈','1990-12-16','男'),
('04','李四','1990-12-06','男'),
('05','周梅','1991-12-01','女'),
('06','吴兰','1992-01-01','女'),
('07','郑珠','1989-01-01','女'),
('09','张三','2017-12-20','女'),
('10','李四','2017-12-16','女'),
('11','李四','2012-06-06','女'),
('12','赵雷','2013-06-13','男'),
('13','孙琦','2014-06-01','女');


# 创建科目表：
create table course(
cid varchar(10),
cname varchar(10),
tid varchar(10)
)engine myisam charset utf8;

# 向科目表中插入数据：
insert into course values
('01','语文','02'),
('02','数学','01'),
('03','英语','03'),
('04','政治','04');


# 创建教师表：
create table teacher(
tid varchar(10),
tname varchar(10)
)engine myisam charset utf8;

# 向教师表中插入数据：
insert into teacher values
('01','张衡'),
('02','李文'),
('03','吴迪'),
('04','王珊');


# 创建成绩表：
create table grade(
sid varchar(10),
cid varchar(10),
score decimal(4,1)
)engine myisam charset utf8;

# 向成绩表中插入数据：
insert into grade values
('01','01',80),
('01','02',90),
('02','01',70),
('02','02',60),
('02','03',80),
('02','04',85),
('03','01',80),
('03','02',80),
('03','03',80),
('03','04',61),
('04','01',75),
('04','02',30),
('04','03',20),
('04','04',45),
('05','01',76),
('05','02',87),
('05','03',99),
('05','04',75),
('06','01',31),
('06','02',65),
('06','03',34),
('07','02',89),
('07','03',98),
('09','02',82),
('10','01',88),
('10','02',90),
('13','01',59),
('13','02',79),
('13','04',81);

注：以下习题解答均测试于MySQL环境下。

习题及其解答：

1.1 查询01课程比02课程成绩高的学生信息及课程分数：

思路：先找出同时选修了01课程和02课程的学生编号和课程对应的成绩，然后筛选出01课程成绩比02课程成绩高的学生编号和课程对应的成绩，最后将筛选结果与学生表student左连接，再次筛选出相应学生的信息。

代码：

select * from 
(select * from (select sid,score as score1 from grade where cid = '01') as t1
inner join 
(select sid,score as score2 from grade where cid = '02') as t2 
using(sid) 
where t1.score1 > t2.score2
) as temp left join student using(sid);

1.2查询同时选修了01课程和02课程的学生的课程分数：

思路：选修了01课程的学生与选修了02课程的学生的交集即是同时选修了01、02两门课程的学生。

代码：

select * from
(select sid,score as score1 from grade where cid = '01') as t1
inner join 
(select sid,score as score2 from grade where cid = '02') as t2 
using(sid) ;

使用left join同样能达成与上述代码同样的效果：

select * from
(select sid,score as score1 from grade where cid = '01') as t1
left join 
(select sid,score as score2 from grade where cid = '02') as t2 
using(sid) 
where t2.score is not null;

1.3查询存在01课程但可能不存在02课程的情况(不存在时显示为 null )

思路：此题为典型的左连接题，可以将选修了01课程的学生的成绩信息左连接于选修了02课程的学生的成绩信息。由于采用左连接，所以02课程不存在时自动补充null值。

代码：

select * from 
(select * from grade where cid = '01') as t1 
left join 
(select * from grade where cid = '02') as t2 
using(sid);

1.4 查询不存在01课程但存在02课程的情况：

思路：先找出没有选修01课程的学生的成绩信息，再进一步筛选出同时选修了02课程的学生的成绩信息。

代码：

select * from grade 
where sid not in (select sid from grade where cid = '01') 
having sid in (select sid from grade where cid = '02');

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平