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

编程入门行业动态更新时间:2024-10-17 11:34:37

数据库系统头歌实验三 SQL的复杂多表查询以及<a href=https://www.elefans.com/category/jswz/34/1770164.html style= 视图1"/>

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

第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 SNOFROM SPJGROUP 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 SPJWHERE JNO='J1'
)########## End ##########

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

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

USE `sqlexp-sztuoj`;
#请在此处添加实现代码
########## Begin ##########
CREATE VIEW temp(id,a) AS select user_id,AVG(MEMORY) FROM solution GROUP BY user_id;
SELECT user_id,problem_id FROM solution,temp
WHERE solution.user_id=temp.id AND solution.MEMORY>temp.a########## End ##########

第16关：用ANY/ALL实现查询2019级选手（user_id前4位为2019）满足比2020级其中一个选手注册时间（reg_time）早即可的选手信息