융무의 기술블로그
article thumbnail
Published 2020. 7. 2. 20:28
[SQL][sqlzoo] SELECT from world Algorithm/SQL

0.1. SELECT from WORLD Tutorial                                                                    

 

SELECT from WORLD Tutorial - SQLZOO

namecontinentarea populationgdp AfghanistanAsia6522302550010020343000000 AlbaniaEurope28748 2831741 12960000000 AlgeriaAfrica2381741 37100000 188681000000 AndorraEurope46878115 3712000000 AngolaAfrica1246700 20609294 100990000000 ... In this tutorial you w

sqlzoo.net

1. 1.Introduction

<sql />
SELECT name, continent, population FROM world

2. 2.Large Countries

<sql />
SELECT name FROM world WHERE population>200000000

3. 3.Per capita GDP

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

4. 4.South America In millions

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

5. 5.France, Germany, Italy

<sql />
SELECT name, population FROM world WHERE name IN ('France','Germany','Italy')

6. 6.United

<sql />
SELECT name FROM world WHERE name LIKE '%United%'

7. 7.Two ways to be big

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

8. 8.One or the other (but not both)

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

9. 9.Rounding

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

10. 10.Trillion dollar economies

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

11. 11.Name and capital have the same length

<sql />
SELECT name,capital FROM world WHERE LENGTH(name)=LENGTH(capital)

12. 12.Matching name and capital

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

13. 13.All the vowels

<sql />
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 '% %'

13.1. BBC QUIZ                                                                                                               

<sql />
SELECT name FROM world WHERE name LIKE 'U%'

2.

<sql />
SELECT population FROM world WHERE name = 'United Kingdom'

3.

'name' should be name

4.

Nauru 990

5.

<sql />
SELECT name, population FROM world WHERE continent IN ('Europe', 'Asia')

6.

<sql />
SELECT name FROM world WHERE name IN ('Cuba', 'Togo')

7.

Brazil
Colombia

13.2.  

profile

융무의 기술블로그

@융무

포스팅이 좋았다면 "좋아요❤️" 또는 "구독👍🏻" 해주세요!