mysql 大数据 【头歌系统数据库实验】实验7 SQL的复杂多表查询-1

目录

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

第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 (

SELECT AVG(WEIGHT)

FROM P

HAVING WEIGHT > AVG(WEIGHT)

);

########## 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(pno) 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 SNO, SNAME, STATUS, CITY

from S

where (STATUS > 20 and STATUS < 50)

or SNAME like '精%' or SNAME like '__益' or SNAME like '__民';

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

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

任务描述

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

相关知识

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

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

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

USE mydata;

#请在此处添加实现代码

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

SELECT SNO, PNO, JNO, QTY

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)

AS 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 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;

########## 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 S,J,SPJ

WHERE S.SNO=SPJ.SNO

AND SPJ.JNO=J.JNO

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 ##########

参考文章

 您阅读本篇文章共花了：