90 lines
2.9 KiB
SQL
90 lines
2.9 KiB
SQL
-- 1、嵌套查询（注意使用ANY、ALL和EXISTS）
|
||
-- （1）使用IN运算的简单嵌套查询
|
||
USE 学生管理;
|
||
SELECT * FROM 课程表 WHERE 责任教师 IN ('T001','T002');
|
||
|
||
-- （2）使用NOT IN运算的简单嵌套查询
|
||
SELECT * FROM 课程表 WHERE 责任教师 NOT IN ('T001','T002');
|
||
|
||
-- （3）使用关系运算（如等于）的简单嵌套查询
|
||
SELECT * FROM 课程表 WHERE 学时 = 64;
|
||
|
||
-- （4）使用ANY或SOME的简单嵌套查询
|
||
SELECT * FROM 课程表 WHERE 学时 = ANY(
|
||
SELECT MAX(学时) FROM 课程表
|
||
);
|
||
|
||
-- （5）使用ALL的简单嵌套查询
|
||
SELECT * FROM 教师表 WHERE 工资 > ALL(
|
||
SELECT AVG(工资) FROM 教师表
|
||
);
|
||
|
||
-- （6）使用连接查询院系名称含“计算机”、职称为教授、所负责教程为必修课的教师姓名、职称、课程名称和课程学时等信息
|
||
SELECT 教师表.姓名,教师表.职称,课程表.课程名称,课程表.学时
|
||
FROM 教师表
|
||
JOIN 院系表 ON 院系表.负责人=教师表.姓名
|
||
JOIN 课程表 ON 课程表.责任教师=教师表.教师编号
|
||
WHERE 院系表.名称 LIKE '%计算机%'
|
||
AND 教师表.职称='教授'
|
||
AND 课程表.课程性质='必修课';
|
||
|
||
-- （7）使用EXISTS的嵌套查询
|
||
SELECT * FROM 课程表 WHERE EXISTS(
|
||
SELECT 平均成绩 FROM 学生表 WHERE 平均成绩 > 90
|
||
);
|
||
|
||
-- （8）使用NOT EXISTS的嵌套查询
|
||
SELECT * FROM 课程表 WHERE NOT EXISTS(
|
||
SELECT 平均成绩 FROM 学生表 WHERE 平均成绩 < 60
|
||
);
|
||
|
||
-- 2、汇总和分组查询
|
||
-- （1）使用COUNT统计数目的查询
|
||
SELECT COUNT(*) FROM 学生表;
|
||
|
||
-- （2）使用SUM计算合计的查询
|
||
SELECT SUM(工资) FROM 教师表;
|
||
|
||
-- （3）一次完成求和、计数、计算平均值的查询
|
||
SELECT SUM(工资),COUNT(*),AVG(工资) FROM 教师表;
|
||
|
||
-- （4）查询所有课程的成绩都大于60分的学生的平均分最高的学生信息
|
||
SELECT TOP 1 学生表.*
|
||
FROM 学生表
|
||
JOIN (
|
||
SELECT 学号, AVG(成绩) AS 平均分
|
||
FROM 选课表
|
||
GROUP BY 学号
|
||
HAVING MIN(成绩) > 60
|
||
) AS 成绩表 ON 学生表.学号 = 成绩表.学号
|
||
ORDER BY 成绩表.平均分 DESC;
|
||
|
||
-- （5）查询数据库课程的成绩大于70分的、所有课程平均分最高的学生信息
|
||
SELECT TOP 1 学生表.*
|
||
FROM 学生表
|
||
JOIN (
|
||
SELECT 学号, AVG(成绩) AS 平均分
|
||
FROM 选课表
|
||
WHERE 课程编号 = (
|
||
SELECT 课程编号
|
||
FROM 课程表
|
||
WHERE 课程名称 LIKE '%数据库%'
|
||
) AND 成绩 > 70
|
||
GROUP BY 学号
|
||
) AS 成绩表 ON 学生表.学号 = 成绩表.学号
|
||
ORDER BY 成绩表.平均分 DESC;
|
||
|
||
-- （6）查询每个学生的平均成绩
|
||
SELECT 学号, AVG(成绩) AS 平均成绩
|
||
FROM 选课表
|
||
GROUP BY 学号;
|
||
|
||
-- （7）查询每个学生的所有成绩的最高成绩、最低成绩、平均成绩和所考课程的门数
|
||
SELECT 学号,
|
||
MAX(成绩) AS 最高成绩,
|
||
MIN(成绩) AS 最低成绩,
|
||
AVG(成绩) AS 平均成绩,
|
||
COUNT(课程编号) AS 课程门数
|
||
FROM 选课表
|
||
GROUP BY 学号;
|