made easy test series

Question

Consider the following schema:
Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
Here key fields are underlined, and the domain of each field is listed after the field name. Therefore sid is the key for Suppliers, pid is the key for Parts, and sid and pid together form the key for Catalog. The Catalog relation lists the prices charged for parts by Suppliers. Consider the table for Part and Catalog given below:

The number of tuples result by given query is  ________.
SELECT C.sid
FROM Parts P, Catalog C
WHERE P.color = ‘red’ AND P.pid = C.pid AND EXISTS (

SELECT P2.pid FROM Parts P2, Catalog C2 

WHERE P2.color = 'green'

AND C2.sid = C.sid AND P2.pid = C2.pid )

 

 

My answer is 3 but given answer is 2 . 

Here i'm not able to understand the flow of query evaluation.  i mean, this is a nested query so which one is outer table and which one is inner? and how the answer is 2.

Comments

If SID would have been Primary key OR if there is SELECT DISTINCT C.sid 

Then we will get 2 answer ..right @Shaik Masthan

---

By the way, post their solution.. I will check it!

They just mentioned ..S1 and S3 is output :)

---

@jatin khachane 1

yes, if distinct keyword used or Sid is primary key in catalog, then it is 2.