Oracle数据库经典50题（附答案

少报幻想，早点转行

已于 2024-07-19 11:11:00 修改

阅读量201

点赞数 3

文章标签：数据库 oracle java

于 2024-07-12 14:41:43 首次发布

本文链接：https://blog.csdn.net/qq_53144670/article/details/140296690

版权

转载自https://blog.csdn.net/weixin_52847838/article/details/119056629的博文，但是做着做着感觉有些答案和我想的不太一致，于是开了个贴附上自己的答案，本人小白请勿指指点点

建表语句

添加数据

题目及代码

建表语句

-- 学生表
CREATE TABLE STUDENT
(
    ID INT PRIMARY KEY NOT NULL,
    STUDENT_NAME VARCHAR2(80),
    BIRTHDAY DATE,
    SEX VARCHAR2(2)
)
-- 课程表
CREATE TABLE COURSE
(
    ID INT PRIMARY KEY NOT NULL,
    COURSE_NAME VARCHAR2(80),
    TEACHER_ID INT
)
-- 教师表
CREATE TABLE TEACHER
(
    ID INT PRIMARY KEY NOT NULL,
    TEACHER_NAME VARCHAR2(80)
)
-- 成绩表
CREATE TABLE SCORE 
(
    ID INT PRIMARY KEY NOT NULL,
    STUDENT_ID INT,
    COURSE_ID INT,
    SCORE INT
)

添加数据

（在这里补充一下，因为我用的是DBeaver，它不能直接运行多条代码，只要选中ALT+X就行了）

-- 学生信息
INSERT INTO STUDENT VALUES(01 , '赵雷' , to_date('1990-01-01','YYYY-MM-DD') , 'm');
INSERT INTO STUDENT VALUES(02 , '钱电' , to_date('1990-12-21','YYYY-MM-DD') , 'm');
INSERT INTO STUDENT VALUES(03 , '孙⻛' , to_date('1990-12-20','YYYY-MM-DD') , 'm');
INSERT INTO STUDENT VALUES(04 , '李云' , to_date('1990-12-06','YYYY-MM-DD') , 'm');
INSERT INTO STUDENT VALUES(05 , '周梅' , to_date('1991-12-01','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(06 , '吴兰' , to_date('1992-01-01','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(07 , '郑⽵' , to_date('1989-01-01','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(09 , '张三' , to_date('2017-12-20','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(10 , '李四' , to_date('2017-12-25','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(11 , '李四' , to_date('2012-06-06','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(12 , '赵六' , to_date('2013-06-13','YYYY-MM-DD') , 'f');
INSERT INTO STUDENT VALUES(13 , '孙七' , to_date('2014-06-01','YYYY-MM-DD') , 'f');
 
-- 课程信息
INSERT INTO COURSE VALUES(01 , '语⽂' , 02);
INSERT INTO COURSE VALUES(02 , '数学' , 01);
INSERT INTO COURSE VALUES(03 , '英语' , 03);
 
-- 教师信息
INSERT INTO TEACHER VALUES(01 , '张三');
INSERT INTO TEACHER VALUES(02 , '李四');
INSERT INTO TEACHER VALUES(03 , '王五');
 
-- 成绩
INSERT INTO SCORE VALUES(1,1 , 1 , 80);
INSERT INTO SCORE VALUES(2,1 , 2 , 90);
INSERT INTO SCORE VALUES(3,1 , 3 , 99);
INSERT INTO SCORE VALUES(4,2 , 1 , 70);
INSERT INTO SCORE VALUES(5,2 , 2 , 60);
INSERT INTO SCORE VALUES(6,2 , 3 , 80);
INSERT INTO SCORE VALUES(7,3 , 1 , 80);
INSERT INTO SCORE VALUES(8,3 , 2 , 80);
INSERT INTO SCORE VALUES(9,3 , 3 , 80);
INSERT INTO SCORE VALUES(10,4 , 1 , 50);
INSERT INTO SCORE VALUES(11,4 , 2, 30);
INSERT INTO SCORE VALUES(12,4, 3 , 20);
INSERT INTO SCORE VALUES(13,5 , 1, 76);
INSERT INTO SCORE VALUES(14,5, 2, 87);
INSERT INTO SCORE VALUES(15,6 , 1, 31);
INSERT INTO SCORE VALUES(16,6 , 3, 34);
INSERT INTO SCORE VALUES(17,7 , 2 , 89);
INSERT INTO SCORE VALUES(18,7, 3 , 98);

题目及代码

-- 1.查询"数学 "课程比" 语文 "课程成绩高的学生的信息及课程分数
SELECT student.*,m.score,c.score
FROM 
(SELECT * FROM score WHERE course_id=1)m
JOIN (SELECT * FROM SCORE WHERE course_id = 2 )c
ON m.student_id=c.student_id
LEFT JOIN STUDENT ON m.student_id=student.id 
WHERE m.score>c.score

-- 1.2 查询存在" 数学 "课程但可能不存在" 语文 "课程的情况(不存在时显示为 null )
SELECT 
    S1.STUDENT_ID,
    S1.COURSE_ID AS MATH_COURSE,
    S2.COURSE_ID AS CHINESE_COURSE
FROM SCORE S1
LEFT JOIN SCORE S2 ON S1.STUDENT_ID = S2.STUDENT_ID AND S2.COURSE_ID = 1
WHERE S1.COURSE_ID = 2;

-- 1.3 查询不存在" 数学 "课程但存在" 语文 "课程的情况
SELECT DISTINCT student_id 
FROM SCORE 
WHERE COURSE_ID =1
AND STUDENT_ID  NOT IN 
(
	SELECT STUDENT_ID 
	from SCORE 
	WHERE COURSE_ID =2
)

-- 2.查询平均成绩大于等于 60 分的同学的学生编号和学生姓名和平均成绩
SELECT s.id, s.student_name, ROUND(AVG(score), 1) AS s1
FROM STUDENT s 
JOIN score ON s.id = score.STUDENT_ID 
GROUP BY s.id, s.student_name
HAVING AVG(score) >= 60;

-- 3.查询在 成绩 表存在成绩的学生信息
SELECT DISTINCT s.*
FROM STUDENT s
LEFT JOIN score s1 ON s.id = s1.student_id;

-- 4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩(没成绩的显示为 null )
SELECT s.id, s.student_name, COUNT(s1.course_id) AS "选课总数", SUM(s1.score) AS "所有课程的总成绩"
FROM STUDENT s 
LEFT JOIN SCORE s1 ON s.id = s1.STUDENT_ID
GROUP BY s.id, s.student_name
ORDER BY s.id

-- 4.1 查有成绩的学生信息
这个和第三题有啥区别

-- 5.查询「李」姓老师的数量
SELECT count(teacher_name)
from TEACHER 
WHERE TEACHER_NAME LIKE '李%'

-- 6.查询学过「张三」老师授课的同学的信息
SELECT DISTINCT s.*
FROM student s
LEFT JOIN SCORE s1 ON s.id=s1.STUDENT_ID 
JOIN COURSE c ON s1.COURSE_ID = c.ID
JOIN TEACHER t ON c.TEACHER_ID = t.ID
WHERE t.TEACHER_NAME = '张三'

-- 7.查询没有学全所有课程的同学的信息
SELECT *
FROM STUDENT
WHERE ID IN (
    SELECT s.ID
    FROM STUDENT s 
    JOIN SCORE s2 ON s.ID = s2.STUDENT_ID 
    GROUP BY s.ID
    HAVING COUNT(s2.COURSE_ID) < 3
);

-- 8.查询至少有一门课与学号为" 01 "的同学所学相同的同学的信息
SELECT DISTINCT S2.ID, S2.STUDENT_NAME, S2.BIRTHDAY, S2.SEX
FROM SCORE SC1
JOIN SCORE SC2 ON SC1.COURSE_ID = SC2.COURSE_ID AND SC1.STUDENT_ID <> SC2.STUDENT_ID
JOIN STUDENT S1 ON SC1.STUDENT_ID = S1.ID
JOIN STUDENT S2 ON SC2.STUDENT_ID = S2.ID
WHERE S1.ID = 01
ORDER BY id;

-- 9.查询和" 01 "号的同学学习的课程 完全相同的其他同学的信息
SELECT DISTINCT S2.ID, S2.STUDENT_NAME, S2.BIRTHDAY, S2.SEX
FROM (
    SELECT SC1.STUDENT_ID, LISTAGG(SC1.COURSE_ID, ',') WITHIN GROUP (ORDER BY SC1.COURSE_ID) AS COURSE_LIST
    FROM SCORE SC1
    WHERE SC1.STUDENT_ID = 01
    GROUP BY SC1.STUDENT_ID
)  S1_COURSE
JOIN (
    SELECT SC2.STUDENT_ID, LISTAGG(SC2.COURSE_ID, ',') WITHIN GROUP (ORDER BY SC2.COURSE_ID) AS COURSE_LIST
    FROM SCORE SC2
    WHERE SC2.STUDENT_ID <> 01
    GROUP BY SC2.STUDENT_ID
) S2_COURSE ON S1_COURSE.COURSE_LIST = S2_COURSE.COURSE_LIST
JOIN STUDENT S2 ON S2_COURSE.STUDENT_ID = S2.ID;


--换种写法
SELECT DISTINCT S.ID, S.STUDENT_NAME, S.BIRTHDAY, S.SEX
FROM SCORE SC
JOIN STUDENT S ON SC.STUDENT_ID = S.ID
WHERE SC.COURSE_ID IN (
    SELECT COURSE_ID FROM SCORE WHERE STUDENT_ID = 01
)
AND SC.STUDENT_ID <> 01
GROUP BY S.ID, S.STUDENT_NAME, S.BIRTHDAY, S.SEX
HAVING COUNT(DISTINCT SC.COURSE_ID) = (
    SELECT COUNT(DISTINCT COURSE_ID) FROM SCORE WHERE STUDENT_ID = 01
);

先这样，下次再写点嘿嘿