SELECT name,ROUND(GDP/population/1000,0)*1000FROM 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,CASEWHEN continent='Australasia'THEN'Oceania'WHEN continent='Oceania'THEN'Australasia'ELSE continent ENDFROM 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,casewhen continent='Europe'then'Eurasia'when continent='Asia'then'Eurasia'when continent='North America'then'America'when continent='South America'then'America'when continent='Caribbean'then'America'ELSE continent endfrom world
where name like'A%'OR name like'B%'
Put the continents right...Oceania becomes Australasia,Countries in Eurasia and Turkey go to Europe/Asia,Caribbean islands starting with 'B' go to North America, other Caribbean islands go to South America,Show the name, the original continent and the new continent of all countries.
select winner,yr,subject
from nobel
where winner like'Sir%'orderby yr desc,winner;
The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1.Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.
SELECT winner, subject
from nobel
WHERE yr='1984'ORDERBYcasewhen subject IN('physics','chemistry')then1else0end,subject,winner