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

there are 3 tuples, but finally they are selecting only SID's of those tuples.
in the 3 tuples, two tuples are having same SID (i.e., SID = 1 )

SELECT statement by default doesn't eliminate the duplicates ===> should be 3 tuples.

---

so according to u their answer is wrong ?

and pls clear this doubt --this is a nested query so which one is outer table and which one is inner?

i mean to say there are two for loops required in query evaluation.  so which table is taken as outer table ?

If we take parts as outer table we might get 2 tuples as answer.

---

From parts P, Catalog C  ===> it forms a temporary table which acts as a outer table.

From parts P2, Catalog C2  ===> it forms a temporary table which acts as a inner table.

 

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

---

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.