SQLZOO错题整理_牛客博客

1.“Mexico 墨西哥”的首都是”Mexico City”。

顯示所有國家名字,其首都是國家名字加上”City”。

SELECT name
  FROM world 
 WHERE capital like concat(name,' City')

注意：Concat(name,' City')使用时，City前须有空格。
2."Monaco-Ville"是合併國家名字 "Monaco" 和延伸詞"-Ville".

顯示國家名字，及其延伸詞，如首都是國家名字的延伸。

你可以使用SQL函數 REPLACE 或 MID.

SELECT name, REPLACE(capital, name,'') AS ext 
FROM world 
WHERE capital LIKE concat( name, '_%' )

3.Find all details of the prize won by EUGENE O'NEILL
Escaping single quotes
You can't put a single quote in a quote string directly. You can use two single quotes within a quoted string.

select * from nobel 
where winner="EUGENE O'NEILL"

注意：在字符串中存在单引号的情况下，sql查询语句应使用双引号进行查询
4. The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1.

Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

SELECT winner, subject
  FROM nobel
 WHERE yr=1984
 ORDER BY subject IN ('Chemistry','Physics'), subject, winner

Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.

Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

The format should be Name, Percentage for example:
name percentage
Albania 3%
Andorra 0%
Austria 11%
... ...
Decimal places
You can use the function ROUND to remove the decimal places.
Percent symbol %
You can use the function CONCAT to add the percentage symbol.

select name, concat(round(100*population/(select population from world where name='Germany'),0),'%') as percentage 
from world
where continent='Europe'

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 >= ALL
 (SELECT area FROM world y
     WHERE y.continent=x.continent
       AND area>0)

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
AND population>0)
```

Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

select name, continent
from world x
where x.population/3>all
(select population from world y where x.continent=y.continent and x.name!=y.name and population>0)

For each continent show the continent and number of countries with populations of at least 10 million
```
select continent,count(name) from world 
where population >=10000000 group by continent
```

List the continents that have a total population of at least 100 million.

select continent 
from world  
group by continent
having sum(population)>100000000