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 |
+-----+-----+-----+----------+