1. 查询重量最重的零件的编号
2. 查询为在北京的工程提供零件的供应商代号。
3. 查询所需零件总数大于1000的工程编号。
4. 查询同时为工程J1和J2提供零件的供应者的代号。
select pno from p where pweight>=all(select pweight from p); select distinct spj.sno from j,spj where j.jno=spj.Jno and j.Jcity='北京'; select jno from spj group by jno having sum(quantity)>1000; select sno from spj where jno='j1' and sno in (select sno from spj where jno='j2') ;
对应关系
供应商（编号，名称，所在城市）
零件（编号，零件名，颜色，重量）
工程（编号，工程名，所在城市）
供应关系（供应商代号，零件号，工程编号,零件数量）
mysql> select * from j; +-----+-------+--------+ | Jno | Jname | Jcity | +-----+-------+--------+ | J1 | JN1 | 成都 | | J2 | JN2 | 北京 | | J3 | JN3 | 北京 | | J4 | JN4 | 成都 | | J5 | JN5 | 成都 | | J6 | JN6 | 武汉 | | J7 | JN7 | 成都 | +-----+-------+--------+ 7 rows in set (0.00 sec) mysql> select * from s; +-----+-------+--------+ | Sno | Sname | Scity | +-----+-------+--------+ | S1 | N1 | 成都 | | S2 | N2 | 北京 | | S3 | N3 | 北京 | | S4 | N4 | 成都 | | S5 | N5 | 南京 | +-----+-------+--------+ 5 rows in set (0.00 sec) mysql> select * from p; +-----+-------+--------+---------+ | Pno | Pname | Pcolor | Pweight | +-----+-------+--------+---------+ | P1 | PN1 | 红 | 12 | | P2 | PN2 | 绿 | 18 | | P3 | PN3 | 蓝 | 20 | | P4 | PN4 | 红 | 13 | | P5 | PN5 | 蓝 | 11 | | P6 | PN6 | 红 | 15 | +-----+-------+--------+---------+ 6 rows in set (0.00 sec) mysql> select * from spj; +-----+-----+-----+----------+ | Sno | Pno | Jno | Quantity | +-----+-----+-----+----------+ | S1 | P1 | J1 | 200 | | S1 | P1 | J4 | 700 | | S2 | P3 | J1 | 400 | | S2 | P3 | J2 | 200 | | S2 | P3 | J3 | 200 | | S2 | P3 | J4 | 500 | | S2 | P3 | J5 | 600 | | S2 | P3 | J6 | 400 | | S2 | P3 | J7 | 800 | | S2 | P5 | J2 | 100 | | S3 | P3 | J1 | 200 | | S3 | P4 | J2 | 500 | | S4 | P6 | J3 | 300 | | S4 | P6 | J7 | 300 | | S5 | P2 | J4 | 100 | | S5 | P3 | J4 | 1200 | +-----+-----+-----+----------+
