Oracle经典50题

-- 学生表
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
);



-- 学生信息
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 c.student_name, '数学' || b.score, '语文' || a.score
  from (select * from score t where t.course_id = 1) a,
       (select * from score t where t.course_id = 2) b,
       (select * from student t) c
 where b.score > a.score
   and a.student_id = b.student_id
   and c.id = a.student_id;

SELECT * FROM 
(
SELECT * FROM SCORE WHERE COURSE_ID ='02'

) a,
(
SELECT * FROM SCORE WHERE COURSE_ID ='01'

) b
inner join STUDENT
ON  STUDENT.ID=STUDENT_ID
WHERE a.STUDENT_ID=b.STUDENT_ID
and a.SCORE>b.SCORE;

--last one
select c.id "学生编号",
       c.student_name "学生姓名",
       nvl(a.score, 0) AS "数学成绩",
       nvl(b.score, 0) "语文成绩"
  from (select * from score t where t.course_id = 2) a
  left join (select * from score t where t.course_id = 1) b
    on a.student_id = b.student_id
 inner join student c
    on c.id = a.student_id
 where a.score > nvl(b.score, 0);

--last two
WITH CTE_A AS
 (SELECT STUDENT_ID,
         (CASE
           WHEN COURSE_ID = 1 THEN
            SCORE
         END) CHINESE,
         (CASE
           WHEN COURSE_ID = 2 THEN
            SCORE
         END) MATH,
         (CASE
           WHEN COURSE_ID = 3 THEN
            SCORE
         END) ENGLISH
    FROM SCORE)
SELECT STUDENT_ID,
       SUM(CHINESE) CHINESE,
       SUM(MATH) MATH,
       SUM(ENGLISH) ENGLISH
  FROM CTE_A
 GROUP BY STUDENT_ID
 ORDER BY STUDENT_ID;