数据库系统习题---实验

实验7 SQL的复杂多表查询-1

第1关：求各颜色零件的平均重量

任务描述

求各颜色零件的平均重量

相关知识

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

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

---

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT COLOR,AVG(WEIGHT)
FROM P
GROUP BY COLOR;

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

第2关：求北京和天津供应商的总个数

任务描述

求北京和天津供应商的总个数

相关知识

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

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

---

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT CITY,COUNT(*) FROM S WHERE CITY IN ('北京','天津') group by CITY;


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

第3关：求各供应商供应的零件总数

任务描述

求各供应商供应的零件总数(SUM_QTY)，结果按SUM_QTY降序排序。

相关知识

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

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

---

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

USE mydata;

#请在此处添加实现代码

########## Begin

SELECT SNO,SUM(QTY) SUM_QTY

FROM SPJ

GROUP BY SNO

ORDER BY SUM_QTY desc;



########## End

第4关：求各供应商供应给各工程的零件总数

任务描述

求各供应商供应给各工程的零件总数(SUM_QTY)，结果先按供应商代码(SNO)降序排序，再按工程项目代码(JNO)降序排序。

相关知识

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

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

---

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT SNO,JNO,SUM(QTY) SUM_QTY
FROM SPJ
GROUP BY SNO,JNO
ORDER BY SNO DESC,JNO DESC;

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

第5关：求重量大于所有零件平均重量的零件名称

任务描述

求重量大于所有零件平均重量的零件名称

相关知识

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

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

---

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT PNAME
FROM P
WHERE WEIGHT >(SELECT AVG(WEIGHT) AS A FROM P HAVING (WEIGHT>A));

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

第6关：查询供应了1000个以上零件的供应商名称

任务描述

查询供应了1000个以上零件的供应商名称,查询结果按供应商名称降序排序。

相关知识

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

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

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

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

---

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT SNAME 
FROM S JOIN SPJ s2 on S.SNO = s2.SNO GROUP BY s2.SNO, S.SNAME HAVING SUM(s2.QTY) > 1000 ORDER BY S.SNAME DESC ;


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

第7关：统计P表中颜色为蓝色的零件个数，并指定该查询列的名称为“蓝色零件数”

任务描述

统计P表中颜色为蓝色的零件个数，并指定该查询列的名称为“蓝色零件数”

相关知识

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

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

---

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT COUNT(*) as '蓝色零件数' 
FROM P
WHERE COLOR ='蓝';


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

第8关：查询P表中各零件的编号，名称及重量按85%计算后的信息，其中重量按85%计算后的查询列名改为“零件净重”

任务描述

查询P表中各零件的编号，名称及重量按85%计算后的信息，其中重量按85%计算后的查询列名改为“零件净重”

相关知识

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

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

---

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

USE mydata;

#请在此处添加实现代码

########## Begin ##########

SELECT PNO,PNAME,WEIGHT*0.85 AS '零件净重'

from P;



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

第9关：查询 S表STATUS值大于20且小于50，或SNAME字段值的第一个字为“精”或第三个字为“益”或“民”的供应商信息

任务描述

查询 S表STATUS值大于20且小于50，或SNAME字段值的第一个字为“精”或第三个字为“益”或“民”的供应商信息

相关知识

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

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

---

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT * 
FROM S
WHERE (STATUS>20 AND STATUS<50 )  or SNAME like '精%' or SNAME like '__益' or SNAME like '__民';

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

第10关：将SPJ表按QTY值降序排列，再找出SPJ表中前6条

任务描述

将SPJ表按QTY值降序排列，再找出SPJ表中前6条记录(用limit 6)

相关知识

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

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

---

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT *
FROM SPJ
ORDER BY QTY DESC
limit  6;

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

第11关：找出供应零件总数量不低于1000的供应商号码，及每个供应商供应的总数量，并且结果按总数量降序排列

任务描述

找出供应零件总数量不低于1000的供应商号码，及每个供应商供应的总数量(SUM_QTY)，并且结果按总数量降序排列

相关知识

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

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

---

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT SNO ,SUM(QTY) SUM_QTY
FROM SPJ
GROUP BY SNO
HAVING SUM_QTY >=1000
ORDER BY SUM_QTY DESC;


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

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

任务描述

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

相关知识

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

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

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

现已构建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 ##########

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

任务描述

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

相关知识

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

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

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

现已构建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 ##########

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

任务描述

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

相关知识

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

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

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

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

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

现已构建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 ##########

第15关：用嵌套查询这样的工程：供给该工程的零件P3的平均供应量，大于其中一种供给工程J1的零件的最大供应量

任务描述

用嵌套查询这样的工程：供给该工程的零件P3的平均供应量，大于其中一种供给工程J1的零件的最大供应量

相关知识

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

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT JNO
FROM (
    SELECT JNO, AVG(QTY) AS AQ
    FROM SPJ
    WHERE PNO = 'P3'
    GROUP BY JNO
) AS V1
WHERE AQ > (
    SELECT MIN(MQ)
    FROM (
        SELECT MAX(QTY) AS MQ
        FROM SPJ
        WHERE JNO = 'J1'
        GROUP BY PNO
    ) AS V2
);
########## End ##########

第16关：用链接查询这样的工程：供给该工程的零件P3的平均供应量，大于其中一种供给工程J1的零件的最大供应量

任务描述

用链接查询这样的工程：供给该工程的零件P3的平均供应量，大于其中一种供给工程J1的零件的最大供应量

相关知识

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

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT Distinct V1.JNO
FROM (
    SELECT JNO, AVG(QTY) AS AQ
    FROM SPJ
    WHERE PNO = 'P3'
    GROUP BY JNO
) AS V1
JOIN (
    SELECT MAX(QTY) AS MQ
    FROM SPJ
    WHERE JNO = 'J1'
    GROUP BY PNO
) AS V2 ON V1.AQ > V2.MQ;
########## End #######

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

任务描述

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

相关知识

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

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT DISTINCT V1.JNO
FROM (
    SELECT JNO, AVG(QTY) AS AQ
    FROM SPJ
    WHERE PNO = 'P3'
    GROUP BY JNO
) AS V1
JOIN (
    SELECT SUM(QTY) AS MQ
    FROM SPJ
    WHERE JNO = 'J1'
    GROUP BY PNO
) AS V2 ON V1.AQ >= V2.MQ;

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