SQL错题记录

1、Show the name and population in millions for the countries of the continent ‘South America’. Divide the population by 1000000 to get population in millions.

SELECT name, population/1000000 FROM world
WHERE continent = 'South America'

2、Show the name and population in millions and the GDP in billions for the countries of the continent ‘South America’. Use the ROUND function to show the values to two decimal places.

For South America show population in millions and GDP in billions both to 2 decimal places.

SELECT name, ROUND(population/1000000, 2), ROUND(gdp/1000000000, 2)
FROM world
WHERE continent = 'South America'

3、Show the name and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). ROUND this value to the nearest 1000.

SELECT name, ROUND(gdp/population/1000, 0) * 1000
FROM world
WHERE gdp >= 1000000000000

4、Show the name and capital where the name and the capital have the same number of characters.

• You can use the LENGTH function to find the number of characters in a string

SELECT name, capital FROM world
WHERE length(name) = length(capital)

5、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.

• You can use the function LEFT to isolate the first character.
• You can use <> as the NOT EQUALS operator.

SELECT name, capital FROM world
WHERE left(name, 1) = left(capital, 1) 
AND name <> capital

6、Find all details of the prize won by EUGENE O’NEILL

SELECT * FROM nobel
WHERE winner = 'EUGENE O\'NEILL'

7、List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.

SELECT winner, yr, subject 
FROM nobel
WHERE winner like 'Sir%'
ORDER BY yr desc, winner

8、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

9、Pick the code that shows the amount of years where no Medicine awards were given

SELECT COUNT(DISTICT yr) FROM nobel
WHERE yr NOT IN (SELECT