学生课程成绩详解数据库

select a.sid from (select sid,score from sc where cid='001') a 
join (select sid,socre from sc    where cid='002') b on a.sid = b.sid 
where a.score > b.score;

select sid,avg(score) from sc group by sid having avg(score)>60;

select stu.sid,stu.sname,count(cid),sum(score) from student stu join sc s on stu.sid = s.sid 

select count(tid) from teacher where tname like '李%';

  --叶平老师所带的课程编号 
  select cid from course where tid = (select tid from teacher where tname="叶平"); 
  ---选过叶平老师课程的学生编号(重要) 
  select distinct sid from sc where cid in (select cid from course where tid = (select tid from teacher where tname="叶平")); 
  ---没有选过叶平老师课程的学生 
  select sid,sname from student where sid not in (select distinct sid from sc where cid in (select cid from course where tid = (select tid from teacher where tname="叶平")));

  --学过“001”--- 
  select sid from sc cid ='001'; 
  --学过“002”--- 
  select sid from sc cid ='002'; 
  --学过'001'同时学过'002'(重要 intersect)--- 
  select sid,sname from student where sid in ((select sid from sc cid ='001') intersect (select sid from sc cid ='002')); 
  --- exists

准备数据的 sql 代码： 
create table score(id int primary key auto_increment,namevarchar(20),subject 
varchar(20),score int); 
insert into score values 
(null,'张三','语文',81), 
(null,'张三','数学',75), 
(null,'李四','语文',76), 
(null,'李四','数学',90), 
(null,'王五','语文',81), 
(null,'王五','数学',100), 
(null,'王五 ','英语',90); 
答案: 
select distinct(name) from score where name not in (select distinct(name) from score where score <80) 
或者 
select distinct(name) from score t1 where 80 < all(select score from score where name = t1.name)

select a.name,b.name from team a, team b where a.name < b.name