SQL模拟考试题

第1题

条件：查询成绩90分以上（包含90分）的学生课程成绩明细

显示：学生学号、学生姓名、课程编号、课程名称、成绩、老师编号、老师姓名

排序：学生学号、课程编号、成绩

说明：老师编号和老师姓名是指该课程对应的老师

分值（5分）

select STUDENT_NO,
       STUDENT_NAME,
       COURSE_NO,
       COURSE_NAME,
       CORE,
       TEACHER_NO,
       COURSE_NAME
  from HAND_STUDENT hs natural
  join HAND_STUDENT_CORE hsc natural
  join HAND_COURSE hc
 where core > 90
 order by STUDENT_NO, COURSE_NO, CORE



运行结果：



 
 


  
  
第2题

 
 

条件：查询老师及其对应的上级老师信息

显示：老师编号、老师姓名、上级老师编号、上级老师姓名

排序：老师编号

说明：如果没有上级老师的，上级老师编号和上级老师姓名放空；上级老师是指直接上级

分值：（5分）

select ht_1.TEACHER_NO,
       ht_1.TEACHER_NAME,
       ht_1.MANAGER_NO,
       ht_2.TEACHER_NAME MANAGER_NAME
  from HAND_TEACHER ht_1
  LEFT JOIN HAND_TEACHER ht_2
    ON ht_1.manager_no = ht_2.TEACHER_NO
 ORDER BY ht_1.manager_no




第3题
条件：查询出生日期最早的男同学和出生日期最晚的女同学

显示：学号、姓名、性别、出生日期

排序：学号

说明：如果出生最早的男同学有多个出生日期相同，则一起查出来，如果出生最晚的女同学有多个出生日期相同，也一起查出来

分值：（10分）

select STUDENT_NO, STUDENT_NAME, STUDENT_GENDER, BIRTH_DATE
  from HAND_STUDENT hs
 where (STUDENT_GENDER, BIRTH_DATE) in
       (select STUDENT_GENDER, min(BIRTH_DATE)
          from HAND_STUDENT hs
         where STUDENT_GENDER = '男'
         group by STUDENT_GENDER)
union
select STUDENT_NO, STUDENT_NAME, STUDENT_GENDER, BIRTH_DATE
  from HAND_STUDENT hs
 where (STUDENT_GENDER, BIRTH_DATE) in
       (select STUDENT_GENDER, max(BIRTH_DATE)
          from HAND_STUDENT hs
         where STUDENT_GENDER = '女'
         group by STUDENT_GENDER)
 order by STUDENT_NO








 
 


  
  
第4题

 
 

条件：查询所有同学课程c001/c002/c003/c004四个课程的成绩

显示：学号、姓名、c001成绩、c002成绩、c003成绩、c004成绩

排序：学号

说明：一个同学只显示一行，一个同学有多个课程成绩，则放在同一行，如果没有该课程则放空，有该课程但是成绩为





with st_core as
 (select hs.student_no, hs.student_name, hsc.course_no, hsc.core
    from HAND_STUDENT hs  LEFT OUTER 
    join HAND_STUDENT_CORE hsc ON hsc.student_no=hs.student_no ),
st_score_dense as
 (select student_no,
         student_name,
         decode(course_no, 'c001', core) "C1",
         decode(course_no, 'c002', core) "C2",
         decode(course_no, 'c003', core) "C3",
         decode(course_no, 'c004', core) "C4"
    from st_core),
st_add_name as(    
select student_no,
       MAX(C1) "C1_CORE",
       MAX(C2) "C2_CORE",
       MAX(C3) "C3_CORE",
       MAX(C4) "C4_CORE"
  from st_score_dense
 group by student_no)
select stn.student_no,hs.student_name,C1_CORE,C2_CORE,C3_CORE,C4_CORE
from st_add_name stn,HAND_STUDENT hs
where stn.student_no=hs.student_no
order by student_no







    
    


     
     
第5题

    
    

条件：查询所有老师和同学的姓名

显示：类别、编号、姓名

排序：姓名

说明：类别里面就显示老师或者学生，姓名显示老师或者学生姓名；编号对老师来说就显示老师编号，对学生来说就显示学生编号

分值：（5分）

with sumperson as
 (select HAND_STUDENT.STUDENT_NO, HAND_STUDENT.STUDENT_NAME
    from HAND_STUDENT
  union
  select HAND_TEACHER.TEACHER_NO, HAND_TEACHER.TEACHER_NAME
    from HAND_TEACHER)
select case
         when student_no like 's%' THEN
          '学生'
         when student_no like 't%' THEN
          '老师'
       end "类别",
       
       STUDENT_NO   "编号",
       STUDENT_NAME "姓名"
  from sumperson
 order BY STUDENT_NAME




第6题
条件：查询课程成绩c001比c002成绩高，并且没有选修c003的同学

显示：学号、姓名、c001成绩c002成绩

排序：按学号倒序

说明：c001比c002成绩高，是指都选修了c001和c002的情况

分值：（5分）

select hs.student_no,hs.student_name,h_1.core "C001_CORE",h_2.core "C002_CORE"
from HAND_STUDENT hs,
     HAND_STUDENT_CORE h_1,
     HAND_STUDENT_CORE h_2,
     HAND_STUDENT_CORE h_3
where hs.student_no=h_1.student_no(+)
      and hs.student_no=h_2.student_no(+)
      and hs.student_no=h_3.student_no(+)
      and h_1.course_no(+)='c001'
      and h_2.course_no(+)='c002'
      and h_3.course_no(+)='c003'
      and h_1.core>h_2.core
      and h_3.core is null
order by hs.student_no desc



第7题
条件：查询每门课程男生女生平均成绩

显示：课程编号、课程名称、男生平均成绩、女生平均成绩

排序：课程编号

说明：如果有课程没有男生或者女生选，则放空，如果同时没有男生也没有女生选课，也要查询出来记录，平均分都放空；平均成绩保留2位小数

分值：（5分）


with add_c as
 (select HSC.STUDENT_NO, hc.course_no, hc.COURSE_NAME, hsc.core
    from HAND_COURSE hc, hand_student_core HSC
   where hc.course_no = HSC.course_no(+)),
A as
 (SELECT HSC.course_no,
         MALE.STUDENT_GENDER   "MALE",
         FEMALE.STUDENT_GENDER "FEMALE",
         HSC.CORE
    FROM add_c HSC, hand_student MALE, hand_student FEMALE
   WHERE MALE.STUDENT_GENDER(+) = '男'
     and HSC.STUDENT_NO = MALE.STUDENT_NO(+)
     AND FEMALE.STUDENT_GENDER(+) = '女'
     and HSC.STUDENT_NO = FEMALE.STUDENT_NO(+)),
--这里有少了两条数据,课程没人选
B as
 (SELECT course_no,
         CASE COUNT(MALE)
           WHEN 0 THEN
            NULL
           ELSE
            SUM(DECODE(MALE, '男', CORE)) / COUNT(MALE)
         END MALE_AVG,
         CASE COUNT(FEMALE)
           WHEN 0 THEN
            NULL
           ELSE
            SUM(DECODE(FEMALE, '女', CORE)) / COUNT(FEMALE)
         END FEMALE_AVG
    FROM A
   GROUP BY course_no)
select hc.course_no,
       hc.course_name,
       ROUND(B.MALE_AVG, 2),
       ROUND(B.FEMALE_AVG, 2)
  from B, HAND_COURSE hc
 where B.course_no = HC.COURSE_NO(+)
 ORDER BY COURSE_NO





    
    


     
     
第8题

    
    

条件：查询各科平均成绩和及格率

显示：课程编号、课程名称、老师编号、老师名称、平均分、及格率

排序：课程编号

说明：及格率的格式例子为85.30%，四舍五入保留2位小数，后面加一个百分号
分值（5分）


with A as
 (select hc.course_no,
         case
           when count(hsc.student_no) > 0 THEN
            sum(hsc.core) / count(hsc.student_no)
         END AVG_CORE,
         case
           when count(hsc.student_no) > 0 THEN
            (count(case
                     when hsc.core > 60 then
                      1
                   end)) / count(hsc.student_no)
         END PASS_RATE
    from HAND_COURSE hc, hand_student_core hsc
   where hc.course_no = hsc.course_no(+)
   GROUP BY hc.course_no)
select A.course_no,
       HC.course_name,
       HC.TEACHER_NO,
       HT.TEACHER_NAME,
       ROUND(AVG_CORE, 2) AVG_SCORE,
       TO_CHAR(PASS_RATE, 'fm99.99') || '%' PASS_RATE
  FROM A, HAND_TEACHER HT, HAND_COURSE HC
 where ht.teacher_no = HC.TEACHER_NO
   AND HC.COURSE_NO = A.course_no
 order by course_no




    
    


     
     
第9题

    
    

    
    

     
     条件：查找每个课程各个分数段的人数
     
     


     
     显示：课程编号、课程名称、
     
     [100-90]
     
     人数、
     
     [90-80]
     
     人数、
     
     [80-60]
     
     人数、
     
     [<60]
     
     人数
     
     


     
     排序：课程编号
     
     


     
     说明：
     
     [100-90]
     
     是指大于等
     
     90
     
     ，小于等于
     
     100

      [90-80]
     
     是指大于等于
     
     80
     
     ，小于等于
     
     89

      [80-60]
     
     是指大于等于
     
     60
     
     ，小于等于
     
     79

      [<60]
     
     是指小于
     
     60

      
     
     提示使用
     
     case when
     
     句式
     
     


     
     分值
    
    

    
    

     
     
     
     
SELECT TEMP.*,
       HC.COURSE_NAME
       --TEMP.num_100_90,
       --TEMP.num_90_80,
       --TEMP.num_80_60,
       --TEMP.num_under_60
  FROM hand_course HC,
       (select hc.course_no,
               count(case
                       when core >= 90 and core <= 100 then
                        1
                     end) "num_100_90",
               count(case
                       when core >= 80 and core <= 89 then
                        1
                     end) "num_90_80",
               count(case
                       when core >= 60 and core <= 79 then
                        1
                     end) "num_80_60",
               count(case
                       when core < 60 then
                        1
                     end) "num_under_60"
          from hand_course hc, hand_student_core hsc
         where hc.course_no = hsc.course_no(+)
         GROUP BY hc.course_no) "TEMP"

 WHERE HC.COURSE_NO = TEMP.COURSE_NO
 order by temp.COURSE_NO


     
     
第十题

     
     
条件：查询两门以上不及格课程的同学及平均成绩

显示：学号、姓名、所有课程平均成绩

排序：学号（倒序）

说明：例如学生李四总共有3门课程，其中2门课程不及格，3门成绩分别为40、50、96，所有课程平均成绩为62

     
     
SELECT HS.STUDENT_NO, HS.STUDENT_NAME, TEMP.AVG_CORE
  FROM (select HSC.STUDENT_NO,
               COUNT(CASE
                       WHEN HSC.CORE < 60 THEN
                        1
                     END) "FAILED_COURSE",
               SUM(hsc.core) / COUNT(STUDENT_NO) "AVG_CORE"
          from hand_student_core HSC
         GROUP BY STUDENT_NO) TEMP,
       Hand_Student HS
 WHERE HS.STUDENT_NO = TEMP.STUDENT_NO
   AND FAILED_COURSE >= 2
  order by HS.STUDENT_NO desc

    
    



第十一题



条件：查询教师“胡明星”的所有主管及姓名:（无主管的教师也需要显示）

显示：教师编号、教师名称、主管编号、主管名称

排序：教师编号

说明：例如，教师B的主管是A，A的主管是C，C没有主管，查询B的所有主管的结果为

       A       C

       B       A

       C        

注意是要根据教师编号排序

分值：（5分）

select hs.teacher_no,
       hs.teacher_name,
       hs.manager_no,
       temp.teacher_name manager_name
  from hand_teacher hs, hand_teacher temp
 where hs.manager_no = temp.teacher_no(+)
 start with hs.teacher_name = '胡明星'
connect by prior hs.manager_no = hs.teacher_no
 order by hs.teacher_no;



第十二题
条件：查询所有课程成绩

显示：学号、课程编号、成绩

排序：成绩前三名的按照升序排在最开头，其余数据排序保持默认

说明：

分值：（10分）
 SELECT hs.student_no,hs.course_no,/*hs.rn,*/hs.core

  FROM (SELECT rownum rn,

               hsc.student_no,

               hsc.course_no,

               hsc.core,

               row_number() OVER(ORDER BY hsc.core DESC) ranks

          FROM hand_student_core hsc

          ) hs

 ORDER BY CASE

            WHEN ranks <= 3 THEN

             -ranks

            ELSE

             null

          END,

          rn;