Hi there! You've made substantial progress, and I'm impressed with all you've learned so far. You've mastered the use of SQL logical and conditional operators and elevated your data analysis with subqueries. In this unit, we're diving into complex queries and conditional logic — an advanced SQL topic that will allow for even more intricate data queries and analysis.
We will continue to work with our sports database, focusing on the Matches
and MatchEvents
tables. As a quick reminder, the Matches
table contains details such as match IDs, dates, and results, while MatchEvents
holds information about events during matches, like minute and event type.
Are you ready to get started? Let's jump right in!
In SQL, the IF
function is one of the most useful tools we have for adding conditional logic to our queries. It operates according to a simple principle: if a specific condition is true, then do something; if it's false, then do something else.
Let's put this into practice by categorizing matches into 'Recent' and 'Earlier' periods using an SQL query:
SQL1SELECT
2 match_id AS MatchID,
3 date AS MatchDate,
4 IF(YEAR(date) > 2015, 'Recent Match', 'Earlier Match') AS MatchPeriod
5FROM
6 Matches;
7
8-- Sneak peek of the output:
9-- | MatchID | MatchDate | MatchPeriod |
10-- |---------|------------|---------------|
11-- | 1 | 2005-05-01 | Earlier Match |
12-- | 2 | 2005-11-02 | Earlier Match |
In the query above, the IF
function checks whether each match's date is after 2015. If the condition is true, 'Recent Match' is returned; otherwise, 'Earlier Match' is returned. This approach allows us to categorize matches based on their dates in a very efficient and readable manner.
The CASE
statement is an exceptionally versatile tool in SQL, allowing us to execute actions based on various conditions, offering a functionality somewhat similar to the IF
statement but with enhanced flexibility. Unlike the IF
function, which is binary in its conditionality (true or false), the CASE
statement supports multiple conditions, providing a robust means to handle complex logical operations within our queries.
Let's explore how we can categorize matches by their event time using the CASE
statement:
SQL1SELECT
2 m.match_id AS MatchID,
3 me.minute AS Minute,
4 CASE
5 WHEN me.minute < 30 THEN 'Early'
6 WHEN me.minute BETWEEN 30 AND 60 THEN 'Mid'
7 ELSE 'Late'
8 END AS EventTimeCategory
9FROM
10 MatchEvents me
11JOIN
12 Matches m ON me.match_id = m.match_id;
13
14-- Sneak peek of the output:
15-- | MatchID | Minute | EventTimeCategory |
16-- |---------|--------|-------------------|
17-- | 1 | 90+1 | Late |
18-- | 2 | 34 | Mid |
In this example, we're joining the Matches
and MatchEvents
tables through the match_id
field. Within our SELECT statement, the CASE
is applied to determine a match event's time category. It sequentially evaluates each condition specified by the WHEN
clauses until one is found true. If none of the WHEN
conditions match, the ELSE
clause (considered a default) is returned. Note that the ELSE
clause is optional; if it is omitted and no conditions are met, the CASE
statement will return NULL. Here's a closer look at the workflow:
CASE
statement starts its evaluation.minute
is less than 30, 'Early' is returned.minute
is between 30 and 60, 'Mid' is returned.ELSE
part catches all remaining possibilities, returning 'Late'.The conclusion of the CASE
statement is marked by the END
keyword, signifying the end of the conditional checks. Following END
, we use AS EventTimeCategory
to label the outcome of our CASE
statement, assigning it as a new column in our results. This naming convention is not just for clarity; it’s essential for referencing the produced column in other parts of our query or in applications that consume this query's output.
Great job! You've just learned to use the IF
function and CASE
statement to write more advanced SQL queries. You now have the skills to filter data more accurately, write conditional statements in SQL, and analyze real-world data more effectively.
Next, try your hands at a series of practice exercises on CodeSignal. We have prepared these exercises to reinforce what you have learned in this lesson. The more you practice these skills, the more confident you'll become in handling real-world scenarios.
Congratulations! You are almost at the end of this course. You should be incredibly proud of your progress. These tools are powerful additions to your data analysis arsenal, and you'll find them invaluable in your future work. Keep practicing and keep learning. We're excited to see where your new skills will take you!