[sqlzoo]SELECT_within_SELECT_Tutorial

SELECT within SELECT Tutorial - SQLZOO.pdf

0.71MB

sqlzoo 닷컴을 활용해서 그간 sas sql 만 활용했던 단점을 보완하려 연습중이다.

 

https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial

SELECT within SELECT Tutorial - SQLZOO

 

모든ㅔ케이스의정답은여기로

https://github.com/trinitybest/SQLZOO-answers/blob/master/4%20SELECT%20within%20SELECT.sql

Bigger than Russia

1.

List each country name where the population is larger than that of 'Russia'.

 

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

 

 

Richer than UK

2.

Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.

Per Capita GDP

 

 

SELECT name  FROM world
   where continent='Europe' and 
   gdp/population>
    (select  gdp/population FROM world
      where name='United Kingdom')

 

Neighbours of Argentina and Australia

3.

 

List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.

select name, continent   from world
 where continent in ('Argentina','Australia')
   order by name

 

 

4. between 과 반대임

Which country has a population that is more than Canada but less than Poland? Show the name and the population.

 

select name,population 
   from world
where population > (select population from world where name in ('Canada')) and 
 population <(select population from world where name in ('Poland'))

 

 

5번의 경우 어려웠다. 유럽중 독일만 골라내서 독일대비 %지를 각 유럽나라별 골라내라는 문제의 의도!!!를 잘 파악하자!!

 

select name, 
       concat(round(100*population/(select population 
                                    from world 
                                    where continent='Germany'))
             ,'%')
from world 
where continent='Europe'

 

요기를 잘 참고해서 비교해보았다!!

https://www.w3schools.com/sql/sql_any_all.asp 

SQL ANY and ALL Operators

 

6. all 은 where 조건 쓸시 모든 조건을 만족해야 할때 쓴다.

Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

 

 

select name
 from world
   where gdp >all(select gdp from world where continent='Europe' and gdp>0)

 

 

7번부터는 너무 어렵다!!!! ㅎㅎ다시보자.