Lesson 5

Question 1

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

(Name 5)

Answer 1

1. String / Varchar

• text data like a name, address, or customer feedback text box

2. 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!)

3. Decimals

• numbers with decimal places like price, revenue, or rainfall amount

4. Dates

• Date values without a timestamp like 2024-01-03

5. Datetimes

• Date values with a timestamp like 2024-01-03 03:09:26

Question 2

What does varchar stand for?

Answer 2

Variable character

• Just means it’s a string or a text field

Question 3

What is data cleaning?

________________________________

Why do we need to do it? (What’s a common issue in a database?)

Answer 3

Data cleaning can involve:

• Cleaning up a field so that values look better and more appropriate in the output
• Converting data types in order to use them in relevant functions
  
  • More common

____________________________________________

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)

Question 4

What is a function?

Answer 4

You put something IN and get something OUT.

• Put in values or columns (called inputs or arguments)
• Get out some sort of output (based on what the function does)

Question 5

How can you check the data type of every single row in the order_date column?

What’s one example of why you would do this?

(Answer & write code)

Answer 5

typeof() function

• Helpful to confirm that data imported correctly

SELECT
    typeof(order_date)
FROM
    orders

• Note: may have slightly different syntax depending on your SQL dialect

Question 6

Be careful – sometimes when you’re importing data, date fields can sometimes import as ___

Answer 6

Be careful – sometimes when you’re importing data, date fields can sometimes import as strings or varchars

Question 7

How can you convert columns between different data types?

When is this especially helpful?

Answer 7

We can use the CAST() function to convert values & fields (columns) between data types.

________________________________________________

CAST can become really handy when:

• Cleaning data
• Converting data types, so the data can be input into different functions

Question 8

How can you:

• Pull the coupon column from the orders table
• Check the data types of the coupon column
• Convert the data types of the coupon column to a decimal data type

(Write code - no short hand)

Answer 8

SELECT
    coupon,
    typeof(coupon),
    cast(coupon as decimal)
FROM
    orders

Question 9

A lot of newer cloud platforms (like Snowflake) support a shorthand notation of cast by using ____.

For example, from the orders table:

• Pull the order_date column and convert it to a varchar data type
• Pull the coupon column and convert it to an integer data type
• Pull the order_date column and convert it to a datetime data type
• Pull the price column and convert it to a decimal data type

This shorthand makes you not look like a newb :)

Answer 9

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

SELECT 
   order_date::varchar, 
   coupon::int, 
   order_date::datetime, 
   price::decimal 
FROM
   orders

Question 10

Changing the data type of a column in a query does / does not change the data type in the original source table.

Answer 10

Changing the data type of a column in a query does NOT change the data type in the original source table.

• It only creates that new version temporarily in your query.