数据查询

1.找出工程项目J2使用的各种零件的名称及其数量

select pname,qty

from spj,p

where spj.pno=p.pno and jno='j2' 

2. 找出使用上海产的零件的工程名称

         select jname

         from spj,s,j

        where spj.sno=s.sno and s.city='上海' and spj.jno=j.jno

3. 找出供应工程J1零件为红色的供应商号码

     select sno

    from spj,p

    where spj.pno=p.pno and p.color='红' and spj.jno='J1'

4. 找出没有使用天津供应商生产的红色零件的工程名称

      select distinct jname

from spj,s,p,j

where spj.sno=s.sno and spj.pno=p.pno and spj.jno=j.jno and spj.pno != all

        (select spj.pno

    from spj,s,p

 where spj.sno=s.sno and spj.pno=p.pno and s.city='天津'

       and p.color='红')

5.

2. 找出提供零件种类超过了2种的供应商号码

      select spj.sno

from spj

group by sno

having count(jno)>=2

6.

找出为3个以上的工程提供零件的供应商名称

    select s.sname

from spj,s

where spj.sno=s.sno

group by s.sname

having count(jno)>=3