SQLZOOL练习题答案和解析 第1关 SELECT name

-- 第1关，
-- 练习 like % _

-- Find the country that start with Y
-- 练习 %
select name
from world 
where name like 'Y%'


-- Find the countries that end with y
-- 练习 %
select name 
from world
where name like '%y'


-- Find the countries that contain the letter x
-- 练习 %
select name 
from world 
where name like '%x%'

-- Find the countries that end with land
-- 练习 %
select name 
from world 
where name like '%land'

-- Find the countries that start with C and end with ia
-- 练习 % 
select name 
from world 
where name like 'C%ia'

-- Find the country that has oo in the name
-- 练习 % 
select name 
from world 
where name like '%oo%'


-- Find the countries that have three or more a in the name
-- 练习 % 
select name 
from world
where name like '%a%a%a%'

-- Find the countries that have "t" as the second character.
-- 练习_
select name
from  world 
where name like '_t%'
order by name 

-- Find the countries that have two "o" characters separated by two others.
-- 练习_
select name 
from world 
where name like '%o__o%'

-- Find the countries that have exactly four characters.
-- 练习_
select name 
from world 
where name like '____'

--Find the country where the name is the capital city.
--练习 where =
select name 
from world 
where name = capital


-- Find the country where the capital is the country plus "City".
-- concat用法
select name
from world
where concat(name, ' city') = capital

--Find the capital and the name where the capital includes the name of the country.
-- like  concat 

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


-- Find the capital and the name where the capital is an extension of name of the country.
-- 练习like concat 和 !=用法
select capital, name 
from world
where capital like concat('%',name,'%') and capital != name 


/* Show the name and the extension where the capital is an extension of name of the country.*/
-- 练习replace
select name 
,replace(capital, name,'') as ext 
from world 
where capital like concat('%',name,'%') 
and capital != name