1
00:00:00,300 --> 00:00:06,360
So next up, we're going to learn about the alter table statement, which helps us change tables.

2
00:00:06,390 --> 00:00:09,250
The thing is, there's a lot of ways to change a table.

3
00:00:09,270 --> 00:00:11,490
We can add columns to a table.

4
00:00:11,520 --> 00:00:13,260
We can remove columns from a table.

5
00:00:13,260 --> 00:00:14,580
We can rename columns.

6
00:00:14,580 --> 00:00:16,800
We can change the data type of a column.

7
00:00:16,800 --> 00:00:19,950
We can add constraints, remove constraints.

8
00:00:20,430 --> 00:00:24,820
If we go to the docs and you look at the altar table statement syntax.

9
00:00:24,840 --> 00:00:28,200
This shows you all of the options for how you run it.

10
00:00:28,650 --> 00:00:34,100
I think this is the most complicated statement in terms of the number of options.

11
00:00:34,110 --> 00:00:35,930
It's actually very simple to use.

12
00:00:35,940 --> 00:00:37,950
It just it's like a Swiss army knife.

13
00:00:37,980 --> 00:00:39,120
It does everything.

14
00:00:39,120 --> 00:00:47,040
So instead of there being a single command called like rename column and then drop column and change

15
00:00:47,040 --> 00:00:54,360
column type and that sort of thing, they all live within alter table, which makes sense.

16
00:00:54,360 --> 00:00:58,440
It's fine, but it does mean that it's easy to forget the syntax.

17
00:00:58,440 --> 00:01:05,310
Sometimes you can come here and find if you're trying to add a column or add a constraint or drop a

18
00:01:05,310 --> 00:01:09,510
column, drop a constraint, modify a column.

19
00:01:09,660 --> 00:01:14,730
All this stuff you can do from within the alter table syntax.

20
00:01:14,730 --> 00:01:19,320
And the first thing I'll show is how to add a new column to an existing table.

21
00:01:19,800 --> 00:01:25,890
So we use alter table, and then the table we're trying to alter and then add column.

22
00:01:25,890 --> 00:01:28,860
Although technically the column portion is optional.

23
00:01:28,860 --> 00:01:35,730
If you look at the syntax, see how it's in brackets right there, ADD is required, column name is

24
00:01:35,730 --> 00:01:42,060
required, but column inside of those brackets means put it in there if you want optional.

25
00:01:42,330 --> 00:01:46,050
I like to put it there because it's a little clearer, but it doesn't matter.

26
00:01:46,050 --> 00:01:47,640
So add some column.

27
00:01:47,640 --> 00:01:50,960
What is the columns name going to be and what is its definition?

28
00:01:50,970 --> 00:01:55,590
The data type, not no constraints, whatever you want afterwards.

29
00:01:55,800 --> 00:02:02,190
So in this case, we're adding a column to companies called City and it's a var char 25.

30
00:02:02,610 --> 00:02:04,770
Let's try an example of this.

31
00:02:05,580 --> 00:02:11,750
We have our companies table at the moment and companies has a name and an address.

32
00:02:11,760 --> 00:02:17,430
What if I wanted to add a phone number column so the tables are already created, right?

33
00:02:17,430 --> 00:02:19,560
If we select star from companies.

34
00:02:21,470 --> 00:02:23,690
From companies.

35
00:02:24,490 --> 00:02:26,090
There's two rows.

36
00:02:26,110 --> 00:02:27,790
Neither of them have a phone number.

37
00:02:27,790 --> 00:02:29,450
So that's something we'll have to consider.

38
00:02:29,470 --> 00:02:33,370
What happens when you add a column to a table that already has data?

39
00:02:33,610 --> 00:02:37,540
What do you do for those rows that don't have a value?

40
00:02:37,600 --> 00:02:39,220
Do you give them a default value?

41
00:02:39,250 --> 00:02:41,080
Do you update them after the fact?

42
00:02:41,110 --> 00:02:42,490
Do you make them null?

43
00:02:42,640 --> 00:02:47,660
So what we'll start with is just a simple alter table.

44
00:02:47,680 --> 00:02:48,610
Companies.

45
00:02:49,360 --> 00:02:52,270
I want to add column.

46
00:02:52,690 --> 00:02:54,430
Remember, column is optional there.

47
00:02:54,700 --> 00:03:00,160
I'm going to call it, phone it to var char, let's say of 15.

48
00:03:00,640 --> 00:03:02,480
So I'm adding it to this table.

49
00:03:02,480 --> 00:03:03,910
It doesn't have a phone column.

50
00:03:04,000 --> 00:03:05,740
And let's say that.

51
00:03:05,740 --> 00:03:10,000
Well, let's just start like this Simple phone is a var char 15.

52
00:03:10,000 --> 00:03:11,380
That's all that we say.

53
00:03:11,740 --> 00:03:13,600
OC Let's try running it.

54
00:03:14,590 --> 00:03:15,550
What do we see?

55
00:03:15,640 --> 00:03:16,760
It looks like it worked.

56
00:03:16,780 --> 00:03:19,840
Let's select star from companies.

57
00:03:20,690 --> 00:03:28,070
And we now see that there is a new column called Phone and the default value for all of the columns

58
00:03:28,070 --> 00:03:34,220
that were already in the or all of the rows that were already in the table is to have phone set to null.

59
00:03:34,670 --> 00:03:38,620
And that's because I never said they couldn't be null.

60
00:03:38,630 --> 00:03:40,580
So that's the logical choice, right?

61
00:03:40,580 --> 00:03:43,520
That means the lack of a value, no phone number.

62
00:03:43,550 --> 00:03:46,520
And those rows did not have a phone number.

63
00:03:46,520 --> 00:03:49,610
They were created at a time there was no phone column.

64
00:03:49,610 --> 00:03:51,200
And maybe that's what I want.

65
00:03:51,200 --> 00:03:54,500
I mean, it really depends on what I'm trying to do, what the situation is.

66
00:03:54,980 --> 00:03:58,070
Do these businesses not have a phone number at all?

67
00:03:58,150 --> 00:04:00,440
Okay, then maybe null makes sense.

68
00:04:00,440 --> 00:04:03,920
Do I need to go and get their phone numbers and update them manually?

69
00:04:04,100 --> 00:04:10,550
Or what I could also do is when I create a column, I can say this cannot be null.

70
00:04:10,550 --> 00:04:16,190
And when I do that, if I say this new column cannot be null, there will be a default value added in

71
00:04:16,190 --> 00:04:17,660
for string columns.

72
00:04:17,660 --> 00:04:19,190
It will be an empty string.

73
00:04:19,190 --> 00:04:22,340
For number columns, it will be zero.

74
00:04:22,340 --> 00:04:23,660
So let me show that.

75
00:04:23,660 --> 00:04:25,700
Let's do an alter table companies.

76
00:04:25,700 --> 00:04:31,700
Once again, let's add a column here, and this time the column will be called Employee Count.

77
00:04:32,810 --> 00:04:37,670
And what I can do is say employee count cannot be null.

78
00:04:38,090 --> 00:04:41,930
And when I run this here, add column, employee count, not null.

79
00:04:41,930 --> 00:04:43,460
What is it complaining about to me?

80
00:04:44,570 --> 00:04:45,140
Oh, duh.

81
00:04:45,170 --> 00:04:46,550
I have to say, it's an integer.

82
00:04:46,670 --> 00:04:48,020
I don't know how I forgot that.

83
00:04:49,340 --> 00:04:49,880
Thank you.

84
00:04:49,910 --> 00:04:50,870
SQL workbench.

85
00:04:50,900 --> 00:04:52,060
Let's try running this.

86
00:04:52,070 --> 00:04:57,770
When I run it, we'll see that none of these rows, the two existing rows, they didn't have employee

87
00:04:57,770 --> 00:05:02,990
count, but when I select staff from companies, they now have zero as their initial value.

88
00:05:03,140 --> 00:05:07,070
But I could also control that if I set up a default.

89
00:05:07,070 --> 00:05:14,450
So if I instead had said default is one employee, which probably makes more sense because, you know,

90
00:05:15,050 --> 00:05:20,630
is there a really any company that has zero employees versus one is probably the real minimum.

91
00:05:20,630 --> 00:05:26,270
If I had done this, which I can't rerun this right now because there already is a column called Employee

92
00:05:26,270 --> 00:05:31,190
count, but if I had done that, we would have seen the default value would be one for all of those

93
00:05:31,190 --> 00:05:35,030
rows that did not yet have an employee count column.

94
00:05:35,540 --> 00:05:38,690
So next, we're going to learn how to drop columns.