select *from   student where roe=wnum<=10;
 2.  查询成绩表中的  最低分,平均分,总分
 
  select COUNT(*) from  course  where tno ='t002' ;
 4. 查询所有老师所带 的课程 数量
 
 select * from student where sname like '张%';
 6.查询课程名称为"数据库",且分数低于60 的学生姓名和分数
 where st.sno=sc.sno and sc.cno=c.cno and c.cname='Oracle' and sc.score<60;
7.查询所有学生的选课情况;
 where sc.sno=st.sno and sc.cno=c.cno;
8.查询任何一门课程成绩在70 分以上的姓名.课程名称和分数;
 where sc.sno=st.sno and sc.cno=c.cno and sc.score>70;
9.查询不及格的课程,并按课程号从大到小排列

 where sc.cno=c.cno and sc.score<60 order by sc.cno desc;
10.查询没学过"谌燕"老师讲授的任一门课程的学生姓名
 where st.sno not in
 where sc.cno=c.cno and c.tno=t.tno and t.tname='谌燕');
11.查询两门以上不及格课程的同学的学号及其平均成绩
 where sno in
 group by sno having count(sno)>1
 
 select sno from sc where cno='c004' and score<90 order by score desc;
13.删除"s002"同学的"c001"课程的成绩
 
 select a.* from
 (select * from sc b where b.cno='c002') b
 或者
 where a.cno='c001'
 and a.sno = b.sno)
15.查询平均成绩大于60 分的同学的学号和平均成绩;
 
 select a.*,s.sname from (select sno,sum(score),count(cno) from sc group by sno) a ,student s where a.sno=s.sno;
17.查询姓"刘"的老师的个数;
 
 select a.sno,a.sname from student a
 not in
  from sc s,
        from course c ,
             from teacher t
        where c.tno=t.tno) b
   或者
 (select distinct sno from sc s join course c on s.cno=c.cno
 
 select st.* from sc a
 join student st
 where a.cno='c001' and b.cno='c002' and st.sno=a.sno;
20.查询学过"谌燕"老师所教的所有课的同学的学号:姓名;
 join course c on s.cno=c.cno
 where t.tname='谌燕';
21.查询课程编号"c002"的成绩比课程编号"c001"课程低的所有同学的学号.姓名;
 join sc a on st.sno=a.sno
 where a.cno='c002' and b.cno='c001' and a.score < b.score;
22.查询所有课程成绩小于60 分的同学的学号.姓名;
 join sc s on st.sno=s.sno
 where s.score <60;
23.查询没有学全所有课的同学的学号.姓名;
 left join sc on stu.sno=sc.sno
 having count(sc.cno)<(select count(distinct cno)from course)
 select * from student where sno in
         (select stu.sno,c.cno from student stu
         minus
 );
24.查询至少有一门课与学号为"s001"的同学所学相同的同学的学号和姓名;
 (select distinct a.sno from
 (select * from sc where sc.sno='s001') b
 where st.sno=h.sno and st.sno<>'s001';
25.查询至少学过学号为"s001"同学所有一门课的其他同学学号和姓名;
 left join student st
 where sc.sno<>'s001'
 (select cno from sc
 
 update sc c set score=(select avg(c.score)  from course a,teacher b
                             and b.tname='谌燕'
                             group by c.cno)
 select cno from course a,teacher b
 and b.tname='谌燕');
27.查询和"s001"号的同学学习的课程完全相同的其他同学学号和姓名;
 minus
 select* from sc
 select * from sc where sno='s001'
 
 delete from sc
 (
 left join teacher t on  c.tno=t.tno
 );
29.向SC 表中插入一些记录,这些记录要求符合以下条件:没有上过编号"c002"课程的同学学号."c002"号课的平均成绩;
 select distinct st.sno,sc.cno,(select avg(score)from sc where cno='c002')
 where not exists
 
 select cno ,max(score),min(score) from sc group by cno;
31.按各科平均成绩从低到高和及格率的百分数从高到低顺序
 as 及格率
 order by avg(score) , 及格率
 
 select max(t.tno),max(t.tname),max(c.cno),max(c.cname),c.cno,avg(score) from sc , course c,teacher t
 group by c.cno
 
select sc.cno,c.cname,
 sum(case  when score between 70 and 85 then 1 else 0 end) AS "[85-70]",
 sum(case  when score <60 then 1 else 0 end) AS "[<60]"
 where  sc.cno=c.cno
 
  select * from
 where rn<4;
35.查询每门课程被选修的学生数
 
 select sc.sno,st.sname,count(cno) from student st
 on sc.sno=st.sno
 
 select ssex,count(*)from student group by ssex;

 select sname,count(*)from student group by sname having count(*)>1;
39.1981 年出生的学生名单(注:Student 表中Sage 列的类型是number)
 
  select cno,avg(score) from sc group by cno order by avg(score)asc,cno desc;
41.查询平均成绩大于85 的所有学生的学号.姓名和平均成绩
 left join sc
 group by st.sno,st.sname having avg(score)>85;

 select st.sno,st.sname,sc.score from sc,student st
 
 select count(distinct sno) from sc;
44.查询选修"谌燕"老师所授课程的学生中,成绩最高的学生姓名及其成绩
 where
 and t.tname='谌燕' and sc.score=
 
 select cno,count(sno) from sc group by cno;
46.查询不同课程成绩相同的学生的学号.课程号.学生成绩
 
 select * from (
 )
 
select cno,count(sno) from sc group by cno
 order by count(sno) desc,cno asc;
49.检索至少选修两门课程的学生学号
 
 select distinct(c.cno),c.cname from course c ,sc
或者
 where c.cno in<br style="\&quot;box-sizing:" border-box;\"="">  (select cno from sc group by cno)