MySQL 练习(SPJ 数据库)

Posted on Edited on In Views:
SPJ 数据库查询练习

SQL 查询练习

SPJ

供应商数据库SPJ中有三张表： S(SNO, SNAME, STATUS, CITY) P(PNO, PNAME, COLOR, WEIGHT, CITY) J(JNO, JNAME,CITY) SPJ(SNO, PNO, JNO, QTY, PRICE)

S表示供应商，各属性依次为供应商号，供应商名，供应商状态值，供应商所在城市； P表示零件，各属性依次为零件号，零件名，零件颜色，零件重量，零件存放的城市； J表示工程，各属性依次为工程号，工程名，工程所在城市； SPJ表示供货关系，各属性依次为供应商号，零件号，工程号，供货数量，单价。

  • 求没有供应零件号为P1和P2两种零件的供应商姓名
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
/* 1 */
select distinct SNAME
from S
where SNO not in (
    select SNO
    from SPJ
    where PNO = 'P1' or PNO = 'P2'
)

/* 2 */
select SNAME
from S
where SNO not in (
    select SNO
    from SPJ
    where PNO = 'P1' or PNO = 'P2'
)
  • 列出所有供应商的信息，包括供应商姓名、所供应的零件名（没有供应零件的供应商也要列出，最后结果中不要出现重复元组）
1
2
3
select distinct S.SNAME, P.PNAME
from S,SPJ,P
where S.SNO = SPJ.SNO and SPJ.PNO = P.PNO
  • 求只向与自己位于不同城市的工程供应零件的供应商姓名
1
2
3
4
5
6
7
select SNAME
from S
where SNAME not in (
    select SNAME
    from S,SPJ,J
    where S.SNO = SPJ.SNO and SPJ.JNO = J.JNO and S.CITY = J.CITY
)
  • 求只向与自己位于不同城市的工程供应零件的供应商姓名
1
2
3
4
5
6
7
select SNAME
from S
where SNAME not in (
    select SNAME
    from S,SPJ,J
    where S.SNO = SPJ.SNO and SPJ.JNO = J.JNO and S.CITY != J.CITY
)
  • 求供应了所有零件的供应商姓名
    • 不存在一个零件，所求的供应商没有供应
1
2
3
4
5
6
7
8
9
10
11
select SNAME
from S
where not exists (
    select PNO /* 使用了*则不可能使用索引 */
    from P
    where not exists (
        select SNO /* 使用了*则不可能使用索引 */
        from SPJ
        where S.SNO = SPJ.SNO and SPJ.PNO = P.PNO
    )
)
  • 求供应了所有红色零件的供应商姓名
1
2
3
4
5
6
7
8
9
10
11
select SNAME
from S
where not exists (
    select PNO
    from P
    where P.COLOR = '红色' and not exists (
        select SNO
        from SPJ
        where S.SNO = SPJ.SNO and SPJ.PNO = P.PNO
    )
)
  • 列出每个城市的工程所使用的零件总的数量
    • left join：可能有 0 个
1
2
3
select CITY, sum(QTY)
from J left join SPJ on J.JNO = SPJ.JNO
group by CITY
  • 按零件数量总和的降序列出每项工程所使用的每种红色零件的总的金额（工程可以向不同的供应商购买同一零件，总金额＝单价*供货数量）。输出工程号、零件号、总金额
1
2
3
4
5
select SPJ.JNO, SPJ.PNO, sum(QTY*PRICE)
from J,SPJ,P
where J.JNO = SPJ.JNO and SPJ.PNO = P.PNO and P.COLOR = '红色'
group by SPJ.JNO, SPJ.PNO
order by sum(QTY) desc
  • 求供应零件数量最多的供应商姓名
1
2
3
4
5
6
7
8
9
10
select SNAME
from S,SPJ,P
where S.SNO = SPJ.SNO and SPJ.PNO = P.PNO
group by SPJ.SNO
having sum(QTY) >= all (
    select sum(QTY)
    from S,SPJ,P
    where S.SNO = SPJ.SNO and SPJ.PNO = P.PNO
    group by SPJ.SNO
)
  • 求每个城市中供应零件数量最多的供应商姓名。
1
2
3
4
5
6
7
8
9
10
select S1.CITY, SNAME
from S S1,SPJ,P
where S1.SNO = SPJ.SNO and SPJ.PNO = P.PNO
group by SPJ.SNO, S1.CITY
having sum(QTY) >= all (
    select sum(QTY)
    from S,SPJ,P
    where S.SNO = SPJ.SNO and SPJ.PNO = P.PNO and S.CITY = S1.CITY
    group by SPJ.SNO
)
  • 列出恰好供应了相同零件的供应商对。输出的时候，供应商对小号在前，相等不输出，要去重，都没有供应零件也算供应了相同零件。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
select S1.SNAME, S2.SNAME
from (
    /* 给每一个供应商组织一个供应零件的序列 */
    select S.SNAME, group_concat(distinct P.PNO order by P.PNO) GC_PNO
    from S, SPJ, P
    where S.SNO = SPJ.SNO and SPJ.PNO = P.PNO
    group by S.SNO
) S1, (
    select S.SNAME, group_concat(distinct P.PNO order by P.PNO) GC_PNO
    from S, SPJ, P
    where S.SNO = SPJ.SNO and SPJ.PNO = P.PNO
    group by S.SNO
) S2
/* 使用小于号去重 */
where S1.GC_PNO = S2.GC_PNO and S1.SNAME < S2.SNAME