cat > script <<'+-+-+-+-+-END-OF-FILE-+-+-+-+-+'
create p (pno integer);
create s (sno integer, sname integer);
create sp (sno integer, pno integer);
insert into p values (1, 2, 3, 4, 5, 6);
insert into s values (1, 10, 2, 20, 3, 30, 4, 40, 5, 50);
insert into sp values (1, 1, 1, 2, 1, 3, 1, 4, 1, 5, 1, 6,
	2, 1, 2, 2, 3, 2, 4, 2, 4, 4, 4, 5);

/* find all the part not supplied by supplier specified */
select p.pno from p where not exists
    (select sp.sno from sp where sp.sno = 1  and  p.pno = sp.pno);
select p.pno from p where not exists
    (select sp.sno from sp where sp.sno = 2  and  p.pno = sp.pno);

/* find the name of the supplier that supply all the part */
select s.sname from s where not exists
    (select p.pno from p where not exists
	(select sp.sno from sp where s.sno = sp.sno  and  p.pno = sp.pno));
+-+-+-+-+-END-OF-FILE-+-+-+-+-+
chmod 644 script
cat > stdout <<'+-+-+-+-+-END-OF-FILE-+-+-+-+-+'
* create p (pno integer);
* create s (sno integer, sname integer);
* create sp (sno integer, pno integer);
* insert into p values (1, 2, 3, 4, 5, 6);
* insert into s values (1, 10, 2, 20, 3, 30, 4, 40, 5, 50);
* insert into sp values (1, 1, 1, 2, 1, 3, 1, 4, 1, 5, 1, 6,
. 	2, 1, 2, 2, 3, 2, 4, 2, 4, 4, 4, 5);
* 
* /* find all the part not supplied by supplier specified */
. select p.pno from p where not exists
.     (select sp.sno from sp where sp.sno = 1  and  p.pno = sp.pno);
   pno

* select p.pno from p where not exists
.     (select sp.sno from sp where sp.sno = 2  and  p.pno = sp.pno);
   pno

     3
     4
     5
     6
* 
* /* find the name of the supplier that supply all the part */
. select s.sname from s where not exists
.     (select p.pno from p where not exists
. 	(select sp.sno from sp where s.sno = sp.sno  and  p.pno = sp.pno));
 sname

    10
* 
+-+-+-+-+-END-OF-FILE-+-+-+-+-+
chmod 644 stdout
cat > tag <<'+-+-+-+-+-END-OF-FILE-+-+-+-+-+'
Test nested select 2 level deep - the supplier/part example
+-+-+-+-+-END-OF-FILE-+-+-+-+-+
chmod 644 tag
