SQL练习题

使用的是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;

题目正在更新！！！