We will take the CASE

We will take the case - introduction video

Relational database.

multiple cases

Topics covered:

• CASE statements
• Simple subqueries
• Correlated subqueries
• Window functions

Prerequisites

• Selecting, filtering, and grouping data

Select user_id, SUM(sales)
FROM sales_data
WHERE user_id BETWEEN 300 AND 400
GROUP BY user_id

• Using joins

SELECT c.country, c.team, SUM(m.goals)
FROM countries AS c
LEFT JOIN matches AS m
ON c.team_id = m.home_team_id
WHERE m.year > 1990
GROUP BY c.country, c.team;

Selecting from the European Soccer Database = relational data

• 25 k matches
• Table
  • team, league and matches

SELECT
	l.name AS league,
	COUNT(m.country_id) as total_matches
FROM league AS l
LEFT JOIN match AS m
ON l.country_id = m.country_id
GROUP BY l.name;