WEBVTT 00:00.250 --> 00:00.900 I'll get. 00:00.930 --> 00:04.460 So this is the solution video to our data types problem. 00:04.470 --> 00:08.600 That first one here what's a good use case for char. 00:08.910 --> 00:14.940 So there's not a ton of times that I end up using them but remember they are fixed length so we have 00:14.940 --> 00:19.980 to specify exactly the length of the char that we want for that column. 00:19.980 --> 00:24.940 So we often only use it for text that we know 100 percent has a fixed length. 00:24.990 --> 00:30.090 So the one that I have use a couple of times the state abbreviations. 00:30.390 --> 00:34.490 If you're doing sex like AM or F. 00:34.530 --> 00:39.600 Or perhaps you're working on a project where I don't know let's say you're working with companies and 00:39.690 --> 00:46.260 you want an abbreviated name for every company that's only five characters or four characters long then 00:46.260 --> 00:49.110 you could use char as well. 00:49.110 --> 00:54.090 Hopefully you came up with something better than the something new and original. 00:54.100 --> 00:56.850 Next up fill in the blanks of this schema. 00:57.310 --> 00:57.610 OK. 00:57.610 --> 01:00.070 So there's a couple of things to talk about. 01:00.070 --> 01:03.160 Item Name is text. 01:03.160 --> 01:08.880 It's gonna be something like I don't know Cabbage Patch doll or baseball hat. 01:09.460 --> 01:16.360 And that should be a far Chaar most likely you could get away with using a char but there's no reason 01:16.360 --> 01:23.280 to do that because it's not fixed length so it makes more sense to do Vardar and then quantity I'll 01:23.290 --> 01:24.280 skip price for a moment. 01:24.280 --> 01:26.950 Quantity makes sense to be an integer. 01:26.950 --> 01:32.260 We are never going to have partial items in stock when I going to have 2.5. 01:32.260 --> 01:33.470 Cabbage Patch dolls. 01:33.530 --> 01:39.550 Hopefully unless there is some sort of massacre with the Cabbage Patch dolls and the chainsaw. 01:39.620 --> 01:43.470 Or hopefully I'm only going to have whole numbers of them so we'll use it. 01:43.690 --> 01:45.700 But price is a little trickier. 01:45.990 --> 01:47.370 And so I added this note. 01:47.440 --> 01:49.970 Price is always less than 1 million dollars. 01:50.030 --> 01:50.540 OK. 01:50.890 --> 01:55.120 But price is not usually or not exclusively a whole number. 01:55.150 --> 01:56.990 We want to allow for sense. 01:57.190 --> 02:00.250 So we're going to want to use a decimal. 02:00.250 --> 02:07.600 Now you could use a float or double but as we discussed decimals better for monetary stuff because it's 02:07.600 --> 02:08.440 more precise. 02:08.440 --> 02:13.070 You don't have to worry about losing precision as you get into longer numbers. 02:13.110 --> 02:16.010 OK so then the question is what do we put here. 02:16.010 --> 02:22.360 It's not just decimal on it's own right we don't just do this. 02:22.360 --> 02:27.900 We have to number three pass in and those two numbers if you can remember the names. 02:27.900 --> 02:31.560 I believe the first one is precision and the second one is scale. 02:31.560 --> 02:32.750 Don't quote me on that though. 02:32.820 --> 02:39.040 But basically it's the total number of digits followed by the number of digits after the decimal. 02:39.480 --> 02:45.090 So we can start with the number of digits after the decimal which when you're working with pricer monetary 02:45.090 --> 02:47.710 information it's pretty much always two. 02:48.210 --> 02:51.850 So then the question becomes how many total digits do we need. 02:51.870 --> 02:57.180 And if I said It's always less than zero It's always less than 1 million. 02:57.210 --> 03:04.090 That means that our highest price we could have is nine nine nine nine nine nine point ninety nine. 03:04.860 --> 03:10.620 So if that's the maximum price then all we need to do is count up the total number of digits. 03:10.680 --> 03:17.810 So that is 3 6 7 8 and that's her answer. 03:17.830 --> 03:21.290 Decimal 8 comma 2. 03:21.520 --> 03:26.560 Next up what's the difference between Date Time and time stamp. 03:27.120 --> 03:33.060 So I have the documentation open for date date time and time stamp and the first thing I'll say is that 03:33.060 --> 03:34.430 there's a couple of differences. 03:34.470 --> 03:40.240 There are a few technical differences as far as how they're stored and the ranges that they support. 03:40.290 --> 03:43.850 And then there's a big difference in how they're putting to use most of the time. 03:44.160 --> 03:45.100 So both of them. 03:45.150 --> 03:46.320 Let's start off. 03:46.320 --> 03:51.480 They both store date and time information so they store the date and then the time. 03:51.480 --> 03:55.910 However there is a big difference in the range that they support. 03:55.920 --> 03:58.420 So time stamps have a much smaller range. 03:58.650 --> 04:00.770 If you remember we went over this. 04:00.930 --> 04:09.260 Where's that time stamp has a range from 1970 to 2038 versus date time goes from 1000 1000 up to year 04:09.330 --> 04:11.370 nine thousand nine hundred ninety nine. 04:11.850 --> 04:13.680 So that's one significant difference. 04:13.680 --> 04:21.090 The other is the size of the memory they take up a date time is roughly twice as large as the time stamp. 04:21.090 --> 04:24.640 Which brings us to the final difference which is how they're actually put into practice. 04:24.900 --> 04:33.260 And typically time stamps are used at least in my experience exclusively when we are trying to add meta 04:33.260 --> 04:38.310 data about when something is created or updated to a table. 04:38.610 --> 04:40.620 Otherwise I use Date Time. 04:40.620 --> 04:45.720 Now that doesn't mean that you can't use time stamp in you know if you wanted to use time stamps to 04:45.720 --> 04:53.620 store birthdays you could but you could run into problems if you have people who are born before 1970. 04:53.700 --> 04:58.920 So again the key differences really boil down to the ranges they support and then how much memory they 04:58.920 --> 05:00.290 take up. 05:01.220 --> 05:03.170 OK moving on. 05:03.210 --> 05:04.950 Print out the current time. 05:04.980 --> 05:08.360 Finally some code so to print out the current time. 05:09.210 --> 05:12.630 It's just a matter of Kuhr time. 05:12.710 --> 05:13.820 Me to select that. 05:15.250 --> 05:16.090 Every go. 05:16.290 --> 05:18.910 Sixteen oh eight 13. 05:19.000 --> 05:20.690 Yours of course will differ. 05:20.710 --> 05:28.590 Most likely print out the current date but not the time so that one you want to do. 05:28.600 --> 05:36.390 Curr date and see April 22nd 2017. 05:36.760 --> 05:41.180 Still my birthday print out the current day of the week. 05:41.500 --> 05:43.140 OK so this one's a little bit different. 05:43.240 --> 05:45.380 There's a couple ways of achieving this. 05:45.400 --> 05:58.630 The first one is to use Select day of week and then we could either give it the date or date or we could 05:58.630 --> 05:59.670 use now. 06:00.070 --> 06:04.550 So today is a Saturday so it gives us 7 just to show you. 06:04.650 --> 06:07.960 And it can replace this with now which is a date time. 06:07.960 --> 06:14.170 They also get 7 but there's another way we could do this which is to use date format and if we go to 06:14.170 --> 06:22.560 the documentation here and refined this date format if we scroll down somewhere on here day that week 06:23.080 --> 06:23.840 where is that. 06:23.850 --> 06:24.580 Here we go. 06:24.720 --> 06:26.290 Day of the week. 06:26.410 --> 06:28.700 Percent w lowercase W. 06:28.720 --> 06:37.200 We could use that use the like date format now or Khurd date comma and the string we want. 06:37.200 --> 06:44.290 It's just percent lower case w and actually I didn't know this until now. 06:44.290 --> 06:45.160 There is a difference. 06:45.170 --> 06:46.670 That's kind of fascinating. 06:46.960 --> 06:52.900 If we read this closely it says day the week zero is Sunday and 6 is Saturday. 06:53.020 --> 06:57.570 So these are not equivalent Actually day of the week. 06:57.580 --> 07:01.470 Sunday is 1 rather than zero and Saturday 7. 07:01.930 --> 07:10.150 So I guess if we wanted DS to be equivalent we would need to do plus 1 and shift everything by one or 07:11.020 --> 07:13.740 shift everything down one for use in the previous. 07:13.870 --> 07:14.730 Interesting. 07:14.940 --> 07:18.850 No idea why that's the case but it is what it is. 07:18.850 --> 07:23.860 So the next thing is to print out the current day of the week but I want the date name day name. 07:23.860 --> 07:25.720 Monday Tuesday Wednesday and so on. 07:26.020 --> 07:33.490 I wouldn't expect you to remember this but there is a day name that we can use like that select day 07:33.490 --> 07:39.170 name or we can do our format date or date format. 07:39.190 --> 07:45.160 I always mix up the order there but its date underscore format for now and then we just have to figure 07:45.160 --> 07:47.340 out how we get the day that week name. 07:47.410 --> 07:55.550 If you go to the docs it's right above weekday name Sunday to Saturday percent upper case w. 07:56.140 --> 08:00.870 So if we switch back just change it to an uppercase W.. 08:00.910 --> 08:04.180 Now we get Saturday OK. 08:04.990 --> 08:05.860 Next up. 08:05.890 --> 08:13.720 Print out the current date and time using this form in month month slash day day slash year year year 08:13.780 --> 08:14.540 year. 08:15.000 --> 08:24.570 OK so we need to use date format and we can just use Kuhr date or we can use now. 08:24.610 --> 08:29.470 I like to use now typically but we were not actually using the time information. 08:29.470 --> 08:31.110 So why don't we just use Kuhr date. 08:31.120 --> 08:34.950 In this case just to show you we can and then the way we want to format it. 08:35.060 --> 08:39.640 If we go to the docs we want first of all with the month. 08:40.150 --> 08:43.690 So we want month Gnumeric which is present. 08:43.810 --> 08:49.030 No that's not what we want what we want is done here this month. 08:49.030 --> 08:51.740 Numeric 0 to 12. 08:52.390 --> 08:55.430 So we get them lowercase M.. 08:55.870 --> 08:58.030 Percent lower case slash. 08:58.150 --> 09:04.190 And then one day of the month numeric 0 0 to 31 years you want those two digits always. 09:04.200 --> 09:05.570 So that's lowercase D. 09:06.530 --> 09:09.210 So we have lowercase m slash or lower D. 09:09.250 --> 09:15.170 And then for a year we want four digits so that's uppercase y. 09:15.250 --> 09:25.230 So let's try that percent lower case M slash percent lower case D slash percent upper case Y. 09:25.680 --> 09:26.140 OK. 09:26.260 --> 09:30.700 So Michael when we go and that works. 09:30.700 --> 09:31.200 Next up. 09:31.210 --> 09:37.540 Print out the current day and time using this format a bit different month the month name and then Anglicize 09:37.540 --> 09:41.790 day at and then hours in minutes. 09:42.580 --> 09:48.730 So let's start off with what we know we're going to need to use date format and we need to use now because 09:48.730 --> 09:57.130 we need date and time rather than date which gives us just the date then our format string. 09:57.130 --> 10:03.100 First thing we want is the month name which is right where are you. 10:03.100 --> 10:05.790 This is abbreviated month name. 10:05.840 --> 10:10.220 Here we go month name uppercase m. 10:10.310 --> 10:25.820 So we want uppercase space and then Anglicized version of the day and where where's that. 10:25.840 --> 10:26.680 Here we go. 10:27.070 --> 10:31.560 Day of the month with English suffix that's what I was trying to say. 10:31.660 --> 10:33.570 So it's uppercase D. 10:33.850 --> 10:38.470 OK let's just see if that works to start. 10:38.500 --> 10:39.010 There we go. 10:39.040 --> 10:48.300 April 22nd followed by at no years necessary so at and then hours and minutes. 10:48.410 --> 10:58.450 So again hours we have upper or lower case H if we want and I guess I didn't specify if we wanted 24 10:58.450 --> 11:04.810 hour time or 12 hours so either these will work I'm just going to do lowercase h colon. 11:04.900 --> 11:07.800 And then for a minute it's lower case. 11:08.140 --> 11:11.050 Well as I is going to get this. 11:11.110 --> 11:11.660 There you go. 11:11.740 --> 11:13.270 I'll work on a case I 11:16.480 --> 11:18.070 so lowercase H. 11:18.530 --> 11:21.550 Colon lowercase i There we go. 11:21.760 --> 11:25.740 April 22nd at 4:15 great. 11:25.770 --> 11:33.240 Now moving on to our final problem create tweets table that stores the tweet content a user name and 11:33.240 --> 11:34.430 the time it was created. 11:35.670 --> 11:41.670 So the tweet itself can vary in length so it should be of our are most likely a username could vary 11:41.670 --> 11:42.820 in length as well. 11:42.930 --> 11:46.840 And then the time it is created could be a date time. 11:46.910 --> 11:48.900 I mean it could just be a time if you wanted it to. 11:48.900 --> 11:54.120 But what makes the most sense as we've discussed already is to use time stamp and we want to set the 11:54.120 --> 11:56.890 default to be now. 11:56.990 --> 12:07.110 So let's try top over here and the first thing we want is a create table tweets and then we're going 12:07.110 --> 12:11.520 to have tweet content so we could call that whatever we want. 12:11.580 --> 12:12.960 I'll just call it content. 12:13.350 --> 12:22.620 Now we are char up to 140 characters and we have user name to also be of our char up to 20 characters. 12:22.620 --> 12:28.620 And then finally we have created that or tweeted add or time or whatever you want to call it which will 12:28.620 --> 12:38.370 be a time stamp and then the important part is default now or default current time stamp. 12:38.370 --> 12:40.060 But I like to use now. 12:40.780 --> 12:43.760 So let's try and see if it works. 12:43.770 --> 12:45.590 Make sure you have the right commas in place. 12:46.770 --> 12:47.830 Semi-colon. 12:48.250 --> 12:51.340 And we tried it missing parentheses around. 12:51.350 --> 12:54.210 Now the semi-colon in as well. 12:54.650 --> 12:57.090 OK make some space. 12:57.090 --> 12:59.230 Now we run it and it appears to work. 12:59.310 --> 13:01.290 Let's just make sure by inserting one tweet 13:04.990 --> 13:06.290 so will do content. 13:06.550 --> 13:10.820 User name number we don't need to insert created out manually 13:14.610 --> 13:16.690 and that the content will be. 13:16.710 --> 13:26.610 This is my first tweet and username will be called kept OK. 13:26.730 --> 13:34.980 Let's do a select star from tweets and you can see we have created that and there is a time stamp works 13:34.980 --> 13:36.000 perfectly. 13:36.000 --> 13:38.150 Just double checked by inserting something else. 13:38.370 --> 13:39.510 Let's just change it slightly. 13:39.510 --> 13:42.850 This is my second tweet. 13:45.730 --> 13:51.610 Now if we select star two different time stamps just a couple of seconds later. 13:51.940 --> 13:52.740 OK. 13:53.260 --> 13:55.070 Now the hardest part. 13:55.240 --> 13:56.480 Put yourself on the back. 13:56.500 --> 13:57.190 You made it through.