实验4 SQL的复杂多表查询以及视图

已于 2022-11-11 10:28:00 修改
阅读量2.7k 收藏 9
点赞数 4
分类专栏： DataBase 文章标签： 数据库
于 2022-11-10 18:07:38 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_51571728/article/details/127790465
版权

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

任务描述
从视图V_SPQ找出供应商S1的供应情况

相关知识
三建工程项目视图V_SPQ由供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)组成。 视图V_SPQ如下图：
在这里插入图片描述
现已构建视图V_SPQ，结构信息如下：
在这里插入图片描述

SELECT PNO,QTY FROM V_SPQ
WHERE SNO='S1';

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

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

相关知识
供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商
供应某种零件 给某工程项目的数量为QTY。
SPJ表如下图：
在这里插入图片描述
现已构建SPJ表，结构信息如下：
在这里插入图片描述

CREATE VIEW V_SPJ2 AS
SELECT *
FROM SPJ
WHERE SNO='S2';

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

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

相关知识
供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成.
S表如下图：
在这里插入图片描述
现已构建S表，结构信息如下：
在这里插入图片描述

CREATE VIEW V_BJS AS
SELECT SNO,SNAME,CITY
FROM S
WHERE CITY='北京';

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

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

相关知识
1、零件表P由零件代码（PNO）、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成；
P表如下图：
在这里插入图片描述
现已构建P表，结构信息如下：
在这里插入图片描述
2、工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成。
J表如下图：
在这里插入图片描述

CREATE VIEW V_PJQ AS
SELECT JNAME,COLOR,SUM(QTY) AS SUM_QTY
FROM J,P,SPJ
WHERE SPJ.JNO=J.JNO 
AND SPJ.PNO=P.PNO
GROUP BY J.JNAME,COLOR;

第5关 将视图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表，结构信息如下：
在这里插入图片描述

UPDATE V_SPQ
SET SNO='S1'
WHERE QTY=400;

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

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

相关知识
solution为选手提交的题目解答
solution表如下图（仅显示前几条）：
在这里插入图片描述
现已构建solution表，结构信息如下：
在这里插入图片描述

CREATE VIEW v_1001 AS
SELECT * 
FROM solution
WHERE contest_id=1001
WITH CHECK OPTION;

第7关 建立2020级做了1003号题的选手视图v_user2020_1003（包括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表，结构信息如下：
在这里插入图片描述

CREATE VIEW v_user2020_1003 AS
SELECT DISTINCT solution.user_id,name,result
FROM users,solution
WHERE users.user_id LIKE '2020%' 
AND problem_id='1003' 
AND users.user_id = solution.user_id;

第8关 建立2020级做了1001号题且result为6的选手视图v_user2020_1001_6

任务描述
建立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表，结构信息如下：
在这里插入图片描述

CREATE VIEW v_user2020_1001_6 AS
SELECT DISTINCT solution.user_id,name,result,problem_id
FROM users,solution
WHERE users.user_id LIKE '2020%' 
AND problem_id='1001' 
AND result=6
AND users.user_id = solution.user_id
ORDER BY users.user_id;

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

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

相关知识
solution为选手提交的题目解答
solution表如下图（仅显示前几条）：
在这里插入图片描述
现已构建solution表，结构信息如下：
在这里插入图片描述

CREATE VIEW v_users_avgmemory(user_id,avgmemory) AS
SELECT DISTINCT user_id,AVG(memory)
FROM solution
GROUP BY user_id;

第10关 删除视图v_1001

任务描述
删除视图v_1001

相关知识
视图v_1001为比赛 1001 的所有解答。
视图v_1001如下图（仅显示前几条）：
在这里插入图片描述
现已构建视图v_1001，结构信息如下：
在这里插入图片描述

DROP VIEW IF EXISTS v_1001;

第11关 在视图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，结构信息如下：

SELECT * 
FROM v_users_avgmemory
WHERE avgmemory<=2000

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

任务描述
在视图v_1001中删除user_id为201902010318的记录

相关知识
视图v_1001为比赛 1001 的所有解答。
视图v_1001如下图（仅显示前几条）：
在这里插入图片描述
现已构建视图v_1001，结构信息如下：
在这里插入图片描述

DELETE FROM v_1001 
WHERE user_id='201902010318'

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

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

相关知识
视图v_users_avgmemory为选手的user_id及解答的平均mavgmemory。
视图v_users_avgmemory如下图（仅显示前几条）：
在这里插入图片描述
现已构建视图v_users_avgmemory，结构信息如下：
在这里插入图片描述

INSERT INTO v_users_avgmemory
VALUES(2020100904,1800);

第14关 在视图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，结构信息如下：
在这里插入图片描述

UPDATE  v_user2020_1003
SET result=10
WHERE user_id='20200202';

第15关 找出工程项目J1使用的各种零件的名称及其数量

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

相关知识
1、零件表P由零件代码（PNO）、零件名(PNAME)、颜色(COLOR)、重量(WEIGHT)组成；
P表如下图：
在这里插入图片描述
现已构建P表，结构信息如下：
在这里插入图片描述
2、供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。
SPJ表如下图：
在这里插入图片描述
现已构建SPJ表，结构信息如下：
在这里插入图片描述

SELECT PNAME,SUM(QTY) AS 'SUM_QTY'
FROM P,SPJ
WHERE SPJ.JNO='J1'
AND P.PNO=SPJ.PNO
GROUP BY P.PNAME
ORDER BY SUM_QTY DESC;

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

任务描述
求使用了300个及以上P1零件的工程名称

相关知识
1、工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成。
J表如下图：
在这里插入图片描述
现已构建J表，结构信息如下：
在这里插入图片描述
2、供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。
SPJ表如下图：
在这里插入图片描述
现已构建SPJ表，结构信息如下：
在这里插入图片描述

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
)

第17关 求各工程（名）使用的各城市供应的零件总数

任务描述
求各工程（名）使用的各城市供应的零件总数，结果先按工程名降序排序，再按城市名降序排序。

相关知识
1、供应商表S由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成.
S表如下图：
在这里插入图片描述
现已构建S表，结构信息如下：
在这里插入图片描述
2、工程项目表J由工程项目代码(JNO)、工程项目名(JNAME)、工程项目所在城市(CITY)组成。
J表如下图：
在这里插入图片描述
现已构建J表，结构信息如下：
在这里插入图片描述
3、供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商 供应某种零件 给某工程项目的数量为QTY。
SPJ表如下图：
在这里插入图片描述
现已构建SPJ表，结构信息如下：
在这里插入图片描述

SELECT JNAME,S.CITY,SUM(QTY) AS SUM_QTY
FROM S,J,SPJ
WHERE SPJ.JNO=J.JNO 
AND SPJ.SNO=S.SNO
GROUP BY J.JNO,S.CITY
ORDER BY JNAME DESC,CITY DESC;

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

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

相关知识
供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商
供应某种零件给某工程项目的数量为QTY。
SPJ表如下图：
在这里插入图片描述
现已构建SPJ表，结构信息如下：
在这里插入图片描述

CREATE VIEW view1 AS
SELECT JNO,AVG(QTY) AS AVG_GTY
FROM SPJ
WHERE PNO='P1'
GROUP BY JNO;
SELECT JNO
FROM view1 WHERE AVG_GTY>=ALL(
    SELECT SUM(QTY)
    FROM SPJ
    WHERE JNO='J1'
    GROUP BY PNO HAVING SUM(QTY) 
)

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

任务描述
求至少用了供应商 S1所供应的全部零件的工程号 JNO

相关知识
供应情况表SPJ由供应商代码(SNO)、零件代码(PNO)、工程项目代码(JNO)、供应数量(QTY)组成，标识某供应商供应某种零件 给某工程项目的数量为QTY。
SPJ表如下图：
在这里插入图片描述
现已构建SPJ表，结构信息如下：
在这里插入图片描述

SELECT DISTINCT JNO
FROM SPJ s
WHERE NOT EXISTS(
    SELECT *
    FROM SPJ s1
    WHERE s1.SNO='S1'
    AND NOT EXISTS(
        SELECT * FROM
        SPJ s2
        WHERE s2.PNO=s1.PNO 
        AND s2.JNO=s.JNO
    )
)
半濠春水
关注
  • 4
    点赞
  • 踩
  • 9
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
sql-显示供应商供应零件的汇总列表(with rollup+coalesce)
12-14
文章目录题目数据表分析代码运行结果 题目 本题目要求编写SELECT语句，在SPJ数据库中，列出各个供应商供应各种零件的数量合计的汇总列表。 要求：显示出每个供应商供应每种零件的数量合计和每个供应商供应所有零件的数量合计以及所有供应商供应的所有零件数量合计。 数据表 链接: spj库 分析 使用WITH ROLLUP：在group分组字段的基础上进行统计数据。 函数COALESCE(expression_1,…,expression_n)依次参考各参数表达式，遇到非null值即停止并返回该值。如果所有的表达式都是空值，最终将返回一个空值。 代码 select coalesce(sno,'所有
《数据库原理与应用》第三章习题（二）
Blog of Mark
03-31 6887
1.解释相关子查询和不相关子查询。 非相关子查询的执行不依赖与外部的查询。 执行过程： （1）执行子查询，其结果不被显示，而是传递给外部查询，作为外部查询的条件使用。 （2）执行外部查询，并显示整个结果。　　 非相关子查询一般可以分为：返回单值的子查询和返回一个列表的子查询。 相关子查询的执行依赖于外部查询。子查询的WHERE子句或者HAVING中引用了外部查询的表。 执行过程： （1）从外层查询...
头歌 实验七 存储过程
最新发布
qq_52754254的博客
05-06 900
use demo;#代码开始#1、定义简单存储过程：计算所有供应商供应零件总数量并修改供应商相关列sqty。beginend//#2、调用存储过程。#代码结束use demo;#代码开始beginend//#2、调用带参数存储过程。#以供应商S1为参数，调用存储过程，将结果存入@sqty1#以供应商S2为参数，调用存储过程，将结果存入@sqty2#代码结束use demo;#代码开始。
上学期大题（不一定有用）
m0_63027821的博客
04-21 1130
创建用户 user2，密码为’12345’，授予其对 doctor 表的查询权限和修改字段doc_name 的权限，并允许将此权限授予其他用户；供应情况表SPJ由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY）组成，表示某供应商 供应某种零件 给某工程项目的数量为QTY。用户 user3 将 doctor 表的查询权限授予 user4，验证 user4 对 doctor 表的查询权限；医院数据库包括医生表 doctor，医生表包含医生工号（doc_id），医生姓名。
数据库概论实验一
As_sBomb的博客
12-06 2505
实验数据库，表，详情见书 实验一 常规数据查询 【实验目的】 1、 熟悉不同数据库管理系统数据库格式的转化； 2、 掌握简单的SQL命令；  掌握Select子句以及Where子句的使用方法；  学会应用Order by子句；  掌握5种基本的聚集函数；  学会分组统计； 3、 熟悉查询分析器的使用环境。 【实验内容】 完成如下查询： select子句： A. 查看所有供应商的信息； select * from s; B. 查看供应商的姓名和所在城市，并且将输出结果中的列名显示为“姓名”、所.
数据库1（MYSQL）大题
m0_64262067的博客
04-22 3046
4、编写一个存储过程p_deleteStudent(in sno char(9),out msg char(8)),其功能是删除指定学号sno的学生记录，如果sc表中有该学生的选课记录，要级联删除该学生的选课记录，并根据删除情况返回msg信息（未找到学生，已经删除学生信息，已经删除学生信息及其选课信息）等。5、编写一个存储存过程 set_cj(IN xh CHAR(9),IN kh CHAR(4))，其功能是设定Mystudent数据库的sc表中指定学号xh，指定课程号kh的学生成绩为空。
mysql 数据检索_MySQL 检索数据总结
weixin_39771969的博客
01-20 133
1. 基本查询检索单个列select prod_name from products;检索所有列select*from products;检索不同的行select distinct vend_id from products;限制结果select prod_name from products limit4,5;1.LIMIT ：若有两个参数，则第一个参数为开始位置(从 0 行开始)，第二个参数为...
数据库实验2 视图与多表查询.doc
12-10
数据库实验2 视图与多表查询 以下是基于给定的文件信息生成的相关知识点： 1. 数据库管理系统（DBMS）的功能和软件组成：DBMS 是一种软件系统，用于定义、创建、维护和操纵数据库。DBMS 的主要功能包括数据定义、...
实验五SQL的视图.doc
12-06
实验五 SQL 的视图实验旨在熟悉 SQL 支持的有关视图的操作，能够熟练使用 SQL 语句来创建需要的视图，对视图进行查询和取消视图。 知识点1：视图的定义 视图是一种基于基本表的虚拟表，通过 SELECT 语句从基本表中...
Oracle实验二-SQL语句综合应用
05-22
在本实验中，我们主要探讨了Oracle数据库中的SQL语句应用，涉及到了表、视图、索引以及序列等核心概念。以下是对这些知识点的详细解释： 1. **创建表**： - 使用`CREATE TABLE`语句创建了一个名为`person`的表，...
数据库实验4 交互式SQL（三）查询操作(B).doc
09-19
实验内容包括创建学生情况表、课程表、学生与课程表，并进行查询操作。 知识点： 1. 数据库基本概念：数据库、数据表、字段、记录、外键等。 2. SQL语言：SELECT、FROM、WHERE、GROUP BY、HAVING、JOIN等语句。 3....
多供方分析报表.sql
06-10
多供方分析报表
数据库实验4报告（视图与索引）
05-07
首先，视图是数据库中的虚拟表，它是由SQL查询语句定义的，并不实际存储数据，而是根据需要动态生成。在实验中，学生被要求创建了几个不同条件的视图。例如，创建了一个包含选修了“数据库”课程且2001年出生的学生...
统计各供应商的零件供应量
ZHANG123HUO的博客
05-24 9039
本题目要求编写Select语句，在SPJ数据库中，统计每个供应商的零件供应量总和。 要求：仅对那些每次供应零件的数量都在100以上（含100）的供应商进行统计。 如：供应商s2 供应工程项目j5 的p3零件数量为50，则不统计供应商s2 select a.sno as 供应商号,sname as 供应商,sum(qty) as 供应总量 from spj a,s where a.sno=s.sno and not exists( select distinct sno from spj b
10-119 spj-显示供应商供应零件的汇总列表
THK的博客
09-10 683
本题目要求编写SELECT语句，在SPJ数据库中，列出各个供应商供应各种零件的数量合计的汇总列表。要求：显示出每个供应商供应每种零件的数量合计和每个供应商供应所有零件的数量合计以及所有供应商供应的所有零件数量合计。提示：请使用 " WITH ROLLUP " 语句作答。
数据库系统概论（第五版）—— 第三章课后题4，5
qq_42898299的博客
04-09 5527
文章目录先来康康要用到的表S:供应商表P：零件表J:工程项目表SPJ:供应情况表开始做题4.针对建立的4个表用SQL完成2章习题6中的查询（1）求供应工程J1零件的供应商号码SNO；（2）求供应工程J1零件P1的供应商号码SNO；（3）求供应工程J1零件为红色的供应商号码SNO；（4）求没有使用天津供应商生产的红色零件的工程号JNO；（5）求至少使用了供应商S1提供的全部零件的工程号JNO； 先来...
数据库实验3答案
物联黄同学的博客
11-03 2535
数据库实验
SQL SERVER专题实验4 复杂查询
热门推荐
qq_45326829的博客
03-27 1万+
本关主要介绍的是概念性知识，因此测试以选择题的形式对本关介绍的知识进行测试。请参考后面的正确运行结果，使用嵌套查询的方案写T-SQL语句。请参考后面的正确运行结果，使用集合查询方案写T-SQL语句。5、关于基于派生表的查询，下面说法正确的有：(ABD)3、关于嵌套查询，下面说法正确的有：(ABCD)4、关于集合查询，下面说法正确的有：(ABCD)1、关于自身连接，下面说法正确的有：(ABC)2、关于自然连接，下面说法正确的有：(ABC)请参考后面的正确运行结果写T-SQL语句。
针对第2章习题6的SPJ数据库，为项目名称为“一汽”的工程项目建立一个供应情况的视图V_SPJ，视图中应包括供应商代码SNO，零件代码PNO，供应数量QTY，并针对该视图完成下列查询 （1）找出一汽
海触的博客
04-25 3280
针对第2章习题6的SPJ数据库，为项目名称为“一汽”的工程项目建立一个供应情况的视图V_SPJ，视图中应包括供应商代码SNO，零件代码PNO，供应数量QTY，并针对该视图完成下列查询 （1）找出一汽工程项目使用的各种零件代码及其数量 （2）找出供应商S1供应一汽工程的情况 创建视图： CREATE VIEW V_SPJ AS SELECT SNO,PNO,QTY FROM SPJ,J W...
sqlserver视图多表查询
06-08
要在 SQL Server 中创建视图多表查询，需要使用 JOIN 语句将多个表连接起来，并在 SELECT 语句中选择需要的列。以下是一个简单的示例： ``` CREATE VIEW myView AS SELECT Orders.OrderID, Customers.CustomerName, Employees.LastName FROM Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID ``` 在此示例中，我们创建了一个名为 myView 的视图，该视图显示了订单信息、客户名称和雇员姓氏。要查询此视图，请使用以下 SELECT 语句： ``` SELECT * FROM myView ``` 这将返回所有列和行，这些列和行是在视图中定义的。

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

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

余额充值