SQL Tutorial - 55: The ALTER TABLE Command Video Lecture | Microsoft SQL for Beginners - Database Management

hello guys welcome to my channel this is
the 55th tutorial in this course and in
this tutorial we are going to talk about
the order table command and the alter
table command is a data definition
language or DDL command and what that
means is that it works with the
structure of tables instead of working
with the data inside the tables right
and it can be used to add or delete or
modify columns or constraints to your
tables and in this tutorial the features
of all the table that we're going to
check out are you know how we can use
the order table command to add a field
in a table how we can use it to remove a
field from a table and then how we can
use it to modify a field from a table so
let's first see how we can add a field
right so I have my MySQL workbench here
and I'm currently in the you know editor
portion so I can type in alter table and
then you have to follow that up those
two keywords with the name of the table
that you wish to work with so I'll work
with the employee table and what I'll do
is I'll add a city field to the employee
table right so it wasn't earlier there
and I want to add it now so you know
alter table employee and then put a
space and then type in add you know so
that's another keyword and then you type
in the name of the field that you wish
to add so you know I want to add city
and I want this to be a character field
and let's say I want it to have a
cutroni character so I'll type in 20
within parentheses for a semicolon at
the end and when I execute the query the
output section I see the message that
the statement the command words executed
successfully and now I can you know see
what has happened to my tables so to do
that I'll type in select star from
employee and when I execute this I see
that another field city has now been
added to the table but you know there's
no data for this field you know in any
of the rows in the table so you know
even after adding the column you still
have to insert data using the you know
update command you know modifying the
values that are there in each row and
you know that can be a pretty tedious
task but then at least order table gives
you the option of modifying the
structure of a table
I'd you know some later point in time so
let's say you create a table with the
some columns or with some constraints
you know in this table we have the ID
field as a primary you know let's say
you want to change you know that and you
want to make the age field the primary
key or something like that you know if
you want to do such operations by
changing the structure of the table then
you know you have a fantastic tool in
the form of the order table command now
let's say you want to remove a field
from this table so let's say we want to
drop the city field we don't like it so
we're going to drop it and the way you
do that is by typing in alter table
again and then again you have to type in
the name of your tables so type an
employee and then you type in drop so
instead of add you will type in drop and
this time you won't have to mention the
datatype of the column that you wish to
drop you just have to mention the name
right and that's convenient obvious you
don't have to mention the datatype here
so when we execute this you know we
don't see anything we don't get a
results or anything but the output
section we do get the message that the
command was executed successfully and
when you run the Select star query you
can see that the field city is no longer
there in the results side right so you
know we've seen two features and the
last one that we're going to check out
is how we can use the order table
command to you know
modify you know the fields in a table so
what we're going to do is let me just
describe the table employee to you guys
so we can see what kind of modifications
we can make so we see here that the name
field for instance is a fear of the
Warka type and you know the not null
constant has not been applied to it you
know only that constraint is applied
only to the ID field because it's a
primary key so it cannot take null
values but all the other fields can take
null values right so you see four yeses
and one no now let's say you want to add
a not null constraint to it and at the
same time you want to change the vodka
type to car type right the character
type you want to change that so the way
you're going to do that is by typing
alter table
and then employee and then you type and
modify this time so we've seen the add
keyword the drop keyword and now we
check not to modify keyword and then you
type in the name of the field that you
wish to modify so that in this case will
be named and then you type in the data
type of the field so I wanted to be a
character field of you know let's say 30
characters and I also wanted to have the
not null constraint so I'll type in all
of that put a semicolon at the end and
when I execute this you know again in
the output section I get the message
that the command was executed
successfully and when I you know run the
describe employee command now I see that
the name field firstly has an ordinal
constraint on it so it cannot take null
values and its type has been changed to
care and the limit is of course 30 right
so that's it about the older table
command and it's a very useful command
and you can just practice it by creating
dummy tables or something and you know
just work with the the command you'll
understand all the three keywords and
how you're supposed to use them and I
hope you guys had fun watching this
tutorial
I assume the next one and you may
subscribe to my channel in case you
haven't already and I'll see you soon