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

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

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

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

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

USE mydata;

#请在此处添加实现代码
########## Begin ##########
SELECT
    CITY,
    COUNT(*)
FROM S
WHERE CITY = '北京'
UNION
SELECT
    CITY,
    COUNT(*)
FROM S
WHERE CITY = '天津'

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

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

USE mydata;

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

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

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

USE mydata;

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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条记录(用limit 6)

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

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

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

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使用的各种零件的名称及其数量

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT
    PNAME,
    SUM(QTY) SUM_QTY
FROM SPJ,P
WHERE SPJ.PNO = P.PNO AND SPJ.JNO = 'J1'
GROUP BY PNAME
ORDER BY SUM_QTY DESC;

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

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT
    JNAME
FROM J
JOIN SPJ USING(JNO)
WHERE SPJ.PNO = 'P1'
GROUP BY JNAME
HAVING  SUM(QTY) > 300;

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

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

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

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

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT JNO
FROM (
    SELECT JNO,AVG(QTY) JQ
    FROM SPJ
    WHERE PNO = 'P3'
    GROUP BY JNO
) T1
WHERE JQ >ANY(
    SELECT PQ
    FROM(
        SELECT MAX(QTY) PQ
        FROM SPJ
        WHERE JNO ='J1'
        GROUP BY PNO
    ) T2
);

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

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT DISTINCT JNO
FROM (
    SELECT JNO,AVG(QTY) JQ
    FROM SPJ
    WHERE PNO = 'P3'
    GROUP BY JNO
) T1
JOIN (
    SELECT MAX(QTY) PQ
    FROM SPJ
    WHERE JNO ='J1'
    GROUP BY PNO
) T2
ON T1.JQ > T2.PQ

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

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

USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT JNO
FROM (
    SELECT JNO,AVG(QTY) JQ
    FROM SPJ
    WHERE PNO='P3'
    GROUP BY JN
) T1
WHERE JQ>=(
    SELECT MIN(PQ)
    FROM (
        SELECT SUM(QTY) PQ
        FROM SPJ
        WHERE JNO = 'J1'
        GROUP BY PNO
    ) T2
)

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