SQLZOO错题集锦

** SELECT names **

1. Find the countries that have three or more a in the name

WHERE name LIKE '%a%a%a%';```

2. Find the country where the name is the capital city.
```SELECT name FROM world
WHERE name = capital;#这里可以直接在一条记录的不同列值之间进行比对```
![](https://img2018.cnblogs.com/blog/1651714/201907/1651714-20190703160618260-1084753645.png)

3. Find the country where the capital is the country plus "City".
```SELECT name,capital
FROM world
WHERE capital = concat(name,' ','city');#可以在WHERE语句中添加函数条件```
![](https://img2018.cnblogs.com/blog/1651714/201907/1651714-20190703161348185-1048789597.png)

4. 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,'%');#这里用concat生成一个统配字符串```
![](https://img2018.cnblogs.com/blog/1651714/201907/1651714-20190703162208187-1701633201.png)

5.You should include Mexico City as it is longer than Mexico. You should not include Luxembourg as the capital is the same as the country.
```select name,capital
    from world
    where capital like concat(name,'_%')#_通配符只能匹配1个字符，%可以匹配0个字符```
![](https://img2018.cnblogs.com/blog/1651714/201907/1651714-20190703164332068-660456248.png)

6.For Monaco-Ville the name is Monaco and the extension is -Ville.

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,'%_')；#这里的replace函数也可以直接识别变量```