SQL Superchallenge_DataCamp.txt

**CASE STATEMENT:

Case statements are SQL's version of an "IF this THEN that! statement.
Case statements have three parts:

1.WHEN clause

CASE WHEN x=1 
CASE WHEN x=2 

2.THEN clause

THEN 'a'
THEN 'b'

3.ELSE clause
'c' 

END AS new_column

Example:

SELECT id, home_goal, away_goal,
CASE WHEN home_goal > away goal then 'Home team Win'
     WHEN home_goal < away_goal THEN 'Away Team Win'
FROM match
WHERE season = '2013/2014';

---------------------------------------- Exercises

1 - identify matches played between FC Schalke 04 and FC Bayern Munich. There are 2 teams identified in each match in the hometeam_id 
and awayteam_id columns, available to you in the filtered matches_germany table. ID can join to the team_api_id column in the teams_germany table, 
but you cannot perform a join on both at the same time.

However, you can perform this operation using a CASE statement once you've identified the team_api_id associated with each team!

a. Select the team's long name and API id from the teams_germany table.
b. Filter the query for FC Schalke 04 and FC Bayern Munich using IN, giving you the team_api_IDs needed for the next step.

SELECT team_long_name, team_api_id
FROM teams_germany
WHERE team_long_name IN ('FC Schalke 04', 'FC Bayern Munich');

c.Create a CASE statement that identifies whether a match in Germany included FC Bayern Munich, FC Schalke 04, or neither as the home team.
d. Group the query by the CASE statement alias, home_team.


SELECT 
    CASE WHEN hometeam_id = 10189 THEN 'FC Schalke 04'
         WHEN hometeam_id = 9823 THEN 'FC Bayern Munich'
         ELSE 'Other' END AS home_team,
	COUNT(id) AS total_matches
FROM matches_germany
GROUP BY home_team;

------------
2 - Create a list of matches in the 2011/2012 season where Barcelona was the home team. You will do this using a CASE statement that compares the 
values of two columns to create a new group -- wins, losses, and ties.

The matches_spain table currently contains Barcelona's matches from the 2011/2012 season, and has two key columns, hometeam_id and awayteam_id, 
that can be joined with the teams_spain table. However, you can only join teams_spain to one column at a time.


a. Select the date of the match and create a CASE statement to identify matches as home wins, home losses, or ties.

SELECT date,
	CASE WHEN home_goal > away_goal THEN 'Home win!'
         WHEN home_goal < away_goal THEN 'Home loss :(' 
         ELSE 'Tie' END AS outcome
FROM matches_spain;

b.Left join the teams_spain table team_api_id column to the matches_spain table awayteam_id. This allows us to retrieve the away team's identity.
c. Select team_long_name from teams_spain as opponent and complete the CASE statement from Step 1.

SELECT 	m.date,
	t.team_long_name AS opponent, 
	CASE WHEN m.home_goal > m.away_goal THEN 'Home win!'
        WHEN m.home_goal < m. away_goal THEN 'Home loss :('
        ELSE 'Tie' END AS outcome
FROM matches_spain AS m
LEFT JOIN teams_spain AS t
ON m.awayteam_id = t.team_api_id;

d. Complete the same CASE statement as the previous steps.
e. Filter for matches where the home team is FC Barcelona (id = 8634).

SELECT 	m.date,
	t.team_long_name AS opponent,
	CASE WHEN m.home_goal > away_goal THEN 'Barcelona win!'
        WHEN m.home_goal < away_goal THEN 'Barcelona loss :(' 
        ELSE 'Tie' END  AS outcome 
FROM matches_spain AS m
LEFT JOIN teams_spain AS t 
ON m.awayteam_id = t.team_api_id
WHERE m.hometeam_id = 8634; 

f.Complete the CASE statement to identify Barcelona's away team games (id = 8634) as wins, losses, or ties.
g. Left join the teams_spain table team_api_id column on the matches_spain table hometeam_id column. This retrieves the identity of the home team opponent.
h. Filter the query to only include matches where Barcelona was the away team.

SELECT  m.date,
	t.team_long_name AS opponent,
	CASE WHEN  home_goal <  away_goal THEN 'Barcelona win!'
        WHEN home_goal > away_goal THEN 'Barcelona loss :(' 
        ELSE 'Tie' END AS outcome
FROM matches_spain AS m
LEFT JOIN teams_spain AS t 
ON m.hometeam_id = t.team_api_id
WHERE m.awayteam_id = 8634;

------------

3-In this exercise, you will retrieve information about matches played between Barcelona (id = 8634) and Real Madrid (id = 8633). Note that the query 
you are provided with already identifies the Clásico matches using a filter in the WHERE clause.

a.Complete the first CASE statement, identifying Barcelona or Real Madrid as the home team using the hometeam_id column.
b.Complete the second CASE statement in the same way, using awayteam_id.

 SELECT date,
	CASE WHEN hometeam_id = 8634 THEN 'FC Barcelona' 
         ELSE 'Real Madrid CF' END AS home,
   	CASE WHEN awayteam_id = 8634 THEN 'FC Barcelona' 
         ELSE 'Real Madrid CF' END AS away
FROM matches_spain
WHERE (awayteam_id = 8634 OR hometeam_id = 8634)
      AND (awayteam_id = 8633 OR hometeam_id = 8633);

c.Construct the final CASE statement identifying who won each match. Note there are 3 possible outcomes, but 5 conditions that you need to identify.
d.Fill in the logical operators to identify Barcelona or Real Madrid as the winner.

SELECT	date,
	CASE WHEN hometeam_id = 8634 THEN 'FC Barcelona' 
         ELSE 'Real Madrid CF' END as home,
	CASE WHEN awayteam_id = 8634 THEN 'FC Barcelona' 
         ELSE 'Real Madrid CF' END as away,
	CASE WHEN home_goal > away_goal AND hometeam_id = 8634 THEN 'Barcelona win!'
         WHEN home_goal > away_goal AND hometeam_id = 8633 THEN 'Real Madrid win!'
         WHEN home_goal < away_goal AND awayteam_id = 8634 THEN 'Barcelona win!'
         WHEN home_goal < away_goal AND awayteam_id = 8633 THEN 'Real Madrid win!'
         ELSE 'Tie!' END AS outcome
FROM matches_spain
WHERE (awayteam_id = 8634 OR hometeam_id = 8634)
      AND (awayteam_id = 8633 OR hometeam_id = 8633);

----------------
4-Identify Bologna's team ID listed in the teams_italy table by selecting the team_long_name and team_api_id.

SELECT team_long_name, team_api_id
FROM teams_Italy
WHERE team_long_name = 'Bologna';

a.Select the season and date that a match was played.
b.Complete the CASE statement so that only Bologna's home and away wins are identified.

SELECT 	season,	date,
	CASE WHEN hometeam_id = 9857 
        AND home_goal > away_goal 
        THEN 'Bologna Win'
		WHEN awayteam_id = 9857 
        AND away_goal > home_goal 
        THEN 'Bologna Win' 
		END AS outcome
FROM matches_italy;

c.Select the home_goal and away_goal for each match.
d.Use the CASE statement in the WHERE clause to filter all NULL values generated by the statement in the previous step.

SELECT 	season, date, home_goal, away_goal
FROM matches_italy
WHERE 
	CASE WHEN hometeam_id = 9857 AND home_goal > away_goal  THEN 'Bologna Win'
		WHEN awayteam_id = 9857 AND away_goal > home_goal THEN 'Bologna Win' 
		END IS NOT null;
--------------
5-You will examine the number of matches played in 3 seasons within each country listed in the database. This is much easier to 
explore with each season's matches in separate columns. Using the country and unfiltered match table, you will count the number 
of matches played in each country during the 2012/2013, 2013/2014, and 2014/2015 match seasons.

a.Create a CASE statement that identifies the id of matches played in the 2012/2013 season. Specify that you want ELSE values to be NULL.
b.Wrap the CASE statement in a COUNT function and group the query by the country alias.

SELECT c.name AS country,
	COUNT(CASE WHEN m.season = '2012/2013' 
          	   THEN m.id ELSE NULL END) AS matches_2012_2013
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
GROUP BY country;

c.Create 3 CASE WHEN statements counting the matches played in each country across the 3 seasons.
d.END your CASE statement without an ELSE clause.

SELECT c.name AS country,
	COUNT(CASE WHEN m.season = '2012/2013' THEN m.id END) AS matches_2012_2013,
	COUNT(CASE WHEN m.season = '2013/2014' THEN m.id END) AS matches_2013_2014,
	COUNT(CASE WHEN m.season = '2014/2015' THEN m.id END) AS matches_2014_2015
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
GROUP BY c.name;

--------------
6-Your goal here is to use the country and match table to determine the total number of matches won by the home team in each 
country during the 2012/2013, 2013/2014, and 2014/2015 seasons.

a.Create 3 CASE statements to "count" matches in the '2012/2013', '2013/2014', and '2014/2015' seasons, respectively.
b.Have each CASE statement return a 1 for every match you want to include, and a 0 for every match to exclude.
c.Wrap the CASE statement in a SUM to return the total matches played in each season.
d.Group the query by the country name alias.

SELECT c.name AS country,
	SUM(CASE WHEN m.season = '2012/2013' AND m.home_goal > m.away_goal 
        THEN 1 ELSE 0 END) AS matches_2012_2013,
	SUM(CASE WHEN m.season = '2013/2014' AND m.home_goal > m.away_goal 
        THEN 1 ELSE 0 END) AS matches_2013_2014,
	SUM(CASE WHEN m.season = '2014/2015' AND m.home_goal > m.away_goal 
        THEN 1 ELSE 0 END) AS matches_2014_2015
FROM country AS c
LEFT JOIN match AS m
ON c.id = m.country_id
GROUP BY country;
--------------
7-Your task is to examine the number of wins, losses, and ties in each country. The matches table is filtered to include all 
matches from the 2013/2014 and 2014/2015 seasons.

a.Create 3 CASE statements to COUNT the total number of home team wins, away team wins, and ties, which will allow you to examine the total number of records.
SELECT c.name AS country,
	COUNT(CASE WHEN m.home_goal > m.away_goal THEN m.id 
        END) AS home_wins,
	COUNT(CASE WHEN m.home_goal < m.away_goal THEN m.id 
        END) AS away_wins,
	COUNT(CASE WHEN m.home_goal = m.away_goal THEN m.id 
        END) AS ties
FROM country AS c
LEFT JOIN matches AS m
ON c.id = m.country_id
GROUP BY country;

b.Calculate the percentage of matches tied using a CASE statement inside AVG.
c.Fill in the logical operators for each statement. Alias your columns as ties_2013_2014 and ties_2014_2015, respectively.

SELECT 	c.name AS country,
	AVG(CASE WHEN m.season= '2013/2014' AND m.home_goal = m.away_goal THEN 1
			 WHEN m.season= '2013/2014' AND m.home_goal != m.away_goal THEN 0
			 END) AS ties_2013_2014,
	AVG(CASE WHEN m.season= '2014/2015' AND m.home_goal = m.away_goal THEN 1
			 WHEN m.season= '2014/2015' AND m.home_goal != m.away_goal THEN 0
			 END) AS ties_2014_2015
FROM country AS c
LEFT JOIN matches AS m
ON c.id = m.country_id
GROUP BY country;

d.The previous "ties" columns returned values with 14 decimal points, which is not easy to interpret. Use the ROUND function to round to 2 decimal points.

SELECT 
	c.name AS country,
	ROUND(AVG(CASE WHEN m.season='2013/2014' AND m.home_goal = m.away_goal THEN 1
			 WHEN m.season='2013/2014' AND m.home_goal != m.away_goal THEN 0
			 END),2) AS pct_ties_2013_2014,
	ROUND(AVG(CASE WHEN m.season='2014/2015' AND m.home_goal = m.away_goal THEN 1
			 WHEN m.season='2014/2015' AND m.home_goal != m.away_goal THEN 0
			 END),2) AS pct_ties_2014_2015
FROM country AS c
LEFT JOIN matches AS m
ON c.id = m.country_id
GROUP BY country;
*********************************

**Short and Simple Subqueries

1- In this exercise, you will generate a list of matches where the total goals scored (for both teams in total) is more than 3 times the average 
for games in the matches_2013_2014 table, which includes all games played in the 2013/2014 season.

a.Select the date, home goals, and away goals in the main query.
b.Filter the main query for matches where the total goals scored exceed the value in the subquery.

SELECT     date,
	home_goal,
	away_goal
FROM  matches_2013_2014
WHERE (home_goal + away_goal) > 
       (SELECT 3 * AVG(home_goal + away_goal)
        FROM matches_2013_2014); 

a.Create a subquery in the WHERE clause that retrieves all unique hometeam_ID values from the match table.
b.Select the team_long_name and team_short_name from the team table. Exclude all values from the subquery in the main query.

SELECT 	team_long_name,
	team_short_name
FROM team 
WHERE team_api_id NOT IN
     (SELECT DISTINCT hometeam_id  FROM match);

----------------------------
2- In the previous exercise, you generated a list of teams that have no home matches listed in the soccer database using a subquery in WHERE. 
Let's do some further exploration in this database by creating a list of teams that scored 8 or more goals in a home match.
In order to do this, you will construct a subquery in the WHERE statement with its own filtering condition.

a.Create a subquery in WHERE clause that retrieves all hometeam_ID values from match with a home_goal score greater than or equal to 8.
b.Select the team_long_name and team_short_name from the team table. Include all values from the subquery in the main query.

SELECT team_long_name,
	team_short_name
FROM team
WHERE team_api_id IN
	  (SELECT hometeam_id 
       FROM match
       WHERE home_goal >= 8);

--------------------------
3-The match table in the European Soccer Database does not contain country or team names. You can get this information
 by joining it to the country table, and use this to aggregate information, such as the number of matches played in each country.

If you're interested in filtering data from one of these tables, you can also create a subquery from one of the tables,
 and then join it to an existing table in the database. A subquery in FROM is an effective way of answering detailed 
questions that requires filtering or transforming data before including it in your final results.

Your goal in this exercise is to generate a subquery using the match table, and then join that subquery to the country
 table to calculate information about matches with 10 or more goals in total! 

a.Create the subquery to be used in the next step, which selects the country ID and match ID (id) from the match table.
b.Filter the query for matches with greater than or equal to 10 goals.

SELECT country_id, id
FROM match
WHERE (home_goal + away_goal) >= 10;]



c.Construct a subquery that selects only matches with 10 or more total goals.
d.Inner join the subquery onto country in the main query.
e.Select name from country and count the id column from match.


SELECT
    c.name AS country_name,
    COUNT(sub.id) AS matches
FROM country AS c
INNER JOIN (SELECT country_id, id 
            FROM match
            WHERE (home_goal + away_goal) >= 10) AS sub
ON c.id = sub.country_id
GROUP BY country_name;

--------------------------

4-In the previous exercise, you found that England, Netherlands, Germany and Spain were the only countries that 
had matches in the database where 10 or more goals were scored overall. Let's find out some more details about 
those matches -- when they were played, during which seasons, and how many of the goals were home versus away goals.

You'll notice that in this exercise, the table alias is excluded for every column selected in the main query. 
This is because the main query is extracting data from the subquery, which is treated as a single table.

a.Complete the subquery inside the FROM clause. Select the country name from the country table, along with the date, the home goal, the away goal, and the total goals columns from the match table.
b.Create a column in the subquery that adds home and away goals, called total_goals. This will be used to filter the main query.
c.Select the country, date, home goals, and away goals in the main query.
d.Filter the main query for games with 10 or more total goals.

SELECT   country,  date, home_goal, away_goal
FROM 
	(SELECT name AS country, 
     	    m.date, 
     		m.home_goal, 
     		m.away_goal,
           (m.home_goal + m.away_goal) AS total_goals
    FROM match AS m
    LEFT JOIN country AS c
    ON m.country_id = c.id) AS subq
WHERE total_goals >= 10;