SELECT Statements with Logical Operators

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 * FROM movie_details
WHERE imdb_rating > 7 AND runtime_minutes < 120;

• 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.

 movie_id | budget_million_usd | box_office_million_usd | imdb_rating | runtime_minutes 
----------+--------------------+------------------------+-------------+-----------------
       12 |                130 |                  519.3 |         7.3 |             117
       14 |                165 |                  677.7 |         7.5 |             115
       20 |                162 |                  622.7 |         7.1 |             118
(3 rows)

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:

SELECT * FROM movie_details
WHERE budget_million_usd > 220 OR box_office_million_usd > 2000;

• 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:

 movie_id | budget_million_usd | box_office_million_usd | imdb_rating | runtime_minutes 
----------+--------------------+------------------------+-------------+-----------------
       11 |                250 |                 1405.4 |         7.3 |             141
       13 |                250 |                 1153.3 |         7.8 |             147
       19 |                321 |                 2048.4 |         8.4 |             149
       22 |                356 |                 2797.8 |         8.4 |             181
       27 |                260 |                 1995.4 |         8.4 |             148
       30 |                250 |                    859 |         7.3 |             161
       32 |                250 |                    845 |         8.1 |             150
       33 |                250 |                    200 |         6.1 |             124
(8 rows)

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 19 meets both criteria.