Locked lesson.
About this lesson
With ALTER TABLE, we can add, remove or change columns, data types and just about anything else
Exercise files
Download this lesson’s related exercise files.
ALTER TABLE.docx59.1 KB ALTER TABLE - Solution.docx
59.6 KB
Quick reference
ALTER TABLE
The ALTER TABLE Command allows us to change the structure of the table itself.
When to use
Use this whenever you want to Add new columns to your table, or change current columns, or remove columns.
Instructions
To add a column named "Zip Code" with the data type of integer:
ALTER TABLE [dbo].[Current_Customers]
ADD "Zip Code" int
To change the data type of a column:
ALTER TABLE [dbo].[Current_Customers]
ALTER COLUMN "First Name" varchar(50)
To remove a column (drop the column):
ALTER TABLE [dbo].[Current_Customers]
DROP COLUMN 'Zip Code'
Hints & tips
- Alter Table allows us to Add or change column names and data types
- ALTER COLUMN lets us change the data type of a column
- ADD lets us add a new column
- DROP COLUMN deletes a column
- 00:05 We've learned how to insert, update, and delete stuff from our table.
- 00:08 Now, we're gonna learn how to change the table itself.
- 00:11 So, if we want to add columns, if we want to change data types, if we want to do all
- 00:15 kinds of stuff like that, we can do that through your SQL as well.
- 00:18 And we're gonna use the ALTER TABLE command to do that.
- 00:20 So get rid of that, it's pretty simple,
- 00:26 just alter table and
- 00:28 then name your table, dbo_customers.
- 00:36 And let's add a column.
- 00:38 So, what do we want to add?
- 00:39 Let's add, how about zip code?
- 00:42 Zip Code.
- 00:43 And now you have to give it the data type as well.
- 00:45 So our zip code, we wanna make it an integer.
- 00:48 So that's all there is to it.
- 00:50 Execute. And you'll see it's NULL, NULL,
- 00:52 NULL, NULL, NULL, cuz we haven't added any data to any of records of this column.
- 00:57 But now we have zip code.
- 00:58 And if we come over here to our Current Customers and right-click and
- 01:02 look at Design, we see zip code is in fact an integer and all things are good.
- 01:07 So, very, very cool.
- 01:08 Now maybe we don't want it to be an integer,
- 01:12 maybe we want it to be a different kind of data type.
- 01:15 We can do that with the alter table, alter column command.
- 01:18 So to do that ALTER TABLE.
- 01:20 This first line stays the same cuz we're still working on that table.
- 01:23 And we just change the second line to ALTER COLUMN, and
- 01:28 then the column name, Zip Code.
- 01:32 And the data type we wanna change it to.
- 01:34 So let's change it to varchar(50).
- 01:41 Boom, it changes.
- 01:42 Now, let's go over here and right-click on our Table, and click on Design.
- 01:48 And sure enough, Zip Code has change to varchar[50].
- 01:50 So easy to do this programmatically ALTER COLUMN.
- 01:54 Now, maybe we changed our mind, maybe we don't want this at all.
- 01:56 Maybe we don't want Zip Code.
- 01:58 So we can do that with the alter table drop column command,
- 02:02 and that's just drop column.
- 02:05 A lot of times when you need a listing from the database they call it
- 02:08 dropping it from the database or drropping it from the table.
- 02:12 So, just a little tribute to keep in mind.
- 02:14 So, now all we have to do is give it the column name, which is zip code.
- 02:19 We don't have to specify the data type this time cuz we're
- 02:22 just deleting everything.
- 02:23 So if we execute this, boom, now that column disappears.
- 02:27 And we can confirm that.
- 02:28 We can go back to our design and the Zip Code is gone.
- 02:32 So ALTER TABLE.
- 02:33 Very useful, you can add columns, you can remove columns, you can alter them.
- 02:38 All with that simple command.
- 02:39 And that's all for this video.
- 02:41 In the next video, we're gonna look at working with multiple tables,
- 02:44 specifically we're gonna look at aliases.
Lesson notes are only available for subscribers.