【头歌系统数据库实验】实验9 SQL视图

第1关：请为三建工程项目建立一个供应情况的视图V_SPQ，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)

第2关：从视图V_SPQ找出三建工程项目使用的各种零件代码及其数量

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

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

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

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

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

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

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

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

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

第12关：删除视图v_1001

第13关：在视图v_users_avgmemory中查询平均memory在2000以下的选手信息及平均memory

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

第15关：在视图v_users_avgmemory中插入一条记录(2020100904，1800)

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

第17关：根据该需求给SPJ表插入相应数据

第18关：创建一个名为V_PJMAXSP的视图

第19关：创建一个名为test的视图

第20关：创建一个名为V_QTY_RANK的视图

如果对你有帮助的话，不妨点赞收藏评论一下吧，爱你么么哒😘❤️❤️❤️

第1关：请为三建工程项目建立一个供应情况的视图V_SPQ，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)

任务描述

请为三建工程项目建立一个供应情况的视图V_SPQ，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)

相关知识

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

现已构建J表，结构信息如下：

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

现已构建SPJ表，结构信息如下：

开始你的任务吧，祝你成功!

USE mydata;
#请在此处添加实现代码
########## Begin ##########
create view V_SPQ
AS
SELECT SNO,PNO,QTY
FROM SPJ,J
WHERE SPJ.JNO=J.JNO AND J.JnaME='三建';
########## End ##########
#以下代码禁止删除
SELECT * FROM V_SPQ;

第2关：从视图V_SPQ找出三建工程项目使用的各种零件代码及其数量

任务描述

从视图V_SPQ找出三建工程项目使用的各种零件代码及其数量(SUM_QTY),结果按SUM_QTY降序排序。

相关知识

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

现已构建视图V_SPQ，结构信息如下：

开始你的任务吧，祝你成功!

USE mydata;
#请在此处添加实现代码
########## Begin ##########
select distinct PNO,sum(QTY) as SUM_QTY
from V_SPQ
group by pno
order by SUM_QTY desc;########## End ##########

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

任务描述

从视图V_SPQ找出供应商S1的供应情况

相关知识

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

现已构建视图V_SPQ，结构信息如下：

开始你的任务吧，祝你成功!

USE mydata;
#请在此处添加实现代码
########## Begin ##########
select PNO,QTY
from V_SPQ
where sno='S1';########## End ##########

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

任务描述

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

相关知识

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

现已构建SPJ表，结构信息如下：

开始你的任务吧，祝你成功!

USE mydata;
#请在此处添加实现代码
########## Begin ##########
CREATE VIEW V_SPJ2
AS
SELECT SNO,	PNO	,JNO, QTY
from SPJ
where sno='S2';########## End ##########
#以下代码禁止删除
SELECT * FROM V_SPJ2;

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

任务描述

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

相关知识

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

现已构建S表，结构信息如下：

开始你的任务吧，祝你成功

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

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

任务描述

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

相关知识

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

现已构建P表，结构信息如下：

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

现已构建J表，结构信息如下：

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

现已构建SPJ表，结构信息如下：

开始你的任务吧，祝你成功!

USE mydata;
#请在此处添加实现代码
########## Begin ##########
create view V_PJQ
AS
SELECT DISTINCT JNAME, COLOR, SUM(QTY) as SUM_QTY
from P,J,SPJ
WHERE J.JNO=SPJ.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;

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

任务描述

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

相关知识

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

现已构建视图V_SPQ，结构信息如下：

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

现已构建J表，结构信息如下：

现已构建SPJ表，结构信息如下：

开始你的任务吧，祝你成功!

USE mydata;
#请在此处添加实现代码
########## Begin ##########
UPDATE V_SPQ
SET SNO='S1'
WHERE QTY=400;########## End ##########
#以下代码禁止删除
SELECT * FROM SPJ;

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

任务描述

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

提示：在视图定义时加上WITH CHECK OPTION子句，表示对视图进行插入、修改、删除操作时要保证插入、修改、删除的行满足视图定义中的谓词条件（即子查询中的条件表达式）。

相关知识

solution为选手提交的题目解答 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;

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

任务描述

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

相关知识

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

现已构建users表，结构信息如下：

2、solution为选手提交的题目解答 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;

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

任务描述

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

相关知识

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

现已构建users表，结构信息如下：

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

现已构建solution表，结构信息如下：

开始你的任务吧，祝你成功!

USE `sqlexp-sztuoj`;#请在此处添加实现代码
########## Begin ##########
CREATE VIEW v_user2020_1001_6
AS
SELECT distinct solution.user_id,name,result,problem_id
from users,solution
WHERE solution.user_id=users.user_id AND solution.user_id LIKE '2020%' AND solution.problem_id=1001 and solution.result=6
order by user_id asc;
########## End ##########
#以下代码禁止删除
SELECT * FROM v_user2020_1001_6;

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

任务描述

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

相关知识

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

现已构建solution表，结构信息如下：

开始你的任务吧，祝你成功!

USE `sqlexp-sztuoj`;#请在此处添加实现代码
########## Begin ##########
create view v_users_avgmemory
as
SELECT user_id,avg(memory) as avgmemory
from solution
group by user_id;########## End ##########
SELECT * FROM v_users_avgmemory;

第12关：删除视图v_1001

任务描述

删除视图v_1001

相关知识

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

现已构建视图v_1001，结构信息如下：

开始你的任务吧，祝你成功!

USE `sqlexp-sztuoj`;#请在此处添加实现代码
########## Begin ##########
DROP VIEW IF EXISTS v_1001;########## End ##########
#保证下面一行MYSQL语句在9行的位置，以保证结果匹配
SELECT v_1001.* FROM v_1001;

第13关：在视图v_users_avgmemory中查询平均memory在2000以下的选手信息及平均memory

任务描述

在视图v_users_avgmemory中查询avgmemory在2000以下的user_id及avgmemory

相关知识

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

现已构建视图v_users_avgmemory，结构信息如下：

开始你的任务吧，祝你成功!

USE `sqlexp-sztuoj`;#请在此处添加实现代码
########## Begin ##########
select user_id,avgmemory
from v_users_avgmemory
where avgmemory<2000
########## End ##########

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

任务描述

在视图v_1001中删除user_id为201902010318的记录

相关知识

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

现已构建视图v_1001，结构信息如下：

开始你的任务吧，祝你成功!

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

第15关：在视图v_users_avgmemory中插入一条记录(2020100904，1800)

任务描述

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

相关知识

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

现已构建视图v_users_avgmemory，结构信息如下：

开始你的任务吧，祝你成功!

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

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

任务描述

在视图v_user2020_1003中将user_id为20200202的result更改为10

相关知识

视图v_user2020_1003为2020级做了1003号题的选手视图。视图v_user2020_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';

第17关：根据该需求给SPJ表插入相应数据

任务描述

供应商号(SNO)为S3的供应商将参加所有项目名称(JNAME)含有“厂”字的项目。根据该需求给SPJ表插入相应数据，PNO设为P4、QTY设置为null(无需判断记录是否已存在)

相关知识

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

现已构建J表，结构信息如下：

现已构建SPJ表，结构信息如下：

第18关：创建一个名为V_PJMAXSP的视图

任务描述

创建一个名为V_PJMAXSP的视图，查找各工程项目号(需显示)里供应最大数量的供应商号(SNO)、名称(SNAME)，并先按JNO升序排序，再按SNO升序排序。

相关知识

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

现已构建S表，结构信息如下：

现已构建SPJ表，结构信息如下：

开始你的任务吧，祝你成功！

USE mydata;
#请在此处添加实现代码
########## Begin ##########
CREATE VIEW V_PJMAXSP AS
SELECT SPJ.JNO,SPJ.SNO,S.SNAME
FROM SPJ,S
WHERE SPJ.QTY = (SELECT MAX(QTY) FROM SPJ SPJ1 WHERE SPJ.JNO=SPJ1.JNO ) AND SPJ.SNO=S.SNO
ORDER BY JNO ASC,SNO ASC;
########## End ##########
#以下代码禁止删除
SELECT * FROM V_PJMAXSP;

第19关：创建一个名为test的视图

任务描述

创建一个名为test的视图，查询这样的选手信息(users表)，参加过三次比赛(不包含null)，且至少解答过"202002020217"选手result为4且采用language为1解答过的所有题目。

提示：count(distinct 列名)：根据指定的列统计记录总数，去重复且不包括NULL值。

相关知识

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

现已构建users表，结构信息如下：

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

现已构建solution表，结构信息如下：

开始你的任务吧，祝你成功!

USE `sqlexp-sztuoj`;#请在此处添加实现代码
########## Begin ##########
CREATE VIEW test ASSELECT users.user_id, users.reg_time, users.nameFROM usersJOIN solution ON users.user_id = solution.user_idWHERE NOT EXISTS (SELECT *FROM solution AS AWHERE A.user_id = '202002020217' AND result = 4 AND language = 1AND NOT EXISTS (SELECT *FROM solution AS BWHERE users.user_id = B.user_id AND A.problem_id = B.problem_id))GROUP BY users.user_idHAVING COUNT(DISTINCT solution.contest_id) = 3;
########## End ##########
#以下代码禁止删除
SELECT * FROM test;