mysql----JOIN Quiz

作者:分分快三全天计划网站

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

coach

id

Netherlands
Poland
Republic of Ireland
Ukraine
GRE
CZE
POL
RUS

RUS

...

1002

Greece

8 June 2012

Fernando Santos

1001

1003

matchid

...

GRE

eteam

Poland

  SELECT DISTINCT player, teamid 
  FROM game JOIN goal ON matchid = id 
  WHERE stadium = 'National Stadium, Warsaw' 
 AND (team1 = 'GER' OR team2 = 'GER')
   AND teamid != 'GER'

  SELECT DISTINCT player, teamid 
   FROM game JOIN goal ON matchid = id 
  WHERE stadium = 'National Stadium, Warsaw' 
 AND (team1 = 'POL' OR team2 = 'POL')
   AND teamid != 'POL'


 SELECT DISTINCT player, teamid 
   FROM game JOIN goal ON matchid = id 
  WHERE stadium = 'National Stadium, Warsaw' AND teamid != 'POL'


 SELECT DISTINCT player, teamid 
   FROM game JOIN goal ON matchid = id 
  WHERE stadium = 'Stadion Miejski (Wroclaw)' 
 AND (team1 = 'POL' OR team2 = 'POL')
  AND teamid != 'POL'


 SELECT DISTINCT stadium, mdate 
   FROM game JOIN goal ON matchid = id 
  WHERE stadium = 'National Stadium, Warsaw' 
 AND (team1 = 'POL' OR team2 = 'POL')
  AND teamid != 'POL'

teamname

player

1001

1004

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:

RUS 9 June 2012
GRE 9 June 2012
RUS 9 June 2012
CZE 9 June 2012
SELECT DISTINCT teamid, mdate
  FROM goal JOIN game on (matchid=id)
 WHERE mdate = '9 June 2012'

teamid

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

Michal Bilek

Robert Lewandowski

POL

stadium

12 June 2012

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

RUS

 eteam JOIN game ON (id=team1)

 eteam JOIN game ON (id=team2)

 eteam JOIN goal ON (teamid=id)

 game  JOIN goal ON (id=matchid)

 game  JOIN goal ON (team1=teamid OR team2=teamid)

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

Poland 76

8 June 2012

POL

Russia

1001

Dimitris Salpingidis

DEN 9 June 2012
DEN 9 June 2012
POL 9 June 2012
RUS 9 June 2012

17

2
2
1
2

Dick Advocaat

RUS

DEN 9 June 2012
GER 9 June 2012

Czech Republic

game

mdate

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

 gtime, mdate, stadium, matchid

 mdate, stadium, id

 matchid, teamid, player, gtime, id, teamname, coach

 matchid, teamid, player, gtime, mdate, stadium, team1

 stadium, team1, team2
Netherlands 2
Poland 2
Republic of Ireland 1
Ukraine 2

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

GRE

51

CZE

goal

CZE

Franciszek Smuda

team1

12 June 2012

POL

...

GRE

SELECT teamname, COUNT(*)
  FROM eteam JOIN goal ON teamid = id
 GROUP BY teamname
HAVING COUNT(*) < 3
SELECT DISTINCT player, teamid, gtime
  FROM game JOIN goal ON matchid = id
 WHERE stadium = 'National Stadium, Warsaw'
   AND (( teamid = team2 AND team1 != 'ITA') OR ( teamid = team1 AND team2 != 'ITA'))

SELECT DISTINCT player, teamid, gtime
  FROM game JOIN goal ON matchid = id
 WHERE stadium = 'Stadion Miejski (Wroclaw)'
   AND (( teamid = team2 AND team1 != 'ESP') OR ( teamid = team1 AND team2 != 'ESP'))

SELECT DISTINCT player, teamid, gtime
  FROM game JOIN goal ON matchid = id
 WHERE stadium = 'Stadion Miejski (Wroclaw)'
   AND (( teamid = team2 AND team1 != 'ITA') OR ( teamid = team1 AND team2 != 'ITA'))

SELECT DISTINCT teamid, gtime
  FROM game JOIN goal ON matchid = id
 WHERE stadium = 'Stadion Miejski (Wroclaw)'
   AND (( teamid = team2 AND team1 != 'ITA') OR ( teamid = team1 AND team2 != 'ITA'))

SELECT DISTINCT player, teamid, gtime
  FROM game JOIN goal ON matchid = id
 WHERE team1 != 'ITA' AND team2 !='ITA'

Alan Dzagoev

CZE

gtime

RUS

DEN
GER

id

National Stadium, Warsaw

GRE

1001

Stadion Miejski (Wroclaw)

National Stadium, Warsaw

15

Republic of Ireland 1

team2

Stadion Miejski (Wroclaw)

82

SELECT player, teamid, COUNT(*)
  FROM game JOIN goal ON matchid = id
 WHERE (team1 = "GRE" OR team2 = "GRE")
   AND teamid != 'GRE'
 GROUP BY player, teamid

SELECT player, teamid, COUNT(*)
  FROM game JOIN goal ON matchid = id
 WHERE (team1 = "GRE") AND teamid != 'GRE'
 GROUP BY player, teamid

SELECT player, teamid, COUNT(*)
  FROM game JOIN goal ON matchid = id
 WHERE (team1 = "POL" OR team2 = "POL")
   AND teamid != 'POL'
 GROUP BY player, teamid

SELECT player, teamid, COUNT(*)
  FROM game JOIN goal WITH matchid = id
 WHERE (team1 = "GRE" OR team2 = "GRE")
   AND teamid != 'GRE'
 GROUP BY player, teamid

SELECT player, teamid
  FROM game JOIN goal ON matchid = id
 WHERE (team1 = "GRE" OR team2 = "GRE")
   AND teamid != 'GRE'
 GROUP BY player, teamid

JOIN quiz

RUS

POL

Roman Pavlyuchenko

1002

本文由分分快三计划发布,转载请注明来源

关键词: 分分快三计划