SQLZOO刷题记录

此文记录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