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

本章結(jié)束郊愧，下一章會講解一個機器學習入門項目：泰坦尼克號生存預測的實現(xiàn)儡循。