WEBVTT 00:00.870 --> 00:06.630 It is important to understand data types because storing data in the appropriate format is fundamental 00:06.660 --> 00:11.010 to building usable databases and performing accurate analysis. 00:11.010 --> 00:19.530 So whenever I dig into a new database, I check that data type specified for each column in each table, 00:19.980 --> 00:20.430 right? 00:20.430 --> 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 00:28.200 --> 00:33.090 whether it's a number, character or other type and explains the column values. 00:33.090 --> 00:39.840 Unfortunately, many organizations don't create and maintain good documentation, so it's not unusual 00:39.840 --> 00:42.300 to hear we don't have data dictionary. 00:42.300 --> 00:50.910 So in that case I inspect the table structures in Pgadmin to learn as much as I can. 00:50.910 --> 00:56.850 So data types are a programming concept available to more than just SQL, and the concepts you will 00:56.850 --> 01:02.050 explore in this section will transfer well to additional languages you may want to learn. 01:02.050 --> 01:11.140 So in SQL database, each column in the table can hold one and only one data type, which you define 01:11.140 --> 01:16.480 in the Create Table statement by declaring the data type after the column name. 01:16.480 --> 01:24.100 So in this simple example here we will create the Create Table Oxley Academy. 01:25.070 --> 01:26.060 Courses. 01:26.420 --> 01:32.780 And here we will add inside this parenthesis, we will add up. 01:33.230 --> 01:35.060 We will have. 01:35.790 --> 01:36.630 Course. 01:36.630 --> 01:38.070 Creation date. 01:38.310 --> 01:38.700 Course. 01:40.180 --> 01:42.180 Course creation date. 01:43.280 --> 01:46.520 One date and here we will add date type. 01:47.220 --> 01:48.120 After that comma. 01:48.120 --> 01:50.430 Here we will also add the. 01:52.480 --> 01:53.260 Integer. 01:53.260 --> 01:58.540 So course number or course sales. 02:00.670 --> 02:02.380 All courses sold. 02:03.310 --> 02:09.820 Here we will add this integer and here we will have also course name. 02:09.820 --> 02:14.560 Right course name or course title is more appropriate. 02:14.560 --> 02:16.810 So course title is going to be text. 02:17.440 --> 02:18.250 That's it. 02:19.560 --> 02:26.430 So here in this simple example table, which you can review but don't need to create, so you will find 02:26.430 --> 02:31.230 columns with three different data types, a date integer and text. 02:31.260 --> 02:37.110 So in this table named Oxford Academy courses for inventory here. 02:37.110 --> 02:39.180 So we declare the. 02:40.310 --> 02:46.790 Course creation date column to hold date values by adding the date type. 02:47.820 --> 02:50.130 Uh, so similarly we said. 02:51.250 --> 03:00.610 Horses sold to hold values or who hold whole numbers with the integer type declaration and declare the 03:00.610 --> 03:10.150 course title to hold characters where text type so that these data types fall into three categories, 03:10.150 --> 03:13.240 which is these are the and you will encourage. 03:13.300 --> 03:16.300 Encounter the most which is characters. 03:16.300 --> 03:20.280 These characters are any character symbol numbers. 03:20.290 --> 03:27.910 These numbers include whole numbers and fractions and we also have dates and types, which is the temporary 03:27.910 --> 03:28.890 information. 03:28.900 --> 03:33.790 So let's actually firstly get let's understand the characters right now. 03:33.790 --> 03:35.410 So firstly, let's create a table. 03:35.410 --> 03:37.780 And here, as you can see, we created our table. 03:37.780 --> 03:39.730 So we have two tables here. 03:39.730 --> 03:40.960 So let's actually refresh it. 03:40.960 --> 03:43.390 And as you can see, we have two tables here. 03:43.480 --> 03:51.730 Under that we have Oxley Academy Table, Oxley Academy courses table, and we have these here. 03:51.730 --> 03:52.990 So let's open class. 03:52.990 --> 03:59.020 We have course title course sold and course creation course creation date. 03:59.260 --> 04:04.390 So we also can use select all from. 04:05.620 --> 04:06.310 From. 04:07.580 --> 04:17.570 Oxley Academy courses and here we are seeing our date, integer and text, but we don't have any values 04:17.570 --> 04:18.880 in it, which we will add. 04:18.890 --> 04:24.710 So now let's start with understanding the characters characters here. 04:24.830 --> 04:32.570 Let's actually open the code, visual code, Visual Studio code, and we will write on that nice dark 04:32.720 --> 04:33.650 background. 04:34.040 --> 04:36.350 So here we will delete this. 04:36.350 --> 04:39.140 And now that's it. 04:39.140 --> 04:41.240 So we have character. 04:41.540 --> 04:42.470 Character. 04:42.890 --> 04:45.680 So this character in here, we will add. 04:46.130 --> 04:55.880 And so character string types are general purpose types suitable for any combination of text numbers 04:55.880 --> 04:56.900 and symbols. 04:56.900 --> 05:00.320 So character types include the car. 05:00.320 --> 05:03.260 Here we also have var car. 05:03.260 --> 05:06.920 So before var car I will explain this here. 05:06.920 --> 05:12.150 So this car, let's actually make it bigger now. 05:12.150 --> 05:21.480 So this character, this is a fixed length column where the character length is specified by n a column. 05:21.480 --> 05:31.350 So that for example, character ten this stores 20 this, this stores ten characters per row, regardless 05:31.350 --> 05:34.080 of how many characters you have inserted. 05:34.080 --> 05:39.630 So here 25 you will this will store 25 characters per row. 05:39.630 --> 05:47.340 So if you insert fewer than 20 characters in any row PostgreSQL pads, the rest of that column with 05:47.340 --> 05:48.000 spaces. 05:48.000 --> 05:53.880 So this type, which is a part of the standard SQL also you can specify with a longer name characters, 05:53.880 --> 05:59.400 for example, a car character. 06:00.180 --> 06:04.740 And and also keep in mind that nowadays is character. 06:05.190 --> 06:10.680 N is used infrequently and is mainly a remnant of legacy computer systems. 06:10.680 --> 06:13.860 And we also have var character N here. 06:13.860 --> 06:21.090 So this is a variable length column where the maximum length is specified by N, So if you insert fewer 06:21.090 --> 06:25.350 than the maximum, Postgres here will not store extra spaces. 06:25.350 --> 06:28.980 So here let's consider that we have five characters here. 06:28.980 --> 06:34.800 So if we input our, if, if our value is going to be, for example, Oxley. 06:34.980 --> 06:36.540 Oxley is actually six. 06:36.660 --> 06:47.160 So let's say make it ten and the SQL will stored here one, two, three, four, five, six here and 06:47.220 --> 06:51.210 SQL will store four more spaces to fill that ten character. 06:51.210 --> 06:58.410 But in this case, Oxley will just store this and will not store extra spaces. 06:58.410 --> 07:01.470 For example, let's actually make it blue here. 07:01.500 --> 07:02.430 Oops, not here. 07:02.430 --> 07:03.600 Let's actually make it. 07:04.200 --> 07:04.500 An. 07:05.900 --> 07:09.410 So here, let's make it a blue. 07:09.530 --> 07:12.530 So blue will take four spaces, right? 07:12.530 --> 07:14.000 Whereas the string. 07:14.090 --> 07:16.330 One, two, three. 07:16.340 --> 07:17.120 This is also a string. 07:17.120 --> 07:19.060 Keep in mind we'll take three. 07:19.070 --> 07:22.310 So in large databases, the practice saves considerable space. 07:22.310 --> 07:30.020 So this type included in a standard SQL also can be used specified using the longer name, which is 07:30.020 --> 07:32.120 this character. 07:33.310 --> 07:34.660 They're worrying. 07:36.960 --> 07:38.190 I ten. 07:38.220 --> 07:39.270 This is the same. 07:40.810 --> 07:41.920 So here. 07:42.820 --> 07:48.070 Oxley and we also have the text type. 07:48.340 --> 07:50.920 So this is a text type, right? 07:50.920 --> 07:51.280 Like this. 07:51.280 --> 07:52.270 So text. 07:52.270 --> 07:56.680 So this is a variable length column of unlimited length. 07:56.710 --> 08:03.340 According to the PostgreSQL documentation, the longest possible character string you can store is about 08:03.370 --> 08:08.770 one gigabyte up to one gigabyte, by the way. 08:08.770 --> 08:13.540 So this has of unlimited length. 08:14.390 --> 08:18.890 And the text type is not part of the SQL standard, but you will find similar implementations in other 08:18.890 --> 08:24.790 database systems, including the Microsoft SQL Server and my SQL. 08:24.950 --> 08:28.850 So in next lecture, we will create some examples with these here. 08:28.850 --> 08:30.080 So I'm waiting you in next lecture. 08:30.080 --> 08:35.990 My name is Stephan and waiting you in another ASM lecture of Oxford Academy.