[SQL] SQLZOO 정복하기 1

▷ select basics

SELECT population FROM world
  WHERE name = 'Germany';

SELECT name, population FROM world
  WHERE name IN ('Sweden', 'Norway', 'Denmark');

SELECT name, area FROM world
  WHERE area BETWEEN 200000 AND 250000;

▷ select name

SELECT name FROM world
  WHERE name LIKE 'Y%'

SELECT name FROM world
  WHERE name LIKE '%y'

SELECT name FROM world
  WHERE name LIKE '%x%'

SELECT name FROM world
  WHERE name LIKE '%land'

SELECT name FROM world
  WHERE name LIKE 'C%ia'

SELECT name FROM world
  WHERE name LIKE '%oo%'

SELECT name FROM world
  WHERE name LIKE '%a%a%a%'

SELECT name FROM world
 WHERE name LIKE '_t%'
ORDER BY name

SELECT name FROM world
 WHERE name LIKE '%o_o%'
 
SELECT name FROM world
 WHERE name LIKE '____'

SELECT name
FROM world
WHERE name = capital
 
SELECT name
FROM world
WHERE capital = concat(name,' City')
 
SELECT capital, name
FROM world
WHERE capital like concat('%', name, '%')
-- sybase에 concat은 없기 때문에 || 또는 +를 사용
-- '%' || name || '%' or '%' + name + '%'

SELECT capital, name
FROM world
WHERE capital like concat('%',name,'%') and capital != name

SELECT name, replace(capital, name, '') as extension
FROM world
WHERE capital like concat('%',name,'%')
and capital != name

▷ select from world

SELECT name, continent, population FROM world

SELECT name FROM world
WHERE population >= 200000000

SELECT name, GDP/population as per_capita_GDP
FROM world
WHERE population >= 200000000

SELECT name, population/1000000 as in_millions
FROM world
WHERE continent = 'South America'

SELECT name, population
FROM world
WHERE name in ('France', 'Germany', 'Italy')

SELECT name
FROM world
WHERE name like '%United%'

SELECT name, population, area
FROM world
WHERE area >= 3000000 or population >= 250000000

SELECT name, population, area
FROM world
WHERE (!(area >= 3000000) and population >= 250000000) or (area >= 3000000 and !(population >= 250000000))

SELECT name, round(population/1000000, 2) as populationInMillions, round(gdp/1000000000, 2) as gdpInBillions
FROM world
WHERE continent = 'South America'

SELECT name, round(gdp/population/1000, 0)*1000 as percapitagdp
FROM world
WHERE gdp >= 1000000000000

SELECT name, capital
FROM world
WHERE LENGTH(name) = LENGTH(capital)

SELECT name, capital
FROM world
WHERE name <> capital and left(name,1) = left(capital,1)

SELECT name
FROM world
WHERE name NOT LIKE '% %'
and name like '%a%'
and name like '%e%'
and name like '%i%'
and name like '%o%'
and name like '%u%'

▷ select from novel

SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950

SELECT winner
FROM nobel
WHERE yr = 1962
AND subject = 'literature'

SELECT yr, subject
FROM nobel
WHERE winner = 'Albert Einstein'

SELECT winner
FROM nobel
WHERE yr >= 2000 and subject = 'peace'

SELECT *
FROM nobel
WHERE subject = 'literature' and yr between 1980 and 1989

SELECT *
FROM nobel
WHERE winner IN ('Theodore Roosevelt',
                  'Thomas Woodrow Wilson',
                  'Jimmy Carter',
                  'Barack Obama')

SELECT winner
FROM nobel
WHERE winner like 'John %'

SELECT *
FROM nobel
WHERE (subject = 'physics' and yr = 1980)
   or (subject = 'chemistry' and yr = 1984)
   
SELECT *
FROM nobel
WHERE yr = 1980 and subject not in ('chemistry', 'medicine')

SELECT *
FROM nobel
WHERE (subject = 'medicine' and yr < 1910)
   or (subject = 'literature' and yr >= 2004)
   
SELECT *
FROM nobel
WHERE winner = 'PETER GRÜNBERG'

SELECT *
FROM nobel
WHERE winner = "EUGENE O'NEILL"

SELECT winner, yr, subject
FROM nobel
WHERE winner like 'Sir%'
ORDER BY yr DESC, winner ASC

-- sybase 사용 불가
SELECT winner, subject
  FROM nobel
 WHERE yr=1984
 ORDER BY subject in ('chemistry', 'physics') asc, subject,winner

▷ select within select

SELECT name FROM world
WHERE population >
        (SELECT population FROM world
        WHERE name='Russia')

SELECT name
FROM world
WHERE continent = 'Europe'
  and gdp/population >
        (SELECT gdp/population
        FROM world
        WHERE name = 'United Kingdom')
        
SELECT name, continent
FROM world
WHERE continent = 'South America' or continent = 'Oceania'
ORDER BY name

SELECT name, population
FROM world
WHERE population >
        (SELECT population
        FROM world
        WHERE name = 'United Kingdom')
  and population <
        (SELECT population
        FROM world
        WHERE name = 'Germany') 

SELECT a.name, concat(round(a.population * 100 / b.population),'%') as percentage
FROM world a
     INNER JOIN (SELECT population 
                   FROM world
                  WHERE name = 'Germany') as b
     ON 1 = 1
WHERE continent = 'Europe'
/* -- 위 코드가 좀 더 깔끔해 보임
SELECT name,
       concat(round(population/
       (SELECT population
       FROM world
       WHERE name = 'Germany')
       * 100),'%') as percentage
FROM world
WHERE continent = 'Europe'
*/

SELECT name
  FROM world
 WHERE gdp > ALL(SELECT gdp
                   FROM world
                  WHERE gdp > 0 -- gdp null 값 방지
                    and continent = 'Europe')
/* -- 두 쿼리의 성능은 차이가 크게 없지만 명확성은 아래 코드가 높다.
SELECT name
  FROM world
 WHERE gdp > (SELECT max(gdp)
                   FROM world
                  WHERE gdp > 0
                    and continent = 'Europe')

SELECT continent, name, area
  FROM world x
 WHERE area >= ALL
       (SELECT area
          FROM world y
         WHERE y.continent=x.continent
           AND area > 0)
/* -- ALL()보다 큰 것은 max를 찾는 것과 같다.
SELECT continent, name, area
  FROM world x
 WHERE area >= (SELECT max(area)
                  FROM world y
                 WHERE y.continent=x.continent
                   AND area > 0)
*/

SELECT continent
      ,name
  FROM world x
 WHERE name = (SELECT min(name)
                FROM world y
               WHERE y.continent = x.continent)
-- ORDER BY continent
/* -- 앞서 풀었던 내용과 같은 내용으로 ALL도 가능
SELECT continent
      ,name
  FROM world x
 WHERE name <= ALL(SELECT name
                     FROM world y
                    WHERE y.continent = x.continent)
-- ORDER BY continent
*/
/* -- group by를 활용하는 것도 가능
SELECT continent
      ,min(name) as name
  FROM world x
 GROUP BY continent
-- ORDER BY continent
*/

SELECT name, continent, population
  FROM world x
 WHERE 25000000 >= (SELECT max(population)
                      FROM world y
                     WHERE y.continent = x.continent)

SELECT name, continent
  FROM world x
 WHERE population >= 3 * (SELECT max(population)
                            FROM world y
                           WHERE y.continent = x.continent
                             and y.name <> x.name)

 

참고

https://sqlzoo.net/wiki/SQL_Tutorial