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

第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';
1
2

第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';
1
2
3
4

第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='北京';
1
2
3
4

第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;
1
2
3
4
5
6

第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;
1
2
3

第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;
1
2
3
4
5

第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;
1
2
3
4
5
6

第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;
1
2
3
4
5
6
7
8

第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;
1
2
3
4

第10关 删除视图v_1001

任务描述
删除视图v_1001

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

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

DROP VIEW IF EXISTS v_1001;
1

第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
1
2
3

第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'
1
2

第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);
1
2

第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';
1
2
3

第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; 
1
2
3
4
5
6

第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
)
1
2
3
4
5
6
7
8
9

第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;
1
2
3
4
5
6

第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) 
)
1
2
3
4
5
6
7
8
9
10
11
12

第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
    )
)
1
2
3
4
5
6
7
8
9
10
11
12
13