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.
For convenience, the tables and columns in our Marvel movies dataset are:
Movies Table
Plain text1 movie_id | movie_name | release_date | phase 2----------+------------+--------------+-------
Movie Details Table
Plain text1 movie_id | budget_million_usd | box_office_million_usd | imdb_rating | runtime_minutes 2----------+--------------------+------------------------+-------------+-----------------
Characters Table
Plain text1 character_id | movie_id | character_name | actor | screen_time_minutes 2--------------+----------+----------------+-------+---------------------
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:
SQL1SELECT movie_name AS "Film Name" 2FROM movies;
The output is:
Plain text1 Film Name 2--------------------------------------------- 3 Iron Man 4 The Incredible Hulk 5 Iron Man 2 6 Thor 7 Captain America: The First Avenger 8 The Avengers 9 Iron Man 3 10 Thor: The Dark World 11 Captain America: The Winter Soldier 12 Guardians of the Galaxy 13 Avengers: Age of Ultron 14 Ant-Man 15 Captain America: Civil War 16 Doctor Strange 17 Guardians of the Galaxy Vol. 2 18 Spider-Man: Homecoming 19 Thor: Ragnarok 20 Black Panther 21 Avengers: Infinity War 22 Ant-Man and The Wasp 23 Captain Marvel 24 Avengers: Endgame 25 Spider-Man: Far From Home 26 Black Widow 27 Shang-Chi and the Legend of the Ten Rings 28 Eternals 29 Spider-Man: No Way Home 30 Doctor Strange in the Multiverse of Madness 31 Thor: Love and Thunder 32 Black Panther: Wakanda Forever 33 Ant-Man and The Wasp: Quantumania 34 Guardians of the Galaxy Vol. 3 35 The Marvels 36(33 rows)
The output selects all movie names, but changes the column name in the output from "movie_name" to "Film Name".
Now let's see an example of creating a new column using string concatenation.
The CONCAT function is used to join or concatenate multiple strings into a single string. This function can significantly enhance the readability and formatting of your query results by allowing you to combine column values with other strings.
The syntax for the CONCAT function is:
SQL1CONCAT(string1, string2, ..., stringN)
string1, string2, ..., stringN are the strings you want to concatenate. They can be column values, literals, or expressions.
Let's take a look at creating a new column called "Budget" that appends a "$" to the beginning of the value and adds " Million" to the end.
SQL1SELECT 2 budget_million_usd, 3 CONCAT('$', budget_million_usd, ' Million') AS "Budget" 4FROM 5 movie_details;
The output is:
Plain text1 budget_million_usd | Budget 2--------------------+-------------- 3 140 | $140 Million 4 150 | $150 Million 5 200 | $200 Million 6 150 | $150 Million 7 140 | $140 Million 8 220 | $220 Million 9 200 | $200 Million 10 170 | $170 Million 11 170 | $170 Million 12 170 | $170 Million 13 250 | $250 Million 14 130 | $130 Million 15 250 | $250 Million 16 165 | $165 Million 17 200 | $200 Million 18 175 | $175 Million 19 180 | $180 Million 20 200 | $200 Million 21 321 | $321 Million 22 162 | $162 Million 23 175 | $175 Million 24 356 | $356 Million 25 160 | $160 Million 26 200 | $200 Million 27 200 | $200 Million 28 200 | $200 Million 29 260 | $260 Million 30 180 | $180 Million 31 200 | $200 Million 32 250 | $250 Million 33 200 | $200 Million 34 250 | $250 Million 35 250 | $250 Million 36(33 rows)
The output shows how we can use string concatenation to create a new column with more descriptive values.
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.
SQL1SELECT
2 movies.movie_name,
3 (movie_details.box_office_million_usd - movie_details.budget_million_usd) AS "Profit in Millions"
4FROM
5 movies
6INNER JOIN
7 movie_details ON movies.movie_id = movie_details.movie_id;In this query:
- We perform an
INNER JOINonmoviesandmovie_detailstables usingmovie_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:
Plain text1 movie_name | Profit in Millions 2---------------------------------------------+-------------------- 3 Iron Man | 445.2 4 The Incredible Hulk | 113.4 5 Iron Man 2 | 423.9 6 Thor | 299.3 7 Captain America: The First Avenger | 230.6 8 The Avengers | 1299.6 9 Iron Man 3 | 1015.4 10 Thor: The Dark World | 474.6 11 Captain America: The Winter Soldier | 544.3 12 Guardians of the Galaxy | 603.3 13 Avengers: Age of Ultron | 1155.4 14 Ant-Man | 389.3 15 Captain America: Civil War | 903.3 16 Doctor Strange | 512.7 17 Guardians of the Galaxy Vol. 2 | 663.8 18 Spider-Man: Homecoming | 705.2 19 Thor: Ragnarok | 670.8 20 Black Panther | 1146.9 21 Avengers: Infinity War | 1727.4 22 Ant-Man and The Wasp | 460.7 23 Captain Marvel | 953.3 24 Avengers: Endgame | 2441.8 25 Spider-Man: Far From Home | 971.9 26 Black Widow | 178.5 27 Shang-Chi and the Legend of the Ten Rings | 230.0 28 Eternals | 202.9 29 Spider-Man: No Way Home | 1735.4 30 Doctor Strange in the Multiverse of Madness | 1414.7 31 Thor: Love and Thunder | 514.3 32 Black Panther: Wakanda Forever | 609 33 Ant-Man and The Wasp: Quantumania | 276 34 Guardians of the Galaxy Vol. 3 | 595 35 The Marvels | -50 36(33 rows)
Using mathematical operators like -, we have created a new column that lists the profit of each movie.
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:
SQL1ROUND(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:
SQL1SELECT
2 movies.movie_name,
3 ROUND(movie_details.box_office_million_usd, 0) AS "Rounded Sales in Millions"
4FROM
5 movies
6INNER JOIN
7 movie_details ON movies.movie_id = movie_details.movie_id;In this query:
- We perform a
INNER JOINonmoviesandmovie_detailstables usingmovie_id. - Select the
movies.movie_namecolumn ROUND(movie_details.box_office_million_usd, 0)calls theROUNDfunction to round the values in thebox_office_million_usdtable to 0 decimal places (i.e., the nearest whole number).AS "Rounded Sales in Millions"creates an alias that gives the rounded value column a user-friendly name.
The output is:
Plain text1 movie_name | Rounded Sales in Millions 2---------------------------------------------+--------------------------- 3 Iron Man | 585 4 The Incredible Hulk | 263 5 Iron Man 2 | 624 6 Thor | 449 7 Captain America: The First Avenger | 371 8 The Avengers | 1520 9 Iron Man 3 | 1215 10 Thor: The Dark World | 645 11 Captain America: The Winter Soldier | 714 12 Guardians of the Galaxy | 773 13 Avengers: Age of Ultron | 1405 14 Ant-Man | 519 15 Captain America: Civil War | 1153 16 Doctor Strange | 678 17 Guardians of the Galaxy Vol. 2 | 864 18 Spider-Man: Homecoming | 880 19 Thor: Ragnarok | 851 20 Black Panther | 1347 21 Avengers: Infinity War | 2048 22 Ant-Man and The Wasp | 623 23 Captain Marvel | 1128 24 Avengers: Endgame | 2798 25 Spider-Man: Far From Home | 1132 26 Black Widow | 379 27 Shang-Chi and the Legend of the Ten Rings | 430 28 Eternals | 403 29 Spider-Man: No Way Home | 1995 30 Doctor Strange in the Multiverse of Madness | 1595 31 Thor: Love and Thunder | 714 32 Black Panther: Wakanda Forever | 859 33 Ant-Man and The Wasp: Quantumania | 476 34 Guardians of the Galaxy Vol. 3 | 845 35 The Marvels | 200 36(33 rows)
We can also increase the precision of a column by increasing the value that represents the number of decimal places.
In this lesson, we:
- Reviewed
ASto create aliases for column names - Used
CONCATto add text before and after values in a column - Computed new values by performing operations like subtraction.
- Used the
ROUNDfunction to format numerical values.
These skills are essential for deriving meaningful insights from your data. Now, it's time for you to practice these concepts through the upcoming exercises. You got this!