6-JOIN.md

Questions Index

• Ques 1

• Ques 2

• Ques 3

• Ques 4

• Ques 5

• Ques 6

• Ques 7

• Ques 8

• Ques 9

• Ques 10

• Ques 11

• Ques 12

• Ques 13

Ques 1. Show the matchid and player name for all goals scored by Germany.

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

Ques 2. Show id, stadium, team1, team2 for just game 1012.

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

Ques 3. Show the player, teamid, stadium and mdate for every German goal.

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

Ques 4. Show the team1, team2 and player for every goal scored by a player called Mario.

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

Ques 5. Show player, teamid, coach, gtime for all goals scored in the first 10 minutes.

SELECT player, teamid, coach, gtime
FROM goal
JOIN eteam ON (teamid=id)
WHERE gtime <= 10

Ques 6. 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 g
JOIN eteam e ON (g.team1 = e.id)
WHERE e.coach = 'Fernando Santos'

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

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

Ques 8. Show the name of all players who scored a goal against Germany.

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

Ques 9. Show teamname and the total number of goals scored.

SELECT teamname, COUNT(teamid) AS total_goals
FROM goal
JOIN eteam ON (teamid = id)
GROUP BY teamname
ORDER BY total_goals DESC

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

SELECT stadium, COUNT(stadium) as total_goals_scored
FROM game
JOIN goal ON (id=matchid)
GROUP BY stadium
ORDER BY total_goals_scored DESC

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

SELECT id, mdate, COUNT(id) as total_goals
FROM game
JOIN goal ON (id = matchid)
WHERE team1 = 'POL' or team2 = 'POL'
GROUP BY id, mdate

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

SELECT id, mdate, COUNT(id) as total_goals
FROM game
JOIN goal ON (id = matchid)
WHERE teamid = 'GER'
GROUP BY id, mdate

Ques 13. List every match with the goals scored by each team. 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 (id = matchid)
GROUP BY mdate, matchid, team1, team2
ORDER BY mdate, matchid, team1, team2