t_tudent(sid,sname,sage,ssex,sdept) 学生表
t_course(cid,cname,tid) 课程表
t_score( scid,sid,cid,grade) 成绩表
t_teacher(tid,tname) 教师表

问题：
1、查询“001”课程比“002”课程成绩高的所有学生的学号；
select t1.sid from

(select sid,grade from t_score where cid = '001') t1,

(select sid,grade from t_score where cid = '002') t2

where t1.grade >t2.grade and t1.sid = t2.sid;

select t2.sid,t2.ca,t2.cb(

select t1.sid,sum(t1.ca) ca,sum(t1.cb) cb

from

(

select sid,

CASE WHEN cid = '001' THEN grade ELSE 0 END ca,

CASE WHEN cid = '002' THEN grade ELSE 0 END cb

from t_score

)t1

GROUP BY sid

)t2

where t2.ca > t2.cb
2、查询平均成绩大于60分的同学的学号和平均成绩；
select sid,avg(grade ) from t_score

group by sid

having avg(grade ) >60;
3、查询所有同学的学号、姓名、选课数、总成绩；
select t1.sid,t1.sname,count(t2.cid),sum(grade ) from t_student t1

left join t_score t2

on t1.sid = t2sid

group by t1sid,t1.sname;
4、查询姓“李”的老师的个数；
select count(distinct(tname)) from t_teacher where tname like '李%';
5、查询没学过“叶平”老师课的同学的学号、姓名；
select sid, sname from t_student

where sid not in

(

select distinct(t1.sid) from t_score t1,t_course t2,t_teacher t3

where t1.cid = t2.cid and t3.tid = t2.tid and t3.tname = '叶平'

);
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
select t0.sid,t0.sname from t_student t0, t_score t1

where t0.sid = t1.sid and t1.cid = '001'

and exists

(

select * from t_score t2 where t2.sid = t1.sid and t2.cid = '002'

);
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
select sid,sname from t_student

where sid in

(

select sid from t_score t1 ,t_course t2,t_teacher t3

where t1.cid=t2.cid

and t3.tid=t2.tid

and t3.tname = '叶平'

group by sid

having count(t1.cid)=(

select count(cid) from t_course t1,t_teacher t2

where t2.tid=t1.tid and t2.tname = '叶平'

)

);
8、查询“001”课程比“002”课程成绩高的所有同学的学号、姓名；
Select sid,sname from

(

select t1.sid,t1.sname,t2.grade ,

(

select grade from t_score t3

where t3.sid = t1.sid and t3.cid = '002'

) grade2

from t_student t1, t_score t2

where t1sid = t2.sid and t2.cid = '001'

) S_2

where grade 2 < grade ;
9、查询所有课程成绩小于60分的同学的学号、姓名；
select sid,sname from t_student

where sid not in

(

select t1.sid from t_student t1, t_score t2

where t1.sid = t2.sid and t2.grade >60

);
10、查询没有学全所有课的同学的学号、姓名；
select t1.sid,t1.sname from Student t1, t_score t2

where t1.sid = t2.sid

group by t1.sid,t1.sname

having count(t2.cid) < (select count(cid) from t_course);

11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
select sid,sname from t_student t1, t_score t2

where t1.sid = t2.sid

and cid in (select cid from t_score where sid = '1001');
12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；
select distinct t2.sid,t1.sname from t_student t1, t_score t2

where t1.sid = t2.sid

and cid in (select cid from t_score where sid = '001');
13、把“ t_score”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
update t_score t4 set grade =(select avg( t3.grade ) from t_score t3 where t3.cid= t4.cid )

from t_course t1,t_teacher t2

where t1.cid = t4.cid

and t1.tid = t2.tid

and t2.tname = '叶平');
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
select sid from t_score

where cid in (select cid from t_score where sid = '1002')

group by sid

having count(*)=(select count(*) from t_score where sid = '1002');
15、删除学习“叶平”老师课的 t_score表记录；
delect t_score from t_course t1 ,t_teacher t2

Where t1.cid = t2.cid

and t1.tid = t2.tid

and t2.tname = '叶平';
16、向 t_score表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、
号课的平均成绩；
insert t_score

select sid,'002',(Select avg(grade ) from t_score where cid = '002')

from t_student