1 00:00:00,300 --> 00:00:01,020 Oh, right. 2 00:00:01,020 --> 00:00:06,390 It's now your turn to get a little practice creating tables and working with column constraints. 3 00:00:06,390 --> 00:00:11,940 So I'd like you to define a table called employees, and you should have the following columns. 4 00:00:11,940 --> 00:00:16,110 ID last name, first name, middle name, age and current status. 5 00:00:16,260 --> 00:00:18,870 So ID should be a primary key. 6 00:00:18,900 --> 00:00:22,110 It should be an integer and it should automatically increment. 7 00:00:22,470 --> 00:00:28,320 Last name, First name and middle name are all text, but last name and first name are mandatory. 8 00:00:28,320 --> 00:00:31,680 They're required and middle name is not required. 9 00:00:31,680 --> 00:00:33,570 Not everybody has a middle name. 10 00:00:34,050 --> 00:00:40,680 Additionally, all these text columns that you see here, I didn't specify a size, but let's just say 11 00:00:40,860 --> 00:00:44,910 100 characters for last name, first name, middle name and current status. 12 00:00:45,000 --> 00:00:46,470 And then we have age. 13 00:00:46,650 --> 00:00:52,650 Age is also a number we only know integer, so it's an integer for now and it is required. 14 00:00:52,650 --> 00:01:01,290 And then current status would be if the employee is currently employed by us, if they are a contractor, 15 00:01:01,290 --> 00:01:05,880 if they have been let go, if they're on paid leave or something. 16 00:01:05,970 --> 00:01:09,570 But it is text, it is required, it's mandatory. 17 00:01:09,570 --> 00:01:14,130 It cannot be null, but it will have a default value of employed. 18 00:01:15,240 --> 00:01:23,250 So try making this and pause the video please, so that you do it and then I'll be back in 3 to 1 with 19 00:01:23,250 --> 00:01:24,000 a solution. 20 00:01:24,000 --> 00:01:24,660 Here we go. 21 00:01:24,660 --> 00:01:26,010 So we're going to define a table. 22 00:01:26,010 --> 00:01:33,630 I think I'm going to do this all in a file using one of my GIs like SQL workbench. 23 00:01:33,630 --> 00:01:45,450 So I'm going to do a create table employees and employees, and I know that we have ID in here and ID 24 00:01:45,480 --> 00:01:52,530 is going to be an integer, and I know that I want it to auto increment, so I can add that in. 25 00:01:52,530 --> 00:01:59,460 Just remember it's auto underscore increment and then I have to decide how I'm going to make it a primary 26 00:01:59,460 --> 00:01:59,670 key. 27 00:01:59,670 --> 00:02:00,780 Am I going to do this? 28 00:02:00,780 --> 00:02:03,690 Which is fine, and that's probably what I prefer. 29 00:02:03,690 --> 00:02:06,300 Or am I going to do it after the fact? 30 00:02:06,360 --> 00:02:13,740 Like this primary key ID, I think it's better in my opinion, just to do it when I'm creating and defining 31 00:02:13,740 --> 00:02:14,190 the column. 32 00:02:14,190 --> 00:02:20,010 So primary key, remember, there's an underscore for auto increment primary key, though there is not 33 00:02:20,280 --> 00:02:25,110 OC Then we have last name, first name in middle name, all of which are var cars, they're text. 34 00:02:25,110 --> 00:02:28,830 So let's start with last name of our car. 35 00:02:28,830 --> 00:02:30,870 And I said 100 I believe. 36 00:02:30,870 --> 00:02:34,110 But all that matters is that you pick some value that's consistent. 37 00:02:34,110 --> 00:02:35,970 I mean, I guess that's not all that matters. 38 00:02:35,970 --> 00:02:42,780 We don't want you to pick five characters, but we'll do last name, first name, middle name, just 39 00:02:42,780 --> 00:02:43,230 like that. 40 00:02:43,230 --> 00:02:44,460 Put my commas in there. 41 00:02:44,460 --> 00:02:48,030 But then we know that last name and first name are required. 42 00:02:48,030 --> 00:02:50,550 So we'll put in not no. 43 00:02:50,760 --> 00:02:52,920 Same thing here, not null. 44 00:02:53,670 --> 00:02:56,970 And then we have age, which is a required number. 45 00:02:56,970 --> 00:03:01,320 So age is an int and it's also not null. 46 00:03:01,680 --> 00:03:04,020 And then finally we have current status. 47 00:03:04,050 --> 00:03:05,280 So current. 48 00:03:07,510 --> 00:03:14,710 Status, which is a var car 100 and it cannot be null. 49 00:03:14,710 --> 00:03:21,880 It's mandatory, so not null and we have a default value of employed. 50 00:03:21,880 --> 00:03:22,540 Right. 51 00:03:22,780 --> 00:03:24,100 Make sure employed. 52 00:03:24,100 --> 00:03:24,580 Yep. 53 00:03:25,150 --> 00:03:25,900 Okay. 54 00:03:25,900 --> 00:03:28,360 So I think that should be it. 55 00:03:28,870 --> 00:03:31,030 We can always run this entire script. 56 00:03:31,030 --> 00:03:35,080 Or I guess you could just copy and paste it over in your terminal however you want to do this. 57 00:03:35,620 --> 00:03:37,450 I'm doing it on the pet shop database. 58 00:03:37,450 --> 00:03:41,800 Still, it doesn't matter because I'm going to drop the table at the end of this video, but let's make 59 00:03:41,800 --> 00:03:43,060 sure that it works. 60 00:03:43,060 --> 00:03:45,280 And also, here's my solution in this slide. 61 00:03:45,280 --> 00:03:50,680 I guess we should compare and make sure I didn't miss anything when typing this out. 62 00:03:50,920 --> 00:03:51,670 A semicolon. 63 00:03:51,670 --> 00:03:52,690 We should put that in there. 64 00:03:52,690 --> 00:03:58,870 But otherwise it's the same right current status of our car 100 not all default employed primary key 65 00:03:58,870 --> 00:04:02,290 auto increment, all that stuff is there, so we should be good. 66 00:04:02,560 --> 00:04:04,810 Let's just run this script. 67 00:04:04,810 --> 00:04:10,990 So in in workbench, in SQL workbench or my SQL workbench, I can run the entire script by clicking 68 00:04:10,990 --> 00:04:11,770 the lightning bolt. 69 00:04:11,800 --> 00:04:13,480 This entire file will run. 70 00:04:13,480 --> 00:04:22,780 Or if I have my cursor in some part of a single query or a single sort of unit of SQL, write one command 71 00:04:22,780 --> 00:04:23,980 what we call a query. 72 00:04:24,370 --> 00:04:30,340 I can use this other version here right next to the lightning bolt that will only run wherever my cursor 73 00:04:30,340 --> 00:04:30,760 is. 74 00:04:31,120 --> 00:04:33,880 So it doesn't matter because I have all this commented out anyway. 75 00:04:33,880 --> 00:04:36,340 But if I do want to do that, I'll run it there. 76 00:04:36,340 --> 00:04:37,900 We get a green checkmark. 77 00:04:37,900 --> 00:04:39,910 Nothing went wrong as far as we can tell. 78 00:04:40,300 --> 00:04:43,120 I could always just refresh over here. 79 00:04:43,120 --> 00:04:49,060 I should have an employees table now and if I look at it, I can see my values down here. 80 00:04:49,060 --> 00:04:55,150 But I'm going to head to my terminal and I'm going to do a describe employees. 81 00:04:55,990 --> 00:04:59,040 And I'm too zoomed in for the size of this table. 82 00:04:59,050 --> 00:04:59,800 There we go. 83 00:04:59,830 --> 00:05:05,650 I zoomed out a bit, and now we can see all the different columns there type's just int and var cars 84 00:05:05,650 --> 00:05:07,900 and then if they can be null or not. 85 00:05:07,900 --> 00:05:12,310 And what our primary key is and what the default values are, if there are any. 86 00:05:12,670 --> 00:05:15,130 And then lastly, auto increment. 87 00:05:15,880 --> 00:05:18,610 So let's try and just insert an employee. 88 00:05:18,610 --> 00:05:25,600 This is not part of the requirements at all for this exercise, but let's insert a new employee. 89 00:05:25,600 --> 00:05:31,930 So insert into employees and let's say that I'm not going to insert the ID, right? 90 00:05:31,930 --> 00:05:33,850 We have auto increment, so no need to do that. 91 00:05:33,850 --> 00:05:40,530 But I'll do first name and last name and age because those are the only ones required, right? 92 00:05:40,570 --> 00:05:46,450 First, last and age, current status has a default, so I'll rely on that default value. 93 00:05:47,290 --> 00:05:49,030 So then I'll do values here. 94 00:05:49,270 --> 00:05:57,940 And his name is Chicken Man, and that's his last name obviously, and his age is 87. 95 00:05:57,940 --> 00:05:59,110 Thomas Chicken Man. 96 00:05:59,920 --> 00:06:03,100 All right, let's select star from employees and make sure. 97 00:06:04,360 --> 00:06:07,960 We see current status has a default value of employed. 98 00:06:08,470 --> 00:06:11,260 Middle name is null because that's totally fine. 99 00:06:11,260 --> 00:06:13,990 That means this person doesn't have a middle name again. 100 00:06:13,990 --> 00:06:17,110 No, it's not always bad or it's not necessarily bad. 101 00:06:17,110 --> 00:06:20,050 It's just a concept of no value. 102 00:06:20,050 --> 00:06:23,070 And many people will have no middle name, right? 103 00:06:23,080 --> 00:06:24,490 So we don't want to make it required. 104 00:06:24,490 --> 00:06:25,840 We don't want to have a default. 105 00:06:26,080 --> 00:06:29,050 I guess we could put a default in there, but why bother? 106 00:06:29,050 --> 00:06:35,890 If NULL serves the purpose we need, let's just make sure that if I try and insert somebody, let's 107 00:06:35,890 --> 00:06:39,370 say missing an age, that we're going to have a problem. 108 00:06:39,370 --> 00:06:44,560 So let's get rid of age there and then it's duplicated, that's fine. 109 00:06:44,560 --> 00:06:49,780 We'll have to Thomas Chicken Mans and but we get an error anyway, saying age doesn't have a default 110 00:06:49,780 --> 00:06:51,520 value, you can't insert that. 111 00:06:51,520 --> 00:06:54,910 So it's working or auto increment is working. 112 00:06:54,910 --> 00:07:02,590 If I just insert another identical employee a couple of times, we should see if I select star from 113 00:07:02,590 --> 00:07:06,550 employees that we have ID of one, two and then three. 114 00:07:07,210 --> 00:07:12,220 So that's our little exercise for creating a table with all those different constraints, those little 115 00:07:12,220 --> 00:07:17,260 add ons we saw auto increment, primary key, not null and default. 116 00:07:17,260 --> 00:07:18,910 We use those all the time.