Mysql --- SQLZOO: Select tutorial/zh from WORLD, mysql --- sqlzoozh

Mysql --- SQLZOO: Select tutorial/zh from WORLD, mysql --- sqlzoozh

Name	Mainland China	Zone	Population	Gdp
Afghanistan	Asia	652230	25500100	20343000000
Albania	Europe	28748	2831741	12960000000
Algeria	Africa	2381741	37100000	188681000000
Andorra	Europe	468	78115	3712000000
Angola	Africa	1246700	20609294	100990000000
...

Name: Country name
Mainland China: continent
Area: Area
Population: Population
Gdp: gdp

Country Profile

In this tutorial, we will use the SELECT statementWorldQuery tables.

 

Show the name and the continent-but substituteEurasiaFor Europe and Asia; substituteAmerica-For each country inNorth AmericaOrSouth AmericaOrCaribbean. Show countries beginning with A or B

Display name and mainland China-butEurasiaAnd Europe to replace Europe and Asia; replaceUSA-North AmericaOrSouth AmericaOrCaribbeanRegion. Show countries starting with A or B

SELECT name,CASE WHEN continent IN('Europe','Asia')     THEN 'Eurasia'     WHEN continent IN('North America','South America','Caribbean')     THEN 'America'     ELSE continent ENDFROM worldWHERE name LIKE 'A%' OR name LIKE 'B%';

Put the continents right...

• Oceania becomes Australasia
• Countries in Eurasia and Turkey goEurope/Asia
• Caribbean islands starting with 'B' goNorth America, Other Caribbean islands goSouth America

Show the name, the original continent and the new continent of all countries.

Place the Chinese mainland in the correct position...

• Oceania becomes Oceania
• Countries in Eurasia and TurkeyEurope/Asia
• Go to the Caribbean islands starting with 'B'North America, Other Caribbean islandsSouth America

Displays the names of all countries, the original continent and the New World.

SELECT name,continent,CASE WHEN continent IN ('Eurasia', 'Turkey')     THEN 'Europe/Asia'     WHEN continent = 'Oceania'      THEN 'Australasia'     WHEN continent = 'Caribbean'          THEN          CASE           WHEN name LIKE 'B%'           THEN 'North America'          ELSE 'South America'          END     ELSE continent      ENDFROM worldORDER BY name ASC;