--Using nested SELECT/zh


 --1
select name
from world
where continent = (select continent
                   from world
                   where name = 'Brazil');


 --2
select 
name 
,continent
from world
where continent in ((select continent 
                     from world
                     where name = 'Brazil'),
                    (select continent
                     from world
                     where name = 'Mexico'));
/*另一种解法*/
select 
name 
,continent 
from world 
where continent in (select continent
                    from world 
                    where name = 'Brazil'
                    or name = 'Mexico');


 --3
select population/(select population
                   from world 
                   where name = 'United Kingdom')
from world
where name = 'China';


 --4
select name
from world
where population > all(select population 
                       from world 
                       where continent = 'Europe');


--SELECT within SELECT Tutorial


 --1
select name
from world
where population > (select population 
                  from world 
                  where name = 'Russia');


 --2
select name 
from world 
where continent = 'Europe' 
and gdp/population > (select gdp/population 
                     from world 
                     where name = 'United Kingdom');


 --3
select 
name 
,continent 
from world 
where continent in 
(
  (
    select continent 
        from world 
        where name = 'Argentina'
    )
,
    (
      select continent
        from world 
        where name = 'Australia'
    )
)
order by name;


 --4
select 
name 
,population
from world
where population between 
(
  select population 
    from world 
    where name = 'Poland'
) 
and 
(
  select population 
    from world
    where name = 'Canada'
);
/*介于两个值之间用between and ,大的值在and前，小的值在and后*/


 --5
select 
name 
,concat
(round(population/(select population 
                from world 
                  where name = 'Germany')*100,0),'%')
from world
where continent = 'Europe';
/*没有直接用给出的德国人口数*/


 --6
select name
from world
where gdp > all(select gdp 
                from world 
                where continent = 'Europe'
                and gdp is not null);
/*判空请用 is not null,不要使用!=和<>永远会返回0行，却不会提示语法错误的，所以我们要牢记：默认情况下做比较条件时使用关键字“is null”和“is not null”*/
/*另一种解法*/
select name
from world
where gdp > all(select gdp 
                from world 
                where continent = 'Europe'
                and gdp > 0);


 --7
select 
continent 
,name 
,area 
from world x
where area >=all(select area
                 from world y
                 where y.continent = x.continent
                 and area is not null);
/*此题用到了关联子查询，关联子查询首先依靠子查询中的关联语句，从主查询中选取关联属性的第一个值，进入子查询中，然后将子查询结果返回主查询，判断where条件，依次类推，直到主查询中关联属性的值选取完毕。*/


 --8
select 
continent  
,name  
from world x
where name <= all(select name
                  from world y
                  where y.continent = x.continent);


 --9
select 
name 
,continent 
,population 
from world x
where 25000000 >= all(select population
                   from world y
                   where y.continent = x.continent);
/*这道题看英文，中文翻译的不是Humanlanguage啊*/


 --10
select 
name 
,continent 
from world x
where population/3 >= all(select population
                  from world y
                  where y.continent = x.continent
                  and y.name != x.name);
/*这道题别忘了把所选国家自己排除掉！*/


--The nobel table can be used to practice more subquery./zh


 --1
select 
winner 
,yr 
from nobel
where yr in (select yr
            from nobel
            where winner = 'International Committee of the Red Cross')
           and subject = 'Literature';
/*这道题刚开始报错，想了好久，获奖可能不止一次，所以年份可能不唯一，这里要用in，而不是=，请注意in和=的区别*/


 --2
select winner
from nobel
where yr in (select yr
             from nobel
             where winner = 'Toshihide Maskawa')
and subject = 'physics'
and winner != 'Toshihide Maskawa';
/*别忘了把Toshihide Maskawa排除！*/


 --3
select winner
from nobel x
where subject = 'Economics'
and yr <= all(select yr
              from nobel y
              where y.subject = x.subject);


 --4
select distinct yr 
from nobel 
where subject = 'physics'
and yr not in (select yr
               from nobel
               where subject = 'chemistry');
/*别忘了年份去重*/


 --5
select 
yr 
,subject 
,winner
from nobel
where yr in(select yr 
            from nobel
            group by yr
            having count(winner) > 12);
/*可能不止一年里得奖人数超过12人，所以要用in*/


 --6
select 
winner
,yr 
,subject
from nobel
where winner in (select winner 
               from nobel
               group by winner
               having count(winner) >1)
order by winner, yr;
/*可能不止一人，获奖超过1次，所以用in*/