Big SQL Energy Beginner Course: Lesson 5 Data Types & Cast

Jan 05, 2025

Got BDE?⚡️

Hi BDEs! Welcome to Lesson 5 of the free Big SQL Energy Beginner Course⚡️ Today we’re learning how to check the data type of a column and convert data types with CAST!

Return to Big SQL Energy Beginner homepage here to access the other lessons:

Return to Homepage 📚

If you’re new here:

💁🏽‍♀️ Who Am I?

I’m Jess Ramos, the founder of Big Data Energy and the creator of the BEST SQL course and community: Big SQL Energy⚡️. Check me out on socials: 🔗LinkedIn, 🔗Instagram, 🔗YouTube, and 🔗TikTok. And of course subscribe to my 🔗newsletter here for all my upcoming lessons and updates— all for free!

⚡️ What is Big SQL Energy?

🔗Big SQL Energy Intermediate is an intermediate SQL course designed to solve real-world business problems hands-on using realistic data and a modern tech stack. You’ll walk away with 2 SQL portfolio projects, portfolio building guidance, access to the Big Data Energy Discord community, and lots of confidence for you upcoming coding interviews! You also get access to all monthly events including masterclasses, office hours, and guest speakers. This course is all of the most important things I’ve learned on the job as a Senior Data Analyst in tech who grew from a $72K salary to over a $150K salary. And now I’m sharing it with YOU! It’s more than just another course— it’s a challenging program designed to upskill your SQL to the intermediate level, get ready to ace your live coding interviews, and build connections in the data community through the Discord and events.

JOIN now (pun intended 😉)

Lesson 5: Data Types & Cast

🎥 Video

Think about all the different kinds of data that exists out there…

A list of customers with their names, address, and phone numbers.
A chart of patients with all their health data like weight, height, and age.
Stock data with prices for every date this year.

There’s a huge variety of data in the real world that results in different formats in the database. Some of the main ones are:

String / Varchar: text data like a name, address, or customer feedback text box
Integers: whole numbers without decimal places like age, number of people, or subscription count (you can’t have 1.35 people or 67.98 subscriptions!)
Decimals: numbers with decimal places like price, revenue, or rainfall amount
Dates: Date values without a timestamp like 2024-01-03
Datetimes: Date values with a timestamp like 2024-01-03 03:09:26

Many of these data types have additional formatting options that are optional where you can specify the date format, max characters in a string, and even the number of decimal places to keep in a decimal number. For these, I just Google as I need them! All you have to do is figure out the correct format for what you’re looking for, do a few tests in SQL, and QA to make sure you really did what you meant to.

There are also a bunch of subcategories for data types as well that can be even more niche and specific, but you really don’t need to know all of them. If you ever have a use case for them, you can just do a quick google or ChatGPT to figure out which one you need. I promise you don’t need to memorize ALL the different formats. Just understand what they mean and be ready to google, apply, and test (QA) as needed! Being a good data analyst (especially senior!) is being able to figure things out and apply examples to your scenario.

Now that we have an understanding of the different data types we might experience in a database, let’s talk about some real stuff for a second. Have you ever heard of data cleaning? Chances are, you’ve heard jokes about it because it’s a huge part of a data analyst’s job (and a huge pain in the B.U.T.T.), but you might not know what it is. One common issue in a database is that data can be imported or loaded into a database in incorrect formats (or maybe the data was just incorrect in the first place at the source). For example, a date might be showing up as a string, an integer might be showing up as a decimal, or a string might be showing up as an integer.

Why does this matter? Well sometimes we need to clean up field so they look better and more appropriate in the output, but more commonly, we need to convert datatypes in order to use them in relevant functions. For example, only dates can be used in date functions, so if your data imported as text, you might be out of luck using that date function. (You’ll get an error about an incorrect data type and need to convert it to fix it and use it in the function. This caused me MANY headaches as a new data analyst, so let’s learn how to fix it!).

(BTW if you’re new to functions in SQL, functions basically mean you put something IN and get something OUT. You put in values or columns (called inputs or arguments) and you get out some sort of output based on what the function does)

We can use the CAST() function to convert values & fields between data types. Here’s some examples below:

select 
   cast(order_date, varchar),    --converts date to string/varchar
   cast(coupon, integer),        --converts decimal to integer
   cast(order_date, datetime),   --converts date to datetime
   cast(price, decimal)          --converts integer to decimal
from 
   orders;

***Note the CAST function syntax may differ slightly between dialects and platforms. ChatGPT if needed!

Here’s a secret insider secret if you don’t wanna look like a newbie:

A lot of newer cloud platforms (like Snowflake) support a shorthand notion of CAST with 2 colons (::) after the value or column name.

select 
   order_date::varchar), 
   coupon::integer, 
   order_date::datetime, 
   price::decimal 
from 
   orders;

Changing the data type of a column in a query doesn’t change the data type in the original source table, only creates that new version temporarily in your query. Now that we know how to convert data types in SQL, let’s move onto some other FUN functions and operations!

If you’ve enjoyed the free BEGINNER Big SQL Energy course, grab my intermediate course. There are no deadlines or timelines for this course— you start and take as long as you want. It will take your beginner skills to the next level and get you ready to ace your interviews. DM me or email me at courses@bdeanalytics.com if you have questions on the intermediate course!