【SQL实验】高级查询（二）

🕗 发布于 2024-11-07 12:11 数据库 sql 笔记 sqlserver

完整代码在文章末尾【文中可能会有不准确或待完善之处，恳请各位读者不吝批评指正，共同促进学习交流】

二、下载educ20201027.bak，打开SSMS（集成管理器），使用菜单操作：将素材的中的educ20201027.bak数据还原到数据库【本文跳过这个内容】

【如果做过之前的实验的话（专栏中有），就没有必要去还原了，直接用前面建的EDUC，数据是一样的】如果没有数据库，需要去还原一下

问题：

1. 查询成绩大于90分的学生的学号和姓名。（使用连接和子查询两种方法）

连接查询：

SELECT student.sno, student.sname
FROM student
JOIN sc ON student.sno = sc.sno
WHERE sc.grade > 90;

子查询：

SELECT sno, sname
FROM student
WHERE sno IN (
    SELECT sno
    FROM sc
    WHERE grade > 90
);

SQL 子查询是嵌套在其他查询中的查询。通常子查询用于在主查询中对表数据进行进一步的条件筛选

连接查询：直接连接 student 和 sc 表，过滤出符合条件的数据。结果包含 student 表中所有符合条件的记录。
子查询：先在 sc 表中查找符合条件的学号，再在 student 表中基于学号进行筛选，达到相同的结果。

运行结果不同的原因

连接查询的结果可能包含重复记录：

如果在 sc 表中，一个学生有多条成绩记录，且每条记录都满足 grade > 90 的条件，连接查询会返回多条结果。例如，Y71814001（陈靖）有多条成绩都大于 90，就会出现重复
子查询的结果通常是去重的：

子查询会首先筛选出符合条件的学号，且在主查询中只查找这些学号。即使 sc 表中有多个相同的学号，只要它们在子查询中存在，主查询结果中也只会出现一次

如果希望在连接查询中去重，可以使用 DISTINCT：

SELECT DISTINCT student.sno, student.sname
FROM student
JOIN sc ON student.sno = sc.sno
WHERE sc.grade > 90;

2. 查询所有学生的学号、姓名，所选课程的课程号、课程名和成绩信息

SELECT student.sno, student.sname, course.cno, course.cname, sc.grade
FROM student
JOIN sc ON student.sno = sc.sno
JOIN course ON sc.cno = course.cno;

3. 查询每一门课的间接先修课（先修课的先修课），显示信息包括课程号、间接先修课课程号。

SELECT c1.cno AS 课程号, c3.cno AS 间接先修课课程号
FROM course c1
JOIN course c2 ON c1.cpno = c2.cno
JOIN course c3 ON c2.cpno = c3.cno;

通过多次连接同一张课程表，寻找课程的间接先修课程

FROM course c1：从课程表 course 中选取数据，并将其赋予别名 c1

4. 查询“Y71814001”号同学选修，但“Y71814003”号同学未选的课程的课程号及课程名

SELECT course.cno, course.cname
FROM course
JOIN sc AS sc1 ON course.cno = sc1.cno
LEFT JOIN sc AS sc2 ON course.cno = sc2.cno AND sc2.sno = 'Y71814003'
WHERE sc1.sno = 'Y71814001' AND sc2.sno IS NULL;

sc1 表示学生 'Y71814001' 选修的课程

LEFT JOIN 没有匹配的记录时会返回 NULL，可以确保只选择 'Y71814001' 选了但 'Y71814003' 没选的课程。

5. 查询程熙的成绩

SELECT course.cno, course.cname, sc.grade
FROM student
JOIN sc ON student.sno = sc.sno
JOIN course ON sc.cno = course.cno
WHERE student.sname = '程熙';

6. 查询没有参加任何一门考试的学生姓名

SELECT sname
FROM student
WHERE sno NOT IN (
    SELECT sno
    FROM sc
);

7. 找出选修‘02’号课程并且分数大于该课程平均分的学生信息(包括学号、成绩)。

SELECT sc.sno, sc.grade
FROM sc
WHERE sc.cno = '02'
AND sc.grade > (
    SELECT AVG(grade)
    FROM sc
    WHERE cno = '02'
);

8. 计算选修了‘02’号课程的每个学生的学号和平均成绩。

SELECT sno, AVG(grade) AS 平均成绩
FROM sc
WHERE sno IN (
    SELECT sno
    FROM sc
    WHERE cno = '02'
)
GROUP BY sno;

错误写法：先筛选后分组，得到的是每个学生的2号课程成绩，不是平均成绩

SELECT sno, AVG(grade) AS 平均成绩
FROM sc
WHERE cno = '02'
GROUP BY sno;

AVG ：聚合函数，返回选定记录的平均值

9. 查询程熙高于其所有选修课程平均分的那些课程的课程名和成绩。（例如：该同学选修了离散数学，分数为90，而他选修的所有课程平均分为85，则显示）

SELECT course.cname, sc.grade
FROM student
JOIN sc ON student.sno = sc.sno
JOIN course ON sc.cno = course.cno
WHERE student.sname = '程熙'
AND sc.grade > (
    SELECT AVG(grade)
    FROM sc
    --WHERE sno = student.sno
);

10. 查询每个学生的成绩在80分以上的各有多少门，显示姓名和门数。

SELECT student.sname, COUNT(*) AS 成绩在80分以上门数
FROM student
JOIN sc ON student.sno = sc.sno
WHERE sc.grade > 80
GROUP BY student.sname;

在上面代码中定义别名时报错

在 SQL 中定义别名时，有一些限制和要求需要遵守，具体如下：

1. 命名规则

字符集：别名可以使用字母、数字和下划线（_），但不能以数字开头。

长度限制：不同的数据库管理系统（DBMS）对别名的最大长度有不同的限制

2. 关键字和保留字

避免使用关键字：避免将别名命名为 SQL 的关键字（如 SELECT、FROM、WHERE 等），以免引起语法错误或混淆。

3. 唯一性

在同一查询中必须唯一：在同一 SQL 查询中，别名必须是唯一的，以避免歧义。

4. 使用场景

仅在查询中有效：别名仅在特定的查询中有效，并不会在数据库的其他地方（如表、列）保留。别名的作用是使查询结果更加易读或便于进一步处理。

5. 引用方式

使用空格时的引用：如果别名包含空格或特殊字符，需要用方括号、反引号或双引号将其括起来。

其他方式：

SELECT student.sname, A.num AS "80分以上的门数"  
FROM student  
JOIN (  
    SELECT sno, COUNT(*) AS num  
    FROM sc  
    WHERE sc.grade > 80  
    GROUP BY sc.sno  
) AS A  
ON student.sno = A.sno;




SELECT student.sno, student.sname, COUNT(*)  
FROM student,sc 
where student.sno = sc.sno and grade> 80
group by student.snastudent.sname

完整代码：

--二
-- 1. 查询成绩大于90分的学生的学号和姓名（使用连接和子查询两种方法）
-- 使用连接
--SELECT student.sno, student.sname
SELECT DISTINCT student.sno, student.sname
FROM student
JOIN sc ON student.sno = sc.sno
WHERE sc.grade > 90;

-- 使用子查询
SELECT sno, sname
FROM student
WHERE sno IN (
    SELECT sno
    FROM sc
    WHERE grade > 90
);

-- 2. 查询所有学生的学号、姓名，所选课程的课程号、课程名和成绩信息
SELECT student.sno, student.sname, course.cno, course.cname, sc.grade
FROM student
JOIN sc ON student.sno = sc.sno
JOIN course ON sc.cno = course.cno;

-- 3. 查询每一门课的间接先修课（先修课的先修课），显示信息包括课程号、间接先修课课程号
SELECT c1.cno AS 课程号, c3.cno AS 间接先修课课程号
FROM course c1
JOIN course c2 ON c1.cpno = c2.cno
JOIN course c3 ON c2.cpno = c3.cno;

-- 4. 查询“Y71814001”号同学选修，但“Y71814003”号同学未选的课程的课程号及课程名
SELECT course.cno, course.cname
FROM course
JOIN sc AS sc1 ON course.cno = sc1.cno
LEFT JOIN sc AS sc2 ON course.cno = sc2.cno AND sc2.sno = 'Y71814003'
WHERE sc1.sno = 'Y71814001' AND sc2.sno IS NULL;

-- 5. 查询程熙的成绩
SELECT course.cno, course.cname, sc.grade
FROM student
JOIN sc ON student.sno = sc.sno
JOIN course ON sc.cno = course.cno
WHERE student.sname = '程熙';

-- 6. 查询没有参加任何一门考试的学生姓名
SELECT sname
FROM student
WHERE sno NOT IN (
    SELECT sno
    FROM sc
);

-- 7. 找出选修‘02’号课程并且分数大于该课程平均分的学生信息(包括学号、成绩)
SELECT sc.sno, sc.grade
FROM sc
WHERE sc.cno = '02'
AND sc.grade > (
    SELECT AVG(grade)
    FROM sc
    WHERE cno = '02'
);

-- 8. 计算选修了‘02’号课程的每个学生的学号和平均成绩
SELECT sno, AVG(grade) AS 平均成绩
FROM sc
WHERE sno IN (
    SELECT sno
    FROM sc
    WHERE cno = '02'
)
GROUP BY sno;


-- 9. 查询程熙高于其所有选修课程平均分的那些课程的课程名和成绩
SELECT course.cname, sc.grade
FROM student
JOIN sc ON student.sno = sc.sno
JOIN course ON sc.cno = course.cno
WHERE student.sname = '程熙'
AND sc.grade > (
    SELECT AVG(grade)
    FROM sc
    WHERE sno = student.sno
);

-- 10. 查询每个学生的成绩在80分以上的各有多少门，显示姓名和门数
SELECT student.sname, COUNT(*) AS 成绩在80分以上门数
FROM student
JOIN sc ON student.sno = sc.sno
WHERE sc.grade > 80
GROUP BY student.sname;

原文地址：https://blog.csdn.net/Z15922342915/article/details/143406709

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

「C/C++」C/C++ 之变量作用域详解
在C++编程中，变量作用域（Variable Scope）是一个非常重要的概念，它决定了变量在程序中的可见性和生命周期。本文将详细介绍C++中的变量作用域，包括全局作用域、局部作用域、块作用域和文件作
阅读更多2024-11-08
Pytorch学习--神经网络--现有网络模型的使用及修改
Pytorch学习--神经网络--现有网络模型的使用及修改
阅读更多2024-11-08
navicat pg库安装mysql fdw 外表扩展
在Windows上手动安装mysql_fdw（MySQL Foreign Data Wrapper）通常涉及一系列步骤，包括下载源码、编译、配置和测试。
阅读更多2024-11-08
笔记整理—linux驱动开发部分（9）framebuffer驱动框架
整理一下学习的内容，个人观点可能存在错误。本章介绍了什么是framebuffer驱动框架，依x210的显示器驱动来介绍相关框架的使用方法，并详细对probe函数进行分析。并介绍了一些相关使用到的技术，
阅读更多2024-11-08
AutoOps 使每个 Elasticsearch 部署都更易于管理
AutoOps for Elasticsearch 通过性能建议、资源利用率和成本洞察、实时问题检测和解决路径显著简化了集群管理。虽然 Elasticsearch 是一款功能强大且可扩展的搜索引擎，可
阅读更多2024-11-08
Elasticsearch可视化工具Elasticvue插件用法
Elasticsearch可视化工具Elasticvue插件用法
阅读更多2024-11-08
爬虫学习6
5. **解析：** JSONP不需要解析，因为它通过JavaScript执行回调函数来传递数据，而JSON需要在客户端使用JSON解析函数（如`JSON.parse()`）来解析成对象。3. **格
阅读更多2024-11-08
EL表达式和JSTL表达式（详解）
3. varStatus 属性：可选属性，用于指定一个变量名，该变量将保存当前迭代的状态信息，包括索引、计数等。它可以用于各种类型的值，包括字符串、集合、数组和对象等。可以是 List、Set、Map
阅读更多2024-11-08
[HarmonyOS]鸿蒙普通搜索（只是静态的待优化中，无接口无法获取数据）
// 创建一个搜索组件，设置占位符和初始值Search({ placeholder: '商品关键字...', value: $$this.keyword }) // 配置搜索图标，包括图标资源和颜色
阅读更多2024-11-08
django Forbidden (403)错误解决方法
在form表单中加{% csrf_token %}django提交请求时，报403错误；
阅读更多2024-11-08

【SQL实验】高级查询（二）

相关文章