后端Boy转为数据库Boy基础最流行的SQL题，学完可以巩固几乎所有的查询，推荐分享

你去把唐僧师徒除掉

于 2023-10-07 21:55:00 发布

阅读量149

点赞数

分类专栏：数据库文章标签： SQL查询

本文链接：https://blog.csdn.net/weixin_45364220/article/details/133494829

版权

数据库专栏收录该内容

8 篇文章 0 订阅

订阅专栏

数据库是Oracle，SQL也都是Oracle数据库的语法格式

0.准备

1.SQL数据

-- 学生信息
create table HAND_STUDENT
(
    STUDENT_NO       VARCHAR2(10) not null,
    STUDENT_NAME     VARCHAR2(20),
    STUDENT_BIRTHDAY DATE,
    STUDENT_GENDER   VARCHAR2(5)
);
comment on table HAND_STUDENT is '学生信息表';
comment on column HAND_STUDENT.STUDENT_NO is '学号';
comment on column HAND_STUDENT.STUDENT_NAME is '姓名';
comment on column HAND_STUDENT.STUDENT_BIRTHDAY is '年龄';
comment on column HAND_STUDENT.STUDENT_GENDER is '性别';
alter table HAND_STUDENT
    add constraint PK_STUDENT primary key (STUDENT_NO);

-- 教师信息表
create table HAND_TEACHER
(
    TEACHER_NO   VARCHAR2(10) not null,
    TEACHER_NAME VARCHAR2(20),
    MANAGER_NO   VARCHAR2(10)
);
comment on table HAND_TEACHER is '教师信息表';
comment on column HAND_TEACHER.TEACHER_NO is '教师编号';
comment on column HAND_TEACHER.TEACHER_NAME is '教师名称';
comment on column HAND_TEACHER.MANAGER_NO is '上级编号';
alter table HAND_TEACHER
    add constraint PK_TEACHER primary key (TEACHER_NO);

-- 课程信息表
create table HAND_COURSE
(
    COURSE_NO   VARCHAR2(10) not null,
    COURSE_NAME VARCHAR2(20),
    TEACHER_NO  VARCHAR2(20) not null
);
comment on table HAND_COURSE is '课程信息表';
comment on column HAND_COURSE.COURSE_NO is '课程号';
comment on column HAND_COURSE.COURSE_NAME is '课程名称';
comment on column HAND_COURSE.TEACHER_NO is '教师编号';
alter table HAND_COURSE
    add constraint PK_COURSE primary key (COURSE_NO, TEACHER_NO);

-- 成绩信息表
create table HAND_STUDENT_CORE
(
    STUDENT_NO VARCHAR2(10) not null,
    COURSE_NO  VARCHAR2(10) not null,
    CORE       NUMBER(4, 2)
);
comment on table HAND_STUDENT_CORE is '学生成绩表';
comment on column HAND_STUDENT_CORE.STUDENT_NO is '学号';
comment on column HAND_STUDENT_CORE.COURSE_NO is '课程号';
comment on column HAND_STUDENT_CORE.CORE is '分数';
alter table HAND_STUDENT_CORE
    add constraint PK_SC primary key (STUDENT_NO, COURSE_NO);


/*******初始化学生表的数据******/
insert into HAND_STUDENT
values ('01', '赵雷', TO_DATE('1990-01-01', 'yyyy-mm-dd'), '男');
insert into HAND_STUDENT
values ('02', '钱电', TO_DATE('1990-12-21', 'yyyy-mm-dd'), '男');
insert into HAND_STUDENT
values ('03', '孙风', TO_DATE('1990-05-20', 'yyyy-mm-dd'), '男');
insert into HAND_STUDENT
values ('04', '李云', TO_DATE('1990-08-06', 'yyyy-mm-dd'), '男');
insert into HAND_STUDENT
values ('05', '周梅', TO_DATE('1991-12-01', 'yyyy-mm-dd'), '女');
insert into HAND_STUDENT
values ('06', '吴兰', TO_DATE('1992-03-01', 'yyyy-mm-dd'), '女');
insert into HAND_STUDENT
values ('07', '郑竹', TO_DATE('1989-07-01', 'yyyy-mm-dd'), '女');
insert into HAND_STUDENT
values ('08', '王菊', TO_DATE('1990-01-20', 'yyyy-mm-dd'), '女');
commit;
/******************初始化教师表***********************/
insert into HAND_TEACHER
values ('01', '张三', '');
insert into HAND_TEACHER
values ('02', '李四', '01');
insert into HAND_TEACHER
values ('03', '王五', '02');
commit;
/***************初始化课程表****************************/
insert into HAND_COURSE
values ('01', '语文', '02');
insert into HAND_COURSE
values ('02', '数学', '01');
insert into HAND_COURSE
values ('03', '英语', '03');
commit;
/***************初始化成绩表***********************/
insert into HAND_STUDENT_CORE
values ('01', '01', 80);
insert into HAND_STUDENT_CORE
values ('01', '02', 90);
insert into HAND_STUDENT_CORE
values ('01', '03', 99);
insert into HAND_STUDENT_CORE
values ('02', '01', 70);
insert into HAND_STUDENT_CORE
values ('02', '02', 60);
insert into HAND_STUDENT_CORE
values ('02', '03', 80);
insert into HAND_STUDENT_CORE
values ('03', '01', 80);
insert into HAND_STUDENT_CORE
values ('03', '02', 80);
insert into HAND_STUDENT_CORE
values ('03', '03', 80);
insert into HAND_STUDENT_CORE
values ('04', '01', 50);
insert into HAND_STUDENT_CORE
values ('04', '02', 30);
insert into HAND_STUDENT_CORE
values ('04', '03', 20);
insert into HAND_STUDENT_CORE
values ('05', '01', 76);
insert into HAND_STUDENT_CORE
values ('05', '02', 87);
insert into HAND_STUDENT_CORE
values ('06', '01', 31);
insert into HAND_STUDENT_CORE
values ('06', '03', 34);
insert into HAND_STUDENT_CORE
values ('07', '02', 89);
insert into HAND_STUDENT_CORE
values ('07', '03', 98);
commit;

2.数据库ER图

在这里插入图片描述
学生表

课程表
在这里插入图片描述

教师表
在这里插入图片描述

张三没有上级领导，说明他就是最大的领导

成绩表
在这里插入图片描述

08编号的学生没有成绩

3.多表链接回顾

在这里插入图片描述

4.SQL的执行和书写顺序

书写顺序
1. SELECT
2. FROM
3. JOIN
4. ON
5. WHERE
6. GROUP BY
7. UNION
8. HAVING
9. ORDER BY
10. LIMIT
执行顺序
1. FROM
  - 取数据，形成虚表 t1
2. ON
  - 这里的 ON 指的是 JOIN 后面的 ON。把 JOIN 的表把不符合的记录给过滤掉，只保留满足条件的记录，第一次过滤数据形成虚表 t2
3. JOIN
  - 根据 JOIN 的类型对虚表t2做增减
    - LEFT JOIN：把上一步舍弃的左表记录补回来，对右表的数据用 NULL 填充
    - RIGHT JOIN：把上一步舍弃的右表记录补回来，对左表的数据用 NULL 填充
    - FULL JOIN：对左右两表都填充
    - INNER JOIN：不会填充，只会保留共有部分
  - 这一步操作主要针对外连接(OUTER JOIN)，形成的虚表 t3
4. WHERE
  - 对增减过后的说有数据即t3进行过滤，第二次过滤数据形成虚表 t4
5. GROUP BY
  - 经过前几步，我们已经可以获取到符合我们要求的数据明细了，而 GROUP BY 是对数据做聚合操作【如果没有取到正确的结果就做聚合操作，那么最终结果也一定是错误的】形成虚表 t5
6. HAVING
  - 对上一步的t5第三次数据过滤，只不过是 GROUP BY 聚合之后的操作，所以它必然也就是在 GROUP BY 之后执行了，基本上到这里数据已经成型了【取出了满足 WHERE 条件的数据，然后按照 GROUP BY 做聚合，再根据 HAVING 对结果集做过滤，下一步就应该处理展示问题】形成虚表 t6
7. SELECT
  - 这一步把虚表t6的数据中取出我们需要数据表中指定的字段数据，比如年龄age，姓名name和聚合后的分数score等等列形成虚表 t7
8. DISTINCT
  - 对t7进行指定列的去重形成虚表 t8
9. ORDER BY
  - 按照指定列进行排序【它也是必须在已经满足需要的结果机上排序而且是已经选择的(SELECT)列，如果SELECT中没有这一列，那么是无法进行排序的】姓曾虚表 t9
10. LIMIT
  - 针对已经做完各种操作之后的结果集做限制输出，所以它在最后执行，对整个过程而言没有过滤数据

了解以上执行顺序也会为得到两个有用的启发：

优化SQL
- 核心思想是减少 JOIN 关联的数据量
报错 WHERE age>AVG(age)
- 聚合函数是对数据确定的结果集进行操作，而WHERE结果还处于数据过滤，理解上也就是如果强行用聚合函数只能得到部分数据而非完整数据

1.查询"01"课程比"02"课程成绩高的学生的信息及课程分数

--1.外连接
SELECT hs.STUDENT_NAME,
       hsc1.CORE,
       hsc2.CORE
FROM HAND_STUDENT hs,
     HAND_STUDENT_CORE hsc1,
     HAND_STUDENT_CORE hsc2
WHERE hsc1.COURSE_NO = '01'
  AND hsc2.COURSE_NO = '02'
  AND hsc1.CORE > hsc2.CORE
  AND hsc1.STUDENT_NO = hsc2.STUDENT_NO
  AND hsc1.STUDENT_NO = hs.STUDENT_NO

--2.行列转换
SELECT hs.*, t.hsc1, t.hsc2
FROM (SELECT hsc.STUDENT_NO,
             MAX(CASE WHEN hsc.COURSE_NO = '01' THEN hsc.CORE END) hsc1,
             MAX(CASE WHEN hsc.COURSE_NO = '02' THEN hsc.CORE END) hsc2
      FROM HAND_STUDENT_CORE hsc
      GROUP BY hsc.STUDENT_NO) t,
     HAND_STUDENT hs
WHERE t.hsc1 > t.hsc2
  AND t.STUDENT_NO = hs.STUDENT_NO

2.查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

--1.外连接
SELECT hs.STUDENT_NO, hs.STUDENT_NAME, ROUND(AVG(hsc.CORE), 2) avg_score
FROM HAND_STUDENT hs LEFT JOIN
     HAND_STUDENT_CORE hsc ON hsc.STUDENT_NO = hs.STUDENT_NO
GROUP BY hs.STUDENT_NO, hs.STUDENT_NAME
HAVING AVG(hsc.CORE) >= 60
ORDER BY avg_score ASC

--2.子查询
SELECT hsc.STUDENT_NO,
       (SELECT hs.STUDENT_NAME FROM HAND_STUDENT hs WHERE hs.STUDENT_NO = hsc.STUDENT_NO) name,
       ROUND(AVG(hsc.CORE), 2)                                                            avg_score
FROM HAND_STUDENT_CORE hsc
GROUP BY hsc.STUDENT_NO
HAVING AVG(hsc.CORE) >= 60
ORDER BY avg_score ASC

这里外层必须是 hsc.STUDENT_NO 而不是 hs.STUDENT_NO 替代。

3.查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩 – (包括有成绩的和无成绩的)

SELECT hs.STUDENT_NO, hs.STUDENT_NAME, ROUND(AVG(NVL(hsc.CORE, 0)), 2) avg_score
FROM HAND_STUDENT hs
         LEFT JOIN
     HAND_STUDENT_CORE hsc ON hs.STUDENT_NO = hsc.STUDENT_NO
GROUP BY hs.STUDENT_NO, hs.STUDENT_NAME
HAVING AVG(NVL(hsc.CORE, 0)) < 60
ORDER BY avg_score ASC

4.查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT hs.STUDENT_NO, hs.STUDENT_NAME, COUNT(hsc.COURSE_NO) total_courses, SUM(NVL(hsc.CORE, 0)) total_scores
FROM HAND_STUDENT hs
         LEFT JOIN HAND_STUDENT_CORE hsc ON hs.STUDENT_NO = hsc.STUDENT_NO
GROUP BY hs.STUDENT_NO, hs.STUDENT_NAME
ORDER BY total_scores ASC

5.查询"李"姓老师的数量

SELECT COUNT(ht.TEACHER_NO)
FROM HAND_TEACHER ht
WHERE ht.TEACHER_NAME LIKE '李%';

6.查询学过"张三"老师授课的同学的信息

SELECT hs.*
FROM HAND_STUDENT hs,
     HAND_COURSE hc,
     HAND_TEACHER ht,
     HAND_STUDENT_CORE hsc
WHERE ht.TEACHER_NAME = '张三'
  AND ht.TEACHER_NO = hc.TEACHER_NO
  AND hc.COURSE_NO = hsc.COURSE_NO
  AND hsc.STUDENT_NO = hs.STUDENT_NO
ORDER BY hs.STUDENT_NO ASC

7.查询没学过"张三"老师授课的同学的信息

--1.NOT EXISTS
SELECT hs.*
FROM HAND_STUDENT hs
WHERE NOT EXISTS(SELECT 1
                 FROM (SELECT hsc.STUDENT_NO
                       FROM HAND_TEACHER ht,
                            HAND_COURSE hc,
                            HAND_STUDENT_CORE hsc
                       WHERE ht.TEACHER_NAME = '张三'
                         AND ht.TEACHER_NO = hc.TEACHER_NO
                         AND hsc.COURSE_NO = hc.COURSE_NO) t
                 WHERE t.STUDENT_NO = hs.STUDENT_NO)

--2.NOT IN
SELECT ht.*
FROM HAND_STUDENT ht
WHERE ht.STUDENT_NO NOT IN (SELECT hsc.STUDENT_NO
                            FROM HAND_COURSE hc,
                                 HAND_TEACHER ht,
                                 HAND_STUDENT_CORE hsc
                            WHERE ht.TEACHER_NAME = '张三'
                              AND ht.TEACHER_NO = hc.TEACHER_NO
                              AND hc.COURSE_NO = hsc.COURSE_NO)
ORDER BY ht.STUDENT_NO ASC;

这里并不能简单的 ht.TEACHER_NAME='张三'

8.查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

--1.自连接
SELECT hs.*
FROM HAND_STUDENT hs,
     HAND_STUDENT_CORE hsc1,
     HAND_STUDENT_CORE hsc2
WHERE (hsc1.COURSE_NO = '01' AND hsc2.COURSE_NO = '02')
  AND hsc1.STUDENT_NO = hs.STUDENT_NO
  AND hsc2.STUDENT_NO = hs.STUDENT_NO

9.查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

--1.行列转换
SELECT hs.*, t.hsc1, t.hsc2
FROM (SELECT hsc.STUDENT_NO,
             MAX(CASE WHEN hsc.COURSE_NO = '01' THEN hsc.CORE END) hsc1,
             MAX(CASE WHEN hsc.COURSE_NO = '02' THEN hsc.CORE END) hsc2
      FROM HAND_STUDENT_CORE hsc
      GROUP BY hsc.STUDENT_NO) t
         LEFT JOIN
     HAND_STUDENT hs ON t.STUDENT_NO = hs.STUDENT_NO
WHERE t.hsc1 IS NOT NULL
  AND t.hsc2 IS NULL

这里不能简单地 hsc2.COURSE_NO != '02'

10.查询没有学全所有课程的同学的信息

SELECT hs.STUDENT_NO, hs.STUDENT_NAME, hs.STUDENT_BIRTHDAY, hs.STUDENT_GENDER, COUNT(hsc.COURSE_NO) total_courses
FROM HAND_STUDENT hs
         LEFT JOIN HAND_STUDENT_CORE hsc ON hs.STUDENT_NO = hsc.STUDENT_NO
GROUP BY hs.STUDENT_NO, hs.STUDENT_NO, hs.STUDENT_NAME, hs.STUDENT_BIRTHDAY, hs.STUDENT_GENDER
HAVING COUNT(hsc.COURSE_NO) < (SELECT COUNT(hc.COURSE_NO) FROM HAND_COURSE hc)
ORDER BY hs.STUDENT_NO

11.查询至少有一门课与学号为"01"的同学所学相同的同学的信息

SELECT hs.STUDENT_NO, hs.STUDENT_NAME, hs.STUDENT_BIRTHDAY, hs.STUDENT_GENDER
FROM HAND_STUDENT hs
         LEFT JOIN HAND_STUDENT_CORE hsc ON hs.STUDENT_NO = hsc.STUDENT_NO
WHERE hsc.COURSE_NO IN (SELECT hsc.COURSE_NO
                        FROM HAND_STUDENT_CORE hsc
                        WHERE hsc.STUDENT_NO = '01')
GROUP BY hs.STUDENT_NO, hs.STUDENT_NAME, hs.STUDENT_BIRTHDAY, hs.STUDENT_GENDER
ORDER BY hs.STUDENT_NO

12.查询和"01"号的同学学习的课程完全相同的其他同学的信息

13.查询没学过"张三"老师讲授的任一门课程的学生姓名

14.查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT hs.STUDENT_NO, hs.STUDENT_NAME, ROUND(AVG(hsc.CORE), 2) avg_core
FROM HAND_STUDENT hs
         LEFT JOIN
     HAND_STUDENT_CORE hsc ON hs.STUDENT_NO = hsc.STUDENT_NO
GROUP BY hs.STUDENT_NO, hs.STUDENT_NAME
HAVING SUM(CASE WHEN hsc.CORE < 60 THEN 1 ELSE 0 END) >= 2
ORDER BY avg_core

15.检索"01"课程分数小于60，按分数降序排列的学生信息

SELECT hs.*, hsc.core
FROM HAND_STUDENT hs
         LEFT JOIN HAND_STUDENT_CORE hsc ON hs.STUDENT_NO = hsc.STUDENT_NO
WHERE hsc.COURSE_NO = '01'
  AND hsc.CORE < 60
ORDER BY hsc.CORE DESC

16.按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

--1.子查询
SELECT t1.*, t2.avg_core
FROM (SELECT * FROM HAND_STUDENT_CORE) t1,
     (SELECT hsc.STUDENT_NO, ROUND(AVG(hsc.CORE), 2) avg_core FROM HAND_STUDENT_CORE hsc GROUP BY hsc.STUDENT_NO) t2
WHERE t1.STUDENT_NO = t2.STUDENT_NO
ORDER BY t2.avg_core DESC

--开窗函数
SELECT hsc.*, ROUND(AVG(hsc.CORE) OVER (PARTITION BY hsc.STUDENT_NO), 2) avg_core
FROM HAND_STUDENT_CORE hsc
ORDER BY avg_core DESC

17.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率

及格为[60, +∞]
中等为[70, 80)
优良为[80, 90)
优秀为[90, +∞]

SELECT hc.COURSE_NO,
       hc.COURSE_NAME,
       MAX(hsc.CORE),
       MIN(hsc.CORE),
       ROUND(AVG(hsc.CORE), 2),
       (ROUND(sum(CASE WHEN hsc.CORE >= 60 THEN 1 ELSE 0 END)/COUNT(1), 2)*100) || '%' "及格率",
       (ROUND(sum(CASE WHEN hsc.CORE >= 70 AND hsc.CORE < 80 THEN 1 ELSE 0 END)/COUNT(1), 3)*100) || '%' "中等率",
       (ROUND(sum(CASE WHEN hsc.CORE >= 80 AND hsc.CORE < 90 THEN 1 ELSE 0 END)/COUNT(1), 3)*100) || '%' "优良率",
       (ROUND(sum(CASE WHEN hsc.CORE >= 90 THEN 1 ELSE 0 END)/COUNT(1), 3)*100) || '%' "优秀率"
FROM HAND_COURSE hc
         LEFT JOIN
     HAND_STUDENT_CORE hsc ON hc.COURSE_NO = hsc.COURSE_NO,
     (SELECT COUNT(hc.COURSE_NO) FROM HAND_COURSE hc) total_course
GROUP BY hc.COURSE_NO, hc.COURSE_NAME

18.按各科成绩进行排序，并显示排名(实现不完全)

--1.开窗函数
SELECT hsc.COURSE_NO, hsc.CORE, RANK() OVER (PARTITION BY hsc.COURSE_NO ORDER BY hsc.CORE DESC) rank
FROM HAND_STUDENT_CORE hsc

--2.子查询
SELECT hsc.COURSE_NO,
       hsc.CORE,
       (SELECT COUNT(hsc2.CORE)
        FROM HAND_STUDENT_CORE hsc2
        WHERE hsc.COURSE_NO = hsc2.COURSE_NO AND hsc.CORE < hsc2.CORE) + 1
FROM HAND_STUDENT_CORE hsc
ORDER BY hsc.COURSE_NO, hsc.CORE DESC

mysql 8.0以后的版本才加入 rank() 函数

RANK()：排序会出现重复，序号不连续
在这里插入图片描述

DENSE_RANK()：排序会出现重复，序号连续
在这里插入图片描述
ROW_NUMBER()：排序不会出现重复，序号连续

19.查询学生的总成绩并进行排名

SELECT t.*, RANK() OVER (ORDER BY t.sum_core DESC) rank
FROM (SELECT hsc.STUDENT_NO, SUM(hsc.CORE) sum_core
      FROM HAND_STUDENT_CORE hsc
      GROUP BY hsc.STUDENT_NO) t

20.查询不同老师所教不同课程平均分从高到低显示

SELECT ht.TEACHER_NAME, hc.COURSE_NAME, ROUND(AVG(hsc.CORE), 2) avg_core
FROM HAND_TEACHER ht
         LEFT OUTER JOIN
     HAND_COURSE hc ON ht.TEACHER_NO = hc.TEACHER_NO
         LEFT JOIN
     HAND_STUDENT_CORE hsc ON hc.COURSE_NO = hsc.COURSE_NO
GROUP BY ht.TEACHER_NAME, hc.COURSE_NAME
ORDER BY avg_core DESC

21.查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT hs.*, t.COURSE_NO, t.CORE
FROM HAND_STUDENT hs
         LEFT JOIN
     (SELECT hsc.*,
             RANK() OVER (ORDER BY hsc.CORE DESC) rank
      FROM HAND_STUDENT_CORE hsc) t ON hs.STUDENT_NO = t.STUDENT_NO
         JOIN HAND_COURSE hc ON hc.COURSE_NO = t.COURSE_NO
WHERE rank IN (2, 3)

22.统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比

--统计各科成绩各分数段人数：课程编号,课程名称,[100-85],[85-70],[70-60],[0-60]及所占百分比
SELECT COUNT(hsc.STUDENT_NO),
       hc.COURSE_NO,
       hc.COURSE_NAME,
       (ROUND((SUM((CASE WHEN hsc.CORE > 85 AND hsc.CORE <= 100 THEN 1 ELSE 0 END))) / COUNT(1), 3) *
        100) || '%' "[100, 85]",
       (ROUND((SUM((CASE WHEN hsc.CORE > 70 AND hsc.CORE <= 85 THEN 1 ELSE 0 END))) / COUNT(1), 3) *
        100) || '%' "[85, 70]",
       (ROUND((SUM((CASE WHEN hsc.CORE >= 60 AND hsc.CORE <= 70 THEN 1 ELSE 0 END))) / COUNT(1), 3) *
        100) || '%' "[60, 70]",
       (ROUND((SUM((CASE WHEN hsc.CORE > 0 AND hsc.CORE < 60 THEN 1 ELSE 0 END))) / COUNT(1), 3) * 100) ||
       '%'          "[60, 0]"
FROM HAND_STUDENT_CORE hsc
         LEFT JOIN
     HAND_COURSE hc ON hsc.COURSE_NO = hc.COURSE_NO
GROUP BY hc.COURSE_NO, hc.COURSE_NAME

23.查询学生平均成绩及其名次

`
SELECT hs.*, t.avg_score, RANK() OVER (ORDER BY t.avg_score DESC) rank
FROM HAND_STUDENT hs
         LEFT JOIN
     (SELECT hsc.STUDENT_NO,
             ROUND(AVG(hsc.CORE), 2) avg_score
      FROM HAND_STUDENT_CORE hsc
      GROUP BY hsc.STUDENT_NO) t
     ON t.STUDENT_NO = hs.STUDENT_NO

24.查询各科成绩前三名的记录

SELECT t.*
FROM (SELECT hc.COURSE_NAME, hsc.CORE, RANK() OVER (PARTITION BY hc.COURSE_NO ORDER BY hsc.CORE DESC) rank
      FROM HAND_STUDENT_CORE hsc
               LEFT JOIN
           HAND_COURSE hc ON hsc.COURSE_NO = hc.COURSE_NO) t
WHERE t.rank <= 3

25.查询每门课程被选修的学生数

SELECT hc.COURSE_NAME, COUNT(1) number_of_elective_courses
FROM HAND_COURSE hc
         LEFT JOIN
     HAND_STUDENT_CORE hsc ON hc.COURSE_NO = hsc.COURSE_NO
GROUP BY hc.COURSE_NAME

26.查询出只有两门课程的全部学生的学号和姓名

SELECT hs.STUDENT_NO, hs.STUDENT_NAME, COUNT(1) number_of_elective_courses
FROM HAND_STUDENT hs
         LEFT JOIN
     HAND_STUDENT_CORE hsc ON hs.STUDENT_NO = hsc.STUDENT_NO
GROUP BY hs.STUDENT_NO, hs.STUDENT_NAME
HAVING COUNT(1) = 2

27.查询男生、女生人数

--1.分组
SELECT hs.STUDENT_GENDER, COUNT(1)
    FROM HAND_STUDENT hs
GROUP BY hs.STUDENT_GENDER

--2.SUM累加
SELECT SUM((CASE WHEN hs.STUDENT_GENDER = '男' THEN 1 ELSE 0 END)) "male",
       SUM((CASE WHEN hs.STUDENT_GENDER = '女' THEN 1 ELSE 0 END)) "female"
FROM HAND_STUDENT hs;

28.查询名字中含有"风"字的学生信息

SELECT hs.STUDENT_NAME, COUNT(1)
FROM HAND_STUDENT hs
WHERE hs.STUDENT_NAME LIKE '%风%'
GROUP BY hs.STUDENT_NAME

29.查询同名同性学生名单，并统计同名人数

--1.分组
SELECT hs.STUDENT_NAME, hs.STUDENT_GENDER, COUNT(1)
    FROM HAND_STUDENT hs
GROUP BY hs.STUDENT_NAME, hs.STUDENT_GENDER
HAVING COUNT(1) > 1

--2.自连接
SELECT hs1.STUDENT_NO, hs1.STUDENT_NAME, COUNT(1)
FROM HAND_STUDENT hs1,
     HAND_STUDENT hs2
WHERE hs1.STUDENT_NAME = hs2.STUDENT_NAME
  AND hs1.STUDENT_GENDER = hs2.STUDENT_GENDER
GROUP BY hs1.STUDENT_NO, hs1.STUDENT_NAME
HAVING COUNT(1) > 1
ORDER BY hs1.STUDENT_NO

30.查询1990年出生的学生名单

SELECT hs.*
FROM HAND_STUDENT hs
WHERE TO_CHAR(hs.STUDENT_BIRTHDAY, 'yyyy') = '1990'
ORDER BY hs.STUDENT_NO

31.查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列

SELECT hsc.COURSE_NO, ROUND(AVG(hsc.CORE), 2) avg_core
FROM HAND_STUDENT_CORE hsc
GROUP BY hsc.COURSE_NO
ORDER BY avg_core DESC, hsc.COURSE_NO

32.查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT hs.STUDENT_NO, hs.STUDENT_NAME, ROUND(AVG(hsc.CORE), 2) avg_core
FROM HAND_STUDENT hs
         LEFT JOIN
     HAND_STUDENT_CORE hsc ON hs.STUDENT_NO = hsc.STUDENT_NO
GROUP BY hs.STUDENT_NO, hs.STUDENT_NAME
HAVING ROUND(AVG(NVL(hsc.CORE, 2)), 2) > 85
ORDER BY avg_core

33.查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT hs.STUDENT_NAME, hsc.CORE
FROM HAND_STUDENT hs
         LEFT JOIN HAND_STUDENT_CORE hsc ON hs.STUDENT_NO = hsc.STUDENT_NO
         LEFT JOIN HAND_COURSE hc ON hsc.COURSE_NO = hc.COURSE_NO
WHERE hc.COURSE_NAME = '数学'
  AND hsc.CORE < 60

34.查询所有学生的课程及分数情况

SELECT hs.STUDENT_NAME, hc.COURSE_NAME, hsc.CORE
FROM HAND_STUDENT hs
         LEFT JOIN HAND_STUDENT_CORE hsc ON hs.STUDENT_NO = hsc.STUDENT_NO
         LEFT JOIN HAND_COURSE hc ON hsc.COURSE_NO = hc.COURSE_NO

35.查询每门课成绩在70分以上的学生姓名、课程名称和课程分数

SELECT hs.STUDENT_NAME, hc.COURSE_NAME, hsc.CORE
FROM HAND_STUDENT hs
         LEFT JOIN
     HAND_STUDENT_CORE hsc ON hs.STUDENT_NO = hsc.STUDENT_NO
         LEFT JOIN HAND_COURSE hc ON hsc.COURSE_NO = hc.COURSE_NO
WHERE hs.STUDENT_NO IN (SELECT hsc.STUDENT_NO
                        FROM HAND_STUDENT_CORE hsc
                        GROUP BY hsc.STUDENT_NO
                        HAVING MIN(hsc.CORE) > 70)

36.查询不及格的课程

--1.子查询
SELECT hc.COURSE_NAME
FROM HAND_COURSE hc
WHERE hc.COURSE_NO IN
      (SELECT hsc.COURSE_NO FROM HAND_STUDENT_CORE hsc GROUP BY hsc.COURSE_NO HAVING MIN(hsc.CORE) < 60)

--2.外连接
SELECT hc.COURSE_NO, hc.COURSE_NAME
FROM HAND_COURSE hc
         LEFT JOIN HAND_STUDENT_CORE hsc ON hc.COURSE_NO = hsc.COURSE_NO
WHERE hsc.CORE < 60

37.查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

SELECT hs.STUDENT_NO, hs.STUDENT_NAME
FROM HAND_STUDENT hs
         LEFT JOIN
     HAND_STUDENT_CORE hsc ON hs.STUDENT_NO = hsc.STUDENT_NO
WHERE hsc.COURSE_NO = '01'
  AND hsc.CORE >= 80

38.求每门课程的学生人数

SELECT hc.COURSE_NO, hc.COURSE_NAME, COUNT(1) "number_of_elective_course"
FROM HAND_COURSE hc
         LEFT JOIN HAND_STUDENT_CORE hsc ON hc.COURSE_NO = hsc.COURSE_NO
GROUP BY hc.COURSE_NO, hc.COURSE_NAME

39.查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT *
FROM (SELECT t.*,
             RANK() OVER (ORDER BY t.max_core DESC) rank
      FROM (SELECT hs.STUDENT_NO, hs.STUDENT_NAME, MAX(hsc.CORE) max_core
            FROM HAND_STUDENT hs
                     LEFT JOIN HAND_STUDENT_CORE hsc ON hs.STUDENT_NO = hsc.STUDENT_NO
                     LEFT JOIN HAND_COURSE hc ON hsc.COURSE_NO = hc.COURSE_NO
                     LEFT JOIN HAND_TEACHER ht ON hc.TEACHER_NO = ht.TEACHER_NO
            WHERE ht.TEACHER_NAME = '张三'
            GROUP BY hs.STUDENT_NO, hs.STUDENT_NAME) t)
WHERE rank = 1

40.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

SELECT DISTINCT hsc1.*, hsc2.COURSE_NO, hsc2.CORE
FROM HAND_STUDENT_CORE hsc1
         INNER JOIN HAND_STUDENT_CORE hsc2
                    ON (hsc1.COURSE_NO != hsc2.COURSE_NO
                        AND hsc1.CORE = hsc2.CORE)

41.查询每门课绩最好的前两名

--1.RANK()函数
SELECT *
FROM (SELECT hsc.COURSE_NO,
             hsc.CORE,
             RANK() OVER (PARTITION BY hsc.COURSE_NO ORDER BY hsc.CORE DESC) rank
      FROM HAND_STUDENT_CORE hsc) t
WHERE t.rank <= 2

--2.子查询【炫技的写法】
SELECT hsc.COURSE_NO, hsc.CORE
FROM HAND_STUDENT_CORE hsc
WHERE (SELECT COUNT(1) FROM HAND_STUDENT_CORE hsc2 WHERE hsc.COURSE_NO = hsc2.COURSE_NO AND hsc.CORE < hsc2.CORE) + 1 <=
      2

42.统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列

SELECT hsc.COURSE_NO, COUNT(1) "number_of_elective_course"
    FROM HAND_STUDENT_CORE hsc
GROUP BY hsc.COURSE_NO
HAVING COUNT(1) >= 5
ORDER BY "number_of_elective_course" DESC

43.检索至少选修两门课程的学生学号

SELECT hsc.STUDENT_NO, COUNT(1) "number_of_elective_course"
FROM HAND_STUDENT_CORE hsc
GROUP BY hsc.STUDENT_NO
HAVING COUNT(1) >= 2

44.查询选修了全部课程的学生信息

SELECT *
FROM HAND_STUDENT hs
WHERE hs.STUDENT_NO IN (SELECT hsc.STUDENT_NO
                        FROM HAND_STUDENT_CORE hsc
                        GROUP BY hsc.STUDENT_NO
                        HAVING COUNT(1) = (SELECT COUNT(1) FROM HAND_COURSE hc))

45.查询各学生的年龄 – 按照出生日期来算，当前月日 < 出生年月的月日则，年龄减一

--1.Oracle
SELECT hs.*, ROUND(MONTHS_BETWEEN(SYSDATE, hs.STUDENT_BIRTHDAY) / 12, 1) "age"
FROM HAND_STUDENT hs

--2.MySQL
SELECT hs.*, (YEAR(NOW()) - YEAR()) "age"
FROM HAND_STUDENT hs

46.查询本周过生日的学生

SELECT hs.*
FROM HAND_STUDENT hs
WHERE TO_CHAR(hs.STUDENT_BIRTHDAY, 'WW') = TO_CHAR(SYSDATE, 'WW')

47.查询下周过生日的学生

SELECT hs.*
FROM HAND_STUDENT hs
WHERE TO_CHAR(hs.STUDENT_BIRTHDAY, 'WW') = TO_CHAR(SYSDATE, 'WW') + 1;

48.查询本月过生日的学生

SELECT hs.*
FROM HAND_STUDENT hs
WHERE EXTRACT(MONTH FROM hs.STUDENT_BIRTHDAY) = EXTRACT(MONTH FROM SYSDATE)

49.查询下月过生日的学生

SELECT hs.*
FROM HAND_STUDENT hs
WHERE EXTRACT(MONTH FROM hs.STUDENT_BIRTHDAY) = EXTRACT(MONTH FROM (ADD_MONTHS(SYSDATE, 1)))