--SELECT from WORLD Tutorial


 --1
select
name
,continent
,population
from world;


 --2
select
name
from world
where population >= 200000000;


 --3
select 
name
,GDP/population as per capita GDP
from world
where population >= 200000000;


 --4
select 
name
,population/1000000
from world
where continent = 'South America';


 --5
select 
name
,population
from world
where name in ('France','Germany','Italy');


 --6
select name
from world
where name like '%United%';


 --7
select
name
,population
,area
from world
where area > 3000000 
or population > 250000000;


 --8
select 
name
,population
,area
from world
where (area > 3000000 
and population < 250000000)
or (area < 3000000 
and population > 250000000);


 --9
select 
name
,round(population/1000000,2)
,round(GDP/1000000000,2)
from world
where continent = 'South America';
/*这道题按照中文翻译会报错，英文中写明了“both”，GDP和population都要保留两位小数*/
 
 --10
select 
name
,round(GDP/population,-3) as per 
from world
where GDP >= 1000000000000;


 --11
select 
name
,capital
from world
where length(name) = length(capital);


 --12
select
name
,capital
from world
where left(name,1) = left(capital,1)
and name != capital;
/*其他解法*/
select
name
,capital
from world
where left(name,1) = left(capital,1)
and name  <> capital;


 --13
select name
from world
where length(name) - length(replace(name,'a','')) = 1
and length(name) - length(replace(name,'e','')) = 1
and length(name) - length(replace(name,'i','')) = 1
and length(name) - length(replace(name,'o','')) = 1
and length(name) - length(replace(name,'u','')) = 1
and name not like '% %' ;
/*注意题中要求每个元音字母仅有一个，划重点，然鹅有些错误答案居然也会显示correct answer，毕竟符合条件的只有Mozambique*/


--SELECT from Nobel Tutorial


 --1
select *
from nobel
where yr = 1950;


 --2
select winner
from nobel
where yr = 1962
and subject = 'Literature';


 --3
select 
yr 
,subject
from nobel
where winner = 'Albert Einstein';


 --4
select winner
from nobel
where yr >= 2000
and subject = 'Peace';


 --5
select *
from nobel
where yr between 1980 and 1989
and subject = 'Literature';


 --6
select *
from nobel
where winner in ('Theodore Roosevelt','Woodrow Wilson','Jimmy Carter');


 --7
select winner
from nobel
where winner like 'John%';


 --8
select *
from nobel
where (yr = 1980 
  and subject = 'physics')
or (yr = 1984 
    and subject = 'chemistry');
/*再次温馨提示看英文的，中文翻译不准确*/


 --9
select *
from nobel
where yr = 1980
and subject != 'Chemistry'
and subject != 'Medicine';
/*其他解法*/
select *
from nobel
where yr = 1980
and subject <> 'Chemistry'
and subject <> 'Medicine';
/*其他解法*/
select *
from nobel
where yr = 1980
and subject not in ('Chemistry','Medicine');


 --10
select *
from nobel
where (subject =  'Medicine' 
  and yr < 1910) 
or (subject = 'Literature' 
    and yr >= 2004);
/*注意 together with*/


 --11
select *
from nobel
where winner = 'PETER GRÜNBERG';


 --12
select *
from nobel
where winner = 'EUGENE O''NEILL';


 --13
select 
winner 
,yr 
,subject 
from nobel
where winner like 'Sir%'
order by yr desc,winner;
/*为啥没用select *，因为排列顺序和答案不一致会报错！*/


 --14
select 
winner 
,subject 
from nobel
where yr = 1984
order by subject in ('Chemistry','Physics'),subject,winner;
/*subject为Chemistry和Physics，值为1（排在最后），否则为o（排在最前）*/