SQL刷题(二)

7. select in select

This tutorial looks at how we can use SELECT statements within SELECT statements to perform more complex queries.

1.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.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.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 IN (SELECT continent FROM world 
                    WHERE name IN ('Argentina','Australia'))
ORDER BY name;

输出的结果为：

name	            continent
Argentina	       South America
Australia	          Oceania
Bolivia	           South America
Brazil	           South America
Chile	           South America
Colombia	       South America
Ecuador	           South America
Fiji	            Oceania
Guyana	          South America
Kiribati	       Oceania
Marshall Islands	Oceania
Micronesia, Federated States of	Oceania
Nauru	            Oceania
New Zealand	       Oceania
Palau	           Oceania
Papua New Guinea	Oceania
Paraguay	       South America
Peru	          South America
Saint Vincent and the Grenadines	South America
Samoa	Oceania
Solomon Islands	Oceania
Suriname	      South America
Tonga	            Oceania
Tuvalu	            Oceania
Uruguay	        South America
Vanuatu	           Oceania
Venezuela	    South America

需要注意的点在于对continent
的理解，说明选择的州在Argentina Australia中，因此需要添加多个循环。

4.Which country has a population that is more than Canada but less than Poland? Show the name and the population.

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

5.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(100*population/(select population from world 
                                   where name = 'Germany'),0) ,'%')

from world where continent = 'Europe'

输出结果为:

name	
Albania	3.000000000000%
Andorra	0.000000000000%
Austria	11.000000000000%
Belarus	12.000000000000%

结果还需要再处理，暂时存疑。具体的写作思路明白即可。

6.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 > (select max(gdp) 
                     		from world 
                     		where continent = 'Europe');

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

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

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

大致的意思说根据字母列举每一个州的第一个国家的名字

select continent,name from world where name in (select min(name) from world group by continent);

9.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 x 
where 25000000 >= all(select population from world y 
                      where x.continent = y.continent 
                      and y.population > 0)

需要注意all
的用法

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

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

需要注意all
的用法

其二需要理解y.continent = x.continent

其三y.name != x.name

• concat函数

CONCAT allows you to stick two or more strings together.

• This operation is concatenation.

CONCAT(s1, s2 ...)

注意点：第三题不是很懂，需要再继续理解；第五题要反复做，第七题不是很好理解，要注意.第九题比较难需要注意。第十题比较难

8.Nest Select quiz

1.Select the code that shows the name, region and population of the smallest country in each region

select region,name,population from bbc x where population <= all(select population from bbc y where x.population = y.population and population > 0);

2. Select the code that shows the countries belonging to regions with all populations over 50000

select name,region,population from bbc x where 50000 < all(select population from bbc y where x.population = y.population and population > 0);

3.Select the code that shows the countries with a less than a third of the population of the countries around it

select name,region from bbc x where population < all(select population/3 from bbc y where y.region = x.region and y.name != x.name);

4.Select the result that would be obtained from the following code:

select name from bbc
where population >
	(select population
    	from bbc
    	where name = 'United Kingdom')
   and region in 
   		(select region 
         	from bbc
         where name = 'United Kingdom')

语法的含义是提取在英国地区人口大于英国的国家名称: 输出结果为:

Table-D
France
Germany
Russia
Turkey

5. Select the code that would show the countries with a greater GDP than any country in Africa (some countries may have NULL gdp values).

select name from bbc where gdp > all(select gdp from bbc where region = 'Africa' and gdp <> null)

with a greater GDP than any country in Africa

然后再选择代码进行处理 上述的答案是错误的，正确的写法是:

select name from bbc where gdp > (select max(gdp) from bbc where region = 'Africa')

6.Select the code that shows the countries with population smaller than Russia but bigger than Denmark

select name from bbc where population between  (select population from bbc where name = 'Denmark') and (select population from bbc where name = 'Russia');

select name from bbc where population < 
(select population from bbc where name = 'Russia') 
and 
population > (select population from bbc where name = 'Denmark');

注意两个条件要分开写，不能添加到一个select中

7.Select the result that would be obtained from the following code:

SELECT name FROM bbc
 WHERE population > ALL
       (SELECT MAX(population)
          FROM bbc
         WHERE region = 'Europe')
   AND region = 'South Asia'

该段代码的意思是：提取比欧洲和南亚人口最多的国家还要多的国家。输出的结果为:

Bangladesh
India
Pakistan

本章本第五题需要重新做

9.SUM and COUNT

This tutorial is about aggregate functions such as COUNT, SUM and AVG. An aggregate function takes many values and delivers just one value. For example the function SUM would aggregate the values 2, 4 and 5 to deliver the single value 11.

world(name, continent, area, population, gdp)

1.Show the total population of the world.

select sum(population) from world;

输出结果为:

Correct answer
7118632738

2.List all the continents - just once each.

select distinct continent from world;

该题注意考查distinct
的用法

3.Give the total GDP of Africa

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

4.How many countries have an area of at least 1000000

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

输出结果为:

Correct answer
28

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

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

输出结果为:

Correct answer
6251750

tips:Using GROUP BY and HAVING

6.For each continent show the continent and number of countries.

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

输出结果为:

continent	
Africa	53
Asia	47
Caribbean	11
Eurasia	2
Europe	44
North America	11
Oceania	14
South America	13

本题使用group by
进行分组统计每个州的国家.

7.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;

本题需要考查的是多增加一个条件进行分组 输出结果为:

Correct answer
continent	
Africa	29
Asia	26
Caribbean	2
Eurasia	1
Europe	14
North America	4
Oceania	1
South America	8

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

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

输出的结果为:

Correct answer
continent
Africa
Asia
Eurasia
Europe
North America
South America

第七第八题不是很懂 第八题主要考查条件的过滤与筛选：需要查找人口超过100000000的大洲 第一步先进行分组，第二步每一组进行人口相加求和。

10. SUM and COUNT Quiz

SUM and COUNT QUIZ

1. Select the statement that shows the sum of population of all countries in 'Europe'

select sum(population) from bbc where region = 'Europe';

2.Select the statement that shows the number of countries with population smaller than 150000

select count(name) from bbc where population < 150000;

3. Select the list of core SQL aggregate functions

AVG(), COUNT(), MAX(), MIN(), SUM()

4.Select the result that would be obtained from the following code:

SELECT region, SUM(area)
   FROM bbc 
  WHERE SUM(area) > 15000000 
  GROUP BY region

输出结果为:

No result due to invalid use of the WHERE function

该题与第九章第八题相似，正确的写法应该是:

select region from world group by region having sum(area) > 15000000;

5.Select the statement that shows the average population of 'Poland', 'Germany' and 'Denmark'

select avg(population) from bbc where name in ('Poland', 'Germany' and 'Denmark');

6.Select the statement that shows the medium population density of each region

select region,sum(population)/sum(area) as density from bbc group by region;

7.Select the statement that shows the name and population density of the country with the largest population

select name,population/area as density from bbc where population > (select max(population) from bbc);

正确的写法是:

select name,population/area as density from bbc where where population = (select max(population) from bbc);

8.Pick the result that would be obtained from the following code:

SELECT region, SUM(area) 
   FROM bbc 
  GROUP BY region 
  HAVING SUM(area)<= 20000000

该行语句的意思是根据region
分组，提取出面积小于20000000的区域。输出结果为:

Americas	732240
Middle East	13403102
South America	17740392
South Asia	9437710

第六题关于中位数的计算不是很懂，需要进一步加强。

11 The JOIN operation

This tutorial introduces JOIN which allows you to use data from two or more tables. The tables contain all matches and goals from UEFA EURO 2012 Football Championship in Poland and Ukraine.

						game

goal表格:

matchid	teamid	player	             gtime
1001	POL	   Robert Lewandowski	  17
1001	GRE	   Dimitris Salpingidis	  51
1002	RUS	   Alan Dzagoev	          15
1002	RUS	   Roman Pavlyuchenko	  82
...

eteam表格:

id	teamname	       coach
POL	Poland	        Franciszek Smuda
RUS	Russia	         Dick Advocaat
CZE	Czech Republic	   Michal Bilek
GRE	Greece	          Fernando Santos
...

### 1.Modify it to show the matchid and player name for all goals scored by Germany. To identify German players, check for: teamid = 'GER'
```mysql
select matchid,player from goal where teamid = 'GER';

Notice in the that the column matchid in the goal table corresponds to the id column in the game table. We can look up information about game 1012 by finding that row in the game table.

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

select id,stadium,team1,team2 from game where id = '1012';

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

select player,teamid,stadium,mdate
			from game join goal on (id = matchid) 
			where teamid = 'GER';

本题需要掌握的知识点：

• 连接表用join on
  不要与其他的连接方式弄混淆

Use the same JOIN as in the previous question.

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.Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10

select play,teamid,coach,gtime from goal join eteam on id = teamid 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 eteam.id = team1 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 goal join game on id = matchid where stadium = 'National Stadium, Warsaw';

8.Instead show the name of all players who scored a goal against Germany.(射入德国队球门的队员)

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

9.Show teamname and the total number of goals scored.

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

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

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

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

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

12.For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'

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

13.Sort your result by mdate, matchid, team1 and team2.

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
order by mdate,matchid,team1,team2;

输出结果为:

Correct answer
mdate	             team1	score1	team2 score2
2012-06-08T00:00:00	  POL	  1	    GRE	    1
2012-06-08T00:00:00   RUS	  4	    CZE	    1
2012-06-09T00:00:00	  NED	  0	    DEN	    1

CASE WHEN的用法

case: allows you to return different values under different conditions.If none of the conditions match (and there is not ELSE) then NULL is returned.

Case when condition1 then value1
	 when condition2 then value2
     else def_value
end

注意: 第三题和第四题不是很懂 第九题注意count(*)
和group by
的用法 第十题需要注意count(*)
的用法 需要重新做的题目比较多，需要反复思考

12.JOIN Quiz

1. You want to find the stadium where player 'Dimitris Salpingidis' scored. Select the JOIN condition to use:

game join goal on (id = matchid)

找到相关的表格进行连接即可

2.You JOIN the tables goal and eteam in an SQL statement. Indicate the list of column names that may be used in the SELECT line:

 matchid, teamid, player, gtime, id, teamname, coach

3. Select the code which shows players, their team and the amount of goals they scored against Greece(GRE).

select player,teamid,count(*) 
					from game join goal on matchid = id
					where (team1 = 'GRE' or team2 = 'GRE')
					and teamid != 'GRE'
					group by player,teamid

4.Select the result that would be obtained from this code:

select distinct teamid,mdate from goal join game on (matchid = id)
where mdate = '9 June 2012';

输出结果为:

DEN	9 June 2012
GER	9 June 2012

5.Select the code which would show the player and their team for those who have scored against Poland(POL) in National Stadium, Warsaw.

select distinct player,teamid
from game join goal on matchid = id
where stadium = 'National Stadium, Warsaw'
and (team1 = 'POL' OR team2 = 'POL')
and teamid != 'POL'

6.Select the code which shows the player, their team and the time they scored, for players who have played in Stadion Miejski (Wroclaw) but not against Italy(ITA).

select distinct player,teamid,gtime 
from game join goal on matchid = id
where stadium = 'Stadion Miejski (Wroclaw)'
and ((teamid = team2 and team1 != 'ITA') or (teamid = team1 and team2 != 'ITA'))

7.Select the result that would be obtained from this code:

select teamname,count(*)
	from eteam join goal on teamid = id
	group by teamname 
	having count(*) < 3;

该句代码的意义是筛选出得分小于3的球队名称 输出结果为:

Netherlands	        2
Poland	                2
Republic of Ireland	1
Ukraine     	        2

(完)