drop table orders;
create table orders (
order_no number(10),
sc varchar2(5),
qty_supplied number(5),
o_date date,
pc varchar2(5),
supply_date date
);
insert into orders ( order_no, sc, qty_supplied, o_date, pc, supply_date)
values (1, 'S1', 300, to_date('12-JAN-1997','DD-MON-YYYY'), 'P1', to_date('12-JAN-1997','DD-MON-YYYY') );
insert into orders ( order_no, sc, qty_supplied, o_date, pc, supply_date)
values (2, 'S1', 200, to_date('12-JAN-1997','DD-MON-YYYY'), 'P2', to_date('14-JAN-1997','DD-MON-YYYY') );
insert into orders ( order_no, sc, qty_supplied, o_date, pc, supply_date)
values (3, 'S1', 400, to_date('15-JAN-1997','DD-MON-YYYY'), 'P3', to_date('25-JAN-1997','DD-MON-YYYY') );
insert into orders ( order_no, sc, qty_supplied, o_date, pc, supply_date)
values (4, 'S1', 200, to_date('18-FEB-1997','DD-MON-YYYY'), 'P4', to_date('18-FEB-1997','DD-MON-YYYY') );
insert into orders ( order_no, sc, qty_supplied, o_date, pc, supply_date)
values (5, 'S1', 100, to_date('22-MAR-1997','DD-MON-YYYY'), 'P5', to_date('23-MAR-1997','DD-MON-YYYY') );
insert into orders ( order_no, sc, qty_supplied, o_date, pc, supply_date)
values (6, 'S1', 100, to_date('25-MAR-1997','DD-MON-YYYY'), 'P6', to_date('25-MAR-1997','DD-MON-YYYY') );
insert into orders ( order_no, sc, qty_supplied, o_date, pc, supply_date)
values (7, 'S2', 300, to_date('28-MAY-1997','DD-MON-YYYY'), 'P1', to_date('02-JUN-1997','DD-MON-YYYY') );
insert into orders ( order_no, sc, qty_supplied, o_date, pc, supply_date)
values (8, 'S2', 400, to_date('23-JUN-1997','DD-MON-YYYY'), 'P2', to_date('23-JUN-1997','DD-MON-YYYY') );
insert into orders ( order_no, sc, qty_supplied, o_date, pc, supply_date)
values (9, 'S3', 200, to_date('23-OCT-1997','DD-MON-YYYY'), 'P2', to_date('23-OCT-1997','DD-MON-YYYY') );
insert into orders ( order_no, sc, qty_supplied, o_date, pc, supply_date)
values (10, 'S4', 200, to_date('14-NOV-1997','DD-MON-YYYY'), 'P2', to_date('14-NOV-1997','DD-MON-YYYY') );
insert into orders ( order_no, sc, qty_supplied, o_date, pc, supply_date)
values (11, 'S4', 300, to_date('18-DEC-1997','DD-MON-YYYY'), 'P4', null );
insert into orders ( order_no, sc, qty_supplied, o_date, pc, supply_date)
values (12, 'S4', 400, to_date('18-JAN-1998','DD-MON-YYYY'), 'P5', to_date('30-JAN-1998','DD-MON-YYYY') );
insert into orders ( order_no, sc, qty_supplied, o_date, pc, supply_date)
values (13, 'S5', 300, to_date('18-FEB-1998','DD-MON-YYYY'), 'P6', null );
commit;
-- <b>Interpretation 1:</b>
-- all suppliers without 'S2' who delivers part 'P1' and 'P2' and more
select distinct a.sc from orders a
where a.sc != 'S2'
and not exists
(
select pc from orders b
where sc = 'S2'
minus
select pc from orders b
where b.sc = a.sc
);
-- <b>Interpretation 2:</b>
-- all suppliers without 'S2' who delivers exactly part 'P1' and 'P2'
select distinct a.sc from orders a
where a.sc != 'S2'
and not exists
(
select pc from orders b
where sc = 'S2'
minus
select pc from orders b
where b.sc = a.sc
)
and not exists
(
select pc from orders b
where b.sc = a.sc
minus
select pc from orders b
where sc = 'S2'
);