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
Movie Details Table
Characters Table
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:
The output is:
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:
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.
The output is:
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.
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:
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:
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:
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:
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!
