Student(S#,Sname,Sage,Ssex) 学生表 S#:学号;Name:学生姓名;Age:学生年龄;Sex:学生性别
Course(C#,Cname,T#) 课程表 C#,课程编号;Name:课程名字;T#:教师编号
SC(S#,C#,score) 成绩表 S#:学号;C#,课程编号;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
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 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