SQL练习

题目均取自sqlzoo, 在此只写下自己的答案。

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

本章结束，下一章会讲解一个机器学习入门项目：泰坦尼克号生存预测的实现。