The Externational

gistfile1.txt

```sql
-- Nomor 1
SELECT player_name, tr.region_name as "Team Region", pr.region_name as "Player Region"
FROM player
JOIN team_detail ON player.player_id = team_detail.player_id
JOIN team ON team_detail.team_id = team.team_id
JOIN region tr ON team.region_id = tr.region_id
JOIN region pr ON player.region_id = pr.region_id
WHERE tr.region_name LIKE '% %'

-- Nomor 2
SELECT match_schedule, team_name, MAX(match_detail.team_score)
FROM match
JOIN match_detail ON match_detail.match_id = match.match_id
JOIN team ON team.team_id = match_detail.team_id
WHERE DATEDIFF(d, match_schedule, '2021-10-20') = 8
GROUP BY match_schedule, team_name

-- Nomor 3
SELECT team_name, COUNT(match_detail.match_id) AS total_play, SUM(match_detail.team_score) AS total_score
FROM team
JOIN match_detail ON match_detail.team_id = team.team_id
GROUP BY team_name
HAVING COUNT(match_detail.match_id) = 2
    AND SUM(match_detail.team_score) BETWEEN 0 AND 3

-- Nomor 4
SELECT player_name, position_name
FROM player
JOIN team_detail ON team_detail.player_id = player.player_id
JOIN position ON team_detail.position_id = position.position_id
JOIN match_detail md on team_detail.team_id = md.team_id
WHERE position_name IN
    (SELECT position_name FROM position WHERE position_name LIKE 'Support%' OR position_name = 'Carry')
GROUP BY player_name, position_name
HAVING SUM(md.team_score) BETWEEN 4 AND 5;

-- Nomor 5
SELECT * FROM
    (
        SELECT TOP 1 region_name, COUNT(team.team_id) AS 'Total Team'
        FROM region
               JOIN team ON region.region_id = team.region_id
        GROUP BY region_name
        ORDER BY [Total Team] DESC
    ) AS E
UNION
SELECT * FROM (
  SELECT TOP 1 region_name, COUNT(team.team_id) AS 'Total Team'
  FROM region
           JOIN team ON region.region_id = team.region_id
  GROUP BY region_name
  ORDER BY [Total Team] ASC
) AS A

-- Nomor 6
SELECT t.team_name, md.team_score, oteam.team_name AS 'Opponent', omd.team_score AS 'Opponent Score',
       CASE WHEN md.team_score = 2 THEN 'WIN' ELSE 'LOSE' END AS Result
FROM team t
JOIN match_detail md ON t.team_id = md.team_id
JOIN match ON md.match_id = match.match_id

JOIN match_detail omd ON omd.match_id = match.match_id
JOIN team oteam ON oteam.team_id = omd.team_id
JOIN match om ON om.match_id = omd.match_id

WHERE t.team_name = 'Team Spirit'
  AND oteam.team_name NOT IN (SELECT team_name FROM team WHERE team_name = 'Team Spirit')


-- Nomor 7
CREATE VIEW [Player Who Played the Most] AS
    SELECT team_name FROM team;

SELECT t.player_name FROM (
      SELECT TOP 20 player.player_name
      FROM player
               JOIN team_detail ON player.player_id = team_detail.player_id
               JOIN team ON team_detail.team_id = team.team_id
               JOIN match_detail ON match_detail.team_id = team.team_id
      GROUP BY player.player_name, player.player_id
      HAVING COUNT(match_detail.match_id) > 1
      ORDER BY COUNT(match_detail.match_id) DESC, player.player_id ASC
  ) t

-- Nomor 8
SELECT player_name, joined_date, team_name
FROM player
JOIN team_detail ON player.player_id = team_detail.player_id
JOIN team ON team_detail.team_id = team.team_id
WHERE DATEDIFF(year, joined_date, '2021-11-01') > 3

-- Nomor 9
SELECT player_name, position_name, team_name, joined_date
FROM player
    JOIN team_detail ON player.player_id = team_detail.player_id
    JOIN position ON position.position_id = team_detail.position_id
    JOIN team ON team_detail.team_id = team.team_id
WHERE YEAR(joined_date) = '2021'
  AND MONTH(joined_date) < 10

-- Nomor 10
SELECT TOP 1 region_name,
       (SELECT COUNT(player_id) FROM player WHERE player.region_id = region.region_id) AS 'Total Player'
FROM region
ORDER BY 'Total Player' DESC
```