有以下几张表及表结构 Student(Sid,Sname,Sage,Ssex) 学生表
Course(Cid,Cname,Tid) 课程表 SC(Sid,Cid,Score) 成绩表 Teacher(Tid,Tname) 教师表
题目： 1.查询“某1”课程比“某2”课程成绩高的所有学生的学号；
select sid
from (select sid,score from sc were cid=‘某课1’) a,(select sid,score from sc were cid=‘某课2’) b
where a.sid=b.sid and a.score>b.score
2.查询平均成绩大于60分的同学的学号和平均成绩；
select sid,avg(score)
from sc
group by sid having avg(score)>60
3.查询所有同学的学号、姓名、选课数、总成绩
select a.sid,a.couresenum,a.totalsocre,b.sname
from
(select sid, count(cid) coursenum,sum(score) totalscore
from sc group by sid) a,student b
where a.sid=b.sid
4.查询姓“李”的老师的个数；
select count(tid) from teacher where tname like ‘李%’
5.查询没学过“叶平”老师课的同学的学号、姓名；
select sid,sname from student
where sid not in
(select sid
from sc
inner join courese on sc.cid=course.cid
inner join teacher on course.tid=teacher.tid
where tname=‘叶平’)
6.查询学过“毛概”并且也学过“马哲”课程的同学的学号、姓名；
select a.sid,student.sname from
(select sid
from sc
inner join course on sc.cid=course.cid
where course.cname=‘毛概’ or course.cname=‘马哲’
group by sid havaing count(sid)>1) a
inner join student on a.sid=student.sid
