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

2023-12-13 04:25:34

目录

第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表，结构信息如下：

,

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

,

现已构建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表，结构信息如下：

,

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

,

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

,

相关提示

若用insert into插入select结果时需插入固定值，语法为如下：

,

其中，tableA是要插入数据的表名，column1, column2, column3, ...是要插入数据的列名；tableB是插入数据的来源，column1, column2, column3, ...,常量是要插入的数据，常量可以是多列。

例如：

,

其中123,'A'是固定常量值。

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
INSERT INTO SPJ(SNO, PNO, JNO, QTY)
SELECT 'S3', 'P4', J.JNO, NULL
FROM J
WHERE J.JNAME LIKE '%厂';

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

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

任务描述

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

相关知识

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

,

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

,

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

,

现已构建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 AS
    SELECT users.user_id, users.reg_time, users.name
    FROM users
    JOIN solution ON users.user_id = solution.user_id
    WHERE NOT EXISTS (
        SELECT *
        FROM solution AS A
        WHERE A.user_id = '202002020217' AND result = 4 AND language = 1
        AND NOT EXISTS (
            SELECT *
            FROM solution AS B
            WHERE users.user_id = B.user_id AND A.problem_id = B.problem_id
        )
    )
    GROUP BY users.user_id
    HAVING COUNT(DISTINCT solution.contest_id) = 3;
########## End ##########
#以下代码禁止删除
SELECT * FROM test;

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

任务描述

创建一个名为V_QTY_RANK的视图，按SPJ表查询信息，新增QTY_RANK列，并按此升序排序，当QTY>=500为A，区间[200,500）为B，低于200为C，QTY为NULL则也为NULL。

相关知识

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

,

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

,

相关提示

可搜索CASE语句等同于IF语句，但是它的构造更加可读。语法为如下：

,

MySQL分别计算WHEN子句中的每个条件，直到找到一个值为TRUE的条件，然后执行THEN子句中的相应<命令>。如果没有一个条件为 TRUE，则执行 ELSE子句中的<命令>。如果不指定ELSE子句，并且没有一个条件为 TRUE，MySQL 将发出错误消息。MySQL不允许在THEN或ELSE子句中使用空的命令。

例如:需要在demo表中新增lev列,列的内容为:当num >=85 为'优秀'，当num >= 60且num < 85为'一般'，其他情况为'不及格'，demo表如下图：

,

则用CASE语句实现上述功能的MYSQL代码为：

,

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
CREATE VIEW V_QTY_RANK AS
SELECT *,
    CASE 
        WHEN QTY >= 500 THEN 'A'
        WHEN QTY >= 200 AND QTY < 500 THEN 'B'
        WHEN QTY < 200 THEN 'C'
        ELSE NULL
    END AS QTY_RANK
FROM SPJ
ORDER BY QTY_RANK ASC;
########## End ##########
#以下代码禁止删除
SELECT * FROM V_QTY_RANK;

文章来源:https://blog.csdn.net/YK3020936216/article/details/134934562
本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。