# SQL练习题(2)

SQL练习

4. SELECT within SELECT

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

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

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

4.4 select name,population from world where population > (select population from world where name= 'Canada') and population < (select population from world where name='Poland')

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

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

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

4.8 select continent, name from world x where name = (select name from world y where x.continent = y.continent order by name limit 1)

4.9 select name, continent, population from world x where 25000000>=all(select population from world y where x.continent = y.continent)

4.10 select name, continent from world x where population >= all(select population*3 from world y where x.continent = y.continent and x.name<>y.name)

5. SUM and COUNT

5.1 select sum(population) from world

5.2 select distinct continent from world

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

5.4 select count(*) from world where area >=1000000

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

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

5.7 select continent, count(name) from world where population > 10000000 group by continent

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

6. The JOIN operation

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

6.2 select id, stadium, team1,team2 from game a join goal b on a.id = b.matchid where player = 'Lars Bender'

6.3 select player, teamid, stadium, mdate from game a join goal b on a.id = b.matchid where teamid = 'GER'

6.4 select team1, team2, player from game a join goal b on a.id = b.matchid where player like 'Mario%'

6.5 select player,teamid,coach,gtime from goal a join eteam b on a.teamid = b.id where gtime <=10

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

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

6.8 select distinct player from goal join game on matchid=id where (teamid=team2 and team1='GER') or (teamid=team1 and team2='GER')

6.9 select teamname,count(teamid) from goal join eteam on teamid=id group by teamname

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

6.11 select matchid, mdate, count(teamid) from game join goal on matchid=id where team1='POL' or team2='POL' group by matchid,mdate

6.12 select matchid, mdate,count(teamid) from game join goal on matchid=id where teamid='GER' group by matchid,mdate

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