The JOIN operation

注意：where语句中对表示条件的需要用单引号， 下面的译文使用的是有道翻译如有不正确，请直接投诉有道

01.Modify it to show the matchid and player name for all goals scored by Germany. To identify German players, check for: teamid = 'GER

译文：修改后显示德国队所有进球的比赛id和球员名字。要确定德国球员，请检查:teamid = 'GER'

SELECT matchid, player FROM goal

 WHERE teamid = 'GER';

02.Show id, stadium, team1, team2 for just game 1012

　　SELECT id,stadium,team1,team2

　　FROM game

　　WHERE id = 1012;

03.Modify it to show the player, teamid, stadium and mdate for every German goal.

译文：修改它，显示球员，teamid，体育场和mdate为每个德国进球。

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

04.Show the team1, team2 and player for every goal scored by a player called Mario player LIKE 'Mario%'

译文：展示一个叫马里奥的球员(比如“马里奥%”)每进一球的团队1、团队2和球员。

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

05.Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10

译文：显示球员，teamid，教练，在前10分钟内所有的进球gtime<=10

SELECT player, teamid,coach, gtime FROM goal JOIN eteam on teamid=id WHERE gtime<=10;

06.List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.

译文：请列出比赛日期和由“费尔南多·桑托斯”担任球队教练的球队名称。

SELECT mdate, teamname FROM game JOIN eteam on (team1=eteam.id) WHERE coach = 'Fernando Santos';

07.List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'

译文：列出球场为“华沙国家体育场”的比赛中每进一球的球员名单

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

08.Instead show the name of all players who scored a goal against Germany.

译文：显示所有在对德国队的比赛中进球的球员的名字。

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

09.Show teamname and the total number of goals scored.

译文：显示球队名称和进球总数

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

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

译文：显示球场和每个球场的进球数

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

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

译文：对于每一场涉及“POL”的比赛，显示比赛id、日期和进球次数。

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

12.For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'

译文：对于每一场“GER”进球的比赛，显示matchid、比赛日期和“GER”进球的次数

SELECT DISTINCT matchid, mdate, COUNT(teamid) FROM game JOIN goal ON matchid = id WHERE ( team1 = 'GER' AND teamid = 'GER') OR (team2 = 'GER' AND teamid = 'GER') group by matchid, mdate;

13.Sort your result by mdate, matchid, team1 and team2.

译文：根据mdate, matchid, team1和team2对你的结果排序。

# 写不出来

练习网址：https://sqlzoo.net/wiki/The_JOIN_operation

——————————————————————————————————————————————————————————————————————————————————————————————————————————

最新文章

  1. 转: CentOS安装jdk8
  2. django url.py使用
  3. 用Visual Studio Code 开发应用之 安装 Visual Studio Code
  4. Wijmo 2016 V1 强势发布，全面支持Angular 2
  5. java练习题：解一元二次方程、判断闰年、判断标准身材、三个数取最大值
  6. java操作excel总结---poi
  7. 安装pdo.so和pdo_mysql.so还有pcntl.so扩展到php中
  8. XAMPP 的安装配置
  9. hdu 1002 A+B
  10. 2014年度辛星css教程夏季版第二节
  11. Bloom Filter 原理与应用
  12. HTML （1）href与Action，get post
  13. WebForm 三级联动
  14. mysql数据表增删改查
  15. Linux-逻辑卷LVM
  16. 类似“未能加载文件或程序集“tesseractengine3”或它的某一个依赖项”等一些问题的解决方案
  17. Linux的/etc/services文件的作用？
  18. Android app启动是出现白屏或者黑屏如何解决？
  19. 第八章 计时器（DIGCLOCK）
  20. wamp下修改mysql root用户的登录密码方法

热门文章

  1. python编程从入门到实践笔记
  2. 理解css中Grid布局，在项目中如何实现grid页面布局
  3. 利用IDEA搭建JDK源码阅读环境
  4. Spark 两种方法计算分组取Top N
  5. pythonl操作数据库
  6. ::before 和 :after 中双冒号和单冒号有什么区别？
  7. C++中类继承public，protected和private关键字作用详解及派生类的访问权限
  8. bzoj2016[Usaco2010]Chocolate Eating*
  9. 从零开始学Electron笔记（六）
  10. Crystal Reports --报表设计