使用PL/SQL软件查询Oracle数据库练习（一）

数据表：

–查询学生的姓名和成绩总和并按总成绩从高到低排序
SELECT T.S_NAME,
SUM(S_SCORE) S
FROM SZY_SHARE.TJ_STUDENTS T
GROUP BY T.S_NAME
ORDER BY S DESC;

–统计所有的学生的姓名，成绩，学生姓名不能重复，每个科目为一列数据
第一种方法：
SELECT T.S_NAME,
SUM(CASE T.S_SUBJECT
WHEN ‘语文’ THEN
T.S_SCORE
ELSE
0
END) AS 语文成绩,
SUM(CASE T.S_SUBJECT
WHEN ‘数学’ THEN
T.S_SCORE
ELSE
0
END) AS 数学成绩,
SUM(CASE T.S_SUBJECT
WHEN ‘英语’ THEN
T.S_SCORE
ELSE
0
END) AS 英语成绩，

   SUM(T.S_SCORE) AS 总成绩

FROM SZY_SHARE.TJ_STUDENTS T
GROUP BY T.S_NAME;

第二种方法（比较简单）：
SELECT T.S_NAME,
SUM(DECODE(T.S_SUBJECT, ‘语文’, T.S_SCORE)) AS 语文成绩,
SUM(DECODE(T.S_SUBJECT, ‘数学’, T.S_SCORE)) AS 数学成绩,
SUM(DECODE(T.S_SUBJECT, ‘英语’, T.S_SCORE)) AS 英语成绩， SUM(T.S_SCORE) AS 总成绩
FROM SZY_SHARE.TJ_STUDENTS T
GROUP BY T.S_NAME
ORDER BY SUM(T.S_SCORE) DESC;

–统计每个学生的平均分数
SELECT T.S_NAME,
ROUND(AVG(T.S_SCORE), 2) AS 平均分
FROM SZY_SHARE.TJ_STUDENTS T
GROUP BY T.S_NAME;

–统计学生平均成绩大于60的学生姓名和平均分数
SELECT T.S_NAME,
ROUND(AVG(T.S_SCORE), 2) AS 平均分
FROM SZY_SHARE.TJ_STUDENTS T
GROUP BY T.S_NAME
HAVING AVG(T.S_SCORE) > 60;

–统计所有科目成绩都大于60的学生姓名，科目，成绩
–第一种方法：not in 只有当子查询中，select 关键字后的字段有not null约束或者有这种暗示时用not in,另外如果主查询中表大，子查询中的表小但是记录多，则应当使用not in，not in的执行顺序是：是在表中一条记录一条记录的查询(查询每条记录）符合要求的就返回结果集，不符合的就继续查询下一条记录，直到把表中的记录查询完。也就是说为了证明找不到，所以只能查询全部记录才能证明。并没有用到索引。
SELECT T.S_NAME,
T.S_SUBJECT,
T.S_SCORE
FROM SZY_SHARE.TJ_STUDENTS T
WHERE T.S_NAME NOT IN (SELECT T.S_NAME
FROM SZY_SHARE.TJ_STUDENTS T
WHERE T.S_SCORE < 60);

–第二种方法：not exists：如果主查询表中记录少，子查询表中记录多，并有索引。not exists的执行顺序是：在表中查询，是根据索引查询的，如果存在就返回true，如果不存在就返回false，不会每条记录都去查询。
之所以要多用not exists，而不用not in，也就是not exists查询的效率远远高与not in查询的效率。
SELECT T1.S_NAME,
T1.S_SUBJECT,
T1.S_SCORE
FROM SZY_SHARE.TJ_STUDENTS T1
WHERE NOT EXISTS (SELECT T2.S_NAME
FROM SZY_SHARE.TJ_STUDENTS T2
WHERE T2.S_SCORE < 60
AND T1.S_NAME = T2.S_NAME);

–统计所有科目成绩都大于60的学生姓名，科目，成绩（学生姓名不能重复，每个科目为一列数据）
SELECT T1.S_NAME AS 学生姓名,
SUM(DECODE(T1.S_SUBJECT, ‘语文’, T1.S_SCORE)) AS 语文成绩,
SUM(DECODE(T1.S_SUBJECT, ‘数学’, T1.S_SCORE)) AS 数学成绩,
SUM(DECODE(T1.S_SUBJECT, ‘英语’, T1.S_SCORE)) AS 英语成绩
FROM SZY_SHARE.TJ_STUDENTS T1
WHERE NOT EXISTS (SELECT T2.S_NAME
FROM SZY_SHARE.TJ_STUDENTS T2
WHERE T2.S_SCORE < 60
AND T1.S_NAME = T2.S_NAME) group by t1.s_name;

–按学生成绩从高到低排序，显示对应的学生姓名及总分
SELECT T.S_NAME,
SUM(T.S_SCORE) S
FROM SZY_SHARE.TJ_STUDENTS T
GROUP BY T.S_NAME
ORDER BY S DESC;

–按科目成绩显示对应的排名信息，显示姓名、科目、成绩、名次字段
SELECT T.S_NAME,
T.S_SUBJECT,
T.S_SCORE,
DENSE_RANK() OVER(PARTITION BY T.S_SUBJECT ORDER BY T.S_SCORE DESC) RK
FROM SZY_SHARE.TJ_STUDENTS T

–按学生成绩总分排序，并显示对应名词（成绩相同为同一名次）
SELECT T.S_NAME,
SUM(T.S_SCORE) S,
DENSE_RANK() OVER(ORDER BY SUM(T.S_SCORE) DESC) RK
FROM SZY_SHARE.TJ_STUDENTS T
GROUP BY T.S_NAME;

–统计每个科目的最高分数，最低分数以及科目
SELECT T.S_SUBJECT,
MAX(T.S_SCORE) AS 最高分数,
MIN(T.S_SCORE) AS 最低分数
FROM SZY_SHARE.TJ_STUDENTS T
GROUP BY T.S_SUBJECT;

–统计每个科目的最高分数，并显示对应的姓名，科目和成绩
SELECT t.s_name,t.s_subject,t.s_score as 科目最高分
FROM (SELECT T.S_SUBJECT S,
MAX(T.S_SCORE) M
FROM SZY_SHARE.TJ_STUDENTS T
GROUP BY T.S_SUBJECT) A,
SZY_SHARE.TJ_STUDENTS T
WHERE A.S = T.S_SUBJECT
AND A.M = T.S_SCORE;