sql选出同时符合多个值记录

create table test (NAME varchar(20) not null, HOBBY varchar(20) not null);
 insert into test values('Adam','basketball');
 insert into test values('Bill','basketball');
 insert into test values('Bill','football');
 insert into test values('Cyper','basketball');
 insert into test values('Cyper','badminton');
 insert into test values('David','basketball');
 insert into test values('David','badminton');
 insert into test values('David','table tennis');

使用SQL语句查出哪些人即会玩basketball又会玩badminton，找出这样的name-hobby组合。（这样表达如何？）

即第5， 6 ，7 ，8行 

 

select *
from testsql
WHERE HOBBY='basketball' and NAME in
(
      select NAME
      from testsql
      where HOBBY ='badminton'
)