我是靠谱客的博主 醉熏小懒虫,这篇文章主要介绍Oracle的sql题1 查询“c001”课程比“c002”课程成绩高的所有学生,显示(学号、姓名)2(*)1992年之后出生的学生名单找出年龄最大和最小的同学,显示(学号、姓名、年龄)3(*****) 查询姓氏数量最多的学生名单,显示(学号、姓名、人数)4 查询教师“胡明星”的所有主管及姓名:(无主管的教师也需要显示),显示(教师编号、教师名称、主管编号、主管名称)5(*****)查询两门以上不及格课程的同学及平均成绩,显示(学号、姓名、平均成绩(保留两位小数))6 (*****) 查询没有学,现在分享给大家,希望可以做个参考。
这里是自己做的一些sql练习题,之后也会在学习中不断优化解法,欢迎交流和分享。
1 查询“c001”课程比“c002”课程成绩高的所有学生,显示(学号、姓名)
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年之后出生的学生名单找出年龄最大和最小的同学,显示(学号、姓名、年龄)
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
另解:
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(*****) 查询姓氏数量最多的学生名单,显示(学号、姓名、人数)
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 查询教师“胡明星”的所有主管及姓名:(无主管的教师也需要显示),显示(教师编号、教师名称、主管编号、主管名称)
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(*****)查询两门以上不及格课程的同学及平均成绩,显示(学号、姓名、平均成绩(保留两位小数))
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 (*****) 查询没有学全所有课的同学,显示(学号、姓名)
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
另解:
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 (*****)查询选修“谌燕”老师所授课程的学生中每科成绩最高的学生,显示(学号、姓名、课程名称、成绩)
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 (*) 查询没学过“谌燕”老师课的同学,显示(学号、姓名)
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分以下为“不及格”,显示(学号、姓名、课程名称、成绩、等级)
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(*****) 按各科平均成绩和及格率的百分数,按及格率高到低的顺序排序,显示(课程号、平均分、及格率)
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查询各科成绩前三名的记录:(不考虑成绩并列情况),显示(学号、课程号、分数)
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),显示(教师名称、课程名称、选课数量)
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句式)
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”中所有学生成绩的学生课程信息,显示(学号,姓名,课程名称、分数)
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分),显示(学号、成绩)
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;
最后
以上就是醉熏小懒虫最近收集整理的关于Oracle的sql题1 查询“c001”课程比“c002”课程成绩高的所有学生,显示(学号、姓名)2(*)1992年之后出生的学生名单找出年龄最大和最小的同学,显示(学号、姓名、年龄)3(*****) 查询姓氏数量最多的学生名单,显示(学号、姓名、人数)4 查询教师“胡明星”的所有主管及姓名:(无主管的教师也需要显示),显示(教师编号、教师名称、主管编号、主管名称)5(*****)查询两门以上不及格课程的同学及平均成绩,显示(学号、姓名、平均成绩(保留两位小数))6 (*****) 查询没有学的全部内容,更多相关Oracle的sql题1内容请搜索靠谱客的其他文章。
本图文内容来源于网友提供,作为学习参考使用,或来自网络收集整理,版权属于原作者所有。
发表评论 取消回复