Google
Information Storage and Retrieval: August 2013

Pages

Tuesday, August 20, 2013

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