It is important to understand data types because storing data in the appropriate format is fundamental
to building usable databases and performing accurate analysis.
So whenever I dig into a new database, I check that data type specified for each column in each table,
right?
So if I'm lucky, I can get my hands on a data dictionary or a document that lists each column, specifies
whether it's a number, character or other type and explains the column values.
Unfortunately, many organizations don't create and maintain good documentation, so it's not unusual
to hear we don't have data dictionary.
So in that case I inspect the table structures in Pgadmin to learn as much as I can.
So data types are a programming concept available to more than just SQL, and the concepts you will
explore in this section will transfer well to additional languages you may want to learn.
So in SQL database, each column in the table can hold one and only one data type, which you define
in the Create Table statement by declaring the data type after the column name.
So in this simple example here we will create the Create Table Oxley Academy.
Courses.
And here we will add inside this parenthesis, we will add up.
We will have.
Course.
Creation date.
Course.
Course creation date.
One date and here we will add date type.
After that comma.
Here we will also add the.
Integer.
So course number or course sales.
All courses sold.
Here we will add this integer and here we will have also course name.
Right course name or course title is more appropriate.
So course title is going to be text.
That's it.
So here in this simple example table, which you can review but don't need to create, so you will find
columns with three different data types, a date integer and text.
So in this table named Oxford Academy courses for inventory here.
So we declare the.
Course creation date column to hold date values by adding the date type.
Uh, so similarly we said.
Horses sold to hold values or who hold whole numbers with the integer type declaration and declare the
course title to hold characters where text type so that these data types fall into three categories,
which is these are the and you will encourage.
Encounter the most which is characters.
These characters are any character symbol numbers.
These numbers include whole numbers and fractions and we also have dates and types, which is the temporary
information.
So let's actually firstly get let's understand the characters right now.
So firstly, let's create a table.
And here, as you can see, we created our table.
So we have two tables here.
So let's actually refresh it.
And as you can see, we have two tables here.
Under that we have Oxley Academy Table, Oxley Academy courses table, and we have these here.
So let's open class.
We have course title course sold and course creation course creation date.
So we also can use select all from.
From.
Oxley Academy courses and here we are seeing our date, integer and text, but we don't have any values
in it, which we will add.
So now let's start with understanding the characters characters here.
Let's actually open the code, visual code, Visual Studio code, and we will write on that nice dark
background.
So here we will delete this.
And now that's it.
So we have character.
Character.
So this character in here, we will add.
And so character string types are general purpose types suitable for any combination of text numbers
and symbols.
So character types include the car.
Here we also have var car.
So before var car I will explain this here.
So this car, let's actually make it bigger now.
So this character, this is a fixed length column where the character length is specified by n a column.
So that for example, character ten this stores 20 this, this stores ten characters per row, regardless
of how many characters you have inserted.
So here 25 you will this will store 25 characters per row.
So if you insert fewer than 20 characters in any row PostgreSQL pads, the rest of that column with
spaces.
So this type, which is a part of the standard SQL also you can specify with a longer name characters,
for example, a car character.
And and also keep in mind that nowadays is character.
N is used infrequently and is mainly a remnant of legacy computer systems.
And we also have var character N here.
So this is a variable length column where the maximum length is specified by N, So if you insert fewer
than the maximum, Postgres here will not store extra spaces.
So here let's consider that we have five characters here.
So if we input our, if, if our value is going to be, for example, Oxley.
Oxley is actually six.
So let's say make it ten and the SQL will stored here one, two, three, four, five, six here and
SQL will store four more spaces to fill that ten character.
But in this case, Oxley will just store this and will not store extra spaces.
For example, let's actually make it blue here.
Oops, not here.
Let's actually make it.
An.
So here, let's make it a blue.
So blue will take four spaces, right?
Whereas the string.
One, two, three.
This is also a string.
Keep in mind we'll take three.
So in large databases, the practice saves considerable space.
So this type included in a standard SQL also can be used specified using the longer name, which is
this character.
They're worrying.
I ten.
This is the same.
So here.
Oxley and we also have the text type.
So this is a text type, right?
Like this.
So text.
So this is a variable length column of unlimited length.
According to the PostgreSQL documentation, the longest possible character string you can store is about
one gigabyte up to one gigabyte, by the way.
So this has of unlimited length.
And the text type is not part of the SQL standard, but you will find similar implementations in other
database systems, including the Microsoft SQL Server and my SQL.
So in next lecture, we will create some examples with these here.
So I'm waiting you in next lecture.
My name is Stephan and waiting you in another ASM lecture of Oxford Academy.