student(sid,sname,sage,ssex) 学生表course(cid,cname,tid) 课程表sc(sid,cid,score) 成绩表teacher(tid,Tnam

student(sid,sname,sage,ssex) 学生表

course(cid,cname,tid) 课程表

sc(sid,cid,score) 成绩表

teacher(tid,Tname) 教师表

练习内容：

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

  --方式1

（1）SELECT a.sid FROM (SELECT sid,score FROM SC WHERE cid='001') a,(SELECT sid,score FROM SC WHERE cid='002') b WHERE a.score>b.score AND a.sid=b.sid;

--方式2

select a.sid

from sc a,sc b

where a.sid=b.sidand a.cid=2 and b.cid=3 and a.score>b.score;

 2、  查询平均成绩大于60分的同学的学号和平均成绩；

select sid,avg(score) as avg

from sc

group by sid

having avg>60;

3、  查询所有同学的学号、姓名、选课数、总成绩；

 --方式1 

select s.sid,sname,count(cid),sum(score)

from student s

left join sc

on s.sid=sc.sid

group by s.sid;

--方式2

select s.sid,sname,a.num,a.totals

from student s

left join (select sid,count(cid) as num,

sum(score) as totals from sc group by sid) as a

on s.sid=a.sid;

--方式3 

select s.sid,sname,(select count(cid) from sc where sid=s.sid) as num,

(select sum(score) from sc where sid=s.sid) as totals

from student s;

4、  查询姓“李”的老师的个数；

select count(tname)

from teacher

where tname like '李%';

 

5、查询没学过“叶平”老师课的同学的学号、姓名；

--方式1

select sid,sname

from student

where sid not in

(select sid from sc where cid in

(select cid from course c,teacher t wherec.tid=t.tid and t.tname='叶平')

);

 --方式2

select sid,sname

from student

where sid not in

(select distinct sid from sc,course c,teacher t where sc.cid=c.cid and c.tid=t.tid and t.tname='叶平');

 

6、查询学过“002”并且也学过编号“003”课程的同学的学号、姓名；

 --方式1

select sid,sname

from student

where sid in (

select a.sid

from (select sid from sc where cid='002') a,(select sid from sc where cid='003') b

wherea.sid=b.sid);

 --方式2

select sid,sname

from student

where sid in (select sid from sc where cid='002')

and

sid in (select sid from sc where cid='003');

--方式3

select s.sid,sname

from student s,sc

where s.sid=sc.sid and sc.cid='002' and

s.sid in (select sid from grade where cid='003');

 --方式4

select s.sid, sname

from student s,sc

where s.sid=sc.sid and sc.cid='002' and

exists( select * from sc as sc1 where sc1.sid=sc.sid and s1.cid='003');