SQLZOO 答案

SELECT basics
1. SELECT population FROM world
WHERE name = ‘Germany’
2. SELECT name, population FROM world
WHERE name IN (‘Sweden’, ‘Norway’, ‘Denmark’);
3. SELECT name, area FROM world
WHERE area BETWEEN 200000 AND 250000

SELECT from WORLD Tutorial
1. SELECT name, continent, population FROM world
2. select name
from world
where population>=200000000
3. select name, gdp/population
from world
where population >=200000000
4. select name, population/1000000
from world
where continent=’South America’
5. select name, population
from world
where name in(‘France’,’Germany’,’Italy’)
6. select name
from world
where name like ‘%United%’
7. select name, population, area
from world
where area>=3000000 or population>=250000000
8. select name, population, area
from world
where (area>=3000000 and population<250000000) or (area<3000000 and population>=250000000)
9. select name, round(population/1000000, 2), round(gdp/1000000000, 2)
from world
where continent=’South America’
10. select name, round(gdp/population, -3)
from world
where gdp>=1000000000000
11. select name, capital
from world
where length(name)=length(capital)
12. select name, capital
from world
where left(name,1)=left(capital,1) and name<>capital
13. SELECT name
FROM world
WHERE contains(name,’a and e and o and i and u’)
AND name NOT LIKE ‘% %’

SELECT from Nobel Tutorial
1. SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950
2. SELECT winner
FROM nobel
WHERE yr = 1962
AND subject = ‘Literature’
3. SELECT yr, subject
FROM nobel
WHERE winner = ‘Albert Einstein’
4. SELECT winner
FROM nobel
WHERE subject= ‘Peace’ and yr>=2000
5. SELECT *
FROM nobel
WHERE subject= ‘Literature’ and yr like ‘198%’
6. SELECT * FROM nobel
WHERE winner IN (‘Theodore Roosevelt’,’Woodrow Wilson’,’Jimmy Carter’,’Barack Obama’)
7. SELECT winner FROM nobel
WHERE winner like ‘John%’
8. SELECT * FROM nobel
WHERE (subject=’Physics’ and yr=1980) or (subject=’Chemistry’ and yr=1984)
9. SELECT * FROM nobel
WHERE yr=1980 and subject not in (‘Chemistry’,’Medicine’)
10. SELECT * FROM nobel
WHERE (subject=’Medicine’ and yr<1910) or (subject=’Literature’ and yr>=2004)
11. SELECT * FROM nobel
WHERE winner=’PETER GRÜNBERG’
12. SELECT * FROM nobel
WHERE winner=’EUGENE O”NEILL’
13. SELECT winner, yr, subject FROM nobel
WHERE winner like ‘Sir%’
order by yr desc, winner
14. SELECT winner, subject FROM nobel
WHERE yr=1984
ORDER BY subject IN (‘Physics’,’Chemistry’),subject,winner