/a/jaguar/irazu/home/morreeuw/oracle :source .oracle ORACLE_SID = [morreeuw] ? iup2 ORACLE_HOME = [/opt/oracle/app/oracle/product/7.3.2] ? /a/jaguar/irazu/home/morreeuw/oracle :sqlplus SQL*Plus: Release 3.3.2.0.0 - Production on Mon Apr 19 14:54:34 1997 Copyright (c) Oracle Corporation 1979, 1994. All rights reserved. Enter user-name: morreeuw Enter password: ******* Error accessing PRODUCT_USER_PROFILE Warning: Product user profile information not loaded! You may need to run PUPBLD.SQL as SYSTEM Connected to: Oracle7 Server Release 7.3.2.1.0 - Production Release With the distributed, replication, parallel query and Spatial Data options PL/SQL Release 2.3.2.0.0 - Production SQL> create table PILOTE 2 ( PLnum number(3) primary key, 3 PLNOM char(30), 4 VILLE char(30) 5 ); Table created. SQL> create table AVION 2 ( AVnum number(3) primary key, 3 AVNOM char(10), 4 VILLE char(30) 5 ); Table created. SQL> create table VOL 2 ( VOLnum char(10) primary key, 3 PLnum number(3), 4 AVnum number(3), 5 VD char(30), 6 VA char(30), 7 HD number(2), 8 HA number(2) 9 ); Table created. SQL> insert into PILOTE values (1, 'SERGE', 'NICE'); 1 row created. SQL> insert into PILOTE values (2, 'JEAN', 'PARIS'); 1 row created. SQL> insert into PILOTE values (3, 'CLAUDE', 'GRENOBLE'); 1 row created. SQL> insert into PILOTE values (4, 'ROBERT', 'TOULOUSE'); 1 row created. SQL> insert into PILOTE values (5, 'MICHEL', 'PARIS'); 1 row created. SQL> insert into AVION values (100, 'A300', 'NICE'); 1 row created. SQL> insert into AVION values (101, 'B707', 'PARIS'); 1 row created. SQL> insert into AVION values (102, 'A300', 'TOULOUSE'); 1 row created. SQL> insert into AVION values (103, 'B727', 'TOULOUSE'); 1 row created. SQL> insert into VOL values ('IT100', 1, 100, 'NICE', 'PARIS', 7, 8); 1 row created. SQL> insert into VOL values ('IT101', 2, 100, 'PARIS', 'TOULOUSE', 11, 12); 1 row created. SQL> insert into VOL values ('IT102', 1, 101, 'PARIS', 'NICE', 12, 13); 1 row created. SQL> insert into VOL values ('IT103', 3, 102, 'GRENOBLE', 'TOULOUSE', 9, 11); 1 row created. SQL> insert into VOL values ('IT104', 3, 102, 'TOULOUSE', 'GRENOBLE', 17, 18); 1 row created. SQL> select * from PILOTE; PLNUM PLNOM VILLE ---------- ------------------------------ ------------------------------ 1 SERGE NICE 2 JEAN PARIS 3 CLAUDE GRENOBLE 4 ROBERT TOULOUSE 5 MICHEL PARIS SQL> select * from AVION; AVNUM AVNOM VILLE ---------- ---------- ------------------------------ 100 A300 NICE 101 B707 PARIS 102 A300 TOULOUSE 103 B727 TOULOUSE SQL> select * from VOL; VOLNUM PLNUM AVNUM VD ---------- ---------- ---------- ------------------------------ VA HD HA ------------------------------ ---------- ---------- IT100 1 100 NICE PARIS 7 8 IT101 2 100 PARIS TOULOUSE 11 12 IT102 1 101 PARIS NICE 12 13 VOLNUM PLNUM AVNUM VD ---------- ---------- ---------- ------------------------------ VA HD HA ------------------------------ ---------- ---------- IT103 3 102 GRENOBLE TOULOUSE 9 11 IT104 3 102 TOULOUSE GRENOBLE 17 18 SQL> select distinct PLNOM 2 from PILOTE, VOL 3 where VD = 'PARIS' 4 and VOL.PLnum = PILOTE.PLnum; PLNOM ------------------------------ JEAN SERGE SQL> select distinct PLNOM 2 from PILOTE, AVION, VOL 3 where AVNOM = 'A300' 4 and AVION.AVnum = VOL.AVnum 5 and PILOTE.PLnum = VOL.PLnum; PLNOM ------------------------------ CLAUDE JEAN SERGE SQL> select distinct PLNOM 2 from PILOTE 3 where PLnum in (select PLnum 4 from VOL 5 where AVnum in (select AVnum 6 from AVION 7 where AVNOM = 'A300')); PLNOM ------------------------------ CLAUDE JEAN SERGE SQL> select distinct PLNOM 2 from PILOTE, VOL 3 where PILOTE.PLnum = VOL.PLnum 4 and AVnum in (select AVnum 5 from AVION 6 where AVnum = VOL.AVnum 7 and AVNOM = 'A300'); PLNOM ------------------------------ CLAUDE JEAN SERGE SQL> select distinct PLNOM 2 from PILOTE 3 where exists (select * 4 from VOL, AVION 5 where AVNOM = 'A300' 6 and AVION.AVnum = VOL.AVnum 7 and PILOTE.PLnum = VOL.PLnum); PLNOM ------------------------------ CLAUDE JEAN SERGE SQL> select distinct PLNOM 2 from PILOTE, AVION 3 where AVNOM = 'A300' 4 and exists (select * 5 from VOL 6 where AVION.AVnum = VOL.AVnum 7 and PILOTE.PLnum = VOL.PLnum); PLNOM ------------------------------ CLAUDE JEAN SERGE SQL> select distinct PLNOM 2 from PILOTE, AVION 3 where AVNOM = 'A300' 4 and AVION.VILLE = PILOTE.VILLE; PLNOM ------------------------------ SERGE ROBERT SQL> select distinct PLNOM 2 from PILOTE, AVION, VOL 3 where PILOTE.PLnum = VOL.PLnum 4 and AVION.AVnum = VOL.AVnum 5 and AVION.VILLE = PILOTE.VILLE 6 and AVNOM = 'A300'; PLNOM ------------------------------ SERGE SQL> select distinct PLNOM 2 from PILOTE, AVION AVION1, VOL 3 where PILOTE.PLnum = VOL.PLnum 4 and AVION1.AVnum = VOL.AVnum 5 and AVION1.AVNOM = 'A300' 6 and exists (select * 7 from AVION AVION2 8 where AVION2.AVNOM = 'A300' 9 and AVION2.VILLE = PILOTE.VILLE); PLNOM ------------------------------ SERGE SQL> select distinct VOL1.VD, VOL1.VA 2 from AVION, VOL VOL1 3 where AVNOM = 'A300' 4 and AVION.AVnum = VOL1.AVnum 5 and VOL1.HD >= 12 and VOL1.HA <= 18 6 and 2 <= (select count(distinct VOL2.PLnum) 7 from VOL VOL2 8 where VOL2.AVnum = VOL1.AVnum); no rows selected SQL> select distinct VOL1.VD, VOL1.VA 2 from AVION, VOL VOL1 3 where AVNOM = 'A300' 4 and AVION.AVnum = VOL1.AVnum 5 and VOL1.HD >= 12 and VOL1.HA <= 18 6 and 1 <= (select count(distinct VOL2.PLnum) 7 from VOL VOL2 8 where VOL2.AVnum = VOL1.AVnum); VD VA ------------------------------ ------------------------------ TOULOUSE GRENOBLE SQL> select distinct VOL1.VD, VOL1.VA 2 from AVION, VOL VOL1 3 where AVNOM = 'A300' 4 and AVION.AVnum = VOL1.AVnum 5 and AVION.AVnum in (select distinct VOL2.AVnum 6 from VOL VOL2 7 where VOL2.HD >= 12 and VOL2.HA <= 18) 8 and 2 <= (select count(distinct VOL3.PLnum) 9 from VOL VOL3 10 where VOL3.AVnum = VOL1.AVnum); no rows selected SQL> select distinct VOL1.VD, VOL1.VA 2 from AVION, VOL VOL1 3 where AVNOM = 'A300' 4 and AVION.AVnum = VOL1.AVnum 5 and AVION.AVnum in (select distinct VOL2.AVnum 6 from VOL VOL2 7 where VOL2.HD >= 12 and VOL2.HA <= 18) 8 and 1 <= (select count(distinct VOL3.PLnum) 9 from VOL VOL3 10 where VOL3.AVnum = VOL1.AVnum); VD VA ------------------------------ ------------------------------ GRENOBLE TOULOUSE TOULOUSE GRENOBLE SQL> select distinct AVION.VILLE as AVILLE, PILOTE.VILLE as PVILLE 2 from AVION, PILOTE, VOL 3 where VOL.AVnum = AVION.AVnum 4 and VOL.PLnum = PILOTE.PLnum; AVILLE PVILLE ------------------------------ ------------------------------ NICE NICE NICE PARIS PARIS NICE TOULOUSE GRENOBLE SQL> select distinct PILOTE.PLnum 2 from PILOTE 3 where not exists (select * 4 from AVION 5 where AVNOM = 'A300' 6 and not exists (select * 7 from VOL 8 where VOL.AVnum = AVION.AVnum 9 and VOL.PLnum = PILOTE.PLnum)); no rows selected SQL> select distinct PILOTE.PLnum 2 from PILOTE 3 where not exists (select * 4 from AVION 5 where AVNOM = 'B707' 6 and not exists (select * 7 from VOL 8 where VOL.AVnum = AVION.AVnum 9 and VOL.PLnum = PILOTE.PLnum)); PLNUM ---------- 1 SQL> select distinct PILOTE.PLnum 2 from PILOTE, VOL VOL1 3 where PILOTE.PLnum = VOL1.PLnum 4 and VOL1.HA = (select max(VOL2.HA) 5 from VOL VOL2 6 where VOL1.PLnum = VOL2.PLnum) 7 and VOL1.VA <> VILLE; PLNUM ---------- 2 SQL>