oracle查询的前50名同姓学生,Oracle sql语句练习

在网上找的

Oracle sql语句练习

终于磕磕绊

绊的做完了！！！！

--1、查询“c001”课程比“c002”课程成绩高的所有学生的学号； select x.sno from   (select * from sc where cno='c001'   )x,   (select * from sc where cno='c002'   )y where x.score>y.score and x.sno    =y.sno; --2、查询平均成绩大于60 分的同学的学号和平均成绩； select sno,   avg(score) from sc group by sno having avg(score)>60 order by avg(score); --3、查询所有同学的学号、姓名、选课数、总成绩； select s1.sno,   s1.sname,   s2.c,   s2.s from student s1,   (select sno, count(cno) c,sum(score) s from sc group by sno   )s2 where s1.sno=s2.sno; --4、查询姓“刘”的老师的个数； select tname,   count(*) from teacher where tname like('刘%') group by tname; --5、查询没学过“谌燕”老师课的同学的学号、姓名； select s1.sno from sc s1,   (select cno   from course t1,     (select distinct tno from teacher where tname='谌燕'     )t2   where t1.tno!=t2.tno   )s2 where s1.cno=s2.cno; select a.sno,   a.sname from student a where a.sno not in   (select distinct s.sno   from sc s,     (select c.*     from course c ,       (select tno from teacher t where tname='谌燕'       )t     where c.tno=t.tno     ) b   where s.cno = b.cno   ) --6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名； select * from student where sno in   (select DISTINCT sno from sc where cno in ('c001','c002')   ); --7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名； select * from student where sno in   (select DISTINCT s3.sno   from sc s3,     (select distinct s1.cno     from course s1,       (select tno from teacher where tname='谌燕'       )s2     where s1.tno=s2.tno     )s4   where s4.cno=s3.cno   ); select distinct st.* from student st join sc s on st.sno=s.sno join course c on s.cno=c.cno join teacher t on c.tno     =t.tno where t.tname='谌燕' --8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名； select * from student where sno in   ( select distinct a.sno   from     (select * from sc where cno='c002'     )a,     (select * from sc where cno='c001'     )b   where a.score60     THEN 1     else 0   end)/count(*) from sc group by cno --19、查询不同老师所教不同课程平均分从高到低显示 select t.tno,   t.tname,   c.cno,   avg(s.score) from sc s,   teacher t,   course c where c.tno= t.tno and c.cno  = s.cno group by c.cno; --20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] select sc.cno,   c.cname,   sum(   case     when score between 85 and 100     THEN 1     else 0   end ) "[100-85]",   sum(   case     when score between 70 and 85     THEN 1     else 0   end )"[85-70]",   sum(   case     when score between 70 and 60     THEN 1     else 0   end ) "[70-60]",   sum(   case     when score<60     THEN 1     else 0   end )"[<60]" FROM sc,   course c where sc.cno = c.cno group by sc.cno,   c.cname; --21、查询各科成绩前三名的记录:(不考虑成绩并列情况) select * from   (select sno,     cno,     score,     row_number()over (partition BY cno order by score DESC)rn   from sc   ) where rn<4; --22、查询每门课程被选修的学生数 select cno, count(sno)from sc group by cno; --23、查询出只选修了一门课程的全部学生的学号和姓名 select s1.sno,   s1.sname from student s1 join   (select sno,count(cno) from sc group by sno HAVING count(cno)=1   )s2 on s1.sno= s2.sno; --24、查询男生、女生人数 select ssex, count(ssex) from student group by ssex; --25、查询姓“张”的学生名单 select * from student where sname like '张%'; --26、查询同名同姓学生名单，并统计同名人数 select sname,   count(*) from student group by sname having count(*)>1; --27、1990 年出生的学生名单(注：Student 表中Sage 列的类型是number) select * from student where to_char(sysdate,'yyyy')- sage=1990; --28、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列 select cno,   avg(score) from sc group by cno order BY avg(score),   cno desc; --29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩 select st.sno,   st.sname,   ss.* from student st,   (select sno,avg(score)ags from sc group by sno having avg(score)>70   )ss where st.sno=ss.sno --30、查询课程名称为“SSH”，且分数低于60 的学生姓名和分数 select distinct st.sname,   sc.score from student st join course c on c.cname='SSH' join sc ON sc.score<80 and sc.sno = st.sno and c.cno  = sc.cno; --31、查询所有学生的选课情况； select st.sno,   st.sname,   c.cname from student st,   sc,   course c where sc.sno=st.sno and sc.cno  =c.cno; --32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数； select st.sname,   c.cname,   sc.score from student st,   course c,   sc where st.sno=sc.sno and sc.cno  =c.cno and sc.score>70; --33、查询不及格的课程，并按课程号从大到小排列 select c.cno,   c.cname,   sc.score from course c,   sc where sc.score<60 and sc.cno    =c.cno; --34、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名； select st.sno,   st.sname from student st,   sc where sc.score>80 and st.sno    =sc.sno and sc.cno    = 'c001'; --35、求选了课程的学生人数 select count(DISTINCT sno)from sc; --36、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩 select st.sname,   sc.score from course c,   sc,   student st,   teacher t where t.tname='谌燕' and t.tno    = c.tno and c.cno    = sc.cno and st.sno   = sc.sno and sc.score =   ( select max(score) from sc where sc.cno= c.cno   ) --37、查询各个课程及相应的选修人数 select cno ,count(sno) from sc group by cno; --38、查询不同课程成绩相同的学生的学号、课程号、学生成绩 select s1.sno,   s1.cno,   s1.score from sc s1,   sc s2 where s1.score=s2.score and s1.cno!   = s2.cno; --39、查询每门功课成绩最好的前两名 --40、统计每门课程的学生选修人数(超过10 人的课程才统计)。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列 select cno,   count(sno) from sc group by cno having count(sno)>10 order by count(sno)desc,   cno ASC; --41、检索至少选修两门课程的学生学号 select sno ,   count(cno) from sc group by sno having count(cno)>=2; --42、查询全部学生都选修的课程的课程号和课程名 select DISTINCT c.cname,   c.cno from course c,   sc where c.cno=sc.cno; --43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名 select st.sname from student st where sno not in   ( select distinct sno   from sc,     course c,     teacher t   where t.tname= '谌燕'   and c.tno    =t.tno   and sc.cno   = c.cno   ) --44、查询两门以上不及格课程的同学的学号及其平均成绩 select sno,   avg(score) FROM sc s where sno in   ( select sno from sc where sc.score<60 group by sno having count(sno)>1   ) group by sno; --45、检索“c004”课程分数小于60，按分数降序排列的同学学号 select sno,   score from sc where score>60 and cno    ='c002' order by score; --46、删除“s002”同学的“c001”课程的成绩 delete sc where cno='c001' and sno='s002'; select * from sc order by sno;