sqlzoo答案

SELECT from Nobel Tutorial

先更这些，我去牛客网练习了，稍后回来

8.Chemistry and Physics from different years   

Show the year, subject, and name of physics winners for 1980 together with the chemistry winners for 1984.

select yr, subject, winner from nobel 
where subject='physics' and yr=1980 
or (subject='chemistry' and yr=1984)

9.Exclude Chemists and Medics

Show the year, subject, and name of winners for 1980 excluding chemistry and medicine

select yr,subject, winner from nobel 
where yr=1980 and subject 
not in ('chemistry', 'medicine')

10.Early Medicine, Late Literature

Show year, subject, and name of people who won a 'Medicine' prize in an early year (before 1910, not including 1910) together with winners of a 'Literature' prize in a later year (after 2004, including 2004)

select yr, subject, winner from nobel 
where (subject='medicine' and yr<1910)
or (subject='literature' and yr>=2004) 

11.Umlaut

Find all details of the prize won by PETER GRÜNBERG

select * from nobel where winner='PETER GRÜNBERG'

12.Apostrophe

Find all details of the prize won by EUGENE O'NEILL

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

13.Knights of the realm（通配符%）

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

14.Chemistry and Physics last（关键in用法）

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 ('physics','chemistry'), subject, winner

SELECT within SELECT Tutorial

1.Bigger than Russia(俩个select）

List each country name where the population is larger than that of 'Russia'.

select name from world 
where population>
(select population from world where name='Russia')

2.Richer than UK

Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.

select name from world 
where continent ='Europe'
and gdp/population > (select gdp/population from world where name='United Kingdom')

3.Neighbours of Argentina and Australia

List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.

select name, continent from world 
where continent = (select continent from world where name = 'Argentina') 
or continent =(select continent from world where name='Australia') 
order by name

或者

select name, continent 
from world 
where continent in 
(select continent from world 
where name in ('Argentina','Australia')) 
order by name

4.Between Canada and Poland

Which country has a population that is more than United Kingdom but less than Germany? Show the name and the population.

select name, population from world 
where population> 
(select population from world where name='United Kingdom') 
and population < 
(select population from world where name='Germany')

5.Percentages of Germany(关键字round，concat)

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.

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

6.Bigger than every country in Europe(新关键字all)

Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

select name from world 
where gdp >
all (select gdp from world where continent='Europe'and gdp>0)

或者（子查询）

select name from world 
where gdp is not null 
and gdp>(select max(gdp) from world where continent = 'Europe')

难死

select ga.mdate, ga.team1, 
sum(case when ga.team1=go.teamid then 1 else 0 end) score1, 
ga.team2, 
sum(case when ga.team2=go.teamid then 1 else 0 end) score2 
from game ga left join goal go on ga.id=go.matchid 
group by ga.mdate, ga.team1, ga.team2 
order by ga.mdate, go.matchid, ga.team1, ga.team2

7.Largest in each continent

Find the largest country (by area) in each continent, show the continent, the name and the area

select continent, name, area 
from world 
where (continent, area) 
in (select continent ,max(area) 
from world group by continent)

或者

select continent, name, area 
from world x 
where area>=all(select area from world y where y.continent=x.continent)

8.First country of each continent (alphabetically)

List each continent and the name of the country that comes first alphabetically.

select continent, name from world a 
where name=(select name from world b 
where a.continent= b.continent order by name limit 1)

9.Difficult Questions That Utilize Techniques Not Covered In Prior Sections

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 
where continent not in 
(select distinct continent from world 
where population>25000000)

或者

select name, continent, population from world 
where 25000000 >= 
all(select population from world 
where population>0)

10.Three time bigger

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

select name, continent from world a 
where population > 
all(select 3*population from world b 
where a.continent=b.continent and a.name!=b.name)

SUM and COUNT

1.Total world population

Show the total population of the world.

SELECT SUM(population)
FROM world

2.List of continents(关键字distinct)

List all the continents - just once each.

select distinct continent from world

3.GDP of Africa(关键字sum）

Give the total GDP of Africa

select sum(gdp) from world where continent='Africa'

4.Count the big countries(关键字count)

How many countries have an area of at least 1000000

select count(name) from world where area>=1000000

5.Baltic states population

What is the total population of ('Estonia', 'Latvia', 'Lithuania')

select sum(population) from world 
where name in ('Estonia','Latvia','Lithuania')

6.Counting the countries of each continent(关键词group by)

For each continent show the continent and number of countries.

select continent, count(name) from world group by continent

7.Counting big countries in each continent

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

8.Counting big continents

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

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

The JOIN operation

1.know

The first example shows the goal scored by a player with the last name 'Bender'. The * says to list all the columns in the table - a shorter way of saying matchid, teamid, player, gtime

select matchid, player from goal where teamid='GER'

2.关键字（join左连接)

From the previous query you can see that Lars Bender's scored a goal in game 1012. Now we want to know what teams were playing in that match.

Show id, stadium, team1, team2 for just game 1012

SELECT id,stadium,team1,team2
FROM game left join goal on id=matchid 
where id ='1012' and player='Lars Bender'

3.关键字（join)

Modify it to show the player, teamid, stadium and mdate for every German goal.

修改它以显示每个德国进球的球员、teamid、体育场和 mdate。

SELECT player,teamid, stadium, mdate
FROM game JOIN goal ON (id=matchid) where teamid='GER'

4.

Show the team1, team2 and player for every goal scored by a player called Mario player LIKE 'Mario%'

select team1,team2,player 
from game join goal on (id=matchid) 
where player like 'Mario%'

5.

The table eteam gives details of every national team including the coach. You can JOIN goal to eteam using the phrase goal JOIN eteam on teamid=id

Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10

SELECT player, teamid, coach,gtime
  FROM goal join eteam on teamid=id
 WHERE gtime<=10

6.

List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.

select mdate, teamname 
from game join eteam on team1=eteam.id 
where coach='Fernando Santos'

7.

List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'

select player 
from game join goal on id=matchid 
where stadium='National Stadium, Warsaw'

8.

The example query shows all goals scored in the Germany-Greece quarterfinal.

Instead show the name of all players who scored a goal against Germany.

SELECT distinct player
  FROM game JOIN goal ON id = matchid 
    WHERE teamid!='GER' and (team1='GER' or team2='GER')

9.

Show teamname and the total number of goals scored.

SELECT teamname, count(*)
FROM eteam JOIN goal ON id=teamid
group by teamname 

10.

Show the stadium and the number of goals scored in each stadium.

select stadium, count(gtime) 
from game join goal on id=matchid 
group by stadium

11.

For every match involving 'POL', show the matchid, date and the number of goals scored.

SELECT matchid,mdate,count(gtime)
FROM game JOIN goal ON matchid = id 
WHERE (team1 = 'POL' OR team2 = 'POL')
group by matchid,mdate