SQL Arithmetic With Practice SQL Exercises
In addition to querying raw data with SQL, you can also use math expressions to transform column values. Here's an example:
Let's dive into each arithmetic operator, one by one.
SQL Arithmetic Operators
SQL Addition +
The operator adds two numbers.
SQL Subtraction -
The operator subtracts one column value from another.
SQL Multiplication *
The operator multiplies two numbers.
Note: this is exactly the same as ... so don't get confused by 's two uses!!
SQL Division /
The operator divides the first column value by the number in the 2nd column.
Always ensure you're not dividing by zero, as it will cause an error. We dive into more nuances and issues with SQL division in a later tutorial.
SQL Modulus %
The operator, also known as the modulus or remainder function, returns the remainder of a division operation.
In the above example, the DBMS returns 5 because 23 divide by 6 is 3, with a remainder of 5.
Odd and Even with Modulus %
The modulus () operator is often used to find odd and even values, like in this hard Google SQL Interview Question.
While solving the entire Google problem is too tricky right now because it needs ranking window functions, let's look at a small snippet from the full solution which finds odd values:
The above SQL query looks at the and looks at the remainder when divided by 2.
Odd numbers like 1, 3, 5, when divided by 2, have a remainder of 1, which is why gets us all odd numbered measurements.
SQL Exponentiation (^)
The operator, also known as the power operator, raises a number to the power of another number.
The above returns 100.
SQL Arithmetic Operator Summary
Here's a summary table that summarizes how the arithmetic operators in SQL work:
| Operator | Description | Example | Result |
|---|---|---|---|
| + | Addition | 15 + 5 | 20 |
| - | Subtraction | 15 - 5 | 10 |
| * | Multiplication | 15 * 5 | 75 |
| / | Division | 15 / 5 | 3 |
| % | Modulus (Remainder of Division) | 14 % 5 | 4 |
| ^ | Exponentiation (Not standard in all DBMS) | 15 ^ 2 | 225 |
| - (as a prefix) | Negation | -15 | -15 |
SQL Arithmetic Order of Operations
Just like in standard arithmetic, SQL follows the order of operations of PEMDAS:
- P: Parentheses first
- E: Exponents (ie. )
- MD: Multiplication and Division (left-to-right)
- AS: Addition and Subtraction (left-to-right)
Here's some SQL examples of PEMDAS:
| SQL Statement | Result | Explanation |
|---|---|---|
| SELECT 3 + 7 * 2; | 17 | Multiplication comes before addition. |
| SELECT (3 + 7) * 2; | 20 | Parentheses means addition happens first. |
| SELECT 10 / 2 + 3 * 4; | 17 | 10/2 = 5, 3*4=12, so 5 + 12 = 17. |
| SELECT (10 / 2) + (3 * 4); | 17 | Same as above, but more explicit with parens! |
To make your code more readable and less confusing, feel free to use parenthesis to make your SQL math formulas more explicit.
SQL Arithmetic Practice Exercises
Let's practice combining the arithmetic operators in this lesson to compute some interesting metrics by analzying CVS Pharmacy, JP Morgan, and FAANG stock datasets.
Practice SQL Subtraction: CVS Pharmacy Interview Question
Here's a real SQL interview question asked by CVS Health for a healthcare analytics job: Write a query to find the top 3 most profitable medicines sold, and how much profit they made.
Your output should look like this:
| drug | total_profit |
|---|---|
| Humira | 81515652.55 |
| Keytruda | 11622022.02 |
| Dupixent | 11217052.34 |
Hint #1: Total Profit = Total Sales - Cost of Goods Sold
Hint #2: To find the top 3 drugs, just do a
Practice SQL Arithmetic: JPMorgan Chase SQL Interview Question
In this JPMorgan Data Analyst interview question, imagine that you're on the credit card marketing analytics team at Chase.
You're preparing to launch a new credit card, and to gain some insights, you're analyzing how many credit cards were issued each month. Write a query that outputs the name of each credit card and the difference in the number of issued cards between the month with the highest issuance cards and the lowest issuance.
Arrange the results based on the largest disparity like as follows:
| card_name | difference |
|---|---|
| Chase Sapphire Reserve | 30000 |
| Chase Freedom Flex | 15000 |
Hint: You'll want to use the and aggregate functions!
FAANG Stocks That Had 'Big-Mover Months'
A "big-mover month" is when a stock closes up or down by greater than 10% compared to the price it opened at. For example, when COVID hit and e-commerce became the new normal, Amazon stock in April 2020 had a big-mover month because the price shot up from $96.65 per share at open to $123.70 at close – a 28% increase!
| ticker | date | open | close | percent_change |
|---|---|---|---|---|
| AMZN | 04/01/2020 00:00:00 | 96.65 | 123.70 | 28.0 |
| NFLX | 04/01/2022 00:00:00 | 376.80 | 190.36 | -49.5 |
Netflix stock had a big-mover month in April 2022 in the reverse direction. That month, Netflix reported that the company lost 200k subscribers in Q1, and expected to lose another two million subs in Q2. In Apr'22, Netflix stock opened that month at $376.80 per share, but closed at $190.36, representing a 49.5% loss – yikes!
Display the stocks which had "big-mover months", and how many of those months they had. Order your results from the stocks with the most, to least, "big-mover months".
What's Next: MATH Functions
In the last SQL exercise about big-mover months, in order to figure out if the difference between the open & close price was greater than 10% of the opening price, we used this clunky SQL snippet:
We wrote it this way, because could be a positive or negative number, depending on whether the stock closed at 10% higher OR 10% lower compared to the start of the month. We needed the to account for both cases.
Now, some of you might be thinking...
damn I really wish SQL had an absolute value function rn
If that's your thinking, good job – that's exactly what we need!
We'll cover the function, along with some other powerful SQL commands in the next tutorial on mathematical functions in SQL!
Next Lesson
MATH FUNCTIONS 📈