Querying With Table Join Operations

00:00 In this lesson, you will learn how a relational data model, namely the child ratings table, linked to its two parents, movies and reviewers, allows extraction of deeper insights from data.

00:14 This is achieved by writing more complex SQL queries that involve table join operations.

00:23 First of all, create a new file,

00:30 select utils kernel,

00:35 and name it MySQLcode _queries.

00:45 You’ll need to reuse a code snippet to connect to the database and you can grab it from the previous file. Also, in this code, you’ll create two complex queries to pull meaningful insights from data.

01:00 Let’s establish the connection to the online_movie_rating database. Enter your username as root and your password.

01:14 Next, you’ll write an SQL query to pull the names of the top five highest rated movies in the database. To extract this insight, you’ll need data from both tables, movies and ratings. Specifically, you will need to utilize in a join operation and to calculate an aggregated metric such as an average rating. Let’s write the following query: sql = """SELECT title, AVG( rating) AS avg_rating.

01:57 This is a new variable that calculates average rating per movie from movies INNER JOIN ratings ON id = movie_id. Here matching of the tables is based on values of primary and foreign keys referencing movie_ids in the movies table.

02:26 Next type GROUP BY movie_id. This line of code groups rows that have the same title and enables averaging over grouped rows.

02:42 Next, type ORDER BY avg_rating DESC. You want to arrange movies in descending order of their average ratings, and at last type LIMIT 5.

03:04 In order to execute this query, you will have to use a cursor object. Type this: connection.cursor() as cursor: cursor. execute(sql).

03:23 Next, you will have to use fetchall() method to pull five records from the query. You type for movie in cursor.fetchall():

03:40 print(movie). Let’s execute the cell. In the output, you can see that The Godfather and Night of the Living Dead are tied as the highest-rated movies in your online movie rating database.

03:58 Another example of a join query that you are going to write: Find the name of the reviewer who gave the most ratings. Let’s create a SQL query. Type sql = """SELECT CONCAT(.

04:19 You use CONCAT() here since you want to combine the first name and the second name of a reviewer. In parentheses, type first_name, ' ', second_

04:39 name), COUNT( and in parenthesis type an asterisk symbol as num. Here you have created a new variable called num that counts the number of records per reviewer.

04:55 FROM reviewers INNER JOIN ratings ON reviewers.id = ratings.reviewer_ id. Here, matching of the tables is based on values of primary and foreign keys referencing reviewer IDs in the reviewer table.

05:29 GROUP BY reviewer_id ORDER BY num DESC. You want to arrange a reviewer list in descending order of the number of their records.

05:51 LIMIT 1. You want to return the reviewer name who gave the most reviews. Now you’re ready to execute this query, and in order to do so, you would reuse the snippet from the previous cell.

06:12 Let’s replace movie by reviewer here,

06:20 and run the cell. Mary Cooper was the reviewer who gave the most reviews, namely four reviews in total. As you can see, the relational data model behind the MySQL database system allows handling of queries irrespective of their level of complexity, and your process for executing the query will always remain the same: pass the query to cursor.execute(), and fetch results using fetchall().

06:54 Congratulations, you have learned how to write more complex queries using table join operations. In the summary that will follow, you’ll reflect on the key insights and skills gained throughout this course and receive recommendations on the next steps in your MySQL learning journey.