Oracle的sql题1 查询“c001”课程比“c002”课程成绩高的所有学生，显示（学号、姓名）2(*)1992年之后出生的学生名单找出年龄最大和最小的同学，显示（学号、姓名、年龄）3(***) 查询姓氏数量最多的学生名单，显示（学号、姓名、人数）4 查询教师“胡明星”的所有主管及姓名:（无主管的教师也需要显示），显示（教师编号、教师名称、主管编号、主管名称）5(*)查询两门以上不及格课程的同学及平均成绩，显示（学号、姓名、平均成绩（保留两位小数））6 (***) 查询没有学

93 阅读 0 评论 62 点赞

我是靠谱客的博主醉熏小懒虫，这篇文章主要介绍Oracle的sql题1 查询“c001”课程比“c002”课程成绩高的所有学生，显示（学号、姓名）2(*)1992年之后出生的学生名单找出年龄最大和最小的同学，显示（学号、姓名、年龄）3(*****) 查询姓氏数量最多的学生名单，显示（学号、姓名、人数）4 查询教师“胡明星”的所有主管及姓名:（无主管的教师也需要显示），显示（教师编号、教师名称、主管编号、主管名称）5(*****)查询两门以上不及格课程的同学及平均成绩，显示（学号、姓名、平均成绩（保留两位小数））6 (*****) 查询没有学，现在分享给大家，希望可以做个参考。

这里是自己做的一些sql练习题，之后也会在学习中不断优化解法，欢迎交流和分享。

1 查询“c001”课程比“c002”课程成绩高的所有学生，显示（学号、姓名）

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT S.STUDENT_NO, S.STUDENT_NAME
  FROM HAND_STUDENT S,
       (
       SELECT HSC.*,
               RANK() OVER(PARTITION BY STUDENT_NO ORDER BY CORE) AS RANK
          FROM HAND_STUDENT_CORE HSC
         WHERE HSC.COURSE_NO = 'c001'
            OR HSC.COURSE_NO = 'c002'
            ) TEMP
 WHERE 1 = 1
 AND S.STUDENT_NO = TEMP.STUDENT_NO
 AND TEMP.COURSE_NO = 'c001'
 AND TEMP.RANK <> 1

2(*)1992年之后出生的学生名单找出年龄最大和最小的同学，显示（学号、姓名、年龄）

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT DISTINCT S.STUDENT_NO, S.STUDENT_NAME, S.STUDENT_AGE
  FROM HAND_STUDENT S,
       (SELECT S.STUDENT_NO,
               S.STUDENT_AGE,
               MAX(S.STUDENT_AGE) OVER() AS MX,
               MIN(S.STUDENT_AGE) OVER() AS MI
          FROM HAND_STUDENT S
         ORDER BY S.STUDENT_AGE) TEMP
 WHERE (S.STUDENT_NO = TEMP.STUDENT_NO AND
       (SELECT Extract(YEAR FROM SYSDATE) - '1992' FROM DUAL) >
       S.STUDENT_AGE)
   AND S.STUDENT_AGE = TEMP.MX
    OR S.STUDENT_AGE = TEMP.MI

另解：

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT 
  S.STUDENT_NO,
  S.STUDENT_NAME,
  S.STUDENT_AGE
  FROM HAND_STUDENT S,
       (SELECT DISTINCT FIRST_VALUE(STUDENT_AGE) OVER(ORDER BY STUDENT_AGE) MI,
                        last_value(student_Age) over(ORDER BY STUDENT_AGE rows between unbounded preceding and unbounded following) MX
          FROM HAND_STUDENT) MM
 WHERE S.STUDENT_AGE = MM.MI
    OR S.STUDENT_AGE = MM.MX
    AND ((SELECT Extract(YEAR FROM SYSDATE) - '1992' FROM DUAL) >
       S.STUDENT_AGE)

3(*****) 查询姓氏数量最多的学生名单，显示（学号、姓名、人数）

复制代码

1
2
3
4
5
6
7
8
9
10
SELECT S.STUDENT_NO, S.STUDENT_NAME, TEMP.COUNT
  FROM (SELECT SUBSTR(STUDENT_NAME, 1, 1) SNAME,
               COUNT(1) COUNT,
               RANK() OVER(ORDER BY COUNT(1) DESC) RANK
          FROM HAND_STUDENT
         GROUP BY SUBSTR(STUDENT_NAME, 1, 1)) TEMP,
       HAND_STUDENT S
 WHERE S.STUDENT_NAME LIKE CONCAT(TEMP.SNAME, '%')
   AND RANK = 1;

4 查询教师“胡明星”的所有主管及姓名:（无主管的教师也需要显示），显示（教师编号、教师名称、主管编号、主管名称）

复制代码

1
2
3
4
5
6
SELECT T.*, M.TEACHER_NAME MNG_NAME
  FROM HAND_TEACHER T, HAND_TEACHER M
 WHERE T.MANAGER_NO = M.MANAGER_NO(+)
 START WITH T.TEACHER_NAME = '胡明星'
CONNECT BY T.TEACHER_NO = PRIOR M.MANAGER_NO

5(*****)查询两门以上不及格课程的同学及平均成绩，显示（学号、姓名、平均成绩（保留两位小数））

复制代码

1
2
3
4
5
6
7
8
9
10
SELECT S.STUDENT_NO, S.STUDENT_NAME, ROUND(AVG(SC.CORE), 2) AS AVG_CORE
  FROM HAND_STUDENT S, HAND_STUDENT_CORE SC
 WHERE SC.STUDENT_NO = (SELECT SC.STUDENT_NO
                          FROM HAND_STUDENT_CORE SC
                         WHERE SC.CORE < 60
                         GROUP BY SC.STUDENT_NO
                        HAVING COUNT(SC.STUDENT_NO) > 1)
   AND S.STUDENT_NO = SC.STUDENT_NO
 GROUP BY S.STUDENT_NAME, S.STUDENT_NO

6 (*****) 查询没有学全所有课的同学，显示（学号、姓名）

复制代码

1
2
3
4
5
6
7
8
9
SELECT S.STUDENT_NO, S.STUDENT_NAME
  FROM HAND_STUDENT S
  LEFT JOIN (SELECT SC.STUDENT_NO
               FROM HAND_STUDENT_CORE SC
              GROUP BY SC.STUDENT_NO
             HAVING COUNT(1) < (SELECT COUNT(1) FROM HAND_COURSE)) TARGET
    ON TARGET.STUDENT_NO = S.STUDENT_NO

另解：

复制代码

1
2
3
4
5
6
7
8
9
10
SELECT S.STUDENT_NO, S.STUDENT_NAME
  FROM HAND_STUDENT S
 WHERE 1 = 1
   AND EXISTS (SELECT 1
          FROM HAND_COURSE C
         WHERE C.COURSE_NO NOT IN
               (SELECT HSC.COURSE_NO
                  FROM HAND_STUDENT_CORE HSC
                 WHERE HSC.STUDENT_NO = S.STUDENT_NO))

7 (*****)查询选修“谌燕”老师所授课程的学生中每科成绩最高的学生，显示（学号、姓名、课程名称、成绩）

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT S.STUDENT_NO, S.STUDENT_NAME, C.COURSE_NAME, CRANK.CORE
  FROM HAND_STUDENT S
 INNER JOIN (SELECT HSC.*,
                    RANK() OVER(PARTITION BY COURSE_NO ORDER BY CORE DESC) AS RANK
               FROM HAND_STUDENT_CORE HSC
              ORDER BY HSC.COURSE_NO, HSC.CORE DESC) CRANK
    ON S.STUDENT_NO = CRANK.STUDENT_NO
 INNER JOIN HAND_COURSE C
    ON CRANK.COURSE_NO = C.COURSE_NO
 INNER JOIN HAND_TEACHER T
    ON C.TEACHER_NO = T.TEACHER_NO
 WHERE RANK = 1
   and T.TEACHER_NAME = '谌燕'

8 (*) 查询没学过“谌燕”老师课的同学，显示（学号、姓名）

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT S.STUDENT_NO, S.STUDENT_NAME
  FROM HAND_STUDENT S
 WHERE 1=1 NOT EXISTS (SELECT HSC.STUDENT_NO
          FROM (SELECT C.COURSE_NO
                  FROM HAND_COURSE C
                 WHERE C.TEACHER_NO IN
                       (SELECT TEACHER_NO
                          FROM HAND_TEACHER
                         WHERE TEACHER_NAME = '谌燕')) TEMP
          LEFT JOIN HAND_STUDENT_CORE HSC
            ON TEMP.COURSE_NO = HSC.COURSE_NO
         WHERE S.STUDENT_NO = HSC.STUDENT_NO)

9 查询课程名称为“J2SE”的学生成绩信息，90以上为“优秀”、80-90为“良好”、60-80为“及格”、60分以下为“不及格”，显示（学号、姓名、课程名称、成绩、等级)

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT S.STUDENT_NO, S.STUDENT_NAME, LV.COURSE_NAME, LV.CORE, LV.等级
  FROM (SELECT CASE
                 WHEN HSC.CORE > 90 THEN
                  '优秀'
                 WHEN HSC.CORE > 80 THEN
                  '良好'
                 WHEN HSC.CORE >= 60 THEN
                  '及格'
                 WHEN HSC.CORE < 60 THEN
                  '不及格'
                 ELSE
                  '错误'
               END AS 等级,
               HSC.STUDENT_NO,
               HSC.CORE,
               TEMP.COURSE_NAME
          FROM HAND_STUDENT_CORE HSC,
               (SELECT C.COURSE_NO, C.COURSE_NAME
                  FROM HAND_COURSE C
                 WHERE COURSE_NAME = 'J2SE') TEMP
         WHERE HSC.COURSE_NO = TEMP.COURSE_NO) LV
  LEFT JOIN HAND_STUDENT S
    ON LV.STUDENT_NO = S.STUDENT_NO

10(*****) 按各科平均成绩和及格率的百分数，按及格率高到低的顺序排序，显示（课程号、平均分、及格率）

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT TEMP.COURSE_NO,
       TEMP.AVG_NUM,
       ROUND((NVL(L.PASS_NUM, 0) / TEMP.TOTAL) * 100, 2)
  FROM (SELECT HSC.COURSE_NO,
               ROUND(AVG(HSC.CORE), 2) AVG_NUM,
               COUNT(HSC.COURSE_NO) TOTAL
          FROM HAND_STUDENT_CORE HSC
         GROUP BY HSC.COURSE_NO) TEMP
  LEFT JOIN (SELECT HSC.COURSE_NO, COUNT(HSC.COURSE_NO) PASS_NUM
               FROM HAND_STUDENT_CORE HSC
              WHERE HSC.CORE > 60
              GROUP BY HSC.COURSE_NO) L
    ON TEMP.COURSE_NO = L.COURSE_NO
    ORDER BY 3 DESC

11查询各科成绩前三名的记录:(不考虑成绩并列情况)，显示（学号、课程号、分数）

复制代码

1
2
3
4
5
6
SELECT STUDENT_NO, COURSE_NO, CORE
  FROM (SELECT HSC.*,
               RANK() OVER(PARTITION BY COURSE_NO ORDER BY CORE DESC) RANK
          FROM HAND_STUDENT_CORE HSC)
 WHERE RANK <= 3

12分别根据教师、课程、教师和课程三个条件统计选课的学生数量：（使用rollup),显示（教师名称、课程名称、选课数量）

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT T.TEACHER_NAME, C.COURSE_NAME, TEMP.COUNT
  FROM (SELECT C.TEACHER_NO, C.COURSE_NO, COUNT(1) COUNT
          FROM HAND_STUDENT_CORE HSC
          LEFT JOIN HAND_COURSE C
            ON C.COURSE_NO = HSC.COURSE_NO
          LEFT JOIN HAND_TEACHER T
            ON T.TEACHER_NO = C.TEACHER_NO
         GROUP BY ROLLUP(C.TEACHER_NO, C.COURSE_NO)) TEMP,
       HAND_TEACHER T,
       HAND_COURSE C
 WHERE T.TEACHER_NO = TEMP.TEACHER_NO
   AND C.COURSE_NO = TEMP.COURSE_NO
 ORDER BY T.TEACHER_NO

13(*****)统计列出矩阵类型各分数段人数，横轴为分数段[100-85]、[85-70]、[70-60]、[<60]，纵轴为课程号、课程名称（提示使用case when句式）

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
select 
hsc.course_no,hc.course_name,
sum(case when hsc.core between 85 and 100 then 1 else 0 end)  "[100-85]",
sum(case when hsc.core between 70 and 85 then 1 else 0 end)  "[85-70]",
sum(case when hsc.core between 60 and 70 then 1 else 0 end) "[70-60]",
sum(case when hsc.core <60 then 1 else 0 end)  "[<60]"
from 
hand_student_core hsc,
hand_course hc
where  hc.course_no = hsc.course_no
group by hsc.course_no, hc.course_name

14查询分数高于课程“J2SE”中所有学生成绩的学生课程信息，显示（学号，姓名，课程名称、分数）

复制代码

1
2
3
4
5
6
7
8
9
10
SELECT *
  FROM HAND_STUDENT_CORE HSC
  LEFT JOIN HAND_COURSE HC
    ON HSC.COURSE_NO = HC.COURSE_NO
 WHERE HSC.CORE > ALL (SELECT HSC.CORE
          FROM HAND_STUDENT_CORE HSC
          LEFT JOIN HAND_COURSE HC
            ON HSC.COURSE_NO = HC.COURSE_NO
         WHERE HC.COURSE_NAME = 'J2SE')

15(*****) 查询所有课程成绩前三名的按照升序排在最开头，其余数据排序保持默认（7分）,显示（学号、成绩)

复制代码

1
2
3
4
5
6
7
8
9
SELECT hs.student_no,
        hs.core
   FROM (SELECT rownum rn,
                hsc.student_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;