1
00:00:00,870 --> 00:00:06,630
It is important to understand data types because storing data in the appropriate format is fundamental

2
00:00:06,660 --> 00:00:11,010
to building usable databases and performing accurate analysis.

3
00:00:11,010 --> 00:00:19,530
So whenever I dig into a new database, I check that data type specified for each column in each table,

4
00:00:19,980 --> 00:00:20,430
right?

5
00:00:20,430 --> 00:00:28,200
So if I'm lucky, I can get my hands on a data dictionary or a document that lists each column, specifies

6
00:00:28,200 --> 00:00:33,090
whether it's a number, character or other type and explains the column values.

7
00:00:33,090 --> 00:00:39,840
Unfortunately, many organizations don't create and maintain good documentation, so it's not unusual

8
00:00:39,840 --> 00:00:42,300
to hear we don't have data dictionary.

9
00:00:42,300 --> 00:00:50,910
So in that case I inspect the table structures in Pgadmin to learn as much as I can.

10
00:00:50,910 --> 00:00:56,850
So data types are a programming concept available to more than just SQL, and the concepts you will

11
00:00:56,850 --> 00:01:02,050
explore in this section will transfer well to additional languages you may want to learn.

12
00:01:02,050 --> 00:01:11,140
So in SQL database, each column in the table can hold one and only one data type, which you define

13
00:01:11,140 --> 00:01:16,480
in the Create Table statement by declaring the data type after the column name.

14
00:01:16,480 --> 00:01:24,100
So in this simple example here we will create the Create Table Oxley Academy.

15
00:01:25,070 --> 00:01:26,060
Courses.

16
00:01:26,420 --> 00:01:32,780
And here we will add inside this parenthesis, we will add up.

17
00:01:33,230 --> 00:01:35,060
We will have.

18
00:01:35,790 --> 00:01:36,630
Course.

19
00:01:36,630 --> 00:01:38,070
Creation date.

20
00:01:38,310 --> 00:01:38,700
Course.

21
00:01:40,180 --> 00:01:42,180
Course creation date.

22
00:01:43,280 --> 00:01:46,520
One date and here we will add date type.

23
00:01:47,220 --> 00:01:48,120
After that comma.

24
00:01:48,120 --> 00:01:50,430
Here we will also add the.

25
00:01:52,480 --> 00:01:53,260
Integer.

26
00:01:53,260 --> 00:01:58,540
So course number or course sales.

27
00:02:00,670 --> 00:02:02,380
All courses sold.

28
00:02:03,310 --> 00:02:09,820
Here we will add this integer and here we will have also course name.

29
00:02:09,820 --> 00:02:14,560
Right course name or course title is more appropriate.

30
00:02:14,560 --> 00:02:16,810
So course title is going to be text.

31
00:02:17,440 --> 00:02:18,250
That's it.

32
00:02:19,560 --> 00:02:26,430
So here in this simple example table, which you can review but don't need to create, so you will find

33
00:02:26,430 --> 00:02:31,230
columns with three different data types, a date integer and text.

34
00:02:31,260 --> 00:02:37,110
So in this table named Oxford Academy courses for inventory here.

35
00:02:37,110 --> 00:02:39,180
So we declare the.

36
00:02:40,310 --> 00:02:46,790
Course creation date column to hold date values by adding the date type.

37
00:02:47,820 --> 00:02:50,130
Uh, so similarly we said.

38
00:02:51,250 --> 00:03:00,610
Horses sold to hold values or who hold whole numbers with the integer type declaration and declare the

39
00:03:00,610 --> 00:03:10,150
course title to hold characters where text type so that these data types fall into three categories,

40
00:03:10,150 --> 00:03:13,240
which is these are the and you will encourage.

41
00:03:13,300 --> 00:03:16,300
Encounter the most which is characters.

42
00:03:16,300 --> 00:03:20,280
These characters are any character symbol numbers.

43
00:03:20,290 --> 00:03:27,910
These numbers include whole numbers and fractions and we also have dates and types, which is the temporary

44
00:03:27,910 --> 00:03:28,890
information.

45
00:03:28,900 --> 00:03:33,790
So let's actually firstly get let's understand the characters right now.

46
00:03:33,790 --> 00:03:35,410
So firstly, let's create a table.

47
00:03:35,410 --> 00:03:37,780
And here, as you can see, we created our table.

48
00:03:37,780 --> 00:03:39,730
So we have two tables here.

49
00:03:39,730 --> 00:03:40,960
So let's actually refresh it.

50
00:03:40,960 --> 00:03:43,390
And as you can see, we have two tables here.

51
00:03:43,480 --> 00:03:51,730
Under that we have Oxley Academy Table, Oxley Academy courses table, and we have these here.

52
00:03:51,730 --> 00:03:52,990
So let's open class.

53
00:03:52,990 --> 00:03:59,020
We have course title course sold and course creation course creation date.

54
00:03:59,260 --> 00:04:04,390
So we also can use select all from.

55
00:04:05,620 --> 00:04:06,310
From.

56
00:04:07,580 --> 00:04:17,570
Oxley Academy courses and here we are seeing our date, integer and text, but we don't have any values

57
00:04:17,570 --> 00:04:18,880
in it, which we will add.

58
00:04:18,890 --> 00:04:24,710
So now let's start with understanding the characters characters here.

59
00:04:24,830 --> 00:04:32,570
Let's actually open the code, visual code, Visual Studio code, and we will write on that nice dark

60
00:04:32,720 --> 00:04:33,650
background.

61
00:04:34,040 --> 00:04:36,350
So here we will delete this.

62
00:04:36,350 --> 00:04:39,140
And now that's it.

63
00:04:39,140 --> 00:04:41,240
So we have character.

64
00:04:41,540 --> 00:04:42,470
Character.

65
00:04:42,890 --> 00:04:45,680
So this character in here, we will add.

66
00:04:46,130 --> 00:04:55,880
And so character string types are general purpose types suitable for any combination of text numbers

67
00:04:55,880 --> 00:04:56,900
and symbols.

68
00:04:56,900 --> 00:05:00,320
So character types include the car.

69
00:05:00,320 --> 00:05:03,260
Here we also have var car.

70
00:05:03,260 --> 00:05:06,920
So before var car I will explain this here.

71
00:05:06,920 --> 00:05:12,150
So this car, let's actually make it bigger now.

72
00:05:12,150 --> 00:05:21,480
So this character, this is a fixed length column where the character length is specified by n a column.

73
00:05:21,480 --> 00:05:31,350
So that for example, character ten this stores 20 this, this stores ten characters per row, regardless

74
00:05:31,350 --> 00:05:34,080
of how many characters you have inserted.

75
00:05:34,080 --> 00:05:39,630
So here 25 you will this will store 25 characters per row.

76
00:05:39,630 --> 00:05:47,340
So if you insert fewer than 20 characters in any row PostgreSQL pads, the rest of that column with

77
00:05:47,340 --> 00:05:48,000
spaces.

78
00:05:48,000 --> 00:05:53,880
So this type, which is a part of the standard SQL also you can specify with a longer name characters,

79
00:05:53,880 --> 00:05:59,400
for example, a car character.

80
00:06:00,180 --> 00:06:04,740
And and also keep in mind that nowadays is character.

81
00:06:05,190 --> 00:06:10,680
N is used infrequently and is mainly a remnant of legacy computer systems.

82
00:06:10,680 --> 00:06:13,860
And we also have var character N here.

83
00:06:13,860 --> 00:06:21,090
So this is a variable length column where the maximum length is specified by N, So if you insert fewer

84
00:06:21,090 --> 00:06:25,350
than the maximum, Postgres here will not store extra spaces.

85
00:06:25,350 --> 00:06:28,980
So here let's consider that we have five characters here.

86
00:06:28,980 --> 00:06:34,800
So if we input our, if, if our value is going to be, for example, Oxley.

87
00:06:34,980 --> 00:06:36,540
Oxley is actually six.

88
00:06:36,660 --> 00:06:47,160
So let's say make it ten and the SQL will stored here one, two, three, four, five, six here and

89
00:06:47,220 --> 00:06:51,210
SQL will store four more spaces to fill that ten character.

90
00:06:51,210 --> 00:06:58,410
But in this case, Oxley will just store this and will not store extra spaces.

91
00:06:58,410 --> 00:07:01,470
For example, let's actually make it blue here.

92
00:07:01,500 --> 00:07:02,430
Oops, not here.

93
00:07:02,430 --> 00:07:03,600
Let's actually make it.

94
00:07:04,200 --> 00:07:04,500
An.

95
00:07:05,900 --> 00:07:09,410
So here, let's make it a blue.

96
00:07:09,530 --> 00:07:12,530
So blue will take four spaces, right?

97
00:07:12,530 --> 00:07:14,000
Whereas the string.

98
00:07:14,090 --> 00:07:16,330
One, two, three.

99
00:07:16,340 --> 00:07:17,120
This is also a string.

100
00:07:17,120 --> 00:07:19,060
Keep in mind we'll take three.

101
00:07:19,070 --> 00:07:22,310
So in large databases, the practice saves considerable space.

102
00:07:22,310 --> 00:07:30,020
So this type included in a standard SQL also can be used specified using the longer name, which is

103
00:07:30,020 --> 00:07:32,120
this character.

104
00:07:33,310 --> 00:07:34,660
They're worrying.

105
00:07:36,960 --> 00:07:38,190
I ten.

106
00:07:38,220 --> 00:07:39,270
This is the same.

107
00:07:40,810 --> 00:07:41,920
So here.

108
00:07:42,820 --> 00:07:48,070
Oxley and we also have the text type.

109
00:07:48,340 --> 00:07:50,920
So this is a text type, right?

110
00:07:50,920 --> 00:07:51,280
Like this.

111
00:07:51,280 --> 00:07:52,270
So text.

112
00:07:52,270 --> 00:07:56,680
So this is a variable length column of unlimited length.

113
00:07:56,710 --> 00:08:03,340
According to the PostgreSQL documentation, the longest possible character string you can store is about

114
00:08:03,370 --> 00:08:08,770
one gigabyte up to one gigabyte, by the way.

115
00:08:08,770 --> 00:08:13,540
So this has of unlimited length.

116
00:08:14,390 --> 00:08:18,890
And the text type is not part of the SQL standard, but you will find similar implementations in other

117
00:08:18,890 --> 00:08:24,790
database systems, including the Microsoft SQL Server and my SQL.

118
00:08:24,950 --> 00:08:28,850
So in next lecture, we will create some examples with these here.

119
00:08:28,850 --> 00:08:30,080
So I'm waiting you in next lecture.

120
00:08:30,080 --> 00:08:35,990
My name is Stephan and waiting you in another ASM lecture of Oxford Academy.