While the majority of cases will directly show the data we SELECT from a table, occasionally we will want to manipulate this data before we present it. Now, I am not suggesting a life of crime in ‘cooking the books’ or any such notion. I am suggesting a much more simple idea of modifying data so it is more presentable to the end user. Changing Date formats to a European standard would be an example; or making a calculation for the total of an invoice so the end user does not have to make this calculation. In some cases the data is not provided and we can make calculations to provide this data. For example, we might not have to know how old a person is, but if we have a birthdate, we can make a calculation to get how old the person is.

Our Cat table includes a column MonthsWithOwner. What if we wanted to present this information in years instead of months, we can make a simple math calculation by dividing the months by 12. I am also going to require a cat has been with an owner at least one year.

SELECT NameOfCat, MonthsWithOwner / 12 AS 'Years WITH Owner'

FROM Cat

WHERE MonthsWithOwner > 12;

 

OUTPUT (Modified)

NameOfCat     Years WITH Owner
————–           —————-

Garfield                        37
Dexter                           2
Arwen                            2
Milo                               3
Bella                              4
Lucy                               1
Luna                              1
Kitty                               1
Piper                              1

(147 row(s) affected)

Of course, we can make all the basic math calculations—addition, subtraction, multiplication, and division—on our numeric data types.

**Warning** While some databases will allow you to make mathematical calculations on text fields, this should be avoided. While your testing may have worked, you have no idea what kind of data you will get in the future. This is a good example of why data types are important in a relational database.

I

Want to follow along on your own database?

Do you want to follow along with the examples?  We have created a sample database you can use to follow along and try in your own database.  Go to the download page and follow the instructions.  Have fun on the SQL trail!