Need a favour in understanding the following query

From: Ramu <haisubbu_at_fastmail.fm>
Date: 6 Feb 2006 22:01:11 -0800
Message-ID: <1139292071.308861.151890@g43g2000cwa.googlegroups.com>

I have a problem while understanding the query.....I want to explain it clearly.....

My Tables are

1.suppliers
2.parts
3.catalog

The tables and the records are looks like this

---

suppliers

---

create table suppliers(

	sid number(9,0) primary key,
	sname varchar2(30),
	address varchar2(40)


	);

Records in Suppliers Table

1,'Acme Widget Suppliers','1 Grub St., Potemkin Village, IL 61801'
2,'Big Red Tool and Die','4 My Way, Bermuda Shorts, OR 90305'
3,'Perfunctory Parts','99999 Short Pier, Terra Del Fuego, TX 41299'
4,'Alien Aircaft Inc.','2 Groom Lake, Rachel, NV 51902'

parts

---

Creating Table Parts

create table parts(

	pid number(9,0) primary key,
	pname varchar2(40),
	color varchar2(15)


	);

Records In Parts Table

1,'Left Handed Bacon Stretcher Cover','red'
2,'Smoke Shifter End','black'
3,'Acme Widget Washer','red'
4,'Acme Widget Washer','silver'
5,'I Brake for Crop Circles Sticker','translucent'
6,'Anti-Gravity Turbine Generator','cyan'
7,'Anti-Gravity Turbine Generator','magenta'
8,'Fire Hydrant Cap','red'
9,'7 Segment Display','green'

catalog

---

create table catalog(

	sid number(9,0),
	pid number(9,0),
	cost number(10,2),
	primary key(sid,pid),
	foreign key(sid) references suppliers,
	foreign key(pid) references parts


	);

Records In Catalogue Table

1,3,0.50
1,4,0.50
1,8,11.70
2,3,0.55
2,8,7.95
2,1,16.50
3,8,12.50
3,9,1.00
4,5,2.20
4,6,1247548.23
4,7,1247548.23

The problem Is
 to find the SIDs of the suppliers who supply every RED Part

The Query for this is

select c.sid from catalog c where not exists(select p.pid from parts p where p.color ='red' and not exists(select c1.sid from catalog c1 where c1.sid=c.sid and c1.pid=p.pid))

The answer is SID-2

Now i need a favor here in this question from you......

1.Please kindly tell me the result set of the third subquery 2.Please kindly tell me the result set of the second subquery where we placed the color='red' statement
3.Please tell me whether the second subquery where we placed c1.sid=c.sid and c1.pid=p.pid,fetches any records from the first sub query,and if so what are the records used in the second subquery for comparision....(c1.pid=p.pid)
4.Please explain me the query(if possible),mentioning how not exists worked, with a little paragraph..

And i am also attaching the tables and records by a file....please kindly find it....

And i am requesting you please help me....By explaining the query....I am very thankful to you Received on Tue Feb 07 2006 - 00:01:11 CST