Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
建表语句:
create table Student(SID varchar(10),Sname varchar(10),Sage datetime,Ssex nvarchar(10)); insert into Student values('01' , '赵雷' , '1990-01-01' , '男'); insert into Student values('02' , '钱电' , '1990-12-21' , '男'); insert into Student values('03' , '孙风' , '1990-05-20' , '男'); insert into Student values('04' , '李云' , '1990-08-06' , '男'); insert into Student values('05' , '周梅' , '1991-12-01' , '女'); insert into Student values('06' , '吴兰' , '1992-03-01' , '女'); insert into Student values('07' , '郑竹' , '1989-07-01' , '女'); insert into Student values('08' , '王菊' , '1990-01-20' , '女');
create table Course(CID varchar(10),Cname varchar(10),TID varchar(10)); insert into Course values('01', '语文' , '02'); insert into Course values('02', '数学' , '01'); insert into Course values('03', '英语' , '03');
create table Teacher(TID varchar(10),Tname varchar(10)); insert into Teacher values('01' , '张三'); insert into Teacher values('02' , '李四'); insert into Teacher values('03' , '王五');
create table SC(SID varchar(10),CID varchar(10),score decimal(18,1)); insert into SC values('01' , '01' , 80); insert into SCvalues('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”课程成绩高的所有学生的学号;
--a.sid可以换为*
--分析:第一步,先查出01课程和02课程的学生id和分数,
第二步,然后通过相同的学生id和a.score>b.score条件获取结果集
select a.sid from
(select sid,score from sc where cid ="01") a,
(select sid,score from sc where cid="02" ) b
where a.score>b.score
and a.sid = b.sid;
2、查询平均成绩大于60分的同学的学号和平均成绩;
--分析:查询学号和平均成绩就需要分组,平均成绩需用到avg()函数,再判断平均成绩大于60
select sid,avg(score) from sc group by sid having avg(score)>60;
3、查询所有同学的学号、姓名、选课数、总成绩;
--分析:需要学生表和分数表,而且是通过学号分组,选课数需要count(cid),总成绩需要sum(score),条件是学生id相等
select stu.sid,stu.sname,COUNT(sc.cid),SUM(sc.score)
from Student stu
left join sc sc
on stu.sid = sc.sid
group by stu.sid,stu.sname;
4、查询姓“李”的老师的个数;
select COUNT(*) from Teacher where Tname like "李%";
5、查询没学过“张三”老师课的同学的学号、姓名;
--分析:先获取学过张三老师课的所有同学的学号,需要通过关联字段关联分数表,课程表和教师表来获取,最后查 学生表中学生id不在此列的即可
select stu.sid,stu.sname from student stu
where stu.sid not in
( select sc.sid from sc,course,teacher
where sc.cid=course.cid
and course.tid = teacher.tid and teacher.tname = "张三");
6、查询学过“01”并且也学过编号“02”课程的同学的学号、姓名;
--分析:先获取学过01的学生id,再获取学过02的学生id,这两个取交集,然后获取学生表中id存在这个交集的
select stu.sid,stu.sname from student stu ,sc sc
where stu.sid in
(select a.sid from
(select sid from sc where sc.CID='01')a,
(select sid from sc where sc.CID='02') b
where a.sid =b.sid )
group by stu.SID;
--另一种方式:先查学生和分数表,获取课程为01的学生id,然后再获取存在于(根据课程02和课程01相同学生的id)
select stu.sid,stu.sname from student stu ,sc sc
where stu.sid = sc.sid and sc.cid="01"
and exists
(select * from sc sc2 where sc2.sid=sc.sid and sc2.cid="02");
7、查询学过“张三”老师所教的所有课的同学的学号、姓名
--分析:第一:先获取学过张三老师课的同学id,name;然后获取张三老师所教的所有课程;然后判断学过张三老师课的课程数量 等于 张三老师所教的所有课程的学生id和name
select stu.sid,stu.sname from student stu
where stu.sid in
( select sc.sid from sc ,course,teacher
where sc.cid = course.cid and course.tid = teacher.tid
and teacher.tname ="张三"
group by sc.sid
having count(sc.cid) =
(select count(*) from course,teacher
where course.tid = teacher.tid and teacher.tname ="张三")
);
8、查询课程编号“02”的成绩比课程编号“01”课程低的所有同学的学号、姓名
--分析一:首先需要查询课程01的学号和成绩,和课程02的学号和成绩,然后比较相同id的成绩的大小,最后找出在那个结果集里面的学生id即可
select sid,sname from student where sid in (select a.sid from (select sid,score from sc where cid="01") a,(select sid,score from sc where cid="02") b where a.score>b.score and a.sid = b.sid);
--分析二:先查询学生id,学生姓名,和分数,然后将分数表再查询一次条件为课程02的分数列,组合在一起,然后根据学生id相同,然后课程01的分数比02的分数高的学生id和姓名
select sid,Sname from (select Student.sid,Student.Sname,score ,(select score from SC SC_2 where SC_2.Sid=Student.Sid and SC_2.Cid='02') score2 from Student,SC where Student.Sid=SC.Sid and Cid='01') S_2 where score2<score;
9、查询所有课程成绩小于60分的同学的学号、姓名;
--分析:首先查询分数表中课程大于60分的,然后查询学生表中id不存在在结果集中,并且还有存在于分数表中的id
select Sid,Sname from Student where sid not in (select sc.sid from sc where sc.score>60 ) and sid in (select sc.sid from sc group by sid);
10、查询sc表中没有学全所有课的同学的学号、姓名;
--分析:首先要查一共多少门课程,再查询所学课程小于这个总课程数的同学
select Student.Sid,Student.Sname from Student,SC where Student.Sid=SC.Sid group by Student.sid having COUNT(Cid)<(select COUNT(cid) from Course);
11、查询至少有一门课与学号为“01”的同学所学相同的同学的学号和姓名;
--分析:首先查出学号为01的同学所学的课程号,然后再查询其他同学的课程号存在于这个结果集的学号和姓名
select Student.Sid,Student.Sname from Student,SC where Student.Sid=SC.Sid and SC.Cid in (select Cid from SC where Sid='01') group by student.SID;
12、查询至少学过学号为“01”同学所有一门课的其他同学学号和姓名;
--分析:与11题相同,此语句用个关键字 distinct
select distinct Student.Sid,Sname from Student,SC where Student.Sid=SC.Sid and Cid in (select cid from SC where Sid='01');
13、把“SC”表中“张三”老师教的课的成绩都更改为此课程的平均成绩
--分析:先查询出张三老师所教的课程的id,根据课程id获取平均成绩,也更新对应的学生课程分数
注:此处获取平均成绩时需要重新获取一个临时表,要不然没法根据sc表查询出数据再更新sc表数据
update SC set score = (select AVG(score) from (select * from SC) a where a.cid = SC.cid) where SC.cid in (select cid from Course,Teacher where Course.tid = Teacher.tid and Teacher.tname ="张三");
14、查询有“02”号的同学学习的所有课程的其他同学学号和姓名;
--分析:首先查询出学号为02的的所有课程,然后通过查询其他所学课程在这个02学生课程结果集的学生id,然后依 靠学生id分组,再根据每个学生id的分组后的重复条数和学生02课程的数目对比,如果相等则符合条件
select stu.sid,stu.sname from Student stu where stu.sid in (select sid from sc where cid in (select cid from sc where sid="02") group by sid having count(*) = (select count(*) from sc where sid="02"));
15、删除学习“张三”老师课的SC表记录;
--分析:首先查询出张三老师课的课程id,然后根据课程id删除sc表记录
delete from sc where cid in (select cid from course,teacher where course.TID =teacher.TID and teacher.Tname="张三")
16、向SC表中插入数据,要求符合以下条件:没有上过编号“02”课程的同学学号、02号课、02号课的平均成绩;
--分析:需要查询出没上过课程为02的学生id,然后获取02号课的平均成绩,然后插入sc表
insert sc select sid,"02",(select avg(score) from sc where cid="02") from student where sid not in (select sid from sc where cid="02") group by sid;
17、按平均成绩从高到低显示所有学生的01“语文”、02“数学”、03“英语”三门的课程成绩,按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分
--分析:根据sc表查询,显示字段,count(*),avg()函数,再order by排序
注:这里我自己写的语句是:
(select COUNT(*) from sc where sc.SID=t.sid ) as "有效课程数",(select avg(score) from sc where sc.SID=t.sid ) as "有效平均分"
不知道和这个语句里的直接count和avg有什么区别或者就是一样,反正结果集是一样的;
select sid as "学生ID", (select score from sc where sc.SID=t.sid and cid="01") as "语文", (select score from sc where sc.SID=t.sid and cid="02") as "数学", (select score from sc where sc.SID=t.sid and cid="03") as "英语", COUNT(*) as "有效课程数", avg(t.score) as "平均成绩" from sc t group by sid order by avg(t.score);
18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
--分析1:查询cid,最高分用max,最低分用min,而且有条件为sc.cid=t.cid限制,最后根据t.cid分组即可
select t.cid as "课程ID",(select MAX(score) from sc where sc.cid=t.cid ) as "最高分",(select MIN(score) from sc where sc.cid=t.cid) as "最低分" from sc t group by t.cid ;
--分析2:查询cid,然后最高分,最低分,再通过where条件中给最低分和最高分赋值
select L.Cid as "课程ID",L.score as "最高分",R.score as "最低分" from SC L ,SC R where L.score (select MAX(IL.score) from SC as IL where L.Cid = IL.Cid ) and R.Score = (select MIN(IR.score) from SC as IR where R.Cid = IR.Cid ) group by L.cid;
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
--分析:首先要确定根据分数表和课程表两张表来查询,然后获取各科的平均成绩,及格率(及格率是由及格人数/学此课的总人数),而及格人数是由分数>60来判断的(其中用到了ifnull()函数),最后根据及格率再排序
select t.Cid as "课程号",COUNT(*) as "学此课人数",course.Cname as "课程名",IFNULL(avg(score),0) as "平均成绩",100 * SUM(case when IFNULL(score,0)>=60 then 1 else 0 end)/COUNT(*) as "及格百分数" from SC T,Course where t.Cid=course.Cid group by t.Cid order by 100 * SUM(case when IFNULL(score,0)>=60 then 1 else 0 end)/COUNT(*) desc
20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 语文(01),数学(02),英语(03)
--分析:需要查询课程的平均成绩(课程总成绩/课程人数)和及格率(及格人数/课程人数)*100%:
课程总成绩=sum(case when cid="01" then score else 0 end)
课程人数=sum(case when cid="01" then 1 else 0 end)
及格人数=sum(case when cid="01" and score>=60 then 1 else 0);
select sum(case when cid="01" then score else 0 end)/sum(case when cid="01" then 1 else 0 end) as 语文课平均分,100 * SUM(case when Cid = '01' and score >= 60 then 1 else 0 end)/SUM(case when Cid = '01' then 1 else 0 end) as 语文及格百分数 ,SUM(case when Cid = '02' then score else 0 end)/SUM(case Cid when '02' then 1 else 0 end) as 数学平均分 ,100 * SUM(case when Cid = '02' and score >= 60 then 1 else 0 end)/SUM(case when Cid = '02' then 1 else 0 end) as 数学及格百分数,SUM(case when Cid = '03' then score else 0 end)/SUM(case Cid when '03' then 1 else 0 end) as 英语平均分,100 * SUM(case when Cid = '03' and score >= 60 then 1 else 0 end)/SUM(case when Cid = '03' then 1 else 0 end) as 英语及格百分数 from sc;
21、查询不同老师所教不同课程平均分从高到低显示
--分析:先查不同课程的平均分,但是有限制条件,必须是同一个老师的id,由老师id进行分组,然后平均分排序
select MAX(Z.Tid) as 教师ID,MAX(Z.Tname) as 教师姓名,C.Cid as 课程ID,MAX(C.Cname) as 课程名称,avg(Score) as 平均成绩 from SC as T,Course as C ,Teacher as Z where T.Cid=C.Cid and C.Tid=Z.Tid group by t.Cid order by avg(Score) desc
22、查询如下课程成绩第 3 名到第 6 名的学生成绩单:语文(01),数学(02),英语(03)
--此题未解决:附上一个不能执行的答案语句:
select distinct top 3 , SC.Sid as 学生学号, Student.Sname as 学生姓名 , T1.score as 语文,T2.score as 数学,T3.score as 英语,IFNULL(T1.score,0) + IFNULL(T2.score,0) + IFNULL(T3.score,0) as 总分 from Student,SC left join SC as T1 on SC.Sid = T1.Sid and T1.Cid = '01' left join SC as T2 on SC.Sid= T2.Sid and T2.Cid = '02' left join SC as T3 on SC.Sid = T3.Sid and T3.Cid = '03' where student.Sid=SC.Sid and IFNULL(T1.score,0) + IFNULL(T2.score,0) + IFNULL(T3.score,0) not in
(select distinct TOP 15 with TIES IFNULL(T1.score,0) + IFNULL(T2.score,0) +IFNULL(T3.score,0)
from sc left join sc as T1 on sc.Sid = T1.Sid and T1.Cid = 'k1' left join sc as T2 on sc.Sid = T2.Sid and T2.Cid = 'k2' left join sc as T3 on sc.Sid = T3.Sid and T3.Cid = 'k3' order by IFNULL(T1.score,0) + IFNULL(T2.score,0) + IFNULL(T3.score,0) desc);
23、统计列各科成绩的各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[<60]
--分析:根据case when then else 语句进行条件查询,关联sc表和course表即可,再根据cid,cname分组
select SC.Cid as 课程ID, Cname as 课程名称,SUM(case when score between 85 and 100 then 1 else 0 end) as "[100 - 85]",SUM(case when score between 70 and 85 then 1 else 0 end) as "[85 - 70]",SUM(case when score between 60 and 70 then 1 else 0 end) as "[70 - 60]",SUM(case when score < 60 then 1 else 0 end) as "[<60]" from SC,Course where SC.Cid=Course.Cid group by SC.Cid,Cname;
24、查询学生平均成绩及其名次;
--分析:学号和平均成绩不用说,名次是根据查询两张同样的表进行比对的,如果第一张表中的每一条数据和第二张 表中的数据进行(平均成绩 > T2.平均成绩)条件查询,如果是第一名按条件查询会获得count()函数结果是0, 所以加1代表第一名,依次类推。这种排名是真正的排名,不依靠平均成绩排序;
注:count(distinct)此处有个distinct关键字,如果有并列名次,你想排名是12345就用上,如果想12335就 去掉关键字即可;
select 1+(select COUNT( distinct 平均成绩) from (select SID,avg(score) as 平均成绩 from SC group by SID) as T1 where 平均成绩 > T2.平均成绩) as 名次, SID as 学生学号,平均成绩 from (select SID,avg(score) 平均成绩 from SC group by SID) as T2 order by 平均成绩 desc;
--分析:下面这条语句也能实现排名,不过这个排名是根据平均成绩排序后的结果集,给定义了一个从1自增的列
select @rank:=@rank+1 as 名次 ,SID as 学生学号,平均成绩 from (select SID,avg(score) 平均成绩 from SC group by SID) as T2 ,(select @rank:=0) B order by 平均成绩 desc;
25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
--分析:查询分数表,然后满足a表中的前三名的数据筛选出来,再根据cid和成绩排序即可
select Sid as 学生ID,CID as 课程ID,Score as 分数 from sc a where 3>(select COUNT(*) from sc b where b.CID=a.CID and b.score>a.score) order by a.CID,a.score desc
26、查询每门课程被选修的学生数
--分析:其实就是按每门课分组查询,然后获取重复次数就可以了
select cid,count(cid) from sc group by cid;
27、查询出只选修了一门课程的全部学生的学号和姓名
--分析:关联student和sc表,根据学生sid分组获取每个学生的选课书count(cid),然后满足为1的即可
select student.sid,sname,count(cid) from student,sc where student.sid=sc.sid group by sc.sid having count(cid) =1;
28、查询男生、女生人数
--分析:不用分析.
select COUNT(*) from student where Ssex="男";
select COUNT(Ssex) as 男生人数 from Student group by Ssex having Ssex='女';
29、查询姓“王”的学生名单
--分析:比较简单
select sname from student where sname like '王%';
30、查询同名学生名单,并统计同名人数
--分析:根据学生姓名分组即可,通过count函数来统计人数
select sname ,count(*) from student group by sname having count(*)>1;
31、1991年出生的学生名单(注:Student表中Sage列的类型是datetime)
--分析:需要根据学生的sage字段获取出生年和条件对比即可;附:月.日.时
select * ,year(sage),month(sage),day(sage),time(sage) from student where year(sage) ='1991';
32、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
--分析:根据课程分组,查询平均成绩,然后根据平均成绩升序排序,然后cid降序即可
select cid,avg(score) from sc group by cid order by avg(score),cid desc;
33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩
--分析:与第二题一样
select Sname,SC.SID ,avg(score) from Student,SC where Student.SID=SC.SID group by SC.SID,Sname having avg(score)>85;
34、查询课程名称为“语文”,且分数低于60的学生姓名和分数
--分析:首先确定三张表student,sc,course然后先确定语文条件,再根据关联id再确定分数<60的条件即可
select Sname,score from Student,SC,course where course.Cname="语文" and course.CID = sc.CID and sc.score<60 and student.SID = sc.SID
35、查询所有学生的选课情况;
--分析:首先确定三张表student,sc,course,再根据关联id查询即可
select sc.SID,sname ,sc.cid,cname from student,sc,course where student.SID = sc.SID and sc.CID=course.CID
36、查询每一门课程成绩在70分以上的姓名、课程名称和分数;
--分析:首先确定三张表student,sc,course,再根据关联id和成绩大于等于70查询即可
select sc.SID, sname,cname,score from student,sc ,course where student.SID = sc.SID and sc.CID=course.CID and score>=70
--有问题语句:此处是理解问题为:满足每一课都在70分以上的学生姓名和分数
--分析:所以分析如下,需要额外加上这个学生的每科的课程数是否和满足70分的课程数相等即可。但是语句没执行成功,有待修正,勿参考此句;
select sc.SID, sname,cname,score from student,sc ,course where student.SID = sc.SID and sc.CID=course.CID and score>=70 group by sname having COUNT(sname) = (select COUNT(*) from sc where cid=CID group by sid)
37、查询不及格的课程,并按课程号从大到小排列
--分析:比较简单,直接查询条件即可
select distinct cid from sc where score<60 order by cid desc;
38、查询课程编号为03且课程成绩在80分以上的学生的学号和姓名;
--分析:根据条件查询即可
select stu.sid,sname from student stu,sc where stu.sid=sc.sid and cid="03" and score>=80;
39、求选了课程的学生人数
--分析:先根据student 和sc表根据sid进行分组查询,获取的结果集再取count(*)即可
select COUNT(*) from ( select student.sid from student,sc where student.SID=sc.SID group by student.sid ) a
40、查询选修“张三”老师所授课程的学生中,成绩最高的学生姓名及其成绩
--分析:根据条件查询出张三老师的学生中的最高分,再根据关联条件获得学生id
select Student.Sname,score from Student,SC,Course C,Teacher where Student.SID=SC.SID and SC.CID=C.CID and C.TID=Teacher.TID and Teacher.Tname="张三" and SC.score=(select MAX(score)from SC where CID=C.CID );
41、查询各个课程及相应的选修人数
--分析:根据cid分组,查询重复次数即可
select cid,count(cid) from sc group by cid;
42、查询不同课程成绩相同的学生的学号、课程号、学生成绩
--分析:根据sc表查询,条件为sid相同,score相同,但是cid不相同
select * from sc a,sc b where a.sid =b.sid and a.score = b.score and a.cid !=b.cid
43、查询每门功成绩最好的前两名
--分析:同25题
select Sid as 学生ID,CID as 课程ID,Score as 分数 from sc a where 2>(select COUNT(*) from sc b where b.CID=a.CID and b.score>a.score) order by a.CID,a.score desc
44、统计每门课程的学生选修人数(超过5人的课程才统计,此处6可替换)。要求输出课程号和选修人数,查询结果 按人数降序排列,查询结果按人数降序排列,若人数相同,按课程号升序排列
--分析:同41题
select CID as 课程号,COUNT(*) as 人数 from sc group by CID having COUNT(*)>5 order by COUNT(*) desc,cID
45、检索至少选修两门课程的学生学号
--分析:根据学生号查询,根据sid分组,超过2的既满足条件
select sid from sc group by sid having count(sid)>=2
46、查询全部学生都选修的课程的课程号和课程名
--分析:先根据sc表根据cid分组获取所有的cid的count和student表的count对比,相等则表示全都选修了这门课 程,然后通过course表获取在这个结果集的课程
select CID,Cname from Course where CID in (select cID from sc group by cID having COUNT(cid) = (select COUNT(sid) from student ))
47、查询两门以上不及格课程的同学的学号及其平均成绩
--分析:先查询sc表中按sid分组然后score<60的重复次数大于2的学生id结果集,然后通过sc依靠sid分组查询
select SID,avg(IFNULL(score,0)) 平均成绩 from SC where SID in(select SID from SC where score<60 group by SID having COUNT(*)>2) group by SID;
48、检索“03”课程分数小于60,按分数降序排列的同学学号
--分析:同38题
select SID from SC where CID='03'and score <60 order by score desc;
49、删除“02”同学的“01”课程的成绩
--分析:在sc表中根据sid=02 和 cid=01 进行删除数据
delete from sc where sid=2 and cid = 01;
50、将竖表sc变为横表,随意构造列
--分析:需要将列先列出来,加max函数就是为了确保获取到分数,把为0的剔除掉。看语句吧
select sid,
MAX(case when cid=01 then score else 0 end ) as 语文,
MAX(case cid when 02 then score else 0 end ) as 数学,
MAX(case cid when 03 then score else 0 end ) as 英语,
SUM(score) as 总分,
avg(score) as 平均分
from sc group by sid