Welcome to the course "Learning SQL with Leo Messi"! In this course, you'll learn how to use SQL to query and analyze data from a real-world dataset revolving around soccer games.

We'll be working with two main tables:

• Matches: Contains data about the matches played.
• MatchEvents: Contains events that occurred during each match.

Here's a quick preview of what these tables look like:

Matches Table:

MatchEvents Table:

Logical operations are vital in SQL for filtering data based on certain conditions. The main logical operators are:

• AND: All conditions must be true.
• OR: At least one condition must be true.
• NOT: The condition must be false.

Here we'll focus on the AND operator, which we'll be using extensively.

Logical operators allow you to fine-tune your data queries, making it possible to extract exactly what you need from your dataset. Understanding these operations is fundamental for effective SQL querying.

Think of SQL statements as real-world phrases that you can dissect into multiple parts. Each part plays a specific role. For instance, consider

SQL
Copy to clipboard
1SELECT *
2FROM Matches
3WHERE result = '4:0' AND YEAR(date) BETWEEN 2010 AND 2013;
4
5-- Sneak peek of the output:
6-- | match_id | season_id | competition_id | matchday    | date       | venue | club_id | opponent_id | result |
7-- |----------|-----------|----------------|-------------|------------|-------|---------|-------------|--------|
8-- |      100 |         6 |              1 | 18          | 2010-01-16 | H     |       1 |           2 | 4:0    |
9-- |      101 |         6 |              1 | 18          | 2010-01-16 | H     |       1 |           2 | 4:0    |

This SQL query has distinct parts:

• SELECT *: This phrase indicates that it wants to retrieve all columns.
• FROM Matches: This phrase specifies the table from which to retrieve the data.
• WHERE: This word starts the condition clause, which refines the query.
• result = '4:0' AND YEAR(date) BETWEEN 2010 AND 2013: These are the conditions that rows must meet to be included in the result.

Notice the AND operator here? It helps us set multiple conditions. Our statement tells SQL to "Show me all columns from the Matches table, but only those which have the result '4:0' (result = '4:0') and whose year is between 2010 and 2013".

We can use the AND logical operator to add multiple conditions to our SQL statement. For instance, in our example, we want a list of matches, but not just any matches. We're looking for the ones with result '4:0' (result = '4:0'), AND those within a certain time range (YEAR(date) BETWEEN 2010 AND 2013). Similarly, the OR operator allows us to query data that meet either one condition or another, enabling more flexible data retrieval based on varying criteria.

Logical operators like AND and OR aren't just for boolean values. They can be used with numeric data, too. In SQL, numeric comparisons are quite straightforward: you can use = for equality, > for greater than, < for less than, >= for greater than or equal to, <= for less than or equal to, and <> or != for not equal to.

Just like numeric fields, we can use logical operators to work with date fields. Here is another example:

SQL
Copy to clipboard
1SELECT MatchEvents.*
2FROM MatchEvents
3JOIN Matches ON Matches.match_id = MatchEvents.match_id
4WHERE Matches.date > '2010-01-01' AND MatchEvents.minute < 30;
5
6-- Sneak peek of the output:
7-- | event_id | match_id | playing_position | minute | at_score | event_type             | goal_assist_id |
8-- |----------|----------|------------------|--------|----------|------------------------|----------------|
9-- |      103 |      103 | RW               | 7      | 1:0      | Left-footed shot       |             19 |
10-- |      110 |      110 | CF               | 13     | 1:0      | Left-footed shot       |             19 |

In this statement, we're looking for all match events (SELECT MatchEvents.*) from the dataset where the date is after the first day of 2010, AND the event happened before the 30th minute (MatchEvents.minute < 30). We use the AND operator to combine these two conditions.

That wraps up our introductory lesson on using AND/OR logical operators in SQL. We've learned how to construct SQL queries using these operators to filter out specific data based on multiple conditions. Now that you've gained a solid understanding of using AND/OR operators, it's time to put this knowledge into practice. In the next section, you'll encounter various exercises to solve, giving you hands-on experience in using logical operators in SQL to retrieve specific data. Keep practicing and happy querying!