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);
- 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;
--分析：与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
--分析:需要查询课程的平均成绩(课程总成绩/课程人数)和及格率(及格人数/课程人数)*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 课程IＤ,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
