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