[SQL][sqlzoo] SELECT from world

SELECT from WORLD Tutorial                                                                    

SELECT from WORLD Tutorial - SQLZOO

1.Introduction

SELECT name, continent, population 
FROM world

2.Large Countries

SELECT name FROM world
WHERE population>200000000

3.Per capita GDP

SELECT name, gdp/population FROM world
  WHERE population > 200000000

4.South America In millions

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

5.France, Germany, Italy

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

6.United

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

7.Two ways to be big

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

8.One or the other (but not both)

select name,population,area
from world
where (area>3000000 or population>250000000)
and not (area>3000000 and population>250000000)

9.Rounding

SELECT name, ROUND(population/1000000,2),
             ROUND(gdp/1000000000,2)
  FROM world
 WHERE continent='South America'

10.Trillion dollar economies

select name, ROUND(gdp/population,-3)
from world
where
gdp>1000000000000

11.Name and capital have the same length

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

12.Matching name and capital

SELECT name,capital
FROM world
WHERE LEFT(name,1)=LEFT(capital,1)
AND name<>capital

13.All the vowels

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

BBC QUIZ                                                                                                               

SELECT name
  FROM world
 WHERE name LIKE 'U%'

2.

SELECT population
  FROM world
 WHERE name = 'United Kingdom'

3.

'name' should be name

4.

Nauru

990

5.

SELECT name, population
  FROM world
 WHERE continent IN ('Europe', 'Asia')

6.

SELECT name FROM world
 WHERE name IN ('Cuba', 'Togo')

7.

Brazil

Colombia