Number of Centuries

Question: You have 2 tables as follows:

create table table1(player varchar2(10), ground_name varchar2(10), num_centuries number);

create table table2(ground_name varchar2(10), country varchar2(10));

Write a SQL to list the player names who have made centuries in every country

Solution:

select p.player

from

(select count(distinct country) cnt from table2) g,

(select player,count(distinct table2.country) cnt from table1,table2 where num_centuries>0

 and table1.ground_name=table2.ground_name

group by player) p

where g.cnt=p.cnt