问题及描述：

--1.学生表

Student(Ssum,Sname,Sage,Ssex) --Snum 学生编号,Sname 学生姓名,Sage 出生年月,Ssex 学生性别

--2.课程表

Course(Cnum,Cname,Tnum) --Cnum --课程编号,Cname 课程名称,Tnum 教师编号

--3.教师表

Teacher(Tnum,Tname) --Tnum 教师编号,Tname 教师姓名

--4.成绩表

SC(Snum,Cnum,score) --Snum 学生编号,Cnum 课程编号,score 分数

*/

--创建测试数据

create table Student(Snum varchar(10),Sname nvarchar(10),Sage datetime,Ssex nvarchar(10));

insert into Student values('01' , N'赵雷' , '1990-01-01' , N'男');

insert into Student values('02' , N'钱电' , '1990-12-21' , N'男');

insert into Student values('03' , N'孙风' , '1990-05-20' , N'男');

insert into Student values('04' , N'李云' , '1990-08-06' , N'男');

insert into Student values('05' , N'周梅' , '1991-12-01' , N'女');

insert into Student values('06' , N'吴兰' , '1992-03-01' , N'女');

insert into Student values('07' , N'郑竹' , '1989-07-01' , N'女');

insert into Student values('08' , N'王菊' , '1990-01-20' , N'女');

create table Course(Cnum varchar(10),Cname nvarchar(10),Tnum varchar(10));

insert into Course values('01' , N'语文' , '02');

insert into Course values('02' , N'数学' , '01');

insert into Course values('03' , N'英语' , '03');

create table Teacher(Tnum varchar(10),Tname nvarchar(10));

insert into Teacher values('01' , N'张三');

insert into Teacher values('02' , N'李四');

insert into Teacher values('03' , N'王五');

create table SC(Snum varchar(10),Cnum 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);

insert into SC values('09' , '03' , 98);

 

#1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

#1.1、查询同时存在"01"课程和"02"课程的情况

⑴select student.*,sc.score sc1,sc02.score sc2 from student

inner join sc on student.snum = sc.snum and sc.cnum = '01'

inner join sc sc02 on student.snum = sc02.snum and sc02.cnum = '02'

where sc.score > sc02.score;

⑵select student.*,a.score sc1,b.score sc2 from (select * from sc where sc.cnum = '01') a

inner join(select * from sc where sc.cnum = '02') b on a.snum = b.snum

inner join student on student.Snum = a.snum

where a.score > b.score

#1.2、存在"01"课程但可能不存在"02"课程的情况(不存在时显示为null)(以下存在相同内容时不再解释)

select student.*,a.score sc1,b.score sc2 from (select * from sc where sc.cnum = '01') a

left join(select * from sc where sc.cnum = '02')b on a.snum = b.snum

inner join student on student.Snum = a.snum

where a.score > ifnull(b.score,0);

解析：左联01课程，判断b.score 是否为null空即为0

#2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数

#2.1、查询同时存在"01"课程和"02"课程的情况

select student.*,sc.score sc1,sc02.score sc2 from student

inner join sc on student.snum = sc.snum and sc.cnum = '01'

inner join sc sc02 on student.snum = sc02.snum and sc02.cnum = '02'

where sc.score < sc02.score;

解析：和第一题一样，只是01课程低

#2.2、查询同时存在"01"课程和"02"课程的情况和不存在"01"课程但存在"02"课程的情况

select student.*,a.score sc01,b.score sc02 from (select * from sc where sc.cnum = '01') a

right join (select * from sc where sc.cnum = '02' ) b on a.snum = b.Snum

inner join student on b.snum = student.snum

where b.score > ifnull(a.score,-1);

解析：友联02课程，判断a.score是否为null

#3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select sc.snum,student.Sname,avg(score) from sc

inner join student on student.snum = sc.snum

group by sc.snum,student.sname having avg(score) > 60;

#4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

select sc.snum,student.Sname,avg(score) from sc

inner join student on student.snum = sc.snum

group by sc.snum,student.sname having avg(score) < 60;

#4.1、查询在sc表存在成绩的学生信息的SQL语句。

select sc.snum,student.Sname,avg(score) from sc

left join student on student.snum = sc.snum

group by sc.snum,student.sname ;

#4.2、查询在sc表中不存在成绩的学生信息的SQL语句。

select student.snum,student.Sname,avg(score) from sc

right join student on student.snum = sc.snum

group by sc.snum,student.sname having ifnull(avg(score),0) = 0;

#5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

#5.1、查询所有有成绩的SQL。

select sc.snum,s.sname,sum(score),count(cnum) from student s

right join sc on s.snum = sc.snum

group by s.snum;

#5.2、查询所有(包括有成绩和无成绩)的SQL。

select sc.snum,s.sname,sum(score),count(cnum) from student s

right join sc on s.snum = sc.snum

group by s.snum

union all   #列数相同，属性尽量相同

select sc.snum,s.sname,sum(score),count(cnum) from student s

left join sc on s.snum = sc.snum

group by s.snum having ifnull(sum(score),0) = 0;

解析：有成绩的+有名字没成绩的；

#6、查询"李"姓老师的数量

select count(*) from  teacher where teacher.tname like '李%'；

#7、查询学过"张三"老师授课的同学的信息

#正确

select student.* from teacher t

inner join course c on t.Tnum = c.tnum

inner join sc s on c.Cnum = s.cnum

inner join student on student.snum = s.snum

where tname = '张三';

select * from student

where snum in (select snum from sc where cnum =

(select cnum from course where tnum =

(select tnum from teacher where tname = '张三')

) );

#8、查询没学过"张三"老师授课的同学的信息

select *from student where snum not in(

select snum from teacher t

inner join course c on t.Tnum = c.tnum

inner join sc s on c.Cnum = s.cnum

where tname = '张三');

select * from student

where snum not in (select snum from sc where cnum =

(select cnum from course where tnum =

(select tnum from teacher where tname = '张三')

) );

#9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

select student.* from student

inner join sc on sc.snum = student.snum and sc.Cnum = '01'

inner join sc sc02 on sc02.snum = student.snum and sc02.Cnum = '02' ;

#10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

select student.* from student

inner join sc on sc.snum = student.snum and sc.Cnum = '01'

where student.snum not in (select snum from sc where sc.cnum = '02');

#11、查询没有学全所有课程的同学的信息

select student.Snum,sname,count(cnum) from student

inner join sc on student.snum = sc.snum

group by student.snum,sname

having count(*) < (select count(cnum )from course);

#12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

select distinct student.* from sc

inner join student on sc.snum = student.snum

where student.snum != '01'

and cnum in(select cnum from sc where snum = '01');

#13、查询和"01"号的同学学习的课程完全相同的其他同学的信息 XX

#"01"号同学选课总数

select count(cnum) from sc where sc.snum = '01' ;

#每个人的选课总数

select count(snum) from sc group by snum;

#在01范围内的选课总数

select count(cnum) from sc where cnum in (select cnum from sc where sc.snum = '01')

group by snum;

select student.*,

(select count(snum) from sc group by snum having student.snum = sc.snum) 选课总数,

(select count(cnum) from sc where cnum in (select cnum from sc where sc.snum = '01')

group by snum having student.snum = sc.snum) 在01范围内的选课总数

from student

having 选课总数 = (select count(cnum) from sc where sc.snum = '01' )

and 在01范围内的选课总数 = (select count(cnum) from sc where sc.snum = '01' );

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

select * from student where snum not in (

select snum from sc where cnum =

(select cnum from course where tnum =

(select tnum from teacher where tname = '张三')

) );

select *from student where snum not in(

select snum from teacher t

inner join course c on t.Tnum = c.tnum

inner join sc s on c.Cnum = s.cnum

where tname = '张三');

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

#这个做法是错误的，因为如果有3门课，两门没及格那么平均成绩为未及格的

select student.snum,sname,avg(score) from sc

inner join student on sc.snum = student.snum

where score < 60 group by sc.snum having count(*) >= 2;

#两门及以上不合格的学号

select snum from sc

where score < 60 group by sc.snum having count(*) >= 2;

#关联sc和student求snum在上面范围内的学生信息

select s.snum,sname,avg(score) from student s

inner join sc on sc.snum = s.snum

group by s.snum having snum in(select snum from sc

where score < 60 group by sc.snum having count(*) >= 2);

#运用case可少遍历一遍

select * ,sum(case when score<60 then 1 else 0 end ) a,avg(score)

from sc group by snum having a >= 2;

#16、检索"01"课程分数小于60，按分数降序排列的学生信息

select student.*,sc.score from sc

inner join student on student.snum = sc.snum

where cnum = '01' and score < 60 order by score desc;

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

#平均成绩列没有group是因为student表中每个学生只有一行

select student.*,

(select score from sc where sc.cnum = '01' and sc.snum = student.snum) sc01,

(select score from sc where sc.cnum = '02' and sc.snum = student.snum) sc02,

(select score from sc where sc.cnum = '03' and sc.snum = student.snum) sc03,

(select avg(score) from sc where sc.snum = student.snum) 平均成绩

from student order by 平均成绩 desc;

#18、查询各科成绩最高分、最低分和平均分：

#以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

#及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

select cnum,cname,

(select max(score) from sc where sc.cnum = c.cnum) maxnum,

(select min(score) from sc where sc.cnum = c.cnum) minnum,

(select avg(score) from sc where sc.cnum = c.cnum) avgnum,

(select count(*) from sc where sc.cnum = c.cnum and score > 60) 及格 ,

(select count(*) from sc where sc.cnum = c.cnum ) 总数 ,

(select count(*) from sc where sc.cnum = c.cnum and score > 60) * 100 / (select count(*) from sc where sc.cnum = c.cnum ) '及格率 % '

from course c ;

#19、按各科成绩进行排序，并显示排名

select * ,

(select count(*)+1 from sc scc where scc.cnum = sc.cnum and sc.score < scc.score) pm

from sc order by cnum,pm;

#20、查询学生的总成绩并进行排名

#总成绩的表

select snum,sum(score) 总成绩 from sc group by snum;

select *,

(select count(*)+1 from

(select snum,sum(score) 总成绩 from sc group by snum) B where A.总成绩 < B.总成绩) pm

from (select snum,sum(score) 总成绩 from sc group by snum) A order by pm ;