第一個例子列出球員姓氏為'Bender'的入球數據。 * 表示列出表格的全部欄位，簡化了寫matchid, teamid, player, gtime語句。
修改此SQL以列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員，要檢查: teamid = 'GER'
SELECT matchid,player FROM goal
WHERE teamid='GER'
由以上查詢，你可見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
我們可以利用JOIN來同時進行以上兩個步驟。
以下SQL列出每個入球的球員(來自goal表格)和場館名(來自game表格)
修改它來顯示每一個德國入球的球員名，隊伍名，場館和日期。
SELECT player,teamid,stadium,mdate
FROM game JOIN goal ON (id=matchid)
where teamid='GER'
列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player
select team1,team2,player
from game join goal on(id=matchid)
where player like 'Mario%'
注意欄位id同時是表格game 和表格 eteam的欄位，你要清楚指出eteam.id而不是只用id
列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期，和隊伍名。
select mdate,teamname
from game join eteam on team1=eteam.id
where coach='Fernando Santos'
列出場館 'National Stadium, Warsaw'的入球球員。
select player
from game join goal on id=matchid
where stadium='National Stadium, Warsaw'
修改它，只列出全部賽事，射入德國龍門的球員名字。
SELECT DISTINCT player
FROM game JOIN goal ON matchid = id
WHERE (team1='GER' OR team2='GER') AND (teamid != 'GER')
列出隊伍名稱 teamname 和該隊入球總數
SELECT teamname, count(gtime)
FROM eteam JOIN goal ON id=teamid
group by teamname
列出場館名和在該場館的入球數字。
select stadium,count(gtime)
from game join goal on id=matchid
group by stadium
每一場波蘭'POL'有參與的賽事中，列出賽事編號 matchid, 日期date 和入球數字。
SELECT matchid,mdate,count(gtime)
FROM game JOIN goal ON matchid = id
WHERE (team1 = 'POL' OR team2 = 'POL')
group by matchid,mdate
每一場德國'GER'有參與的賽事中，列出賽事編號 matchid, 日期date 和德國的入球數字。
select matchid,mdate,count(teamid)
from game join goal on matchid=id
where teamid='GER'
group by matchid,mdate
| mdate | team1 | score1 | team2 | score2 |
|---|---|---|---|---|
| 1 July 2012 | ESP | 4 | ITA | 0 |
| 10 June 2012 | ESP | 1 | ITA | 1 |
| 10 June 2012 | IRL | 1 | CRO | 3 |
| ... | ||||
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,matchid,team1,team2