oracle sql语句练习

创建表

CREATE TABLE student 
  ( 
     s#    number,   --学生编号
     sname varchar2(32), --学生姓名
     sage  number, --学生年龄
     ssex  varchar2(8) --学生性别
  ) 
--课程表 
CREATE TABLE course 
  ( 
     c#    number,  --课程编号
     cname varchar2(32), --课程姓名
     t#    number--老师编号
  ) 
--成绩表 
CREATE TABLE sc 
  ( 
     s#    number, --学生编号
     c#    number, --课程编号
     score number--成绩
  ) 
--教师表
CREATE TABLE teacher 
  ( 
     t#    number, --老师编号
     tname varchar2(16) --老师姓名
  ) 

insert into Student select 1,'刘一',18,'男'  from dual union all
 select 2,'钱二',19,'女'  from dual union all
 select 3,'张三',17,'男'  from dual union all
 select 4,'李四',18,'女'  from dual union all
 select 5,'王五',17,'男'  from dual union all
 select 6,'赵六',19,'女' from dual 
 
 insert into Teacher select 1,'叶平'  from dual union all
 select 2,'贺高'  from dual union all
 select 3,'杨艳'  from dual union all
 select 4,'周磊'from dual 
 
 insert into Course select 1,'语文',1  from dual union all
 select 2,'数学',2  from dual union all
 select 3,'英语',3  from dual union all
 select 4,'物理',4 from dual 
 
 insert into SC 
 select 1,1,56  from dual union all 
 select 1,2,78  from dual union all 
 select 1,3,67  from dual union all 
 select 1,4,58  from dual union all 
 select 2,1,79  from dual union all 
 select 2,2,81  from dual union all 
 select 2,3,92  from dual union all 
 select 2,4,68  from dual union all 
 select 3,1,91  from dual union all 
 select 3,2,47  from dual union all 
 select 3,3,88  from dual union all 
 select 3,4,56  from dual union all 
 select 4,2,88  from dual union all 
 select 4,3,90  from dual union all 
 select 4,4,93  from dual union all 
 select 5,1,46  from dual union all 
 select 5,3,78  from dual union all 
 select 5,4,53  from dual union all 
 select 6,1,35  from dual union all 
 select 6,2,68  from dual union all 
 select 6,4,71 from dual 

答案

--1.查询“语文”课程比“数学”课程成绩高的所有学生的学号；
   select * from sc where c#=(select c# from course where cname='语文')
   select * from sc where c#=(select c# from course where cname='数学') 

   select * from sc s,sc s1 where s.s#=s1.s# and s.c#=(select c# from course where cname='语文') and s1.c#=(
     select c# from course where cname='数学'
   ) and s.score>s1.score 

--2.查询平均分大于60的学生的学号和平均分
   select s#,avg(score) from sc group by s# having avg(score)>60
--3.查询所有学生的学号，姓名，选课数，总分
 select s.s#,s.sname,count(*),sum(score) from sc sc inner join student s on sc.s#=s.s# group by s.s#,s.sname
 select s#,(select sname from student where s#=sc.s#),count(*),sum(score) from sc sc group by s# 
 
--4.查询姓叶的老师的个数
   select count(*) from teacher where tname like '%叶%'
--5.查询学过“语文”并且也学过“数学”课程的同学的学号、姓名；
    select * from sc s,sc s1 where s.s#=s1.s# and s.c#=(select c# from course where cname='语文') and s1.c#=(
     select c# from course where cname='数学'
   ) 
--6.查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
 select * from sc s,sc s1 where s.s#=s1.s# and s.c#=1 and s1.c#=2 and s.score>s1.score
--7.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
   select c#,max(score),min(score) from sc group by c#
--8.查询不同老师所教不同课程平均分从高到低显示
   select avg(score) from sc sc inner join course c on sc.c#=c.c#  group by c.c#,c.t#  order by avg(score) desc 
--9.统计列印各科成绩,各分数段人数:
      --课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] 
      
      select c#,(
         select cname from course where c#=s1.c#
      
      ),
      (
         select count(score) from sc where c#=s1.c# and score between 85 and 100
      ) as "[100-85]"
      ,
      (
         select count(score) from sc where c#=s1.c# and score between 70 and 84
      )
      as "[85-70]"
      ,
      (
         select count(score) from sc where c#=s1.c# and score between 60 and 69
      )
      as "[70-60]"
      ,
      (
         select count(score) from sc where c#=s1.c# and score<60
      )
      as "[ <60]" 
       from sc s1 group by c#;
 

--10.查询出只选修了一门课程的全部学生的学号和姓名 
    select s#  from sc group by s# having count(*)=1
    
--11.查询没学过“叶平”老师课的同学的学号、姓名；
    select * from student where s# not in(
     select s# from sc where c# =any(
          select c# from course where t#=(select t# from teacher where tname='叶平')   
     )
     )

--12.查询学过“叶平”老师所教的所有课的同学的学号、姓名；
     select distinct s# from sc where c# =any(
          select c# from course where t#=(select t# from teacher where tname='叶平')   
     )
--13.查询所有课程成绩小于60分的同学的学号、姓名；
     select s#,count(*) from sc s1 group by s# having count(*)=(select count(*) from sc where score<60 and s#=s1.s#  group by s#)
--14.查询没有学全所有课的同学的学号、姓名；
     select s#,count(*) from sc s1 group by s# having count(*)<>(select count(*) from course)
--15.查询至少有一门课与学号为“1”的同学所学相同的同学的学号和姓名；
   select  distinct s.s#,s.sname from sc inner join student s on sc.s#=s.s# where c# in(select c# from sc where s#=1)

--16.查询与学号为“6”同学所学课门数相同的其他同学学号和姓名；
     select sc.s#,sname from sc inner join student s on sc.s#=s.s# group by sc.s#,sname having count(*)=(select count(*) from sc where s#=6)

--17.查询和“1”号的同学学习的课程完全相同的其他同学学号和姓名；
      
      SELECT st.s,sname  FROM student st WHERE st.s IN(SELECT s FROM sc WHERE c IN(SELECT c FROM sc WHERE s=1) GROUP BY s HAVING COUNT(*)=(SELECT COUNT(*) FROM sc WHERE s=1))

--18.按各科平均成绩从低到高和及格率的百分数从高到低顺序
    select c#,avg(score),
    ((select count(*) from sc where c#=s1.c# and score>60 )
    /(select count(*) from sc where c#=s1.c# ))*100||'%'
     from sc s1 group by c# 
     order by avg(score) asc,
     ((select count(*) from sc where c#=s1.c# and score>60 )
    /(select count(*) from sc where c#=s1.c# ))*100||'%' desc
     
  

--19.查询每门功成绩最好的前两名 

   select 
      (select s# from sc where score=(select max(score) from sc group by c# having c#=s.c# ) and sc.c#=s.c#)
     
       ,(select s# from sc where score=(select max(score) from sc where score not in(
       
       select max(score) from sc group by c# having c#=s.c#
       
       ) group by c# having c#=s.c# ) and sc.c#=s.c#)
   
        from course s