Supplier(Sno, Sname)
Part ( Pno, Pname )
Project (Jno, Jname)
Supply (Sno, Pno, Jno)

I need to write a query to:

retrieve the part numbers that are supplied to exactly to exactly two projects

what I wrote was:

SELECT Pno FROM Supply WHERE (SELECT COUNT(Pno) from Supply)=2;

I do however think this is wrong as when I run a similar query on a table I have, it returns an empty set ... I essentially just want to pull the elements that their are 2 of in a specified column.

your subquery counts the overall number of rows in the supply table

so unless there are exactly two rows in that table, the outer query returns nada, squadoosh, no results

I have worked out the answer:

SELECT Pno FROM Supply
GROUP by Pno
HAVING (COUNT (Pno) = 2)

thanks

yeah, that's it

although, as a matter of coding style, the parentheses are unnecessary, and of course since Pno is part of that table's primary key, it can't be null, so you can just count rows instead of counting non-null values...

HAVING COUNT(*) = 2