表如下:
Student(s#,Sname,Sage,Ssex);学生表
Course(c#,Cname,T#);课程表
SC(s#,c#,score);成绩表
Teacher(t#,Tname);老师表
1.查出所有课程001成绩比课程002成绩好的学生的学号
2.查出所有平均成绩大于60的学生的学号,平均成绩;
3.查出所有同学的学号,姓名,选课数,总成绩;
4.查出姓李的老师的个数
5.查出所有未选“叶平”的课的同学的学号,姓名;
6.查出所有同时选了课程001,002的同学的学号,姓名;
7.查出所有选了"叶平"的课的同学的学号,姓名;
8.查出所有课程001的成绩低于课程002成绩的同学的学号,姓名
9.查出所有其每个课程的成绩都小于60的同学的学号,姓名
10.查出未选所有课的学生的学号姓名
思路如下:
1,6,8相关联;5,7正好相反;4相对简单;2,3,9,10;
1.
select s# from SC sc1
inner join SC sc2
on sc1.s#=sc2.s#
where sc1.c#=001 and sc2.c#=002 and sc1.score>sc2.score;
4.
select count(t.t#) from Teacher t where t.Tname like "李%";
6.
select s# from SC sc1
inner join SC sc2
on sc1.s# = sc2.s#
where sc1.c#=001 and sc2.c#=002;
7.
1)查出c#
select c.c# from Course c
inner join Teacher t
on c.t# = t.t#
where t.Tname = "叶平"
inner join SC sc
on c.c# = sc.c#
8.
select s# from SC sc1
inner join SC sc2
on sc1.s#=sc2.s#
where sc1.c#=001 and sc2.c#=002 and sc1.score<sc2.score;