Creating New Columns and Mathematical Operations in PostgreSQL

Introduction and Context Setting

Welcome to this lesson on creating new columns and performing mathematical operations in PostgreSQL. This lesson builds on what you've learned so far about text-based queries and subqueries. We will dive into ways to derive insightful data by applying mathematical operations and creating new columns.

Dataset Review

For convenience, the tables and columns in our Marvel movies dataset are: Movies Table text movie_id | movie_name | release_date | phase ----------+------------+--------------+------- Movie Details Table text movie_id | budget_million_usd | box_office_million_usd | imdb_rating | runtime_minutes ----------+--------------------+------------------------+-------------+----------------- Characters Table text character_id | movie_id | character_name | actor | screen_time_minutes --------------+----------+----------------+-------+---------------------

Reviewing Column Aliases

The SELECT statement is used to retrieve data from a database. Aliases are used to give columns a temporary name, which makes the output more readable. We can use an alias to rename the movie_name column "Film Name" as follows: SQL SELECT movie_name AS "Film Name" FROM movies; The output is: text Film Name --------------------------------------------- Iron Man The Incredible Hulk Iron Man 2 Thor Captain America: The First Avenger The Avengers Iron Man 3 Thor: The Dark World Captain America: The Winter Soldier Guardians of the Galaxy Avengers: Age of Ultron Ant-Man Captain America: Civil War Doctor Strange Guardians of the Galaxy Vol. 2 Spider-Man: Homecoming Thor: Ragnarok Black Panther Avengers: Infinity War Ant-Man and The Wasp Captain Marvel Avengers: Endgame Spider-Man: Far From Home Black Widow Shang-Chi and the Legend of the Ten Rings Eternals Spider-Man: No Way Home Doctor Strange in the Multiverse of Madness Thor: Love and Thunder Black Panther: Wakanda Forever Ant-Man and The Wasp: Quantumania Guardians of the Galaxy Vol. 3 The Marvels (33 rows) The output selects all movie names, but changes the column name in the output from "movie_name" to "Film Name".

Creating New Columns with String Concatenation

Join the 1M+ learners on CodeSignal

Be a part of our community of 1M+ users who develop and demonstrate their skills on CodeSignal

Basic Mathematical Operations

PostgreSQL supports various mathematical operations such as addition (+), subtraction (-), multiplication (*), and division (/). We will use these operations to create new columns in our results.

Let's start by calculating the profit for each movie, which is the difference between box office earnings and the budget.

SQL

SELECT 
    movies.movie_name,
    (movie_details.box_office_million_usd - movie_details.budget_million_usd) AS "Profit in Millions"
FROM 
    movies
INNER JOIN 
    movie_details ON movies.movie_id = movie_details.movie_id;

In this query:

We perform an INNER JOIN on movies and movie_details tables using movie_id.
The subtraction operation (movie_details.box_office_million_usd - movie_details.budget_million_usd) computes the profit.
The alias "Profit in Millions" is used to name the computed column.

The output is:

text

                 movie_name                  | Profit in Millions 
---------------------------------------------+--------------------
 Iron Man                                    |              445.2
 The Incredible Hulk                         |              113.4
 Iron Man 2                                  |              423.9
 Thor                                        |              299.3
 Captain America: The First Avenger          |              230.6
 The Avengers                                |             1299.6
 Iron Man 3                                  |             1015.4
 Thor: The Dark World                        |              474.6
 Captain America: The Winter Soldier         |              544.3
 Guardians of the Galaxy                     |              603.3
 Avengers: Age of Ultron                     |             1155.4
 Ant-Man                                     |              389.3
 Captain America: Civil War                  |              903.3
 Doctor Strange                              |              512.7
 Guardians of the Galaxy Vol. 2              |              663.8
 Spider-Man: Homecoming                      |              705.2
 Thor: Ragnarok                              |              670.8
 Black Panther                               |             1146.9
 Avengers: Infinity War                      |             1727.4
 Ant-Man and The Wasp                        |              460.7
 Captain Marvel                              |              953.3
 Avengers: Endgame                           |             2441.8
 Spider-Man: Far From Home                   |              971.9
 Black Widow                                 |              178.5
 Shang-Chi and the Legend of the Ten Rings   |              230.0
 Eternals                                    |              202.9
 Spider-Man: No Way Home                     |             1735.4
 Doctor Strange in the Multiverse of Madness |             1414.7
 Thor: Love and Thunder                      |              514.3
 Black Panther: Wakanda Forever              |                609
 Ant-Man and The Wasp: Quantumania           |                276
 Guardians of the Galaxy Vol. 3              |                595
 The Marvels                                 |                -50
(33 rows)

Using mathematical operators like -, we have created a new column that lists the profit of each movie.

Rounding Box Office Earnings

The ROUND function is used to round a numerical value to a specified number of decimal places. This function is useful for formatting numerical data to a desired precision.

The basic syntax for the ROUND function is:

SQL

ROUND(number, decimal_places)

number: The numerical value you want to round. This can be a column value, a constant, or an expression.
decimal_places: The number of decimal places to round the value to. This must be an integer.

Let's round the box office sales to the nearest integer:

SQL

SELECT 
    movies.movie_name,
    ROUND(movie_details.box_office_million_usd, 0) AS "Rounded Sales in Millions"
FROM 
    movies
INNER JOIN 
    movie_details ON movies.movie_id = movie_details.movie_id;