sql练习随笔一_insert into sc values('01' , '01' , 80); insert in-CSDN博客

本文链接：https://blog.csdn.net/weixin_42513478/article/details/88540776

数据表介绍
–1.学生表
Student(SId,Sname,Sage,Ssex)
–SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
–2.课程表
Course(CId,Cname,TId)
–CId 课程编号,Cname 课程名称,TId 教师编号
–3.教师表
Teacher(TId,Tname)
–TId 教师编号,Tname 教师姓名
–4.成绩表
SC(SId,CId,score)
–SId 学生编号,CId 课程编号,score 分数
学生表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
insert into Student values(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
insert into Student values(‘03’ , ‘孙风’ , ‘1990-12-20’ , ‘男’);
insert into Student values(‘04’ , ‘李云’ , ‘1990-12-06’ , ‘男’);
insert into Student values(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);
insert into Student values(‘06’ , ‘吴兰’ , ‘1992-01-01’ , ‘女’);
insert into Student values(‘07’ , ‘郑竹’ , ‘1989-01-01’ , ‘女’);
insert into Student values(‘09’ , ‘张三’ , ‘2017-12-20’ , ‘女’);
insert into Student values(‘10’ , ‘李四’ , ‘2017-12-25’ , ‘女’);
insert into Student values(‘11’ , ‘李四’ , ‘2012-06-06’ , ‘女’);
insert into Student values(‘12’ , ‘赵六’ , ‘2013-06-13’ , ‘女’);
insert into Student values(‘13’ , ‘孙七’ , ‘2014-06-01’ , ‘女’);

科目表 Course
create table Course(CId varchar(10),Cname nvarchar(10),TId varchar(10));
insert into Course values(‘01’ , ‘语文’ , ‘02’);
insert into Course values(‘02’ , ‘数学’ , ‘01’);
insert into Course values(‘03’ , ‘英语’ , ‘03’);

教师表 Teacher
create table Teacher(TId varchar(10),Tname varchar(10));
insert into Teacher values(‘01’ , ‘张三’);
insert into Teacher values(‘02’ , ‘李四’);
insert into Teacher values(‘03’ , ‘王五’);

成绩表 SC
create table SC(SId varchar(10),CId varchar(10),score decimal(18,1));
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);

练习题目

1.查询" 01 “课程比” 02 "课程成绩高的学生的信息及课程分数
select * from Student RIGHT JOIN (
select t1.SId , c1, c2 from

(select SId, score AS c1 from sc where sc.CId = ‘01’) AS t1,
(select SId, score AS c2 from sc where sc.CId = ‘02’) AS t2
where t1.SId = t2.SId and t1.c1 > t2.c2
) AS r
on Student.SId = r.SId;

1.1 查询同时存在" 01 “课程和” 02 "课程的情况
select * from
(SELECT * from sc where sc.CId = ‘01’)as t1,
(select * from sc where sc.CId = ‘02’)as t2
WHERE t1.SId = t2.SId;

1.2 查询存在" 01 “课程但可能不存在” 02 "课程的情况(不存在时显示为 null )
select * from
(SELECT * from sc where sc.CId = ‘01’)as t1
left join
(select * from sc where sc.CId = ‘02’)as t2
on t1.SId = t2.SId;

1.3 查询不存在" 01 “课程但存在” 02 "课程的情况
select * from sc
where sc.SId not in
(select SId from sc where sc.CId=‘01’)
and sc.CId=‘02’;

2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT Student.SId, Sname, ass from Student, (
select SId,AVG(score)as ass from sc
group by SId having AVG(score) > 60)AS r
where r.SId = student.SId;

3.查询在 SC 表存在成绩的学生信息
select distinct student.* from student,
sc where sc.SId = student.SId;

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
select student.SId, student.Sname, r.t, r.ss from student, (
select sc.SId, sum(sc.score)as t , count(sc.CId)as ss from sc group by sc.SId
)as r
where r.SId = student.SId;
没成绩显示为空
select s.SId, s.Sname, r.t, r.ss from
(
select sc.SId, sum(sc.score)as t , count(sc.CId)as ss from sc group by sc.SId
)as r right join
(select student.SId, student.Sname from student)as s
on r.SId = s.SId;

4.1 查有成绩的学生信息
内查询
exists 查询
select * from student where
exists (select sc.SId from sc where sc.SId=student.SId);
in查询
select * from student where SId
in (select sc.SId from sc );

5.查询「李」姓老师的数量
select count(*) from teacher where
Tname like ‘李%’;

6.查询学过「张三」老师授课的同学的信息
select student.* from student, teacher, sc, course
where student.SId = sc.SId
and sc.CId = course.CId
and course.TId = teacher.TId
and teacher.Tname = ‘张三’;

7.查询没有学全所有课程的同学的信息
select distinct student.*
from student where student.SId not in
(select sc.SId from sc group by sc.SId
having count(sc.CId)= (select count(CId) from course));

8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
select * from student
where student.SId in(select sc.SId from sc
where sc.CId in(select sc.CId from sc where sc.SId = ‘01’));

9查询和" 01 "号的同学学习的课程完全相同的其他同学的信息
select * from student where SId in(
select SId from sc where SId not in(
select SId from sc where CId not in(
select CId from sc where SId = ‘01’))
group by SId having count(sc.CId) =(select count(CId) from sc where SId = ‘01’)and SId!=‘01’);

10查询没学过"张三"老师讲授的任一门课程的学生姓名
select Sname from student
where student.SId not in (
select sc.SId from teacher, sc, course
where sc.CId = course.CId
and course.TId = teacher.TId
and teacher.Tname = ‘张三’);

11查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select student.SId, student.Sname, avg(sc.score) from student, sc
where student.SId = sc.SId
and sc.score<60 group by sc.SId having count(*)>1;

12检索" 01 "课程分数小于 60，按分数降序排列的学生信息
select student.*,sc.score from student, sc
where student.SId = sc.SId
and sc.score < 60
AND sc.CId = ‘01’
order by sc.score desc;
默认升序，desc降序

13按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select * from sc
left join (
select sid, avg(sc.score)as avg1 from sc
group by SId)as r
on r.sid = sc.SId
order by avg1 desc;数据表介绍
–1.学生表
Student(SId,Sname,Sage,Ssex)
–SId 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别
–2.课程表
Course(CId,Cname,TId)
–CId 课程编号,Cname 课程名称,TId 教师编号
–3.教师表
Teacher(TId,Tname)
–TId 教师编号,Tname 教师姓名
–4.成绩表
SC(SId,CId,score)
–SId 学生编号,CId 课程编号,score 分数
学生表 Student
create table Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values(‘01’ , ‘赵雷’ , ‘1990-01-01’ , ‘男’);
insert into Student values(‘02’ , ‘钱电’ , ‘1990-12-21’ , ‘男’);
insert into Student values(‘03’ , ‘孙风’ , ‘1990-12-20’ , ‘男’);
insert into Student values(‘04’ , ‘李云’ , ‘1990-12-06’ , ‘男’);
insert into Student values(‘05’ , ‘周梅’ , ‘1991-12-01’ , ‘女’);
insert into Student values(‘06’ , ‘吴兰’ , ‘1992-01-01’ , ‘女’);
insert into Student values(‘07’ , ‘郑竹’ , ‘1989-01-01’ , ‘女’);
insert into Student values(‘09’ , ‘张三’ , ‘2017-12-20’ , ‘女’);
insert into Student values(‘10’ , ‘李四’ , ‘2017-12-25’ , ‘女’);
insert into Student values(‘11’ , ‘李四’ , ‘2012-06-06’ , ‘女’);
insert into Student values(‘12’ , ‘赵六’ , ‘2013-06-13’ , ‘女’);
insert into Student values(‘13’ , ‘孙七’ , ‘2014-06-01’ , ‘女’);