SQLZOO练习-- SELECT names（含题目翻译）

知识点

like和%、concat、replace

 

数据表

world

备注：此处应该还少了一列，内容为‘capital’（首都名称）

 

题目内容

1.Find the country that start with Y (查找名称以‘y’开头的国家)

select name from world
where name like 'y%'

 

2.Find the countries that end with y (查找名称以‘y’结尾的国家)

select name from world
where name like '%y'

 

3.Find the countries that contain the letter x (查找名称包含‘x’的国家)

select name from world
where name like '%x%'

 

4.Find the countries that end with land (查找名称以‘land’结尾的国家)

select name from world
where name like '%land'

 

5.Find the countries that start with C and end with ia (查找名称以‘C’开头且以‘ia’结尾的国家)

select name from world
where name like 'C%ia'

 

6.Find the country that has oo in the name (查找名称包含‘oo’的国家)

select name from world
where name like '%oo%'

 

7.Find the countries that have three or more a in the name (查找名称中至少有3个‘a’的国家)

select name from world
where name like '%a%a%a%'

 

8.Find the countries that have "t" as the second character (查找名称中第二位是‘t’的国家)

select name from world
where name like '_t%'

 

9.Find the countries that have two "o" characters separated by two others.(查找名称中带有两个‘o’，且被两个字母分割开的国家)

select name from world
where name like '%o__o%'

 

10.Find the countries that have exactly four characters.(查找名称为4个字母的国家)

select name from world
where name like '____'

 

练习进阶

11.Find the country where the name is the capital city.(查找‘首都名称和国家名称相同’的国家)

select name  from world
where name = capital

 

12.Find the country where the capital is the country plus "City".(查找‘首都名称=国家名称+city’的国家)

select name  from world
where capital = concat(name,' city') 

 

13.Find the capital and the name where the capital includes the name of the country.(查找‘首都名称包含国家名称’的国家)

select capital,name from world
where capital like concat('%',name,'%')

 

14.Find the capital and the name where the capital is an extension of name of the country.(查找‘首都名称包含国家名称，且不等于国家名称’的国家)

select capital,name from world 
where capital like concat('%',name,'%') 
and capital <> name

 

15.Show the name and the extension where the capital is an extension of name of the country.(查找‘首都名称包含国家名称’的国家和扩展词，扩展词=首都名称-国家名称)

select name,replace(capital, name, '') from  world 
where capital like concat(name,'%_')