数据库系统头歌实验三 SQL的复杂多表查询以及视图1

stearm210

已于 2022-12-23 16:08:08 修改

阅读量3.6k

点赞数 4

分类专栏：数据库实验文章标签：数据库 sql 服务器

于 2022-12-23 15:48:46 首次发布

本文链接：https://blog.csdn.net/m0_53227534/article/details/128420326

版权

数据库实验专栏收录该内容

7 篇文章 7 订阅

订阅专栏

第1关：查询所有“红色”的15公斤及以上的零件名

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

P表如下图：

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT PNAME
FROM P
WHERE WEIGHT>15 AND COLOR='红'
########## End ##########

第2关：查询工程名称中含有“厂”字的工程明细

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

J表如下图：

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT*
FROM J
WHERE JNAME LIKE '%厂'

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

第3关：查询所有“红色”的15公斤及以上的零件名

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

P表如下图：

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT PNAME
FROM P
WHERE WEIGHT>15 AND COLOR='红'
########## End ##########

第4关：查询工程名称中含有“厂”字的工程明细

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT*
FROM J
WHERE JNAME LIKE '%厂'

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

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

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

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

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

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


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

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

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

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

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

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT SNAME
FROM S
WHERE SNO IN (
    SELECT SNO
    FROM SPJ
    GROUP BY QTY HAVING SUM(QTY)>1000
)
ORDER BY SNAME DESC
########## End ##########

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

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

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

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

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

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT PNO,PNAME,0.85*WEIGHT AS '零件净重'
FROM P

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

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

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT SNO,SNAME,STATUS,CITY
FROM S
WHERE (STATUS>20 AND STATUS<50) OR (SNAME LIKE '精%') OR (SNAME='__益' OR SNAME='__民')

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

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

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

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

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

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

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT JNO
FROM SPJ
WHERE PNO='P1'
GROUP BY JNO HAVING AVG(QTY)>any(
    SELECT MAX(QTY)
    FROM SPJ
    WHERE JNO='J1'
)

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

第15关：基于派生表查询每个队员解答中超过他平均memory的user_id及题目编号problem_id（查询结果无需去重）

solution：选手提交的题目解答 solution表如下图（仅显示前几条）：