数据库概论——作业四

Homework4

Based on MarkDown, 推荐使用markdown阅读器,也可在线查看

S(SNO, SNAME, CITY)
P(PNO, PNAME, COLOR, PRICE) 
J(JNO, JNAME,CITY) 
SPJ(SNO, PNO, JNO, QTY) 
S表示供应商,各属性依次为供应商号,供应商名,供应商所在城市; 
P表示零件,各属性依次为零件号,零件名,零件颜色,零件价格; 
J表示工程,各属性依次为工程号,工程名,工程所在城市; 
SPJ表示供货关系,各属性依次为供应商号,零件号,工程号,供货数量
请基于以上SPJ关系模式用SQL完成下面的操作: 

  1. 求供应了p1和p2两种零件的供应商姓名。 
  2. 求没有供应p1和p2两种零件的供应商姓名。 
  3. 列出所有供应商的信息,包括供应商姓名、所供应的零件名(没有供应零件的供应商也要列出,最后结果中不要出现重复行) 
  4. 求只向与自己位于不同城市的工程供应零件的供应商姓名。 
  5. 求供应了所有红色零件的供应商姓名。
  6. 列出每个城市的工程所使用的零件总的数量。 
  7. 求每个城市中供应零件数量最多的供应商姓名。
  8. 求所供应零件完全相同的供应商对
  1. select SNAME 
    from S
    where SNO in (
        select SPJ.SNO SNO from SPJ, P
        where P.PNO=SPJ.PNO
            and P.PNAME='p1'
    )
        and SNO in(
            select SPJ.SNO SNO from SPJ, P
            where P.SNO=SPJ.PNO
                and P.PNAME='p2'
        )
  2. select SNAME from S
    where SNO not in(
        select SPJ.SNO SNO from SPJ, P
        where P.PNO=SPJ.PNO 
        and (
            P.PNAME='p1'
         or 
            P.PNAME='p2'
        )
    )
  3. select distinct SNAME, PNAME 
    from S left outer join (
        select SPJ.SNO, P.PNAME 
        from P, SPJ
        where P.PNO=SPJ.PNO 
    ) as SP
    on S.SNO=SP.SNO
  4. select distinct SNAME
    from S, SPJ
    where S.SNO=SPJ.SNO 
        and SNO not in (
            select distinct S.SNO
            from S, SPJ, J
            where S.SNO=SPJ.SNO
                and J.JNO=SPJ.JNO
                and S.CITY=J.CITY
        )
  5. select distinct SNAME
    from S
    where not exists(
        select PNO
        from P
        where COLOR='红色'
        and not exists(
            select SNO, PNO
            from SPJ
            where P.PNO=SPJ.PNO
                and S.SNO=SPJ.SNO
        )
    )
  6. select SUM(QTY), J.CITY
    from SPJ, J
    where SPJ.JNO=J.JNO
    group by J.CITY
  7. select SNAME, CITY
    from S, SPJ
    where S.SNO=SPJ.SNO
    group by CITY, S.SNO, SNAME
    having sum(QTY)>=ALL(
        select sum(t.QTY)
        from S m, SPJ t
        where t.SNO=m.SNO
            and m.CITY=S.CITY
        group by m.CITY, t.SNO
    )
  8. select S1.SNAME NAME1, S2.SNAME NAME2
    from S S1, S S2, SPJ
    where S1.SNO<>S2.SNO 
        and not exists(
            select *
            from SPJ SPJ1
            where SPJ1.SNO=S1.SNO
                and not exists(
                    select *
                    from SPJ
                    where SPJ.SNO=S2.SNO 
                        and SPJ.PNO=SPJ1.PNO
                )
        ) and not exists(
            select *
            from SPJ SPJ2
            where SPJ SPJ2
            where SPJ2.SNO=S2.SNO
                and not exists(
                    select *
                    from SPJ
                    where SPJ.SNO=S1.SNO
                        and SPJ.PNO=SPJ2.PNO
                )
        )
最后修改:2019 年 08 月 14 日 08 : 44 PM
如果觉得我的文章对你有用,请随意赞赏

发表评论