Skip to content
Data-Driven Decision Making in SQL
  • AI Chat
  • Code
  • Report

    • Data-Driven Decision Making in SQL

    Here you can access every table used in the course. To access each table, you will need to specify the movies schema in your queries (e.g., movies.movies for the movies table, and movies.customers for the customers table).

    Throughout this course, we will work with a Postgres database from a fictional movie rental company called MovieNow. MovieNow offers an online platform for streaming movies. Customers can rent a movie for 24 hours. For all movies, the company stores additional information such as the genre or the main actors. MovieNow also stores information about customers and movie ratings.

    MOVIENOW DATA STRUCTURE

    An overview of the tables in the database:

    Customers Table: columns 'customer_id', a number which is a unique identifier for each customer, 'name', 'country', 'gender', 'date_of_birth', and the final column is the date when the account for MovieNow was created, 'date_account_start'.

    Movies Table: columns include a unique identifier movie_id, the title of the movie, the movie genre, the runtime, the release year, and, finally, what it costs to rent the movie.

    The table 'renting' records all movie rentals. 'renting_id' is a unique identifier for each movie rental. The column 'customer_id' tells us which customer rented the movie and 'movie_id' tells us which movie the customer rented. The rating a customer gives after watching the movie is stored in the column 'rating' which has values between 1 and 10, where 10 is the best rating. The final column is the rental date.

    The table 'actors' contains information about the actors in the movies. Besides the unique identifier 'actor_id', we have the actor's name, year of birth, nationality, and gender.

    The table 'actsin' shows which actor appears in which movie. Besides the unique identifier actsin_id, it includes movie_id and actor_id.

    Exploring the table renting

    Select all columns from renting.

    Unknown integration
    DataFrameavailable as
    df
    variable
    SELECT *
FROM movies.renting;

    Columns from renting which are needed to calculate the average rating per movie.

    Unknown integration
    DataFrameavailable as
    df1
    variable
    SELECT movie_id, rating
FROM movies.renting;

    Which is the most recently rented movie from movienow?

    Unknown integration
    DataFrameavailable as
    df2
    variable
    SELECT movie_id, date_renting
FROM movies.renting
ORDER BY date_renting DESC;
    Unknown integration
    DataFrameavailable as
    df3
    variable
    SELECT title
FROM movies.movies
WHERE movie_id = 50;

    Winter's Bone is the most recently rented movie from MovieNow.

    Which is the most expensive movie to rent?

    Unknown integration
    DataFrameavailable as
    df4
    variable
    SELECT *
FROM movies.movies
ORDER BY renting_price DESC;

    Morning Glory and 11'09''01 September 11 are the most expensive movies to rent. with the cost of 2.99 as renting price.

    Only some users give a rating after watching a movie. Let's explore only those movie rentals where a rating was provided.

    ‌
    ‌
    ‌