SQLZOO——JOIN Quiz

目录

（选择对应题目直接跳转即可）

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

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: 

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

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

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

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

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

---

          小小的进步✌✌✌

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

 

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: 

 

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

---筛选所有与GRE比过赛的队伍，并且打败了GRE(teamid != 'GRE')

 

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

---首先利用DISTINCT去重 排除一些有重复项的选项，然后加上有日期的项，即可选出答案

SELECT DISTINCT teamid, mdate
  FROM goal JOIN game on (matchid=id)
 WHERE mdate = '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.

---筛选那些与POL比赛的队伍（team1 = 'POL' OR team2 = 'POL'），但是打败了POL的队伍（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).

---与上题解题思路一致

 

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