sqlzoo习题答案

sqlzoo作为入门sql的练习网站，上面的题目可以作为练习用来提高自身对sql语句的掌握。下面是个人在做的过程中的习题答案，希望能和大家一起交流进步，能为大家提供一些简单的参考帮助。
备注：有一两道题的答案系统无法给出正确的笑脸，希望能得到大家的意见进行改正。

第一天：SQL基础

SELECT basics/zh

1.這個例子顯示’France法國’的人口。字串應該在’單引號’中。修改此例子,以顯示德國 Germany 的人口。

SELECT population FROM world
  WHERE name = 'Germany'

2.查詢顯示面積為 5,000,000 以上平方公里的國家,該國家的人口密度(population/area)。人口密度並不是 WORLD 表格中的欄,但我們可用公式(population/area)計算出來。修改此例子,查詢面積為 5,000,000 以上平方公里的國家,對每個國家顯示她的名字和人均國內生產總值(gdp/population)。

SELECT name, gdp/population FROM world
  WHERE area > 5000000

3.檢查列表:單詞“IN”可以讓我們檢查一個項目是否在列表中。
此示例顯示了“Luxembourg 盧森堡”,“Mauritius 毛里求斯”和“Samoa 薩摩亞”的國家名稱和人口。顯示“Ireland 愛爾蘭”,“Iceland 冰島”,“Denmark 丹麥”的國家名稱和人口。

SELECT name, population FROM world
  WHERE name IN ('Ireland', 'Iceland', 'Denmark')

4.哪些國家是不是太小,又不是太大?BETWEEN 允許範圍檢查 - 注意,這是包含性的。 此例子顯示面積為 250,000 及 300,000 之間的國家名稱和該國面積。修改此例子,以顯示面積為 200,000 及 250,000 之間的國家名稱和該國面積。

SELECT name, area FROM world
  WHERE area BETWEEN 200000 AND 250000

SELECT names/zh

1.你可以用WHERE name LIKE 'B%'來找出以 B 為開首的國家。
%是萬用字元,可以用代表任何字完。找出以 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.“Iceland 冰島”和“Switzerland 瑞士”的名字都是以”land”作結束的。還有其他嗎?找出所有國家,其名字以 land 作結尾。

SELECT name FROM world
  WHERE name LIKE '%land'

第二天：一般查询

SQLZOO:SELECT from WORLD Tutorial/zh

1.閱讀此表的注意事項 觀察運行一個簡單的SQL命令的結果。

SELECT name, continent, population 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)

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

SELECT name, ROUND(population/1000000, 2), ROUND(gdp/1000000000, 2)
FROM world
WHERE continent='South America'

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

SELECT name, ROUND(gdp/population, -3)
FROM world
WHERE gdp>1000000000000

11.The CASE statement shown is used to substitute North America for Caribbean in the third column.Show the name - but substitute Australasia for Oceania - for countries beginning with N.

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

12.Show the name and the continent - but substitute Eurasia for Europe and Asia; substitute America - for each country in North America or South America or Caribbean. 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 AustralasiaCountries in Eurasia and Turkey go to Europe/AsiaCaribbean islands starting with ‘B’ go to North America, other Caribbean islands go to South AmericaShow the name, the original continent and the new continent of all countries.

SELECT name, continent, CASE WHEN continent='Oceania' THEN 'Australasia'
WHEN continent IN ('Europe ','Asia') THEN 'Europe/Asia'
WHEN continent='Caribbean'  THEN (CASE WHEN name like 'B%' THEN  'North America' ELSE 'South America' END) 
ELSE continent END
FROM world

SELECT from Nobel Tutorial/zh

1.更改查詢以顯示1950年諾貝爾獎的獎項資料。

SELECT yr, subject, winner
  FROM nobel
 WHERE yr = 1950

2.顯示誰贏得了1962年文學獎(Literature)。

SELECT winner
  FROM nobel
 WHERE yr = 1962
   AND subject = 'Literature'

3.顯示“愛因斯坦”(‘Albert Einstein’) 的獲獎年份和獎項。

SELECT yr, subject
FROM nobel
WHERE winner='Albert Einstein'

4.顯示2000年及以後的和平獎(‘Peace’)得獎者。

SELECT winner
  FROM nobel
 WHERE yr >=2000
   AND subject = 'Peace'

5.顯示1980年至1989年(包含首尾)的文學獎(Literature)獲獎者所有細節（年，主題，獲獎者）。

SELECT yr, subject, winner
  FROM nobel
 WHERE yr BETWEEN 1980 AND 1989
   AND subject = 'Literature'

6.顯示總統獲勝者的所有細節：西奧多•羅斯福 Theodore Roosevelt伍德羅•威爾遜 Woodrow Wilson吉米•卡特 Jimmy Carter

SELECT * FROM nobel
WHERE winner IN ('Theodore Roosevelt',
                  'Woodrow Wilson',
                  'Jimmy Carter')

7.顯示名字為John 的得獎者。 (注意:外國人名字(First name)在前，姓氏(Last name)在後)

SELECT winner 
FROM nobel
WHERE winner like 'John%'

8.顯示1980年物理學(physics)獲獎者，及1984年化學獎(chemistry)獲得者。

SELECT yr, subject, winner
FROM nobel
WHERE (yr=1980 AND subject='physics') OR (yr=1984 AND subject='chemistry')  

9.查看1980年獲獎者，但不包括化學獎(Chemistry)和醫學獎(Medicine)。

SELECT yr, subject, winner 
FROM nobel
WHERE yr=1980 and subject not in ('Chemistry', 'Medicine')

10.顯示早期的醫學獎(Medicine)得獎者（1910之前，不包括1910），及近年文學獎(Literature)得獎者（2004年以後，包括2004年）。

SELECT yr, subject, winner 
FROM nobel
WHERE (yr<1910 AND subject ='Medicine') OR (yr>=2004 AND subject ='Literature')

11.Find all details of the prize won by PETER GRÜNBERG

SELECT yr, subject, winner 
FROM nobel
WHERE winner='PETER GRÜNBERG'

12.查找尤金•奧尼爾EUGENE O’NEILL得獎的所有細節 Find all details of the prize won by EUGENE O’NEILL

SELECT yr, subject, winner 
FROM nobel
WHERE winner='EUGENE O\'NEILL'

13.騎士列隊 Knights in order列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者，然後同年再按名稱順序排列。

SELECT winner, yr, subject 
FROM nobel
WHERE winner like 'Sir%' 
ORDER BY yr DESC, winner 

14.The expression subject IN (‘Chemistry’,‘Physics’) can be used as a value - it will be 0 or 1.Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

SELECT winner, subject
  FROM nobel
 WHERE yr=1984
 ORDER BY  subject IN ('Physics','Chemistry'), subject, winner

第三天：子查询

SELECT within SELECT Tutorial/zh

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 gdp/population >
     (SELECT gdp/population FROM world
      WHERE name='United Kingdom')
and continent='Europe'

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/(SELECT population FROM world WHERE name='Germany')*100,0),'%') 
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 a
WHERE name =
(SELECT name FROM world b WHERE a.continent=b.continent ORDER BY name limit 1)

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

SELECT a.name, a.continent, a.population
FROM world a, 
(SELECT continent, MAX(population) population FROM world GROUP BY continent) b
WHERE a.continent=b.continent AND b.population<=25000000

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

SELECT a.name, a.continent FROM world a,
(SELECT sum(population) sum_population, continent
from world 
GROUP BY continent) b
WHERE a.continent=b.continent AND a.population>=3*(b.sum_population-a.population)

The nobel table can be used to practice more subquery./zh

1.紅十字國際委員會 (International Committee of the Red Cross) 曾多次獲得和平獎。 試找出與紅十字國際委員會同年得獎的文學獎(Literature)得獎者和年份。

SELECT winner, yr FROM nobel a
WHERE a.yr in 
(SELECT yr FROM nobel 
where winner = 'International Committee of the Red Cross')
and a.subject='Literature'

2.日本物理學家益川敏英 (Toshihide Maskawa) 曾獲得物理獎。同年還有兩位日本人一同獲得物理獎。試列出這2位日本人的名稱。

SELECT winner FROM nobel a
WHERE a.yr=
(select yr
from nobel
where winner = 'Toshihide Maskawa')
AND a.subject='Physics'
AND winner <> 'Toshihide Maskawa'

3.首次頒發的經濟獎 (Economics)的得獎者是誰?

SELECT a.winner FROM nobel a
WHERE a.yr=
(SELECT yr FROM nobel
WHERE subject='Economics'
ORDER BY yr
LIMIT 1)
AND subject='Economics'

4.哪幾年頒發了物理獎，但沒有頒發化學獎?

SELECT DISTINCT yr FROM nobel a
WHERE 'Physics' in 
(SELECT DISTINCT subject FROM nobel b WHERE a.yr=b.yr)
AND 'Chemistry' NOT IN (SELECT DISTINCT subject FROM nobel b WHERE a.yr=b.yr)

5.哪幾年的得獎者人數多於12人呢? 列出得獎人數多於12人的年份，獎項和得獎者。

SELECT a.yr, a.subject, a.winner
FROM nobel a,(SELECT yr,count(winner) FROM nobel GROUP BY yr having count(winner)>12) b
WHERE a.yr=b.yr 

6.哪些得獎者獲獎多於1次呢？他們是哪一年獲得哪項獎項呢？ 列出他們的名字，獲獎年份及獎項。先按名字，再按年份順序排序。

SELECT a.winner, a.yr, a.subject FROM nobel a,
(SELECT winner, count(winner) FROM nobel GROUP BY winner having count(winner)>1) b
WHERE a.winner=b.winner
ORDER BY a.winner, a.yr

第四天：聚合函数和NULL

SUM and COUNT/zh

1.展示世界的總人口。

SELECT SUM(population)
FROM world

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

SELECT DISTINCT continent 
FROM world

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

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

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 nobel table can be used to practice more SUM and COUNT functions./zh

1.找出總共有多少個獎頒發了。

SELECT COUNT(subject) FROM nobel

2.列出每一個獎項(subject), 只列一次

SELECT DISTINCT(subject) FROM nobel

3.找出物理獎的總頒發次數。

SELECT COUNT(subject)
 FROM nobel
WHERE subject='Physics'

4.對每一個獎項(Subject),列出頒發數目。

SELECT subject, COUNT(subject)
 FROM nobel
GROUP BY subject

5.對每一個獎項(Subject),列出首次頒發的年份。

SELECT distinct a.subject,a.yr  FROM nobel a
WHERE a.yr=(SELECT yr FROM nobel b WHERE a.subject=b.subject ORDER BY yr LIMIT 1)
ORDER BY a.subject

6.對每一個獎項(Subject),列出2000年頒發的數目。

SELECT subject, count(subject)
FROM nobel
WHERE yr=2000
GROUP BY subject  

7.對每一個獎項(Subject),列出有多少個不同的得獎者。

SELECT subject,COUNT( DISTINCT winner)
FROM nobel
GROUP BY subject

8.對每一個獎項(Subject),列出有多少年曾頒發過。

SELECT subject, COUNT(DISTINCT yr)
FROM nobel
GROUP BY subject

9.列出哪年曾同年有3個物理獎Physics得獎者。

SELECT yr
FROM nobel
WHERE subject='Physics'
GROUP BY yr HAVING COUNT(winner)=3

10.列出誰得獎多於一次。

SELECT winner
FROM nobel
GROUP BY winner
HAVING COUNT(winner)>1

11.列出誰獲得多於一個獎項(Subject)

SELECT winner
FROM nobel
GROUP BY winner
HAVING COUNT(DISTINCT subject)>1

12.哪年哪獎項，是同一獎項(subject)頒發給3個人。只列出2000年及之後的資料。

SELECT yr, subject
FROM nobel
WHERE yr>=2000
GROUP BY yr, subject
HAVING COUNT(winner)=3

Using Null/zh

1.列出學系department是NULL值的老師。

SELECT name
FROM teacher
WHERE dept IS NULL

2.注意INNER JOIN 不理會沒有學系的老師及沒有老師的學系。

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

3.使用不同的JOIN(外連接)，來列出全部老師。

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

4.使用不同的JOIN(外連接)，來列出全部學系。

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.使用COUNT來數算老師和流動電話數目。

SELECT COUNT(name), COUNT(mobile)
FROM teacher

8.使用COUNT 和 GROUP BY dept.name來顯示每一學系的老師數目。 使用 RIGHT JOIN 以確保工程系Engineering 是在當中。

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 name,CASE WHEN 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 name,CASE WHEN dept IN ('1','2') THEN 'Sci' 
WHEN dept='3' THEN 'Art' 
ELSE 'None' END
FROM teacher

Scottish Parliament/zh

1.一個成員被工黨逐出黨，現沒屬任何黨。找出他。

SELECT name
FROM msp
WHERE party IS NULL

2.列出每個黨及其領導人。

SELECT name, leader
FROM party

3.列出每個黨及其領導人，這些黨其實是沒有領導人的。

SELECT name, leader
FROM party
WHERE leader IS NOT NULL

4.列出政黨名單，當中最少有一名黨員在議會內。

SELECT DISTINCT party.name FROM party JOIN msp on party.code=msp.party

5.列出議會成員的名單，如有所屬政黨，一同列出。確保 Canavan MSP, Dennis 是在名單中。 按msp.name順序排列。

SELECT msp.name, party.name
FROM msp left join party on msp.party=party.code
order by msp.name

6.列出議會中每一政黨的黨員人數。

SELECT party.name, count(msp.name)
FROM msp JOIN party on msp.party=party.code
GROUP BY party.name

7.列出每一政黨的議會中黨員人數，包括沒有黨員在議會中的政黨。

SELECT party.name, count(msp.name)
FROM msp RIGHT JOIN party on msp.party=party.code
GROUP BY party.name

第五天：表连接

The JOIN operation/zh

1.第一個例子列出球員姓氏為’Bender’的入球數據。 * 表示列出表格的全部欄位，簡化了寫matchid, teamid, player, gtime語句。
修改此SQL以列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查: teamid = ‘GER’

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

2.由以上查詢，你可見Lars Bender’s 於賽事 1012入球。.現在我們想知道此賽事的對賽隊伍是哪一隊。留意在 goal 表格中的欄位 matchid ，是對應表格game的欄位id。我們可以在表格 game中找出賽事1012的資料。只顯示賽事1012的 id, stadium, team1, team2

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

3.我們可以利用JOIN來同時進行以上兩個步驟。SELECT *
FROM game JOIN goal ON (id=matchid)
語句FROM 表示合拼兩個表格game 和 goal的數據。語句 ON 表示如何找出 game中每一列應該配對goal中的哪一列 – goal的 id 必須配對game的 matchid 。 簡單來說，就是ON (game.id=goal.matchid)以下SQL列出每個入球的球員(來自goal表格)和場館名(來自game表格)修改它來顯示每一個德國入球的球員名，隊伍名，場館和日期。

SELECT player,teamid,stadium,mdate
  FROM game JOIN goal ON (id=matchid)
WHERE teamid='GER'

4.使用上題相同的 JOIN語句，列出球員名字叫Mario (player LIKE ‘Mario%’)有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player

SELECT team1,team2,player
  FROM game JOIN goal ON (id=matchid)
WHERE player like 'Mario%'

5.表格eteam 貯存了每一國家隊的資料，包括教練。你可以使用語句 goal JOIN eteam on teamid=id來合拼 JOIN 表格goal 到 表格eteam。列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime

SELECT player, teamid, coach,gtime
  FROM goal join eteam on goal.teamid=eteam.id
 WHERE gtime<=10

6.要合拼JOIN 表格game 和表格 eteam，你可以使用game JOIN eteam ON (team1=eteam.id)
或game JOIN eteam ON (team2=eteam.id)注意欄位id同時是表格game 和表格 eteam的欄位，你要清楚指出eteam.id而不是只用id列出’Fernando Santos’作為隊伍1 team1 的教練的賽事日期，和隊伍名。

SELECT mdate, teamname
  FROM game join eteam on game.team1=eteam.id
 WHERE eteam.coach='Fernando Santos'

7.列出場館 'National Stadium, Warsaw’的入球球員。

SELECT player FROM goal join game on goal.matchid=game.id
WHERE game.stadium= 'National Stadium, Warsaw'

8.以下例子找出德國-希臘Germany-Greece 的八強賽事的入球
修改它，只列出全部賽事，射入德國龍門的球員名字。

SELECT DISTINCT player
  FROM game JOIN goal ON matchid = id 
    WHERE  (team1='GER' OR team2= 'GER') AND teamid<>'GER'

9.列出隊伍名稱 teamname 和該隊入球總數
COUNT and GROUP BY

SELECT teamname,COUNT(player)
  FROM eteam JOIN goal ON id=teamid
 GROUP BY teamname

10.列出場館名和在該場館的入球數字。

SELECT stadium,COUNT(player)
  FROM game JOIN goal ON id=matchid
 GROUP BY stadium

11.每一場波蘭’POL’有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。

SELECT matchid,mdate,COUNT(player)
  FROM game JOIN goal ON matchid = id 
 WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY matchid,mdate

12.每一場德國’GER’有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。

SELECT matchid,mdate,COUNT(player)
  FROM game JOIN goal ON matchid = id 
 WHERE (team1 = 'GER' OR team2 = 'GER') AND teamid=''
GROUP BY matchid,mdate

13.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 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,team1,team2

Music Tutorial/zh

1.找出　收錄　歌曲song ‘Alison’　碟名title 和 歌手　artist。.

SELECT title, artist
  FROM album JOIN track
         ON (album.asin=track.album)
 WHERE song = 'Alison'

2.哪一歌手artist 錄了歌曲 song ‘Exodus’?

SELECT artist FROM album join track on album.asin=track.album
WHERE song='Exodus'

3.為大碟album ‘Blur’， 顯示每一首歌的歌名 song 。

SELECT song FROM  album join  track on album.asin=track.album
WHERE title='Blur'

4.為每一大碟album顯示歌名title和每大碟的歌曲。 track數量。

SELECT title,count(song)  FROM album JOIN track ON (asin=album)
 GROUP BY title

5.為每一大碟album列出碟名title 歌名中有’Heart’一詞的歌曲數量。 (沒有這些歌的大碟不用列出).

SELECT title,count(song)  FROM album JOIN track ON (asin=album)
WHERE song like '%Heart%'
 GROUP BY title

6.主題歌曲是歌名 song 和大碟名字 title相同。找出主題歌曲。

SELECT song  FROM album JOIN track ON (asin=album)
WHERE song =title

7.同名大碟是指大碟和歌手名字相同。 (例如大碟’Blur’ 是由樂隊 'Blur’主唱)。 找出同名大碟。

SELECT title FROM album
WHERE title=artist

8.找出歌曲收錄在２隻以上的大碟中。列出收錄次數。

SELECT song,count(album)  FROM track
 GROUP BY song
HAVING count(album)>2

9.好價大碟是指大碟中每一首歌曲的價格是少於5角。 找出好價大碟，列出大碟名字，售價和歌曲數量。

SELECT title,price,song_qty  from album a join  ( SELECT album,count(song) song_qty FROM track
 GROUP BY album) b on a.asin=b.album
WHERE price/song_qty<0.5
ORDER BY title

10.歌手Wagner的大碟 Ring cycle 有173首歌曲, 歌手Bing Crosby有一大碟 收錄了 101首歌曲。按歌曲量(多至少)列出每一大碟的碟名和歌曲數量。

SELECT title,song_qty  from album a  right join  ( SELECT album,count(song) song_qty FROM track
 GROUP BY album) b on a.asin=b.album
ORDER BY song_qty  DESC

More JOIN operations/zh

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 actor.name FROM actor,movie,casting
WHERE actor.id=casting.actorid and movie.id=casting.movieid  and title='Casablanca'

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

SELECT actor.name FROM (actor join casting on actor.id=casting.actorid) join movie on casting.movieid=movie.id
WHERE title='Alien'

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

SELECT title FROM actor,movie,casting
WHERE actor.id=casting.actorid and movie.id=casting.movieid  and name='Harrison Ford'

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

SELECT title FROM actor,movie,casting
WHERE actor.id=casting.actorid and movie.id=casting.movieid  and name='Harrison Ford' and ord<>1

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

SELECT title,name FROM actor,movie,casting
WHERE actor.id=casting.actorid and movie.id=casting.movieid  and yr=1962 and ord=1

12.尊·特拉華達’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主角。

SELECT title,name FROM actor,movie,casting 
WHERE actor.id=casting.actorid AND movie.id=casting.movieid AND movieid in 
(SELECT movieid FROM casting join actor on actor.id=casting.actorid 
WHERE name='Julie Andrews') AND ord=1

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

SELECT name FROM actor JOIN casting on actor.id=casting.actorid 
WHERE ord=1
GROUP BY name
HAVING COUNT(name)>=30

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

SELECT title,count(actorid) qty 
FROM  movie join casting on movie.id=casting.movieid
WHERE yr=1978
GROUP BY title
ORDER BY qty DESC

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

SELECT name FROM actor join casting on actor.id=casting.actorid
WHERE name<>'Art Garfunkel' AND movieid in 
(SELECT movieid FROM casting join actor on actor.id=casting.actorid
WHERE name='Art Garfunkel')

第六天：自连接及函数

Self join/zh

1.數據庫中有多少個站stops。

SELECT COUNT(*) FROM stops

2.找出車站 ‘Craiglockhart’ 的 id

SELECT id FROM stops
WHERE name= 'Craiglockhart'

3.列出巴士公司’LRT’的’4’號巴士線的站編號id 和 站名name

SELECT id,name
FROM route join stops on stops.id=route.stop
WHERE company='LRT' AND num='4'

4.以下查詢列出途經 London Road (149) 或 Craiglockhart (53)的巴士線號碼。注意有兩條路線會經過這兩個站兩次。 加入 HAVING 語句來限制只列出這兩條路線。

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

5.執行自我合拼來，留意b.stop代表由Craiglockhart出發不用轉車可前住的地方。 修改它來顯示由Craiglockhart到 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.此題和上題相似，但是用兩個stops表來自我合拼。這樣我們可以用站名而非站編號。 修改它來顯示由Craiglockhart到 London Road的服務資料。 如你太悶，可試一試由 ‘Fairmilehead’ 到 ‘Tollcross’ (系統會當答錯的。正確有3條路線:11,15,315)

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.列出連接115 和 137 (‘Haymarket’ 和 ‘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.列出連接車站stops ‘Craiglockhart’ 到 ‘Tollcross’ 的公司名和路線號碼。

SELECT DISTINCT a.company,a.num
FROM route a 
JOIN route b ON a.company = b.company AND a.num = b.num
JOIN stops c ON c.id = a.stop
JOIN stops d ON d.id = b.stop
WHERE c.name = 'Craiglockhart' AND d.name = 'Tollcross'

9.不重覆列出可以由 ‘Craiglockhart’ 乘一程車到達的站stops，包括’Craiglockhart’本身。 列出站名，公司名和路線號碼。

SELECT d.name,a.company,a.num
FROM route a 
JOIN route b ON a.company = b.company AND a.num = b.num
JOIN stops c ON c.id = a.stop
JOIN stops d ON d.id = b.stop
WHERE c.name = 'Craiglockhart'

10.Find the routes involving two buses that can go from Craiglockhart to Sighthill.
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 bus1.num1,bus1.company1,bus2.stop ,bus2.num2,bus2.company2 FROM
(SELECT a.num num1,a.company company1,c.name name1,d.name name2
FROM route a 
JOIN route b ON a.company = b.company AND a.num = b.num
JOIN stops c ON c.id = a.stop
JOIN stops d ON d.id = b.stop
WHERE  c.name='Craiglockhart') bus1
JOIN 
(SELECT a.stop ,a.num num2,a.company company2,c.name name3,d.name name4
FROM route a 
JOIN route b ON a.company = b.company AND a.num = b.num
JOIN stops c ON c.id = a.stop
JOIN stops d ON d.id = b.stop
WHERE  d.name='Sighthill') bus2
ON bus1.name1=bus2.name3 AND bus1.name2=bus2.name4