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

第10关-将SPJ表按QTY值降序排列，再找出SPJ表中前6条记录(用limit 6)-107092195
USE mydata;
#请在此处添加实现代码
########## Begin ##########
select SNO,PNO,JNO,QTY
from SPJ
order by QTY desc
limit 6

########## End ##########
1
2
3
4
5
6
7
8
9
10

第11关-找出供应零件总数量不低于1000的供应商号码，及每个供应商供应的总数量，并且结果按总数量降序排列-107092196
USE mydata;
#请在此处添加实现代码
########## Begin ##########
select SNO,SUM(QTY) SUM_QTY
from SPJ
group by SNO
having SUM_QTY>=1000
order by SUM_QTY desc

########## End ##########
1
2
3
4
5
6
7
8
9
10
11

第12关-找出工程项目J1使用的各种零件的名称及其数量-107092197
USE mydata;
#请在此处添加实现代码
########## Begin ##########
select PNAME,SUM(QTY) SUM_QTY
from P,SPJ
where P.PNO=SPJ.PNO and SPJ.JNO='J1'
group by P.PNO
order by SUM_QTY desc;

########## End ##########
1
2
3
4
5
6
7
8
9
10
11

第13关-求使用了300个及以上P1零件的工程名称-107092198
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 ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

第14关-求各工程（名）使用的各城市供应的零件总数-107092199
USE mydata;
#请在此处添加实现代码
########## Begin ##########
select JNAME,S.CITY,SUM(QTY) SUM_QTY
from J,S,SPJ 
where J.JNO=SPJ.JNO
and S.SNO=SPJ.SNO
group by J.JNO,S.CITY
order by JNAME desc,CITY desc;

########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12

第15关-用嵌套查询这样的工程：供给该工程的零件P3的平均供应量，大于其中一种供给工程J1的零件的最大供应量-107092200
USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT JNO
FROM(
    SELECT JNO, AVG(QTY) AS AVG_Q
    FROM SPJ
    WHERE PNO = 'P3'
    GROUP BY JNO
) V1
WHERE AVG_Q > (
    SELECT MIN(MAX_QTY)
    FROM(
        SELECT MAX(QTY) AS MAX_QTY
        FROM SPJ
        WHERE JNO = 'J1'
        GROUP BY PNO
    ) V2
);
########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

第16关-用链接查询这样的工程：供给该工程的零件P3的平均供应量，大于其中一种供给工程J1的零件的最大供应量-107092201
USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT Distinct view1.JNO
FROM (
    SELECT JNO, AVG(QTY) AQ
    FROM SPJ
    WHERE PNO = 'P3'
    GROUP BY JNO
) view1
JOIN (
    SELECT MAX(QTY) MQ
    FROM SPJ
    WHERE JNO = 'J1'
    GROUP BY PNO
) view2 ON view1.AQ > view2.MQ;

########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

第17关-查询这样的工程号：供应该工程零件P3的平均供应量，不小于工程J1使用各零件合计数量的最小值-107092202
USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT DISTINCT VIEW1.JNO
FROM(
    SELECT JNO,AVG(QTY) AVG_QTY
    FROM SPJ
    WHERE PNO='P3'
    GROUP BY JNO
) VIEW1
JOIN (
    SELECT SUM(QTY) SUM_QTY
    FROM SPJ
    WHERE JNO='J1'
    GROUP BY PNO
) VIEW2
ON VIEW1.AVG_QTY>=VIEW2.SUM_QTY;

########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

第1关-求各颜色零件的平均重量-107092186
USE mydata;
#请在此处添加实现代码
########## Begin ##########
select COLOR, AVG(WEIGHT) from P group by color;

########## End ##########
1
2
3
4
5
6
7

第2关-求北京和天津供应商的总个数-107092187
USE mydata;
#请在此处添加实现代码
########## Begin ##########
select CITY,COUNT(*) from S where city in("天津","北京") group by city;

########## End ##########
1
2
3
4
5
6
7

第3关-求各供应商供应的零件总数-107092188
USE mydata;
#请在此处添加实现代码
########## Begin ##########
select SNO, SUM(QTY) SUM_QTY from SPJ
group by SNO
order by SUM_QTY DESC;
########## End ##########
1
2
3
4
5
6
7
8

第4关-求各供应商供应给各工程的零件总数-107092189
USE mydata;
#请在此处添加实现代码
########## Begin ##########
select SNO,JNO,SUM(QTY) SUM_QTY from SPJ
group by SNO,JNO
order by SNO DESC,JNO DESC;
########## End ##########
1
2
3
4
5
6
7
8

第5关-求重量大于所有零件平均重量的零件名称-107092190
USE mydata;
#请在此处添加实现代码
########## Begin ##########
select PNAME from P
where(
    select avg(weight) from P having weight>avg(weight)
    )

########## End ##########
1
2
3
4
5
6
7
8
9
10

第6关-查询供应了1000个以上零件的供应商名称-107092191
USE mydata;
#请在此处添加实现代码
########## Begin ##########
SELECT SNAME 
FROM S
WHERE SNO IN (
    SELECT SNO 
    FROM SPJ 
    GROUP BY SNO 
    HAVING SUM(QTY) > 1000
)
ORDER BY SNAME DESC;

########## End ##########
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

第7关-统计P表中颜色为蓝色的零件个数，并指定该查询列的名称为“蓝色零件数”-107092192
USE mydata;
#请在此处添加实现代码
########## Begin ##########
select count(*) `蓝色零件数` 
from P
where color='蓝'
group by color;

########## End ##########
1
2
3
4
5
6
7
8
9
10

第8关-查询P表中各零件的编号，名称及重量按85%计算后的信息，其中重量按85%计算后的查询列名改为“零件净重”-107092193
USE mydata;
#请在此处添加实现代码
########## Begin ##########
select PNO,PNAME,0.85*weight `零件净重`
from P


########## End ##########
1
2
3
4
5
6
7
8
9

第9关-查询 S表STATUS值大于20且小于50，或SNAME字段值的第一个字为“精”或第三个字为“益”或“民”的供应-107092194
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 ##########
1
2
3
4
5
6
7
8
9