SQLZOO JOIN/联结 答案

编程入门 行业动态 更新时间:2024-10-07 06:38:07

SQLZOO JOIN/联结 <a href=https://www.elefans.com/category/jswz/34/1769768.html style=答案"/>

SQLZOO JOIN/联结 答案

ZOO 联结 歐洲國家盃 练习1

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

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

2.由以上查詢,你可見Lars Bender’s 於賽事 1012入球。.現在我們想知道此賽事的對賽隊伍是哪一隊。
只顯示賽事1012的 id, stadium, team1, team2

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

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

SELECT g2.player, g2.teamid, g1.stadium, g1.mdate
FROM game g1 JOIN goal g2 ON g1.id = g2.matchid
WHERE g2.teamid = 'GER';

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

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

5.列出每場球賽中首10分鐘gtime<=10有入球的球員 player, 隊伍teamid, 教練coach, 入球時間gtime

SELECT g.player, g.teamid, e.coach, g.gtime
FROM eteam e JOIN goal g ON e.id = g.teamid
WHERE g.gtime <= 10;

6.列出’Fernando Santos’作為隊伍1 team1 的教練的賽事日期,和隊伍名。

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

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

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

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

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

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

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

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

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

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

SELECT

更多推荐

SQLZOO JOIN/联结 答案

本文发布于:2024-02-13 09:28:36,感谢您对本站的认可!
本文链接:https://www.elefans.com/category/jswz/34/1758115.html
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系,我们将在24小时内删除。
本文标签:答案   SQLZOO   JOIN

发布评论

评论列表 (有 0 条评论)
草根站长

>www.elefans.com

编程频道|电子爱好者 - 技术资讯及电子产品介绍!