In this lesson, we'll delve into the basics of SQL functions, with a particular focus on the COUNT function. This function serves as a convenient tool when conducting quantitative analysis of our data set, such as determining the total number of movies or characters in a table. The COUNT function in SQL is crucial for summarizing data, allowing you to count rows in a table based on certain criteria. Let's get started.

In the previous course, we used a simplified Marvel movies dataset in the lessons, and used an expanded dataset in the practice tasks. In this lesson, we will switch to using the expanded dataset in the lesson. Let's take a look!

Movies Table

The movies table includes the first 33 Marvel movies. Each entry in the table includes a value for movie_id, movie_name, release_date and phase. The movie_id column corresponds to the movie_id column in the movie_details and characters tables.

Movie Details Table

The movie_details table includes details for the first 33 Marvel movies. Each entry in the table includes a value for movie_id, budget_million_usd, box_office_million_usd, imdb_rating, and runtime_minutes. The movie_id column corresponds to the movie_id columns in the movies and characters table.

Characters Table

The characters table contains 243 entries for characters that appear in the first 33 Marvel movies. Each entry has a character_id, movie_id, character_name, actor, and screen_time_minutes. The movie_id column corresponds with the movie_id column from the movies and movie_details table.

Now let's dive into the main content of the lesson. The COUNT function in SQL is used to count the number of rows in a table. The basic syntax is as follows:

Here’s a breakdown of the syntax:

• SELECT: This keyword is used to select data from a database.
• COUNT(column_name): This function counts the number of entries in the specified column.
• FROM table_name: This specifies the table from which to count the entries.

The COUNT function can be used to count all rows or rows that meet specific criteria (using the WHERE clause).

Let's start with a simple example where we count all entries in the characters table.

This command counts all rows in the characters table, regardless of their content. Executing this query will produce the following output:

The output tells us that there are 243 rows in the characters table.

Now, let's move on to conditional counting. Suppose we want to find the number of movies in phase 3. The SQL query is:

This command counts the rows in the movies table where the phase column's value is 3. Executing this query will produce the output:

Using the COUNT function, we know that phase 3 has 11 movies.

Awesome work! In this lesson, we covered:

• The basics and syntax of the COUNT function
• Using the COUNT function on tables
• Combining COUNT with the WHERE clause to perform quantitative analysis

Understanding and effectively using the COUNT function enables you to perform essential quantitative analysis and extract valuable insights from your datasets. Now, you're ready to tackle more advanced practice exercises. Happy coding!