[MySQL]练习题2

美國、印度和中國(USA, India, China)是人口又大，同時面積又大的國家。排除這些國家。 顯示以人口或面積為大國的國家，但不能同時兩者。顯示國家名稱，人口和面積

SELECT name,population,area FROM world WHERE (area>3000000 AND population<250000000) OR (area<3000000 AND population>250000000)

顯示國家有至少一個萬億元國內生產總值（萬億，也就是12個零）的人均國內生產總值。四捨五入這個值到最接近1000。顯示萬億元國家的人均國內生產總值，四捨五入到最近的$ 1000

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

The CASE statement shown is used to substitute North America for Caribbean in the third column. Show the name - but substitute Australasia for Oceania - for countries beginning with N.

SELECT name,
       CASE WHEN continent='Oceania' THEN 'Australasia'
            ELSE continent END
  FROM world
 WHERE name LIKE 'N%'

Show the name and the continent - but substitute Eurasia for Europe and Asia; substitute America - for each country in North America or South America or Caribbean. Show countries beginning 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 end
from world
where name like 'A%' or name like 'B%'

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