首页 > 代码库 > SQLZOO网页中SQL的答案

SQLZOO网页中SQL的答案

SELECT from world篇

 


 

11.

题目:

The CASE statement shown is used to substitute North America forCaribbean in the third column.

Show the name - but substitute Australasia for Oceania - for countries beginning with N.
(翻译:让你给出名字和所属大洲的名字,
              其中所属大洲的名字是Oceania的改成Australasia,
              条件是国名的第一个字符是N)
答案:
            SELECT name,
                          CASE WHEN continent=‘Oceania‘ THEN ‘Australasia‘
                                    ELSE continent  END
            FROM world
            WHERE name LIKE ‘N%‘;
解答:
        CASE有点想IF THEN语句。

 
12.
题目:
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
(翻译:显示name,continent
              要求当continent为Asia和Europe时,修改continent为Eurasia。
                     当continent为North America或South America或Caribbean时,修改为America.
              条件是name以A或B开头)
答案:

SELECT name,
               CASE WHEN continent IN (‘Asia‘,‘Europe‘) 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,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 
     END
FROM world
ORDER BY name ASC;

 
 

SQLZOO网页中SQL的答案