In previous lessons, we've discussed the basics of databases, SQL syntax, and how to retrieve data using SELECT statements. We also explored how to filter data using the WHERE clause. In this lesson, we'll dive deeper into filtering data by using logical operators such as AND, OR, IN, and BETWEEN. These operators help refine your data queries, allowing you to extract more specific results.

The AND operator allows you to combine multiple conditions in a query. All the conditions connected by AND must be true for a row to be included in the result set.

Let's say you want to find movies that have an IMDb rating greater than 7 and a runtime of fewer than 120 minutes. Here's how you'd write that query:

• SELECT *: This retrieves all columns from the table.
• FROM movie_details: This specifies the table we're querying.
• WHERE imdb_rating > 7 AND runtime_minutes < 120: This condition selects only those rows where the imdb_rating is greater than 7 and the runtime_minutes are less than 120.

In the resulting output, we can see that all entries have an imdb_rating greater than 7 and a runtime of less than 120 minutes.

The output contains the movie_id column from the movie_details table. The movie_id uniquely identifies each movie in the tables. To find the movie title of each row, we can find the corresponding movie_id column in the movies table. From the movies table, we know that the movie with ID 12 is "Ant-Man", movie 14 is "Doctor Strange", and movie 20 is "Ant-Man and The Wasp".

The OR operator also allows you to combine multiple conditions, but in this case, only one of the conditions needs to be true for a row to be included in the result set.

Let's say you want to select movies that have a budget greater than 220 million USD or box office sales greater than 2000 million USD. Here's how you'd write that query:

• WHERE budget_million_usd > 220 OR box_office_million_usd > 2000: This condition selects rows where either the budget_million_usd is greater than 220 or the box_office_million_usd is greater than 2000.

The output is:

In the output, all rows have budget_million_usd greater than 220 OR a box_office_million_usd greater than 2000. The output also includes rows that meet both the conditions. For example, the movie with movie_id 11 meets both criteria.

The IN operator simplifies checking if a value matches any value in a list of values.

Let's say you want to find movies that belong to specific phases, for example, phases 1 and 5. Here's how you'd write that query:

• WHERE phase IN (1, 5): This condition selects rows where the phase is either 1 or 5.

The output shows only the movies from phases 1 and 5.

The BETWEEN operator allows you to select values within a certain range. It's inclusive, meaning that it includes the start and end values.

Let's say you want to find movies with a release date within the year 2023. Here's how you'd write that query:

• WHERE release_date BETWEEN '2023-01-01' AND '2023-12-31': This condition selects rows where the release_date falls between January 1, 2023, and December 31, 2023, inclusive.

The output is:

From the table, we can see these are the only 3 Marvel movies released in 2023.

In this lesson, we covered the use of logical operators such as AND, OR, IN, and BETWEEN in SQL queries. These operators allow you to filter data more precisely, making your queries more powerful and specific.

As you move to the practical exercises, you will be able to solidify your understanding by applying these operators in real-world scenarios. Keep practicing and explore the upcoming lessons to continue your journey in mastering SQL with PostgreSQL.

Happy querying!