此文记录sqlzoo的全部刷题记录,不包括那些比较简单的题
1.SELECT names
表名: world
字段名: name continent area population gdp capital tld flag
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,'%')
Find the capital and the name where the capital is an extension of name of the country.
SELECT capital, name
FROM world
WHERE captial like concat('%', name, '%') and capital <> name
Show the name and the extension where the capital is an extension of name of the country.
SELECT name, replace(capital, name, '') as extension
FROM world
WHERE capital like conat('%', name, '%') and capital <> name
2.SELECT FROM world
表结构同上
Show the name and the capital where the first letters of each match. Don’t include countries where the name and the capital are the same word.
SELECT name, capital
FROM world
WHERE LEFT(name,1) = LEFT(capital,1)
AND name <> capital
Find the country that has all the vowels and no spaces in its name.
SELECT name
FROM world
WHERE name LIKE '%a%'
AND name LIKE '%e%'
AND name LIKE '%i%'
AND name LIKE '%o%'
AND name LIKE '%u%'
AND name NOT LIKE '% %'
3.Select within select
表结构同上
Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)
注意NULL和ALL连用的坑
SELECT name
FROM world
WHERE gdp > all (SELECT gdp
FROM world
WHERE continent='europe'
AND gdp IS NOT NULL)
Find the largest country (by area) in each continent, show the continent, the name and the area
关联子查询
SELECT continent, name, area
FROM world x
WHERE area = (SELECT MAX(area)
FROM world y
WHERE x.continent = y.continent)
or
SELECT continent, name, area
FROM world x
WHERE area >= ALL (SELECT area
FROM world y
WHERE x.continent = y.continent)
Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.
SELECT name, continent, population
FROM world x
WHERE 25000000 >= ALL(SELECT population
FROM world y
WHERE x.continent = y.continent)
Some countries have