SQL语句

来源：互联网发布：丰城生活丰城网络问政编辑：程序博客网时间：2024/05/30 05:05

Student(S#,Sname,Sage,Ssex) 学生表 S#：学号；Name：学生姓名；Age：学生年龄；Sex：学生性别
Course(C#,Cname,T#) 课程表 C#,课程编号；Name：课程名字；T#：教师编号
SC(S#,C#,score) 成绩表 S#：学号；C#,课程编号；Score：成绩

Teacher(T#,Tname) 教师表 T#：教师编号； Name：教师名字

1、查询“001”课程比“002”课程成绩高的所有学生的学号；

2、查询没有学全所有课的同学的学号、姓名；
3、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
4、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
5、查询各科成绩前三名的学生

一、select SC1.`S#` from SC SC1 JOIN SC SC2 ON SC1.`S#`=SC2.`S#` WHERE SC1.`C#`='1' AND SC2.`C#`='2' AND SC1.Score > SC2.Score

一、select Student.`S#` from (select `s#`,score from SC where `C#`='1') Student,(select `S#`,score from SC where `C#`='2') Course WHERE Student.`S#`=Course.`S#`

二、select Student.`S#`,Student.`Name` from Student,SC where Student.`S#`=SC.`S#` group by Student.`S#`,Student.`Name` having count(`C#`) < (select count(*) from Course)

三、select `C#` as '课程ID',MAX(Score) as '最高分',MIN(Score) as '最低分' from SC group by `C#`

四、select distinct SC.`S#`,Student.`Name` from SC JOIN Student ON SC.`S#`=Student.`S#` where SC.`S#` not in (select `S#` from sc where `C#` not in (select `C#` from sc where `S#`='2')) AND SC.`S#`<>2 group by SC.`S#` having count(*) = (select count(*) from sc where `S#`='2');

五、SELECT a.`S#` '学生ID',a.`C#` '课程ID',a.Score '分数' FROM sc a WHERE (SELECT COUNT(`C#`) FROM sc WHERE `C#`=a.`C#` AND a.Score<Score)<3 ORDER BY `C#` ASC, Score DESC

0 0