sqlzoo整理————The JOIN operation/zh

近在练习sql,整理了sqlzoo中的答案,特此记录。

The_JOIN_operation/zh

1.列出 賽事編號matchid 和球員名 player ,該球員代表德國隊Germany入球的。要找出德國隊球員,要檢查: teamid = 'GER'

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

2.只顯示賽事1012的 id, stadium, team1, team2

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

3.顯示每一個德國入球的球員名,隊伍名,場館和日期。

SELECT player,teamid,stadium, mdate FROM goal
JOIN game ON game.id = goal.matchid
WHERE teamid="GER"

4.列出球員名字叫Mario (player LIKE 'Mario%')有入球的 隊伍1 team1, 隊伍2 team2 和 球員名 player

SELECT team1,team2,player  FROM goal
JOIN game
ON goal.matchid = game.id
WHERE player LIKE 'Mario%'

5.列出每場球賽中首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.列出'Fernando Santos'作為隊伍1 team1 的教練的賽事日期,和隊伍名。

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

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

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

8.列出全部賽事,射入德國龍門的球員名字。

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

9.列出隊伍名稱 teamname 和該隊入球總數

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

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

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

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

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

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

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

13.List every match with the goals scored by each team as shown. 

SELECT mdate,team1,
(SELECT COUNT(teamid) FROM goal WHERE goal.teamid = game.team1 AND game.id = goal.matchid) AS score1,
team2,
(SELECT COUNT(teamid) FROM goal WHERE goal.teamid = game.team2 AND game.id = goal.matchid) AS score2
FROM game 
ORDER BY mdate, id, team1 and team2

 

 

以上,记录本人学习过程。

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值