数据库系统头歌实验四 SQL的复杂多表查询以及视图2

已于 2022-12-23 16:17:22 修改
阅读量3.1k 收藏 14
点赞数 3
分类专栏： 数据库实验 文章标签： 数据库 sql
于 2022-12-23 16:06:40 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_53227534/article/details/128420565
版权

第1关：从视图V_SPQ找出供应商S1的供应情况

三建工程项目视图V_SPQ由供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)组成。 视图V_SPQ如下图：

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT PNO,QTY
FROM V_SPQ
WHERE SNO='S1'

########## End ##########

第2关：定义查询S2供应商的所有供应明细的视图V_SPJ2

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。 SPJ表如下图：

USE mydata;
#请在此处添加实现代码
########## Begin ##########
CREATE VIEW V_SPJ2
AS 
SELECT * FROM SPJ
WHERE SNO='S2';

########## End ##########
#以下代码禁止删除
SELECT * FROM V_SPJ2;

第3关：定义查询北京的供应商的编号、名称和城市的视图V_BJS

供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成. S表如下图：

USE mydata;
#请在此处添加实现代码
########## Begin ##########
CREATE VIEW V_BJS
AS
SELECT SNO,SNAME,CITY
FROM S
WHERE CITY='北京';
########## End ##########
#以下代码禁止删除
SELECT * FROM V_BJS;

第4关：定义查询各工程名称使用的各种颜色零件的个数的视图V_PJQ

零件表P由零件代码（PNO）、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成； P表如下图：

工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成。 J表如下图：

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。 SPJ表如下图：

USE mydata;
#请在此处添加实现代码
########## Begin ##########
CREATE VIEW V_PJQ AS
SELECT JNAME,COLOR,SUM(QTY) AS SUM_QTY
FROM SPJ,P,J
WHERE SPJ.JNO=J.JNO AND SPJ.PNO=P.PNO
GROUP BY J.JNO,COLOR;

########## End ##########
#以下代码禁止删除
SELECT * FROM V_PJQ ORDER BY V_PJQ.JNAME ASC, V_PJQ.COLOR ASC;

第5关：将视图V_SPQ中供应数量为400的供应商改为'S1'，并观察基本表SPJ的变化

三建工程项目视图V_SPQ由供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)组成并由基本表J,SPJ构建。 视图V_SPQ如下图：

工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成。 J表如下图：

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。 SPJ表如下图：

USE mydata;
#请在此处添加实现代码
########## Begin ##########
UPDATE V_SPQ
SET SNO='S1'
WHERE QTY=400;

########## End ##########
#以下代码禁止删除
SELECT * FROM SPJ;

第6关：建立比赛 1001 的所有解答的视图v_1001，并要求进行修改和插入操作时仍需保证该视图只有比赛1001的解答。

solution为选手提交的题目解答 solution表如下图（仅显示前几条）：

USE `sqlexp-sztuoj`;

#请在此处添加实现代码
########## Begin ##########
CREATE VIEW v_1001 AS 
SELECT* FROM solution
WHERE contest_id='1001' WITH CHECK OPTION;
########## End ##########
#保证下面一行MYSQL语句在16行的位置，以保证结果匹配
UPDATE v_1001 SET v_1001.contest_id = 1002;

第7关：建立2020级做了1003号题的选手视图v_user2020_1003（包括user_id、name、result),注意user_id、name、result构成的记录去重。

users为选手信息表； users表如下图（仅显示前几条）：

solution为选手提交的题目解答 solution表如下图（仅显示前几条）：

USE `sqlexp-sztuoj`;

#请在此处添加实现代码
########## Begin ##########
CREATE VIEW v_user2020_1003 AS
SELECT distinct solution.user_id,name,result
FROM users,solution
WHERE solution.user_id=users.user_id AND solution.user_id LIKE '2020%' AND solution.problem_id=1003;
########## End ##########
#以下代码禁止删除
SELECT * FROM v_user2020_1003;

第8关：建立2020级做了1001号题且result为6的选手视图v_user2020_1001_6,包括user_id、name、result、problem_id,且按user_id升序排序，注意user_id、name、result、problem_id构成的记录去重。

users为选手信息表； users表如下图（仅显示前几条）：

solution为选手提交的题目解答 solution表如下图（仅显示前几条）：

USE `sqlexp-sztuoj`;

#请在此处添加实现代码
########## Begin ##########
CREATE VIEW v_user2020_1001_6 AS
SELECT distinct users.user_id,name,result,problem_id
FROM users,solution
WHERE users.user_id=solution.user_id and users.user_id LIKE '2020%' and problem_id='1001' and solution.result=6
ORDER BY user_id ASC;
########## End ##########
#以下代码禁止删除
SELECT * FROM v_user2020_1001_6;

第9关：将选手的user_id及解答的平均avgmemory定义为一个视图v_users_avgmemory

solution为选手提交的题目解答 solution表如下图（仅显示前几条）：

USE `sqlexp-sztuoj`;

#请在此处添加实现代码
########## Begin ##########
CREATE VIEW v_users_avgmemory(user_id,avgmemory) AS
SELECT user_id,AVG(memory) as 'avgmemory'
FROM solution
GROUP BY user_id;
########## End ##########
SELECT * FROM v_users_avgmemory;

第10关：删除视图v_1001

视图v_1001为比赛 1001 的所有解答。 视图v_1001如下图（仅显示前几条）：

USE `sqlexp-sztuoj`;

#请在此处添加实现代码
########## Begin ##########
DROP VIEW IF EXISTS v_1001;

########## End ##########
#保证下面一行MYSQL语句在9行的位置，以保证结果匹配
SELECT v_1001.* FROM v_1001;

第11关：在视图v_users_avgmemory中查询avgmemory在2000以下的user_id及avgmemory

视图v_users_avgmemory为选手的user_id及解答的平均avgmemory。 视图v_users_avgmemory如下图（仅显示前几条）：

USE `sqlexp-sztuoj`;

#请在此处添加实现代码
########## Begin ##########
SELECT user_id,avgmemory
FROM v_users_avgmemory
WHERE avgmemory<2000;
########## End ##########

第12关：在视图v_1001中删除user_id为201902010318的记录

视图v_1001为比赛 1001 的所有解答。 视图v_1001如下图（仅显示前几条）：

USE `sqlexp-sztuoj`;

#请在此处添加实现代码
########## Begin ##########
DELETE FROM v_1001 WHERE user_id='201902010318';
########## End ##########
#以下代码禁止删除
SELECT * FROM v_1001;

第13关：在视图v_users_avgmemory中插入一条记录(2020100904,1800)，并分析结果。

视图v_users_avgmemory为选手的user_id及解答的平均mavgmemory。 视图v_users_avgmemory如下图（仅显示前几条）：

USE `sqlexp-sztuoj`;

#请在第5行开始添加实现代码，务必保证从第5行开始添加代码，以保证结果匹配
########## Begin ##########
INSERT INTO v_users_avgmemory values(2020100904,1800);
########## End ##########

第14关：在视图v_user2020_1003中将user_id为20200202的result更改为10

视图v_user2020_1003为2020级做了1003号题的选手视图。 视图v_user2020_1003如下图（仅显示前几条）：

USE `sqlexp-sztuoj`;

########## Begin ##########
update v_user2020_1003
set result=10
where user_id=20200202;
########## End ##########
#以下代码禁止删除
SELECT v_user2020_1003.* FROM v_user2020_1003 WHERE v_user2020_1003.user_id = '20200202';

第15关：找出工程项目J1使用的各种零件的名称及其数量(SUM_QTY)，查询结果按数量降序排序。

零件表P由零件代码（PNO）、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成； P表如下图：

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。 SPJ表如下图：

USE mydata;
#请在此处添加实现代码
########## Begin ##########
select distinct PNAME,SUM(QTY) AS SUM_QTY
FROM P,SPJ
WHERE SPJ.PNO=P.PNO AND SPJ.JNO='J1'
GROUP BY PNAME
ORDER BY SUM_QTY DESC;

########## End ##########

第16关：求使用了300个及以上P1零件的工程名称

工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成。 J表如下图：

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。 SPJ表如下图：

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT DISTINCT JNAME
FROM J,SPJ
WHERE J.JNO=SPJ.JNO AND PNO='P1' AND J.JNO IN(
    SELECT JNO 
    FROM SPJ
    GROUP BY PNO,JNO HAVING SUM(QTY)>300
)
########## End ##########

第17关：求各工程（名）使用的各城市供应的零件总数，结果先按工程名降序排序，再按城市名降序排序。

供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成. S表如下图：

工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成。 J表如下图：

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。 SPJ表如下图：

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT JNAME,S.CITY,SUM(QTY) AS SUM_QTY
FROM SPJ,J,S
WHERE SPJ.JNO=J.JNO AND SPJ.SNO=S.SNO 
GROUP BY J.JNO,S.CITY
ORDER BY JNAME DESC ,CITY DESC;

########## End ##########

第18关：查询这样的工程号：供应该工程零件P1的平均供应量，不小于工程J1使用各零件合计数量的最大值.

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。 SPJ表如下图：

USE mydata;
#请在此处添加实现代码
########## Begin ##########
select x1.JNO
from SPJ x1
where (
    select AVG(xx.QTY)
    from SPJ xx
    where xx.sno='p1'
)>=(
    select MAX(x3.max_sum)
    from (
        select sum(QTY) as max_sum
        from SPJ x2
        where x2.JNO='J1'
        GROUP BY x2.PNO
    )as x3
)
########## End ##########

第19关：求至少用了供应商 S1所供应的全部零件的工程号 JNO

供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。 SPJ表如下图：

USE mydata;
#请在此处添加实现代码
########## Begin ##########
select distinct JNO
from SPJ
where not exists(
    select *
    from SPJ SS
    where SS.SNO='S1' AND NOT exists(
        select SSS.PNO 
        from SPJ SSS
        where SSS.JNO=SPJ.JNO and SSS.PNO=SS.PNO
    )
)

########## End ##########
stearm210
关注
  • 3
    点赞
  • 踩
  • 14
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 0
    评论
专栏目录
SQL SERVER专题实验4 复杂查询
Junds0的博客
10-08 2411
用 SELECT 语句完成含含普通连接、自然连接和外连接等在内的连接查询；在前几个实训的基础上，完成基于复杂查询的SELECT语句综合练习任务。本关任务：根据复杂查询相关知识介绍，完成相关选择题。用 SELECT 嵌套查询获取满足指定条件的结果；使用并、交、差的查询方式获取满足条件的查询结果；使用基于派生表的查询语句完成指定的数据查询；按要求返回查询表的属性列。按要求返回查询表的属性列。按要求输出目标列表达式。
sql replace替换多个字符_复杂查询（SQL）
weixin_39762856的博客
12-06 3608
一、视图视图视图中存放的是SQL的查询语句，当使用客户端连接到数据库使用视图时，视图中的SQL查询语句会创建一张临时表。当客户端和数据库连接断开时，临时表会被自动删除。使用视图创建视图create view 视图名称(<视图列表1>,<视图列表2>...)as<select 查询语句>;注：select子句中的列名顺序要和create view中的列名顺序一致，...
数据库头歌 SQL-CRUD-2
最新发布
weixin_73255571的博客
04-25 761
（DML，Data Manipulation Language） ，熟练掌握这三个语句的使用方法，能让大家快速入门 sqlserver。学习完后，你也有机会亲手试试，如何来修改数据库中的数据。补全右侧代码片段中 create database 下 Begin-End 区间的代码，用来建立数据库 website；喂一句心灵鸡汤：人生，从来都没有 delete 键，你能做的，只有负重前行，心向阳光。至少在虚拟世界，后悔药是有的卖的，你是可以重新来过的。中国有句俗语：人生在世熟能无过，过而能改，善莫大焉!
SQL Server数据库实验指导书 实验教程 数据库实验04 复杂查询.pdf
09-21
一、实验目的 1.掌握SQL Server 2005的安装。 2.掌握SQL Server Management Studio的启动和使用。 3.掌握SQL Server 2005服务器的配置和注册。 4.掌握SQL Server 2005查询的基本使用。 5.掌握应用SQL Server Management Studio创建数据库的方法。 6.掌握应用SQL Server Management Studio修改和查看数据库的方法。 7.掌握应用SQL Server Management Studio删除数据库的方法。 8.掌握应用Transact-SQL语句创建数据库的方法。 9.掌握应用Transact-SQL修改和查看数据库的方法。 10.掌握通过Transact-SQL删除数据库的方法。 11.掌握SQL Server 2005数据库和操作系统物理文件的关系。 12.掌握数据库的分离和附加方法。 二、实验内容 1.完成SQL Server 2005开发版的安装。 提示：若计算机系统中已经安装有SQL Server 2005系统，则在安装时需要选择安装命名实例。安装过程中身份验证模式选择“混合模式”并设置sa账户的密码。 2.利用SQL Server配置管理器启动、停止SQL Server服务（包括默认实例和命名实例），配置SQL Server服务为自动启动。 3.利用SQL Server配置管理器配置进行SQL Server 2005网络配置，启用默认实例和命名实例的TCP/IP协议。 4.利用SQL Server外围配置器配置数据库引擎的服务及远程连接，设置为“本地连接和远程连接”，选择“同时使用TCP/IP和named pipes”。 5.利用SQL Server Management Studio注册安装的命名实例。 6.利用SQL Server Management Studio注册远程服务器。 提示：注册远程服务器时需要使用混合验证模式，利用sa账户和密码登录远程服务器。 7.启动SQL Server Management Studio，连接到服务器。新建一个查询，在其中输入如下代码： DECLARE @position int, @string char(5) SET @position = 1 SET @string = 'China' WHILE @position <= DATALENGTH(@string) BEGIN SELECT SUBSTRING(@string, @position, 1) 字符, ASCII(SUBSTRING(@string, @position, 1)) ASCII码 SET @position = @position + 1 END
实验4 SQL语句——复杂查询及数据更新
goubixiazimo的博客
03-28 3180
一、课程名称： 数据库原理 二、实验名称：实验4 SQL语句——复杂查询及数据更新 三、实验地点与实验环境： 勤13-201 装有MySQL的PC机。 四、实验内容 数据查询： 1. 查询在2004年1月1日之前借书读者的编号、书名、借阅日期的信息。 2. 查询借阅了“应用文写作”这本书的读者的编号、姓名。 3. 使用子查询求其它类别中比管理类图书总......
实验五SQL的视图.doc
12-06
实验五 SQL 的视图实验旨在熟悉 SQL 支持的有关视图的操作，能够熟练使用 SQL 语句来创建需要的视图，对视图进行查询和取消视图。 知识点1：视图的定义 视图是一种基于基本表的虚拟表，通过 SELECT 语句从基本表中...
数据库实验4 交互式SQL（三）查询操作(B).doc
09-19
本资源是数据库实验第四部分，主要内容是交互式SQL查询操作。实验目的包括熟悉数据库的交互式SQL工具、熟悉通过SQL对数据库进行操作。实验工具使用的是MS SQL Server 2012。实验内容包括创建学生情况表、课程表、...
数据库实验二复杂SQL数据操作.doc
10-03
"数据库实验二复杂SQL数据操作" 这篇实验报告的主要目的是让学生熟悉使用SQL语言对数据库进行操作，包括各种复杂的SQL数据操作，如数据控制、权限管理、视图创建等。 一、实验目的 本实验的目的是让学生熟悉使用...
数据库系统原理实验报告4-南京信息工程大学
07-16
通过本实验，学生掌握了数据库系统原理的基本概念和操作，包括连接查询、嵌套查询、集合查询、视图查询等，并学会了使用 T-SQL 语句对表进行数据插入、修改、删除操作。这些知识点对数据库系统的设计、开发和应用...
中北大学数据库概论实验报告三（视图的使用（2学时）-验证型实验）
12-06
中北大学数据库概论实验报告...实验内容设计实现指定的数据库定义后，熟练掌握创建表的视图、利用视图完成表的查询、删除表的视图。能够判别数据是否满足完整性约束，同时可以利用触发器实现复杂的数据完整性约束控制。
SQL数据库_实验四_复杂查询(2)解答[整理].pdf
10-20
SQL数据库_实验四_复杂查询(2)解答[整理].pdf
数据库实验5：视图的使用.pdf
10-02
数据库实验5：视图的使用 视图是数据库管理系统中的一种虚拟表，它不存储实际数据，而是根据定义的查询结果动态生成的。视图的使用可以简化复杂的查询操作，提高查询效率，并且可以隐藏底层表的结构，从而提高数据...
《数据库原理》实验报告
12-15
1.学会使用对象资源管理器创建数据库、创建基本表和查看数据库属性。 2. 学会使用对象资源管理器向数据库输入数据，修改数据，删除数据的操作。...3. 在SQL Server查询编辑器中完成复杂查询及视图定义
北邮大三数据库实验六数据查询分析实验_(2).docx
07-18
本实验报告的主要内容是数据库查询分析实验，旨在通过对不同情况下查询语句的执行分析，巩固和加深对查询和查询优化相关理论知识的理解，提高优化数据库系统的实践能力，熟悉了解Sybase中查询分析器的使用，并进一步...
数据库原理-视图的创建与使用-实验报告.doc
10-03
① 创建一个名为stuview2的水平视图，从数据库XSGL的student表中查询出性别为“男”的所有学生的资料。 ② 创建一个名为stuview3的视图，能检索出CS系所有女生的学号、选修的课程名及相应的成绩。 ③ 创建一个名为...
mysql数据库复杂_数据库 | MySQL | 5. 数据操作（复杂查询）
weixin_28826545的博客
02-08 145
查询排序顺序SELECT * FROM `website` ORDER BY `id` ASC;SELECT * FROM `runoob_tbl` ORDER BY `runoob_submission_date` ASC;倒序SELECT * FROM `website` ORDER BY `id` DESC;SELECT * FROM `runoob_tbl` ORDER BY `runoo...
【头歌系统数据库实验】实验8 SQL的复杂多表查询-2
学习的博客
12-10 2201
任务描述基于派生表查询每个队员解答中超过他平均memory的user_id及题目编号problem_id（查询结果无需去重）相关知识solution：选手提交的题目解答 solution表如下图（仅显示前几条）：现已构建solution表，结构信息如下：开始你的任务吧，祝你成功!
头歌-数据库 --SQLServer第一章 - 初识SQLServer
banana_778的博客
03-29 2077
本关任务：创建你的第一个 SQLServer 数据库。数据库其实就是可以存放大量数据的仓库，学习数据库我们就从创建一个数据库开始吧。为了完成本关任务，你需要掌握：1.如何在 Linux 系统中连接 SQLServer 数据库，2.如何创建数据库。在 Windows 系统中，可以直接通过 SQLServer 的应用程序连接数据库，不过很多时候在生产环境下，我们需要通过命令行来连接数据库。下面我们就来看看，如何使用命令行连接数据库和创建数据库。1.打开右侧命令行窗口2.输入sqlcmd命令SQLServer 提
写一个数据库系统SQLServer实验中单表查询实验结果分析
06-02
好的，以下是一个数据库系统 SQL Server 实验中单表查询的实验结果分析。 单表查询实验通常旨在通过 SQL 语句查询单个表格中的数据，并对查询结果进行分析和解释。在 SQL Server 中，可以使用 SELECT 语句来进行单表查询，例如： ``` SELECT * FROM tableName; ``` 这个语句将检索表格中的所有数据，并将其返回到查询结果中。 在单表查询实验中，你可以执行多种类型的查询，例如： 1. 简单查询：使用基本的 SELECT 语句查询表格中的数据。例如： ``` SELECT firstName, lastName FROM employees; ``` 这个查询将返回表格中所有员工的名字和姓氏。 2. 条件查询：使用 WHERE 子句过滤表格中的数据。例如： ``` SELECT * FROM employees WHERE department = 'Sales'; ``` 这个查询将返回所有在销售部门工作的员工的信息。 3. 聚合查询：使用聚合函数（如 COUNT、AVG、MAX、MIN 和 SUM）对表格中的数据进行计算。例如： ``` SELECT COUNT(*) FROM employees; ``` 这个查询将返回表格中员工的总数。 4. 排序查询：使用 ORDER BY 子句对查询结果进行排序。例如： ``` SELECT * FROM employees ORDER BY hireDate DESC; ``` 这个查询将返回按照入职日期降序排列的所有员工信息。 在分析单表查询实验结果时，你需要考虑以下因素： 1. 查询语句的正确性：确保查询语句正确无误，并且可以返回你所期望的结果。 2. 查询结果的准确性：分析查询结果，确保其包含完整、准确的数据。 3. 查询效率：评估查询语句的执行效率，尤其是对大型数据集进行查询时。 4. 数据库性能：分析查询对数据库性能的影响，例如查询语句是否会锁定表格或影响其他查询的执行。 通过对这些因素进行分析，你可以更好地理解单表查询实验的结果，并对数据库系统的性能和优化提出建议。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

stearm210

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值