学生表 教师表 科目表 成绩表 sql练习

学生表
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' , '女');
教师表
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 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');
成绩表
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);

答案：

课程比" 02 "课程成绩高的学生的信息及课程分数

最终查询的信息是学生的信息 同时要关联课程分数

可以先分别查出01课和02课的成绩

select * from (

select t1.sid,class1,class2

from

(select SId,score as class1 from sc where sc.CId= '01') as t1,

(select Sid,score as class2 from sc where sc.CId= '02') as t2

where t1.sid=t2.sid and t1.class1>t2.class2) r

LEFT JOIN student on student.SId=r.sid;

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

这里只用根据学生的id把成绩分组，对分组中的score求平均值，最后选取结果中avg大于60的即可

必须要给计算得到的avg结果一个字段

用得到学生的信息的时候就可以join

select student.SId,sname,ss from student,(select sid,avg(score) as ss from sc GROUP BY sid HAVING avg(score)>=60) r where student.SId =r.sid;

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

最终要学生编号、学生姓名、选课总数、所有课程的成绩总和，发现学生编号和姓名都在学生表中，选课总数在成绩表中，成绩总和在成绩表中

select s.sid,s.Sname,r.coursenumber,r.scoresum from (

(select student.sid,student.Sname from student) as s

LEFT JOIN

(select sc.SId,sum(sc.score) as scoresum,count(sc.CId) as coursenumber from sc GROUP BY sc.sid) as r

on s.sid=r.sid);

4. 张三"老师讲授的任一门课程的学生姓名

要查询所有张三老师授课的学生id 然后来个反向判断即可

SELECT     * FROM         student

WHERE         student.sid NOT IN (      SELECT

               sc.sid

         FROM

                  sc,

                  course,

                  teacher

         WHERE

                  sc.cid = course.CId

                  AND course.TId = teacher.TId

         AND teacher.Tname = '张三')

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

从sc表中选取score小于60的并group by sid，having count>1 作用是保留包含多行的分组

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

where

  student.SId=sc.SId and sc.score<60 GROUP BY sc.SId HAVING count(*)>1;

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

select * from sc LEFT JOIN

  (select sid,avg(score) as avgscore from sc GROUP BY sid) r

         on sc.sid=r.sid ORDER BY avgscore desc;

7. 查询出只选修两门课程的学生学号和姓名

先在成绩表中查询出来每个学生共有2个成绩的学生的id

select student.SId,student.Sname from student

where student.sid in (SELECT sc.sid from sc GROUP BY sid HAVING count(sc.cid)=2);

8. 嵌套查询列出同名的全部学生的信息

select * from student where sname in (select sname from student GROUP BY sname HAVING count(*)>1)

9. 查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

select sc.CId,course.Cname,avg(sc.score) as avgscore from sc,course

where sc.CId=course.CId GROUP BY sc.CId ORDER BY avgscore desc,cid asc;

10. 找到选修张三老师的所有的学生信息，包含成绩信息，并对成绩进行排序，降序，保留第一条即可

select student.*,sc.score,sc.CId,teacher.Tname from student,sc,course,teacher

where teacher.TId=course.TId

and sc.sid=student.SId

and sc.cid=course.CId

and teacher.Tname='张三'

ORDER BY score desc LIMIT 1;

11.  成绩有重复的情况下，查询选修「张三」老师所授课程的学生中，成绩最高的学生信息及其成绩

UPDATE sc set score=90 where  sid='07' and cid = '02'

select student.*,sc.score,sc.CId,teacher.Tname from student,sc,course,teacher

where teacher.TId=course.TId

and sc.sid=student.SId

and sc.cid=course.CId

and teacher.Tname='张三'

and score = (select max(sc.score) from sc,student,teacher,course

where teacher.TId=course.TId

and sc.SId=student.SId

and sc.cid = course.cid

and teacher.Tname='张三');