sql系统学习

https://blog.csdn.net/fengfeng91/article/details/15029173

https://blog.csdn.net/rosy_dawn/article/details/53261412

1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；  
select *  
from sc a, sc b 
where a.sno=b.sno and a.cno='c001' and b.cno='c002' and a.score>b.score; 

2、查询平均成绩大于60 分的同学的学号和平均成绩；  
select st.sname,ss.sco 
from student st,(select sno,avg(score)sco from sc group by sno)ss 
where st.sno=ss.sno and sco>=60; 

3、查询所有同学的学号、姓名、选课数、总成绩；  
select st.sname,ss.* 
from student st,(select sno,count(score)sco,sum(score) from sc group by sno)ss 
where st.sno=ss.sno;

4、查询姓“刘”的老师的个数；  
select count(*) 
from teacher 
where tname like '%刘%';

5、查询没学过“谌燕”老师课的同学的学号、姓名；  
select sc.sno,acno.* 
from sc sc,(select co.cno from course co,
(select tno 
from teacher 
where tname='谌燕')tn 
where co.tno<>tn.tno)acno 
where acno.cno=sc.cno; 

6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；  
select sa.sname,sc.sno from sc sc,(select st.sname,s.* 
from student st,(select sno,cno 
from sc 
where cno in('c001'))s 
where st.sno=s.sno)sa 
where sc.sno=sa.sno and sc.cno='c002';

7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；  
select distinct st.sname 
from student st,sc sc join (select cno from teacher te join course co on(te.tno=co.tno) 
where te.tname='谌燕')aca on(sc.cno=aca.cno) 
where st.sno=sc.sno; 

9、查询所有课程成绩小于60 分的同学的学号、姓名；  
select st.sname,sc.sno 
from student st join sc sc on(st.sno=sc.sno)
where sc.score<60; 

10、查询没有学全所有课的同学的学号、姓名；  
select sname,st.sno,scn.cn 
from student st join (select distinct sno,count(*)cn from sc group by sc.sno)scn on(st.sno=scn.sno) 
where scn.cn=(select distinct count(cno) from course);  

select st.sname,st.sno 
from student st join (select sc.sno sn,count(sc.cno) cou from sc group by sc.sno) scs on st.sno=scs.sn 
where scs.cou <(select count(cno) from course); 

11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；  
select distinct st.sno, sname  
  from student st  
  join sc sc on (st.sno = sc.sno)  
 where sc.cno in (select cno from sc where sno = 's001') and sc.sno<>'s001';  

13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；  
select tc.cno,round(avg(sc.score),2) from (select * from teacher te join course co on(te.tno=co.tno) 
where te.tname='谌燕')tc join sc sc on(tc.cno=sc.cno) 
group by tc.cno;

14.删除学习“谌燕”老师课的SC 表记录；  
delete from sc 
where sc.cno in(select cno from teacher te join course co on(te.tno=co.tno) 
where te.tname='谌燕') 

17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分  
select cno,max(score),min(score) from sc group by cno;

18、按各科 平均成绩 从 低到高 和 及格率的百分数 从高到低顺序  
select count(score) from sc where score>=60 group by cno ;  
select cno,avg(score)acs,savg.cnu,(count(score)-savg.cnu)/count(score)*100 from sc,  
(select count(score)cnu,avg(score)acs2 from sc where score<60 order by acs2 desc)savg   
group by savg.cnu,cno order by acs desc;