一、表结构

1.1 学生表

create table student(
    sid varchar(10) NOT NULL COMMENT '学生ID',
    username varchar(20) NOT NULL COMMENT '学生名称',
    sex enum("man","woman") NOT NULL DEFAULT 'man' COMMENT '性别',
    birthday datetime NOT NULL DEFAULT '1971-01-01 00:00:00' COMMENT '出生日期',
    primary key(sid)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='学生表';

insert into student values('01', '赵雷', '男', '1990-01-01');
insert into student values('02', '钱电', '男', '1990-12-21');
insert into student values('03', '孙风', '男', '1990-05-20');
insert into student values('04', '李云', '男', '1990-08-06');
insert into student values('05', '周梅', '女', '1991-12-01');
insert into student values('06', '吴兰', '女', '1992-03-01');
insert into student values('07', '郑竹', '女', '1989-07-01');
insert into student values('08', '王菊', '女', '1990-01-20');

1.2 教师表

create table teacher(
    tid varchar(10) NOT NULL COMMENT '教师ID',
    tname varchar(20) NOT NULL COMMENT '教师名称',
    primary key(tid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='教师表';

insert into teacher values('01' , '张三'),('02' , '李四'),('03' , '王五');

1.3 课程表

create table course(
    cid varchar(10) NOT NULL COMMENT '课程ID',
    tid varchar(10) NOT NULL COMMENT '教师ID',
    cname varchar(20) NOT NULL COMMENT '课程名称',
    primary key(cid)  
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='课程表';

insert into course values('01', '02', '语文');
insert into course values('02', '01', '数学');
insert into course values('03', '03', '英语');

1.4 成绩表

create table SC(
    sid varchar(10) NOT NULL COMMENT '学生ID',
    cid varchar(10) NOT NULL COMMENT '课程ID',
    score tinyint(3) NOT NULL COMMENT '分数' 
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='成绩表';

insert into SC values('01' , '01' , 80);
insert into SC values('01' , '02' , 90);
insert into SC values('01' , '03' , 99);
insert into SC values('02' , '01' , 70);
insert into SC values('02' , '02' , 60);
insert into SC values('02' , '03' , 80);
insert into SC values('03' , '01' , 80);
insert into SC values('03' , '02' , 80);
insert into SC values('03' , '03' , 80);
insert into SC values('04' , '01' , 50);
insert into SC values('04' , '02' , 30);
insert into SC values('04' , '03' , 20);
insert into SC values('05' , '01' , 76);
insert into SC values('05' , '02' , 87);
insert into SC values('06' , '01' , 31);
insert into SC values('06' , '03' , 34);
insert into SC values('07' , '02' , 89);
insert into SC values('07' , '03' , 98);

二、SQL查询场景

2.1 [ * ]所有表关联查询, 使用左连接是避免学生没分数记录

select * from student a left join (
	select a.tid,a.tname,b.cid,b.score,b.sid,c.cname from teacher a,SC b,course c where a.tid=c.tid and b.cid=c.cid
) b on a.sid=b.sid;
+-----+----------+-----+---------------------+------+-------+------+-------+------+-------+
| sid | username | sex | birthday            | tid  | tname | cid  | score | sid  | cname |
+-----+----------+-----+---------------------+------+-------+------+-------+------+-------+
| 01  | 赵雷     | 男  | 1990-01-01 00:00:00 | 02   | 李四  | 01   |    80 | 01   | 语文  |
| 01  | 赵雷     | 男  | 1990-01-01 00:00:00 | 01   | 张三  | 02   |    90 | 01   | 数学  |
| 01  | 赵雷     | 男  | 1990-01-01 00:00:00 | 03   | 王五  | 03   |    99 | 01   | 英语  |
| 02  | 钱电     | 男  | 1990-12-21 00:00:00 | 02   | 李四  | 01   |    70 | 02   | 语文  |
| 02  | 钱电     | 男  | 1990-12-21 00:00:00 | 01   | 张三  | 02   |    60 | 02   | 数学  |
| 02  | 钱电     | 男  | 1990-12-21 00:00:00 | 03   | 王五  | 03   |    80 | 02   | 英语  |
| 03  | 孙风     | 男  | 1990-05-20 00:00:00 | 02   | 李四  | 01   |    80 | 03   | 语文  |
| 03  | 孙风     | 男  | 1990-05-20 00:00:00 | 01   | 张三  | 02   |    80 | 03   | 数学  |
| 03  | 孙风     | 男  | 1990-05-20 00:00:00 | 03   | 王五  | 03   |    80 | 03   | 英语  |
| 04  | 李云     | 男  | 1990-08-06 00:00:00 | 02   | 李四  | 01   |    50 | 04   | 语文  |
| 04  | 李云     | 男  | 1990-08-06 00:00:00 | 01   | 张三  | 02   |    30 | 04   | 数学  |
| 04  | 李云     | 男  | 1990-08-06 00:00:00 | 03   | 王五  | 03   |    20 | 04   | 英语  |
| 05  | 周梅     | 女  | 1991-12-01 00:00:00 | 02   | 李四  | 01   |    76 | 05   | 语文  |
| 05  | 周梅     | 女  | 1991-12-01 00:00:00 | 01   | 张三  | 02   |    87 | 05   | 数学  |
| 06  | 吴兰     | 女  | 1992-03-01 00:00:00 | 02   | 李四  | 01   |    31 | 06   | 语文  |
| 06  | 吴兰     | 女  | 1992-03-01 00:00:00 | 03   | 王五  | 03   |    34 | 06   | 英语  |
| 07  | 郑竹     | 女  | 1989-07-01 00:00:00 | 01   | 张三  | 02   |    89 | 07   | 数学  |
| 07  | 郑竹     | 女  | 1989-07-01 00:00:00 | 03   | 王五  | 03   |    98 | 07   | 英语  |
| 08  | 王菊     | 女  | 1990-01-20 00:00:00 | NULL | NULL  | NULL | NULL  | NULL | NULL  |
+-----+----------+-----+---------------------+------+-------+------+-------+------+-------+

2.2 查询语文比数学成绩高的学生信息及课程分数

select a.*,b.score 语文,c.score 数学 from student a,SC b,SC c where a.sid=b.sid and a.sid=c.sid and b.cid='01' and c.cid='02' and b.score>c.score;
+-----+----------+-----+---------------------+------+------+
| sid | username | sex | birthday            | 语文 | 数学 |
+-----+----------+-----+---------------------+------+------+
| 02  | 钱电     | 男  | 1990-12-21 00:00:00 |   70 |   60 |
| 04  | 李云     | 男  | 1990-08-06 00:00:00 |   50 |   30 |
+-----+----------+-----+---------------------+------+------+

2.3 查询平均成绩大于等于90分的同学的学生编号，学生姓名，平均成绩

select b.sid,a.username,avg(b.score) '平均成绩' from student a,SC b where a.sid=b.sid group by b.sid having avg(b.score)>90;
+-----+----------+----------+
| sid | username | 平均成绩 |
+-----+----------+----------+
| 07  | 郑竹     | 93.5     |
+-----+----------+----------+

2.4 查询没有成绩的学生信息

select * from student where student.sid not in (select sid from SC group by sid);
+-----+----------+-----+---------------------+
| sid | username | sex | birthday            |
+-----+----------+-----+---------------------+
| 08  | 王菊     | 女  | 1990-01-20 00:00:00 |
+-----+----------+-----+---------------------+

2.5 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩, 降序

select a.sid,a.username,count(b.cid) '选课总数',sum(b.score) '总分数' from student a left join SC b on a.sid=b.sid group by a.sid order by sum(b.score) desc;
+-----+----------+----------+--------+
| sid | username | 选课总数 | 总分数 |
+-----+----------+----------+--------+
| 01  | 赵雷     |        3 | 269    |
| 03  | 孙风     |        3 | 240    |
| 02  | 钱电     |        3 | 210    |
| 07  | 郑竹     |        2 | 187    |
| 05  | 周梅     |        2 | 163    |
| 04  | 李云     |        3 | 100    |
| 06  | 吴兰     |        2 | 65     |
| 08  | 王菊     |        0 | NULL   |
+-----+----------+----------+--------+

2.6 查询张三老师教过的所有同学的信息

select a.* from student a, teacher b,course c, SC d where a.sid=d.sid and c.cid=d.cid and b.tid=c.tid and b.tname="张三";
+-----+----------+-----+---------------------+
| sid | username | sex | birthday            |
+-----+----------+-----+---------------------+
| 01  | 赵雷     | 男  | 1990-01-01 00:00:00 |
| 02  | 钱电     | 男  | 1990-12-21 00:00:00 |
| 03  | 孙风     | 男  | 1990-05-20 00:00:00 |
| 04  | 李云     | 男  | 1990-08-06 00:00:00 |
| 05  | 周梅     | 女  | 1991-12-01 00:00:00 |
| 07  | 郑竹     | 女  | 1989-07-01 00:00:00 |
+-----+----------+-----+---------------------+

2.7 查询张三老师没教过的所有同学的信息

(先把教过的学生sid找出来，然后用sid not in)

select * from student where sid not in(
	select a.sid from SC a,course b,teacher c where a.cid=b.cid and b.tid=c.tid and c.tname="张三"
);
+-----+----------+-----+---------------------+
| sid | username | sex | birthday            |
+-----+----------+-----+---------------------+
| 06  | 吴兰     | 女  | 1992-03-01 00:00:00 |
| 08  | 王菊     | 女  | 1990-01-20 00:00:00 |
+-----+----------+-----+---------------------+

2.8 查询同时报了语文和数学课的同学信息

把学了语文或数学的记录找出来，然后按学生id分组大于等于2即可

select a.*,count(b.cid) from student a,SC b where a.sid=b.sid and b.cid in (
	select cid from course where cname="语文" or cname="数学"
) group by b.sid having count(b.cid)>=2;
+-----+----------+-----+---------------------+--------------+
| sid | username | sex | birthday            | count(b.cid) |
+-----+----------+-----+---------------------+--------------+
| 01  | 赵雷     | 男  | 1990-01-01 00:00:00 |            2 |
| 02  | 钱电     | 男  | 1990-12-21 00:00:00 |            2 |
| 03  | 孙风     | 男  | 1990-05-20 00:00:00 |            2 |
| 04  | 李云     | 男  | 1990-08-06 00:00:00 |            2 |
| 05  | 周梅     | 女  | 1991-12-01 00:00:00 |            2 |
+-----+----------+-----+---------------------+--------------+

2.9 查询报了语文但是没报数学课的同学的信息

select c.* from student c,SC d where c.sid=d.sid and c.sid not in (
	select sid from SC a,course b where a.cid=b.cid and b.cname="数学"
) and d.cid=(select cid from course where course.cname="语文");
+-----+----------+-----+---------------------+
| sid | username | sex | birthday            |
+-----+----------+-----+---------------------+
| 06  | 吴兰     | 女  | 1992-03-01 00:00:00 |
+-----+----------+-----+---------------------+

2.10 查询没有学全所有课程的同学的信息

select a.* from student a,SC b where a.sid=b.sid group by b.sid having count(b.cid)<(select count(*) from course);
+-----+----------+-----+---------------------+
| sid | username | sex | birthday            |
+-----+----------+-----+---------------------+
| 05  | 周梅     | 女  | 1991-12-01 00:00:00 |
| 06  | 吴兰     | 女  | 1992-03-01 00:00:00 |
| 07  | 郑竹     | 女  | 1989-07-01 00:00:00 |
+-----+----------+-----+---------------------+

2.11 查询与吴兰课程有交集的同学

select a.* from student a,SC b where a.sid=b.sid and b.cid in (select cid from student a,SC b where a.sid=b.sid and a.username="吴兰") group by b.sid;
+-----+----------+-----+---------------------+
| sid | username | sex | birthday            |
+-----+----------+-----+---------------------+
| 01  | 赵雷     | 男  | 1990-01-01 00:00:00 |
| 02  | 钱电     | 男  | 1990-12-21 00:00:00 |
| 03  | 孙风     | 男  | 1990-05-20 00:00:00 |
| 04  | 李云     | 男  | 1990-08-06 00:00:00 |
| 05  | 周梅     | 女  | 1991-12-01 00:00:00 |
| 06  | 吴兰     | 女  | 1992-03-01 00:00:00 |
| 07  | 郑竹     | 女  | 1989-07-01 00:00:00 |
+-----+----------+-----+---------------------+

2.12 查询和赵雷同学的课程完全相同的同学信息

把赵雷的每个课的同学记录找出来，学生id分组后课程数=赵雷的课程数即可

select a.*,count(b.cid) from student a,SC b where a.sid=b.sid and b.cid in (
	select cid from student a,SC b where a.sid=b.sid and a.username="赵雷"
) group by sid having count(cid)=(
	select count(b.cid) from student a,SC b where a.sid=b.sid and a.username="赵雷"
);
+-----+----------+-----+---------------------+--------------+
| sid | username | sex | birthday            | count(b.cid) |
+-----+----------+-----+---------------------+--------------+
| 01  | 赵雷     | 男  | 1990-01-01 00:00:00 |            3 |
| 02  | 钱电     | 男  | 1990-12-21 00:00:00 |            3 |
| 03  | 孙风     | 男  | 1990-05-20 00:00:00 |            3 |
| 04  | 李云     | 男  | 1990-08-06 00:00:00 |            3 |
+-----+----------+-----+---------------------+--------------+

2.13 查询没学过"张三"老师讲授的任一门课程的学生姓名

先把学过张三的学生记录找出来，再反向查询

select a.* from student a,SC b where a.sid=b.sid and a.sid not in (select b.sid from teacher a,SC b,course c where a.tid=c.tid and b.cid=c.cid and a.tname="张三") group by a.sid;
+-----+----------+-----+---------------------+
| sid | username | sex | birthday            |
+-----+----------+-----+---------------------+
| 06  | 吴兰     | 女  | 1992-03-01 00:00:00 |
+-----+----------+-----+---------------------+

2.14 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

select b.sid,a.username,count(b.cid),avg(b.score)'平均成绩'
 from student a,SC b where a.sid=b.sid and b.score<60 group by sid having count(b.cid)>=2;
+-----+----------+--------------+----------+
| sid | username | count(b.cid) | 平均成绩 |
+-----+----------+--------------+----------+
| 04  | 李云     |            3 | 33.3333  |
| 06  | 吴兰     |            2 | 32.5     |
+-----+----------+--------------+----------+

2.15 检索语文分数小于60，按分数降序排列的学生信息

select a.*,b.score from student a,SC b,course c where a.sid=b.sid and b.cid=c.cid and c.cname='语文' and b.score<60 order by b.score desc;
+-----+----------+-----+---------------------+-------+
| sid | username | sex | birthday            | score |
+-----+----------+-----+---------------------+-------+
| 04  | 李云     | 男  | 1990-08-06 00:00:00 |    50 |
| 06  | 吴兰     | 女  | 1992-03-01 00:00:00 |    31 |
+-----+----------+-----+---------------------+-------+

2.16 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

–先计算学生ID对应的平均成绩记录==>sid,avg
–再用成绩表左连接on sid >sid,avg,cid,score
–再用课程表连接查询>sid,cid,score,avg,cname
–再用学生表左连接on sid, 并根据avg排序

select * from student left join (
	select c.cname,d.sid,d.cid,d.score,d.avg from course c, (
		select a.sid,cid,score,avg from SC a left join (
			select b.sid,avg(score) avg from SC b group by b.sid
		) b on a.sid=b.sid
	) d where c.cid=d.cid
) b on student.sid=b.sid order by b.avg desc;
+-----+----------+-----+---------------------+-------+------+------+-------+---------+
| sid | username | sex | birthday            | cname | sid  | cid  | score | avg     |
+-----+----------+-----+---------------------+-------+------+------+-------+---------+
| 07  | 郑竹     | 女  | 1989-07-01 00:00:00 | 数学  | 07   | 02   |    89 | 93.5    |
| 07  | 郑竹     | 女  | 1989-07-01 00:00:00 | 英语  | 07   | 03   |    98 | 93.5    |
| 01  | 赵雷     | 男  | 1990-01-01 00:00:00 | 语文  | 01   | 01   |    80 | 89.6667 |
| 01  | 赵雷     | 男  | 1990-01-01 00:00:00 | 数学  | 01   | 02   |    90 | 89.6667 |
| 01  | 赵雷     | 男  | 1990-01-01 00:00:00 | 英语  | 01   | 03   |    99 | 89.6667 |
| 05  | 周梅     | 女  | 1991-12-01 00:00:00 | 语文  | 05   | 01   |    76 | 81.5    |
| 05  | 周梅     | 女  | 1991-12-01 00:00:00 | 数学  | 05   | 02   |    87 | 81.5    |
| 03  | 孙风     | 男  | 1990-05-20 00:00:00 | 语文  | 03   | 01   |    80 | 80      |
| 03  | 孙风     | 男  | 1990-05-20 00:00:00 | 数学  | 03   | 02   |    80 | 80      |
| 03  | 孙风     | 男  | 1990-05-20 00:00:00 | 英语  | 03   | 03   |    80 | 80      |
| 02  | 钱电     | 男  | 1990-12-21 00:00:00 | 数学  | 02   | 02   |    60 | 70      |
| 02  | 钱电     | 男  | 1990-12-21 00:00:00 | 英语  | 02   | 03   |    80 | 70      |
| 02  | 钱电     | 男  | 1990-12-21 00:00:00 | 语文  | 02   | 01   |    70 | 70      |
| 04  | 李云     | 男  | 1990-08-06 00:00:00 | 数学  | 04   | 02   |    30 | 33.3333 |
| 04  | 李云     | 男  | 1990-08-06 00:00:00 | 英语  | 04   | 03   |    20 | 33.3333 |
| 04  | 李云     | 男  | 1990-08-06 00:00:00 | 语文  | 04   | 01   |    50 | 33.3333 |
| 06  | 吴兰     | 女  | 1992-03-01 00:00:00 | 英语  | 06   | 03   |    34 | 32.5    |
| 06  | 吴兰     | 女  | 1992-03-01 00:00:00 | 语文  | 06   | 01   |    31 | 32.5    |
| 08  | 王菊     | 女  | 1990-01-20 00:00:00 | NULL  | NULL | NULL | NULL  | NULL    |
+-----+----------+-----+---------------------+-------+------+------+-------+---------+

2.17 查询学生的总成绩并进行排名

select a.*,sum(b.score) from student a,SC b where a.sid=b.sid group by sid order by sum(b.score) desc; -- 使用左连接是避免空成绩
select * from student a left join (select SC.sid,sum(SC.score) sum from SC group by SC.sid) b on a.sid=b.sid order by sum desc;
+-----+----------+-----+---------------------+------+------+
| sid | username | sex | birthday            | sid  | sum  |
+-----+----------+-----+---------------------+------+------+
| 01  | 赵雷     | 男  | 1990-01-01 00:00:00 | 01   | 269  |
| 03  | 孙风     | 男  | 1990-05-20 00:00:00 | 03   | 240  |
| 02  | 钱电     | 男  | 1990-12-21 00:00:00 | 02   | 210  |
| 07  | 郑竹     | 女  | 1989-07-01 00:00:00 | 07   | 187  |
| 05  | 周梅     | 女  | 1991-12-01 00:00:00 | 05   | 163  |
| 04  | 李云     | 男  | 1990-08-06 00:00:00 | 04   | 100  |
| 06  | 吴兰     | 女  | 1992-03-01 00:00:00 | 06   | 65   |
| 08  | 王菊     | 女  | 1990-01-20 00:00:00 | NULL | NULL |
+-----+----------+-----+---------------------+------+------+

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

(左连接是避免有些老师没教书)

select b.cid,c.cname,d.tname,avg(b.score) from SC b,course c,teacher d where b.cid=c.cid and c.tid=d.tid group by b.cid order by avg(b.score) desc;
+-----+-------+-------+--------------+
| cid | cname | tname | avg(b.score) |
+-----+-------+-------+--------------+
| 02  | 数学  | 张三  | 72.6667      |
| 03  | 英语  | 王五  | 68.5         |
| 01  | 语文  | 李四  | 64.5         |
+-----+-------+-------+--------------+

2.19 查询总成绩第2名到第3名的学生信息及该课程成绩

select a.*,sum(b.score) from student a, SC b where a.sid=b.sid group by b.sid order by sum(b.score) desc limit 1,2;
+-----+----------+-----+---------------------+--------------+
| sid | username | sex | birthday            | sum(b.score) |
+-----+----------+-----+---------------------+--------------+
| 03  | 孙风     | 男  | 1990-05-20 00:00:00 | 240          |
| 02  | 钱电     | 男  | 1990-12-21 00:00:00 | 210          |
+-----+----------+-----+---------------------+--------------+

2.20 统计各科成绩各分数段人数：课程编号,课程名称, 100-85 , 85-70 , 70-60 , 0-60 及所占百分比

select a.cid,b.cname,sum(a.score>85) '85-100',sum(a.score>=60 and a.score<85) '60-84',sum(a.score<60) '0-59' from SC a,course b where a.cid=b.cid group by a.cid;
+-----+-------+--------+-------+------+
| cid | cname | 85-100 | 60-84 | 0-59 |
+-----+-------+--------+-------+------+
| 01  | 语文  | 0      | 4     | 2    |
| 02  | 数学  | 3      | 2     | 1    |
| 03  | 英语  | 2      | 2     | 2    |
+-----+-------+--------+-------+------+

2.21 查询各科选修人数

select a.cid,b.cname,count(a.sid) from SC a,course b where a.cid=b.cid group by a.cid;
+-----+-------+--------------+
| cid | cname | count(a.sid) |
+-----+-------+--------------+
| 01  | 语文  |            6 |
| 02  | 数学  |            6 |
| 03  | 英语  |            6 |
+-----+-------+--------------+

2.22 查询只选修两门课的学生信息

select a.*,b.sid,count(b.cid) from student a,SC b where a.sid=b.sid group by b.sid having count(b.cid)=2;
+-----+----------+-----+---------------------+-----+--------------+
| sid | username | sex | birthday            | sid | count(b.cid) |
+-----+----------+-----+---------------------+-----+--------------+
| 05  | 周梅     | 女  | 1991-12-01 00:00:00 | 05  |            2 |
| 06  | 吴兰     | 女  | 1992-03-01 00:00:00 | 06  |            2 |
| 07  | 郑竹     | 女  | 1989-07-01 00:00:00 | 07  |            2 |
+-----+----------+-----+---------------------+-----+--------------+

2.23 查询男生、女生人数

select sex,count(sid) from student group by sex;
+-----+------------+
| sex | count(sid) |
+-----+------------+
| 男  |          4 |
| 女  |          4 |
+-----+------------+

2.24 查询1990后出生的学生名单(注：Student表中Sage列的类型是datetime)

select * from student where year(birthday)>1990;
+-----+----------+-----+---------------------+
| sid | username | sex | birthday            |
+-----+----------+-----+---------------------+
| 05  | 周梅     | 女  | 1991-12-01 00:00:00 |
| 06  | 吴兰     | 女  | 1992-03-01 00:00:00 |
+-----+----------+-----+---------------------+

2.25 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select a.cid,b.cname,avg(a.score) from SC a,course b where a.cid=b.cid group by a.cid order by avg(a.score) desc,a.cid asc;
+-----+-------+--------------+
| cid | cname | avg(a.score) |
+-----+-------+--------------+
| 02  | 数学  | 72.6667      |
| 03  | 英语  | 68.5         |
| 01  | 语文  | 64.5         |
+-----+-------+--------------+

2.26 查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

select a.sid,b.username,avg(a.score) avg from SC a,student b where a.sid=b.sid group by a.sid having avg>85;
+-----+----------+---------+
| sid | username | avg     |
+-----+----------+---------+
| 01  | 赵雷     | 89.6667 |
| 07  | 郑竹     | 93.5    |
+-----+----------+---------+

2.27 查询数学课程分数低于60的学生姓名和分数

select c.username,a.score from SC a,course b,student c where a.sid=c.sid and a.cid=b.cid and b.cname='数学' and a.score <60;
+-----+----------+-------+
| sid | username | score |
+-----+----------+-------+
| 04  | 李云     |    30 |
+-----+----------+-------+

2.28 查询任何一门课程成绩在70分以上的姓名、课程名称和分数

select a.username,c.cname,b.score from student a,SC b,course c where a.sid=b.sid and b.cid=c.cid and score>70;
+----------+-------+-------+
| username | cname | score |
+----------+-------+-------+
| 赵雷     | 语文  |    80 |
| 赵雷     | 数学  |    90 |
| 赵雷     | 英语  |    99 |
| 钱电     | 英语  |    80 |
| 孙风     | 语文  |    80 |
| 孙风     | 数学  |    80 |
| 孙风     | 英语  |    80 |
| 周梅     | 语文  |    76 |
| 周梅     | 数学  |    87 |
| 郑竹     | 数学  |    89 |
| 郑竹     | 英语  |    98 |
+----------+-------+-------+

2.29 查询选修了全部课程的学生信息

select a.sid,b.username,count(cid) from SC a,student b where a.sid=b.sid group by sid having count(cid)=(select count(*) from course);
+-----+----------+------------+
| sid | username | count(cid) |
+-----+----------+------------+
| 01  | 赵雷     |          3 |
| 02  | 钱电     |          3 |
| 03  | 孙风     |          3 |
| 04  | 李云     |          3 |
+-----+----------+------------+