原始数据

# 创建习题数据库
create database school;
# 使用习题数据库
use school;
# 创建学生信息表
create table student(
sid int(4) comment '学号',
sname varchar(4) comment '姓名',
sage int(2) comment '年龄',
ssex char(1) comment '性别',
primary key(sid)
)
char set utf8;
# 创建教师信息表
create table teacher(
tid int(1) comment '教师编号',
tname varchar(4) comment '教师姓名',
primary key(tid)
)
char set utf8;
# 创建课程信息表
create table course(
cid char(3) comment '课程编号',
cname varchar(10) comment '课程名',
tid int(1) comment '教师编号',
primary key(cid)
)
char set utf8;
# 创建学生成绩表
create table sc(
sid int(4) comment '学号',
cid int(3) comment '课程编号',
score int(2) comment '成绩'
);
# 学生信息表插值
insert into student values
(1001,'张三',10,'男'),
(1002,'李四',11,'女'),
(1003,'王五',12,'男'),
(1004,'马六',19,'女'),
(1005,'孙七',22,'女'),
(1006,'钱八',18,'男'),
(1007,'赵九',11,'女'),
(1008,'周公',19,'男');
# 教师信息表插值
insert into teacher values
(1,'叶平'),
(2,'李浩然'),
(3,'胡平原'),
(4,'朱清时'),
(5,'赛先生'),
(6,'宋三东');
# 课程信息表插值
insert into course values
('001','PHP',1),
('002','C#',1),
('003','C++',2),
('004','JAVA',3),
('005','Python',4),
('006','R',5),
('007','HTML',6);
# 学生成绩表插值
insert into sc values
(1001,001,89),
(1002,001,80),
(1003,001,30),
(1004,001,78),
(1005,001,68),
(1006,001,93),
(1007,001,62),
(1001,002,67),
(1002,002,86),
(1003,002,67),
(1004,002,77),
(1005,002,66),
(1006,002,84),
(1007,002,72),
(1001,003,82),
(1002,003,85),
(1003,003,32),
(1004,003,73),
(1005,003,64),
(1006,003,87),
(1007,003,77),
(1008,003,94),
(1001,004,39),
(1002,004,80),
(1003,004,80),
(1004,004,88),
(1005,004,68),
(1006,004,59),
(1007,004,42),
(1008,004,64),
(1001,005,89),
(1002,005,70),
(1003,005,60),
(1004,005,58),
(1005,005,38),
(1006,005,89),
(1007,005,72),
(1008,005,64),
(1001,006,49),
(1002,006,90),
(1003,006,70),
(1004,006,48),
(1005,006,98),
(1006,006,59),
(1007,006,72),
(1008,006,74),
(1001,007,49),
(1002,007,50),
(1003,007,70),
(1004,007,88),
(1005,007,78),
(1006,007,99),
(1007,007,82);

查询练习

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

select a.sid from
(select * from sc
where cid = '001') as a,
(select * from sc
where cid = '002') as b
where a.sid = b.sid and a.score > b.score;

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

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

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

select student.sid, student.sname, count(cid), sum(score) from
student left join sc on student.sid = sc.sid
group by sc.sid;

4. 查询姓'李'的老师的个数

select count(distinct(tname)) from teacher
where tname like '李%';

5. 查询没学过'叶平'老师课的同学的学号, 姓名

select sid, sname from student
where sid not in(
select distinct(sid) from teacher, course, sc
where sc.cid = course.cid
and course.tid = teacher.tid
and teacher.tname = '叶平');

6. 查询学过'001'并且也学过编号'002'课程的同学的学号, 姓名

select student.sid, sname from student, sc
where student.sid = sc.sid
and sc.cid = '001'
and exists(
select * from student, sc
where student.sid = sc.sid
and sc.cid = '002');