数据库实验3答案_基于派生表查询每个队员解答中超过他平均memory的user_id及题目编号problem_id(查

数据库实验3答案

前言

没啥意思，就是想帮助到同学们，当然希望不要直接抄。

1

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

select PNAME from P where COLOR = '红' and WEIGHT >= 15;
1

2

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

SELECT JNO, JNAME, CITY FROM J  WHERE J.JNAME LIKE '%厂%';
1

3

求各颜色零件的平均重量

SELECT COLOR, AVG(P.WEIGHT) FROM P GROUP BY COLOR;
1

4

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

SELECT CITY, COUNT(*) FROM S WHERE CITY IN ('北京', '天津') GROUP BY CITY;
1

5

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

SELECT SNO, SUM(QTY) SUM_QTY FROM SPJ GROUP BY SNO ORDER BY SUM_QTY DESC;
1

6

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

SELECT SNO, JNO, SUM(QTY) SUM_QTY FROM SPJ GROUP BY SNO, JNO ORDER BY SNO DESC, JNO DESC;
1

7

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

SELECT PNAME FROM P WHERE (SELECT AVG(WEIGHT) AS AW FROM P HAVING WEIGHT > AW);
1

8

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

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

9

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

select count(pno) as '蓝色零件数' from P where COLOR = '蓝';
1

10

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

select PNO, PNAME, WEIGHT * 0.85 as '零件净重' from P;
1

11

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

select SNO, SNAME, STATUS, CITY from S where STATUS > 20 and STATUS < 50
                                          or SNAME like '精%' or SNAME like '__益' or SNAME like '__民';
1
2

12

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

SELECT SNO, PNO, JNO, QTY FROM SPJ ORDER BY QTY DESC LIMIT 6;
1

13

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

SELECT SNO, SUM(QTY) AS SUM_QTY FROM SPJ GROUP BY SNO HAVING SUM_QTY >= 1000 ORDER BY SUM_QTY DESC ;
1

14

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

CREATE VIEW V1 AS SELECT JNO, AVG(QTY) AS AQ FROM SPJ WHERE PNO = 'P1' GROUP BY JNO;
CREATE VIEW V2 AS SELECT MAX(QTY) AS MQ FROM SPJ WHERE JNO = 'J1' GROUP BY PNO;

SELECT JNO FROM V1 WHERE AQ > (SELECT MIN(MQ) FROM V2);
1
2
3
4

15

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

select solution.user_id, problem_id from solution
join (select user_id, avg(memory) as am from solution group by user_id) uam
on solution.user_id = uam.user_id and memory > am;
1
2
3

16

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

select user_id, reg_time, name from users
where user_id like '2019%' and reg_time < any
(select reg_time from users where user_id like '2020%');
1
2
3

17

用聚集查询实现查询2019级选手（user_id前4位为2019）满足比2020级其中一个选手注册时间（reg_time）早即可的选手信息

select user_id, reg_time, name from users
where user_id like '2019%' and
      reg_time < (select max(reg_time) from users where user_id like '2020%');
1
2
3

18

用ANY/ALL实现查询2019级选手所有比2020级选手注册时间都早的选手信息

select user_id, reg_time, name from users
where user_id like '2019%' and
      reg_time < all(select reg_time from users where user_id like '2020%');
1
2
3

19

聚集查询实现查询2019级选手所有比2020级选手注册时间都早的选手信息

select user_id, reg_time, name from users
where user_id like '2019%' and
        reg_time < (select min(reg_time) from users where user_id like '2020%');
1
2
3

20 特别难

用 EXISTS 实现查询至少参与过"202002020217"选手参与过的所有比赛的选手信息，contest_id不为NULL

select u.user_id, u.reg_time, u.name from users u
where not exists(select contest_id from (select s.contest_id from solution s
    where s.user_id = '202002020217' and s.contest_id is not null) s1
    where not exists(select user_id, contest_id from solution s2
        where s1.contest_id = s2.contest_id and u.user_id = s2.user_id));
1
2
3
4
5

21

请为三建工程项目建立一个供应情况的视图V_SPQ，包括供应商代码(SNO)、零件代码(PNO)、供应数量(QTY)

create view V_SPQ as
    select SNO, PNO, QTY from SPJ, J
    where J.JNAME = '三建' and J.JNO = SPJ.JNO;
1
2
3

22

从视图V_SPQ找出三建工程项目使用的各种零件代码及其数量(SUM_QTY),结果按SUM_QTY降序排序。

select PNO, sum(QTY) as SUM_QTY from V_SPQ group by PNO order by SUM_QTY desc ;
1