[SQL] Tricky -to me!- SQL query.

Dear All,

Consider the following table:

dev_brecard=> select * from test order by person;
person|fruit
------+---------
lucy |mandarins
lucy |tomatoes
lucy |pears
lucy |oranges
lucy |apples
peter |pears
peter |apples
peter |oranges
peter |prunes
robert|figs
robert|dates
stuart|apples
stuart|pears
stuart|prunes
stuart|bananas
stuart|kumquats
(16 rows)

(code for creating and populating table is in a PS at the end of this posting)

You can assume that the table is appropriately normalised and that there is
a composite primary key for it (i.e. each COMBINATION of person and fruit
will appear only once and neither of the fields can be NULL)

How do I select from all person who like 'pears' and 'apples' (in this
case, lucy, peter and stuart) or 'pears', 'apples' and 'oranges' (in this
case, lucy and peter)?

I re-read my SQL books but I am still somewhat stumped. Things I could
think of for that sort of query:

1) Select all persons who like 'pears'; Select all persons who like
'apples'; Select all persons who like 'oranges'; Calculate the INTERSECTION
of these sets (this sort of operation appears to use the EXISTS operator?)

2) Use nested subselects:

Select person from test where person in (
Select person from test where fruit='pears' and person in (
Select person from test where fruit='apples' and person in (
Select person from test where fruit='oranges'
)
)
)

What way do you suggest????

Also, am I storing this sort of data in to wrong kind of form (should I
somehow denormalise? if so, how?)?

Could you please cc your answers to: stuart(at)ludwig(dot)ucl(dot)ac(dot)uk

thanks for any help out there!

regards,

Stuart.

PS. Code to cut and paste for table:

create table test (person varchar(25), fruit varchar(25));
insert into test values ('stuart','apples');
insert into test values ('stuart','pears');
insert into test values ('stuart','bananas');
insert into test values ('stuart','kumquats');
insert into test values ('peter','oranges');
insert into test values ('peter','prunes');
insert into test values ('lucy','mandarins');
insert into test values ('lucy','tomatoes');
insert into test values ('peter','apples');
insert into test values ('lucy','apples');
insert into test values ('peter','pears');
insert into test values ('lucy','pears');
insert into test values ('lucy','oranges');
insert into test values ('stuart','prunes');
insert into test values ('robert','figs');
insert into test values ('robert','dates');

+-------------------------+--------------------------------------+
| Stuart Rison | Ludwig Institute for Cancer Research |
+-------------------------+ 91 Riding House Street |
| Tel. (0171) 878 4041 | London, W1P 8BT, UNITED KINGDOM. |
| Fax. (0171) 878 4040 | stuart(at)ludwig(dot)ucl(dot)ac(dot)uk |
+-------------------------+--------------------------------------+