数据库题

数据库（题附答案）

 

问题及描述：
--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 ;