SQLZOO练习

---

前言

　　这是来自SQLZOO的练习
地址：https://igs.sqlzoo.net/

---

一、SELECT basics

1.顯示德國 Germany 的人口

select name, population 
from world 
where name='Germany';

2.查詢面積為 5,000,000 以上平方公里的國家,對每個國家顯示她的名字和人均國內生產總值(gdp/population)

select name,gdp/population 
from world 
where area>5000000;

3.顯示“Ireland 愛爾蘭”,“Iceland 冰島”,“Denmark 丹麥”的國家名稱和人口

select name ,population 
from world 
where name in('Ireland','Iceland','Denmark');

4.顯示面積為 200,000 及 250,000 之間的國家名稱和該國面積。

select name,area 
from world 
where area between 200000 and 250000;

二、SELECT name

1.%是萬用字元,可以用代表任何字元。找出以 Y 為開首的國家。

select name 
from world 
where name like 'Y%';

2.找出以 Y 為結尾的國家.

select name 
from world 
where name like '%Y';

3.“Luxembourg 盧森堡”中有一個x字母,還有一個國家的名字中有x。列出這兩個國家。找出所有國家,其名字包括字母x。

SELECT name 
FROM world 
WHERE name LIKE '%x%';

4.找出所有國家,其名字以 land 作結尾。

SELECT name 
FROM world 
WHERE name LIKE '%land';

5.找出所有國家,其名字以 C 作開始,ia 作結尾。

SELECT name 
FROM world 
WHERE name LIKE 'c%ia';

6.找出所有國家,其名字包括字母oo。

SELECT name 
FROM world 
WHERE name LIKE '%oo%'

7.找出所有國家,其名字包括三個或以上的a。

select name 
from world 
where name like '%a%a%a%';

8.找出所有國家,其名字以t作第二個字母。

SELECT name 
FROM world 
WHERE name LIKE '_t%' 
ORDER BY name;

9.找出所有國家,其名字都有兩個字母 o,被另外兩個字母相隔着。

select name 
from world 
where name like'%o__o%';

10.找出所有國家,其名字都是 4 個字母的。
```sql
SELECT name 
FROM world 
WHERE name LIKE '____';

11.顯示所有國家名字,其首都和國家名字是相同的。

SELECT name 
FROM world 
WHERE name=capital;

12.顯示所有國家名字,其首都是國家名字加上”City”

SELECT name 
FROM world 
WHERE capital LIKE concat(name,' City');

13.找出所有首都和其國家名字,而首都要有國家名字中出現。

select capital,name 
from world 
where capital like concat('%',name,'%');

14.找出所有首都和其國家名字,而首都是國家名字的延伸。你應顯示 Mexico City,因它比其國家名字 Mexico 長。你不應顯示 Luxembourg,因它的首都和國家名相是相同的。

select name,capital 
from world 
where capital like concat(name,'_','%');

15.“Monaco-Ville"是合併國家名字 “Monaco” 和延伸詞”-Ville".顯示國家名字，及其延伸詞，如首都是國家名字的延伸。

select name,replace(capital,name,' ') as ext 
from world 
where capital like concat(name,'_','%');

三、SELECT from WORLD

2.如何使用WHERE來篩選記錄。 顯示具有至少2億人口的國家名稱。 2億是200000000，有八個零。

SELECT name 
FROM world
WHERE population>=200000000;

3.找出有至少200百萬(2億)人口的國家名稱，及人均國內生產總值。
求助：如何人均國內生產總值計算

SELECT name,gdp/population 
FROM world
WHERE population>=200000000;

4.顯示’South America’南美洲大陸的國家名字和以百萬為單位人口數。 將人口population 除以一百萬(1000000)得可得到以百萬為單位人口數。

SELECT name,population/1000000 
FROM world
WHERE continent='South America';

5.顯示法國，德國，意大利(France, Germany, Italy)的國家名稱和人口

SELECT name,population 
FROM world
WHERE name in('France', 'Germany', 'Italy');

6.顯示包含單詞“United”為名稱的國家。

SELECT name 
FROM world
WHERE name like '%United%';

7.成為大國的兩種方式：如果它有3百萬平方公里以上的面積，或擁有250百萬(2.5億)以上人口。
展示大國的名稱，人口和面積

SELECT name,population,area 
FROM world
WHERE area>=3000000 or population >=250000000;

8.美國、印度和中國(USA, India, China)是人口又大，同時面積又大的國家。排除這些國家。
顯示以人口或面積為大國的國家，但不能同時兩者。顯示國家名稱，人口和面積。

select name,population,area 
from world 
where (area > 3000000 and population <= 250000000) 
or (area <= 3000000 and population > 250000000);

SELECT name,population,area 
FROM world
WHERE not(area>=3000000 and population >=250000000) 
and(area>=3000000 or population >=250000000);

9.除以為1000000（6個零）是以百萬計。除以1000000000（9個零）是以十億計。使用 ROUND 函數來顯示的數值到小數點後兩位。
對於南美顯示以百萬計人口，以十億計2位小數GDP。

select name, round(population/1000000,2),round(gdp/1000000000,2) 
from world 
where continent='South America';

10.顯示國家有至少一個萬億元國內生產總值（萬億，也就是12個零）的人均國內生產總值。四捨五入這個值到最接近1000。
顯示萬億元國家的人均國內生產總值，四捨五入到最近的$ 1000。

select name,round(gdp/population,-3) 
from world 
where gdp>1000000000000;

11.Show the name - but substituteAustralasiaforOceania- for countries beginning with N.

SELECT name,
CASE WHEN continent='Australasia' THEN 'Oceania'
ELSE continent END
FROM world
WHERE name LIKE 'N%';

12.Show the name and the continent - but substituteEurasiafor Europe and Asia; substituteAmerica- for each country inNorth AmericaorSouth AmericaorCaribbean. Show countries beginning with A or B

select name ,
case when continent in('Europe','Asia') then 'Eurasia'
when continent in('North America','South America','Caribbean') then 'America'
else continent end
from world
where name like 'A%' or name like 'B%';

13.Put the continents right…
• Oceania becomes Australasia
• Countries in Eurasia and Turkey go to Europe/Asia
• Caribbean islands starting with ‘B’ go to North America, other Caribbean islands go to South America
Show the name, the original continent and the new continent of all countries.

select name,continent,
case when continent = 'Oceania' then 'Australasia'
when continent in ('Eurasia','Turkey') then 'Europe/Asia'
when continent = 'Caribbean'and name like 'B%' then 'North America'
when continent = 'Caribbean'and name not like 'B%' then 'South America'
else continent end
from world
order by name

四、SELECT in SELECT

1. 列出每個國家的名字 name，當中人口 population 是高於俄羅斯’Russia’的人口。

SELECT name FROM world
WHERE population >
(SELECT population FROM world WHERE name='Russia');

2. 列出歐州每國家的人均GDP，當中人均GDP要高於英國’United Kingdom’的數值。

SELECT name
FROM world
WHERE continent = 'Europe'
AND gdp/population > (SELECT gdp/population 
FROM world 
WHERE name = 'United Kingdom');

3. 在阿根廷Argentina 及 澳大利亞 Australia所在的洲份中，列出當中的國家名字 name 及洲分 continent 。按國字名字順序排序

SELECT name, continent
FROM world
WHERE continent in (SELECT continent 
FROM world 
WHERE name in ('Argentina', 'Australia')) ORDER BY name;

4. 哪一個國家的人口比加拿大Canada的多，但比波蘭Poland的少?列出國家名字name和人口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. Germany德國（人口8000萬），在Europe歐洲國家的人口最多。Austria奧地利（人口850萬）擁有德國總人口的11％。顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。

SELECT name, CONCAT(ROUND(population*100/(SELECT population 
FROM world WHERE name = 'Germany')),  '%')
FROM world
WHERE continent = 'Europe';

6. 哪些國家的GDP比Europe歐洲的全部國家都要高呢? [只需列出 name 。] (有些國家的記錄中，GDP是NULL，沒有填入資料的。)

SELECT name
FROM world
WHERE gdp > ALL(SELECT gdp 
FROM world 
where continent = 'Europe' AND gdp > 0);

7. 在每一個州中找出最大面積的國家，列出洲份 continent, 國家名字 name 及面積 area。 (有些國家的記錄中，AREA是NULL，沒有填入資料的。)

SELECT continent, name, area FROM world x
WHERE area >= ALL
(SELECT area FROM world y
 WHERE y.continent=x.continent
 AND area>0);

8. 列出洲份名稱，和每個洲份中國家名字按子母順序是排首位的國家名。(即每洲只有列一國)

SELECT continent, name 
FROM world x
WHERE x.name = (SELECT name 
FROM world y 
WHERE x.continent = y.continent 
LIMIT 1);

9. 找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 洲份和population人口。

SELECT name, continent, population
FROM world as a
WHERE 25000000 > ALL(SELECT population 
from world as b 
where a.continent = b.continent 
and population > 0);

10. 有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。

SELECT name, continent
FROM world AS a
WHERE population/3 >= ALL(SELECT population 
FROM world AS b 
WHERE a.continent = b.continent 
AND a.population != b.population)

五、SUM and COUNT

1. 展示世界的總人口。

SELECT SUM(population)
FROM world;

2. 列出所有的洲份, 每個只有一次。

SELECT DISTINCT(continent)
FROM world;

3. 找出非洲(Africa)的GDP總和。

SELECT SUM(gdp)
FROM world
WHERE continent = 'Africa'3;

4. 有多少個國家具有至少百萬(1000000)的面積。

SELECT COUNT(name)
FROM world
WHERE area > 1000000;

5. (‘France’,‘Germany’,‘Spain’)（“法國”，“德國”，“西班牙”）的總人口是多少？

SELECT SUM(population)
FROM world
WHERE name IN ('France', 'Germany', 'Spain');

6. 對於每一個洲份，顯示洲份和國家的數量。

SELECT continent, COUNT(name)
FROM world
GROUP BY continent;

7. 對於每一個洲份，顯示洲份和至少有1000萬人(10,000,000)口國家的數目。

SELECT continent, COUNT(name)
FROM world
WHERE population > 10000000
GROUP BY continent;

8. 列出有至少100百萬(1億)(100,000,000)人口的洲份。

SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population)>= 100000000;

六、The JOIN operation

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
Modify it to show the matchid and player name for all goals scored by Germany. To identify German players, check for: teamid = ‘GER’

SELECT matchid, player 
FROM goal 
WHERE teamid = 'GER';

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.
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.
Show id, stadium, team1, team2 for just game 1012

SELECT id, stadium, team1, team2 
FROM game
WHERE id = 1012;

You can combine the two steps into a single query with a JOIN.
SELECT * FROM game JOIN goal ON (id=matchid)
The FROM clause says to merge data from the goal table with that from the game table. The ON says how to figure out which rows in game go with which rows in goal - the matchid from goal must match id from game. (If we wanted to be more clear/specific we could say
ON (game.id=goal.matchid)
The code below shows the player (from the goal) and stadium name (from the game table) for every goal scored.
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 goal.teamid = 'GER';

4.Use the same JOIN as in the previous question.
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 goal.player, goal.teamid, eteam.coach, goal.gtime 
FROM goal JOIN eteam ON (teamid = id)
WHERE gtime <= 10

6.To JOIN game with eteam you could use either
game JOIN eteam ON (team1=eteam.id) or game JOIN eteam ON (team2=eteam.id)
Notice that because id is a column name in both game and eteam you must specify eteam.id instead of just id
List the dates of the matches and the name of the team in which ‘Fernando Santos’ was the team1 coach.

SELECT game.mdate, eteam.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 goal JOIN game ON(game.id = goal.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 (game.team1='GER' or game.team2='GER');

Show teamname and the total number of goals scored.

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

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

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

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

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

For every match where ‘GER’ scored, show matchid, match date and the number of goals scored by ‘GER’

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

List every match with the goals scored by each team as shown. This will use “CASE WHEN” which has not been explained in any previous exercises.

Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.

SELECT game.mdate, game.team1,
SUM(CASE WHEN teamid = team1 THEN 1 ELSE 0 END) AS score1,team2,
SUM(CASE WHEN teamid = team2 THEN 1 ELSE 0 END) AS score2 
FROM game LEFT JOIN goal ON (game.id = matchid)  
GROUP BY mdate, matchid, team1, team2;

七、More JOIN operation

1.列出1962年首影的電影， [顯示 id, title]

SELECT id, title
FROM movie
WHERE yr=1962;

2.電影大國民 ‘Citizen Kane’ 的首影年份

select yr 
from movie
where title = 'Citizen Kane';

3.列出全部Star Trek星空奇遇記系列的電影，包括id, title 和 yr(此系統電影都以Star Trek為電影名稱的開首)。按年份順序排列。

select id, title, yr
from movie
where title like 'Star Trek%'
order by yr;

4.id是 11768, 11955, 21191 的電影是什麼名稱?

select title
from movie
where id in(11768, 11955, 21191);

5.女演員’Glenn Close’的編號 id是什麼?

select id
from actor
where name = 'Glenn Close';

6.電影北非諜影’Casablanca’ 的編號 id是什麼?

select id 
from movie
where title = 'Casablanca';

7.列出電影北非諜影 'Casablanca’的演員名單。
什麼是演員名單?
演員名單,即是電影中各演員的真實姓名清單。
使用 movieid=11768, 這是你上一題得到的結果。

select name
from actor join casting on id = actorid
where(movieid = 11768)

8.顯示電影異型’Alien’ 的演員清單。

select name
from actor join casting on actor.id=casting.actorid
where movieid =(select id from movie where title = 'Alien'); 

9.列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影。

select title
from movie join casting on id = movieid
where actorid = (select id from actor where name = 'Harrison Ford');

10.列出演員夏里遜福 ‘Harrison Ford’ 曾演出的電影，但他不是第1主角。

select title
from movie join casting on id = movieid
where actorid = (select id from actor where name = 'Harrison Ford') and ord !=1;

11.列出1962年首影的電影及它的第1主角。

select title, name
from movie 
join casting on movie.id = movieid
join actor on actor.id = actorid
where yr = 1962 and ord = 1;

尊·特拉華達’John Travolta’最忙是哪一年? 顯示年份和該年的電影數目。

SELECT yr,COUNT(title) 
FROM movie 
JOIN casting ON movie.id=movieid
JOIN actor   ON actorid=actor.id
where name='John Travolta'
GROUP BY yr
HAVING COUNT(title)=(SELECT MAX(c) 
FROM (SELECT yr,COUNT(title) AS c 
FROM movie 
JOIN casting ON movie.id=movieid
JOIN actor   ON actorid=actor.id
where name='John Travolta'
GROUP BY yr) AS t)

13.列出演員茱莉·安德絲’Julie Andrews’曾參與的電影名稱及其第1主角。
是否列了電影 "Little Miss Marker"兩次?

select title,name 
from movie 
JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
where ord=1 and movie.id in 
(select movie.id from movie join casting on movie.id=movieid
join actor on actorid=actor.id where name='Julie Andrews');

14.列出按字母順序，列出哪一演員曾作30次第1主角。

select name 
from actor
where id in(select actorid
from casting
where ord = 1
group by actorid
having count(movieid) >=30)
order by name

15.列出1978年首影的電影名稱及角色數目，按此數目由多至少排列。

select title,count(actorid) 
from movie join casting on id=movieid
where yr = 1978
group by title
order by count(actorid) desc

列出曾與演員亞特·葛芬柯’Art Garfunkel’合作過的演員姓名。

select name 
from actor join casting on actorid=id
where name!='Art Garfunkel'
and movieid in (select movieid from casting join actor on actorid=id
where name ='Art Garfunkel');

八、Using Null

1.List the teachers who have NULL for their department.

select name 
from teacher
where dept is null

2.Note the INNER JOIN misses the teachers with no department and the departments with no teacher.

SELECT teacher.name, dept.name
FROM teacher INNER JOIN dept ON (teacher.dept=dept.id)

3.Use a different JOIN so that all teachers are listed.

select teacher.name,dept.name 
from teacher left join dept on (teacher.dept=dept.id)

4.Use a different JOIN so that all departments are listed.

select teacher.name,dept.name 
from teacher right join dept on (teacher.dept=dept.id)

5.Use COALESCE to print the mobile number. Use the number ‘07986 444 2266’ if there is no number given. Show teacher name and mobile number or ‘07986 444 2266’

select name,coalesce(mobile,'07986 444 2266')
from teacher

6.Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string ‘None’ where there is no department.

select teacher.name,coalesce(dept.name,'None')
from teacher left join dept on (teacher.dept=dept.id)

7.Use COUNT to show the number of teachers and the number of mobile phones.

select count(name),count(mobile)
from teacher

8.Use COUNT and GROUP BY dept.name to show each department and the number of staff. Use a RIGHT JOIN to ensure that the Engineering department is listed.

select dept.name,count(teacher.name)
from teacher right join dept on (teacher.dept=dept.id)
group by dept.name

9.Use CASE to show the name of each teacher followed by ‘Sci’ if the teacher is in dept 1 or 2 and ‘Art’ otherwise.

select teacher.name
       ,case when teacher.dept in(1,2) then 'Sci'
             else 'Art'
        end 
from teacher

10.Use CASE to show the name of each teacher followed by ‘Sci’ if the teacher is in dept 1 or 2, show ‘Art’ if the teacher’s dept is 3 and ‘None’ otherwise.

select teacher.name
       ,case when teacher.dept in(1,2) then 'Sci'
             when teacher.dept=3 then 'Art'
             else 'None'
        end 
from teacher

九、Self join

1.How many stops are in the database.

select count(*) 
from stops

2.Find the id value for the stop ‘Craiglockhart’

select id
from stops
where name = 'Craiglockhart'

3.Give the id and the name for the stops on the ‘4’ ‘LRT’ service.

select id, name
from stops join route on stops.id = route.stop
where num = 4 and company = 'LRT'

4.The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53). Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes.

SELECT company, num, COUNT(*)
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num
having count(*) = 2

5.Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart, without changing routes. Change the query so that it shows the services from Craiglockhart to London Road.

SELECT a.company, a.num, a.stop, b.stop
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
WHERE a.stop=53 and b.stop=149

6.The query shown is similar to the previous one, however by joining two copies of the stops table we can refer to stops by name rather than by number. Change the query so that the services between ‘Craiglockhart’ and ‘London Road’ are shown. If you are tired of these places try ‘Fairmilehead’ against ‘Tollcross’

SELECT a.company, a.num, stopa.name, stopb.name
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name='Craiglockhart' and stopb.name='London Road'

7.Give a list of all the services which connect stops 115 and 137 (‘Haymarket’ and ‘Leith’)

select distinct a.company, a.num
from route a join route b on (a.num=b.num AND a.company=b.company)
where a.stop=115 and b.stop=137

8.Give a list of the services which connect the stops ‘Craiglockhart’ and ‘Tollcross’

select r1.company, r1.num
from route r1 join route r2 on (r1.num=r2.num AND r1.company=r2.company) 
join stops s1 on r1.stop=s1.id JOIN stops s2 ON r2.stop=s2.id
WHERE s1.name='Craiglockhart' AND s2.name='Tollcross'

9.Give a distinct list of the stops which may be reached from ‘Craiglockhart’ by taking one bus, including ‘Craiglockhart’ itself, offered by the LRT company. Include the company and bus no. of the relevant services.

select distinct s2.name, r2.company, r2.num
from stops s1, stops s2, route r1, route r2
where s1.name='Craiglockhart'
and s1.id=r1.stop and r1.company=r2.company 
and r1.num=r2.num and r2.stop=s2.id

10.Find the routes involving two buses that can go from Craiglockhart to Lochend.
Show the bus no. and company for the first bus, the name of the stop for the transfer,
and the bus no. and company for the second bus.

select distinct bus1.num, bus1.company, name, bus2.num, bus2.company 
from (select start1.num, start1.company, stop1.stop from route start1 join route stop1 on start1.num = stop1.num 
and start1.company = stop1.company and start1.stop != stop1.stop 
where start1.stop = (select id from stops where name = 'Craiglockhart')) bus1 join (select start2.num, start2.company, start2.stop from route start2 join route stop2 on start2.num = stop2.num 
and start2.company = stop2.company and start2.stop != stop2.stop 
where stop2.stop = (select id from stops where name = 'Sighthill')) bus2 ON bus1.stop = bus2.stop join stops ON bus1.stop = stops.id

未完待续，更新中…