sqlzoo--select basics,select from world

• sqlzoo–select basics,select from world

• sqlzoo–select from nobel ,select in select

• sqlzoo–sum and count

• sqlzoo–the join operation

• sqlzoo–More JOIN operations

• sqlzoo–using null

• sqlzoo–self join

select basics

world表格:

name	continent	area	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
…

1. 显示德国Germany的人口

   SELECT population FROM world
     WHERE name = 'Germany';
   

2. 查询面积为5000000以上平方公里的国家，并显示每个国家的名称及人均国内生产总值（gdp/population)

   SELECT name, gdp/population FROM world
     WHERE area > 5000000;
   

3. ’IN’可以检查一个项目是否在列表中

   显示Ireland,Iceland,Denmark的国家名称和人口

   SELECT name, population FROM world
     WHERE name IN ('Ireland', 'Iceland', 'Denmark');
   

4. 显示面积为200,000及250,000之间的国家名称和该国面积

   SELECT name, area FROM world
     WHERE area BETWEEN 200000 AND 250000;
   

name	continent
Afghanistan	Asia
Albania	Europe
Algeria	Africa
Andorra	Europe
Angola	Africa
…

1. %表示任意多个任意字符

   找出名称以Y开头的国家

   SELECT name FROM world
     WHERE name LIKE 'Y%';
   

2. 找出以Y结尾的国家

   SELECT name FROM world
     WHERE name LIKE '%Y';
   

3. 找出名称中包含字母x的所有国家

   SELECT name FROM world
     WHERE name LIKE '%x%';
   

4. 名字以land结尾的所有国家

   SELECT name FROM world
     WHERE name LIKE '%land';
   

5. 名字以C开头，ia结尾的所有国家

   SELECT name FROM world
     WHERE name LIKE 'C%ia';
   

6. 名字包含字母oo的所有国家

   SELECT name FROM world
     WHERE name LIKE '%oo%';
   

7. 名字包含三个及以上的a的所有国家

   SELECT name FROM world
     WHERE name LIKE '%a%a%a%';
   

8. _表示一个任意字符

   名字以t作为第二个字母的所有国家

   SELECT name FROM world
    WHERE name LIKE '_t%'
   ORDER BY name;
   

9. 名字都有两个字母o且被另外两个字母相隔的所有国家

   SELECT name FROM world
    WHERE name LIKE '%o__o%';
   

10. 名字为4个字母的所有国家

    SELECT name FROM world
     WHERE name LIKE '____';
    

11. 显示首都和国家同名的所有国家名称

    SELECT name FROM world
     WHERE name = capital;
    

12. 首都都是国家名加上‘ City’的所有国家名称

    CONCAT函数用于将两个字符串连接起来，形成一个单一的字符串

    SELECT name FROM world
     WHERE capital = concat(name, ' City');
    

13. 找出所有首都和国家名字，而首都中要有国家名字出现

    SELECT capital, name FROM world
     WHERE capital LIKE concat('%',name,'%');
    

14. 显示首都名称是国家名称延伸（不包括同名）的所有国家

    SELECT name,capital FROM world
     WHERE capital LIKE concat('%',name,'%_');
    

15. 显示国家名称及其延伸词，如首都都是国家名字的延伸

    SELECT name,replace(capital,name,'')as ext FROM world
     WHERE capital LIKE concat(name,'%_');
    

name	continent	area	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
…

1. 显示具有至少两亿人口的国家名称

   SELECT name FROM world
   WHERE population > 200000000;
   

2. 找出至少2亿人口的国家名称及人均国内生产总值

   SELECT name, gdp/population as pgdp FROM world
   WHERE population > 200000000;
   

3. 显示南美洲大陆的国家名字和以百万为单位人口数

   SELECT name, population/1000000 as population FROM world
   WHERE continent = 'South America';
   

4. 显示法国，德国，意大利的国家名称及人口

   SELECT name,population FROM world
   WHERE name IN ('France', 'Germany', 'Italy');
   

5. 显示包含单词United为名称的国家

   SELECT name FROM world
   WHERE name LIKE concat('%', 'United', '%');
   

6. 显示三百万平方公里以上面积或2.5亿人口以上的大国的名称，人口以及面积

   SELECT name,population,area FROM world
   WHERE area > 3000000 OR population > 250000000;
   

7. 显示以人口或面积为大国，但不能同时满足人口和面积的大国条件的国家名称，人口以及面积

   SELECT name,population,area FROM world
   WHERE area > 3000000 XOR population > 250000000;
   

8. 对于南美洲的各个国家以百万计算人口，以十亿记两位小数GDP

   ROUND（a,b）有两个输入参数，a是待四舍五入的数，b是在小数点后哪位四舍五入，也可以是负数，表示在小数点之前四舍五入。

   SELECT name,round(population/1000000, 2) as population, round(gdp/1000000000,2) as gdp FROM world
   WHERE continent = 'South America';
   

9. 显示万亿元国家的人均国内生产总值，四舍五入到最近的1000元

   SELECT name,round(gdp/population,-3) as pgdp FROM world
   WHERE gdp > 1e12;
   

10. Show the name - but substitute Australasia for Oceania - for countries beginning with N.

    对于名称以N开头的国家将其中属于Australia的大洲替换成Oceania

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

11. 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

    将欧洲和亚洲的continent替换成Eurasia,每个南美，北美以及加勒比地区的国家替换成America，显示所有符合条件的以A或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%';
    

12. 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.

    • Oceania 更改为Australasia
    • 欧亚和土耳其的国家更改为 Europe/Asia
    • 以b开头命名的加勒比岛改为北美，其他的加勒比岛改为南美

    显示更改后所有国家的name,原始大洲，更改后的大洲

    SELECT name, continent as 'original continent',
           CASE 
             WHEN continent = 'Oceania' THEN 'Australasia'
             WHEN continent IN ('Eurasia', 'Turkey') THEN 'Europe/Asia'
             WHEN name LIKE 'B%' AND continent = 'Caribbean' THEN 'North America'
             WHEN NOT name LIKE 'B%' AND continent = 'Caribbean' THEN 'South America'
             ELSE continent 
           END AS 'new continent'
      FROM world
      ORDER BY name;