Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /* Show matchid and player name for all goals scored by Germany */
- SELECT matchid, player
- FROM goal
- WHERE teamid = 'GER'
- /* Show id, stadium, team1, team2 for just game 1012 */
- SELECT id, stadium, team1, team2
- FROM game
- WHERE id = 1012
- /* Show player, teamid, stadium and mdate for every German goal */
- SELECT player, teamid, stadium, mdate
- FROM game JOIN goal ON (id=matchid)
- WHERE teamid = 'GER'
- /* Show 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%'
- /* Show player, teamid, coach, gtime for all goals stcored in the first 10 minutes */
- SELECT player, teamid, coach, gtime
- FROM goal JOIN eteam ON (goal.teamid = eteam.id)
- WHERE gtime <= 10
- /* 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 (game.team1 = eteam.id)
- WHERE coach = 'Fernando Santos'
- /* List the player for every goal scored in a game where the stadium was
- National Stadium, Warsaw */
- SELECT player
- FROM game JOIN goal ON (game.id = goal.matchid)
- WHERE stadium = 'National Stadium, Warsaw'
- /* More Difficult Questions */
- /* Show the name of all players who scored a goal against Germany */
- /* Show teamname and the total number of goals scored */
- SELECT teamname, COUNT(gtime)
- FROM eteam JOIN goal ON (id = teamid)
- GROUP BY teamname
- /* Show the stadium and the number of goals scored in each stadium */
- SELECT stadium, COUNT(gtime) AS goals_scored
- FROM game JOIN goal ON game.id = goal.matchid
- GROUP BY teamid
- /* For every match involving Poland, show the matchid, date, and number of goals scored */
- SELECT matchid, mdate, COUNT(gtime) AS goals_scored
- FROM goal JOIN game ON matchid = id
- WHERE (team1 = 'POL' OR team2 = 'POL')
- GROUP BY matchid, mdate
- /* For every match where Germany scored, show matchid, match date and the number
- of goals scored by Germany */
- SELECT matchid, mdate, COUNT(gtime) AS goals_scored
- FROM goal JOIN game ON matchid = id
- WHERE (teamid = 'GER')
- GROUP BY matchid, mdate
Add Comment
Please, Sign In to add comment