使用的是mysql数据库 本文参考:https://blog.csdn.net/flycat296/article/details/63681089
数据———————————————————————————————
1.创建数据库
CREATE DATABASE `mytest`
2.创建course表
CREATE TABLE `course` (
`Cid` varchar(10) NOT NULL DEFAULT '' COMMENT '课程id',
`Cname` varchar(10) NOT NULL COMMENT '课程名称',
`Tid` varchar(10) NOT NULL DEFAULT '' COMMENT '教师id'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表';
插入如下数据
3.创建SC表
CREATE TABLE `sc` (
`Sid` varchar(10) NOT NULL DEFAULT '' COMMENT '学生id',
`Cid` varchar(10) NOT NULL DEFAULT '' COMMENT '课程id',
`score` decimal(18,1) NOT NULL COMMENT '分数'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='成绩表';
插入如下数据
4.创建student表
CREATE TABLE `student` (
`Sid` varchar(10) NOT NULL DEFAULT '' COMMENT '学生id',
`Sname` varchar(10) NOT NULL COMMENT '姓名',
`Sage` datetime NOT NULL COMMENT '年龄',
`Ssex` varchar(10) NOT NULL COMMENT '性别'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表';
插入如下数据
5.创建teacher表
CREATE TABLE `teacher` (
`Tid` varchar(10) NOT NULL DEFAULT '' COMMENT '教师id',
`Tname` varchar(10) NOT NULL COMMENT '教师姓名'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='教师表';
插入如下数据
题目———————————————————————————————
1. 查询" 01 "课程比" 02 "课程成绩高的学生的信息及课程分数
select C.*,A.Cid,A.score,B.Cid,B.score
from (select * from sc where Cid=01)A
left join (select * from sc where Cid=02)B on A.Sid=B.Sid
inner join student C on B.Sid=C.Sid
where B.score<A.score
查询结果如下
解析:本题采用的是左连接
2. 查询同时存在" 01 "课程和" 02 "课程的情况
select A.*,B.Cid,B.score
from (select * from sc where Cid=01)A
left join (select *from sc where Cid=02)B on A.Sid=B.Sid
where B.Sid
查询结果如下
解析:先将sc(成绩表)里Cid(课程号)=01与Cid(课程号)=02的信息分出——>
select * from sc where Cid=01
执行语句如下图所示
select * from sc where Cid=02
执行语句如下图所示
然后用左连接left join 在学号相同的情况下将这两张表连接
得到如下表
上表显示06没有02课程的信息,所以用where Sid is not null 语句进行筛选最终得到题目所要求答案
3.查询不存在" 01 "课程但存在" 02 "课程的情况
方法一:
select B.*
from (select * from sc where Cid=02)B
left join (select *from sc where Cid=01)A on A.Sid=B.Sid
where A.Sid is null
方法二:
select B.*
from (select * from sc where Cid=01)A
right join (select *from sc where Cid=02)B on A.Sid=B.Sid
where A.Sid is null
方法三:
select * from sc where Sid not in (select Sid from sc where Cid=01)
4.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
select B.* ,A.dc from ( select Sid,avg(score)dc from sc group by Sid) A inner join student B on B.Sid=A.Sid where A.dc>=60
查询结果如下
5. 查询在 SC 表存在成绩的学生信息
如下四种方法:
select A.* from student A inner join (select distinct Sid from sc)B on A.Sid=B.Sid
select A.* from student A inner join (select Sid from sc group by Sid)B on A.Sid=B.Sid
select A.* from student A where A.Sid in(select distinct Sid from sc )
select A.* from student A where A.Sid in(select Sid from sc group by Sid)
查询结果如下
6.查询所有同学的学生信息、选课总数、所有课程的总成绩(没成绩的显示为 null )
select A.*,B.sumsc from student A left join (select sum(score)sumsc,Sid from sc group by Sid)B on A.Sid=B.Sid
查询结果如下
7.查询姓李老师的总数
select count(*) from teacher where Tname like '李%'
查询结果如下
8.查询姓李老师的信息以及总数
select Tid,Tname,count(*) from teacher where Tname like '李%' group by Tname,Tid;
查询结果如下
9.查询上过张三老师课的同学的信息
select * from student where Sid in (
select Sid from sc where Cid=(
select Cid from course where Tid=(
select Tid from teacher where Tname='张三') ) )
查询结果如下
10.查询没有学全所有课程的同学的信息
select * from student where Sid in (select Sid from (select Sid,count(score)dc from sc group by Sid)A where A.dc <3) ;
查询结果如下
11.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select * from student where Sid in (select Sid from sc where Cid in (select Cid from sc where Sid =01) )
查询结果如下
12.查询与学号为" 01 "的同学所学科目全部相同的同学的信息
方法一
select * from student where Sid in(
select Sid from (
select Sid,group_concat(Cid order by Cid) Scid from sc group by Sid)A where A.SCid=(
select group_concat(Cid order by Cid)MCid from sc where Sid=01))
查询结果如下
13. 查询没学过"张三"老师讲授的任一门课程的学生信息
select * from student
where Sid not in (select Sid from Sc
where Cid in (select Cid from course
where Tid =(select Tid from teacher
where Tname='张三') ))
查询结果如下
14. 查询各科成绩课程ID,课程名称,最高分,最低分,平均分,及格率(>=60),中等率(70-80),优良率(80-90),优秀率(>=90)
select sc.Cid,Cname ,max(score),min(score),avg(score),
sum(score>=60)/count(score) as qualified,
sum(score>=70 and score<=80)/count(score) as midle,
sum(score>=80 and score<=90)/count(score) as fine,
sum(score>=90)/count(score) as excellent
from sc left join course on sc.Cid=course.Cid group by sc.Cid,course.Cname;
![](https://i-blog.csdnimg.cn/blog_migrate/d9d11d1c1eeb287d968d659bdc2e382d.png)
题目正在更新!!!