Understanding the ALTER Command in SQL: A Comprehensive Guide

Hello friends, welcome to my channel This is Varun Singla In today's video we are going to discuss

About ALTER command As we had discussed in the last video In DDL the first command about create

In which we had done create table with implementation In this video we are going to discuss Second command

Or second DDL command ALTER After ALTER we will also do its implementation

In Oracle First of all what can ALTER do Means what is the use of ALTER command

So the major use of ALTER command are this What is to be done in ALTER command If we want to change anything in schema

Like I am focusing again and again on this schema word I am concentrating because Schema means structure

Structure means where I want to put data We call it as structure And what is structure

What is structure in relational database A table, which we also call as relation So in structure data we always create table only

To store the data Let's say we have a student table This is its structure

Structure means it has 2 columns Id and Name Id lets say is an integer

And name is varchar This is already given It is created

Because if you want to make new thing Then to make new things we already have create command So when you are making new thing with create command

Then according to your requirement Put the values But the meaning of alter is

The thing that is already made If I want to make some change in that Then over there I use

What? ALTER command So suppose that over here student table Is already made

We have already take Id of int type Name is already made of varchar data type So now what we can do in this

Let's say our requirement is To add a new column Let's say over here

I want to add a new column City Or Address

Take anything, let's say I want to add Address So to add address column What we will have to do

We have to make change in this structure So whenever you want to make change in the structure What do we use?

ALTER command So generally what we do, Alter table tablename, lets say table name is student

Add, over here which keyword do we use Add Add column name, let's say what is the column name?

We have Address And the address If you will run this then this will give an error

Reason, because How does the compiler knows the address Our computer are not so much artificially intelligent

Still there is artificial intelligence in them That they cannot say by own that address will be varchar Over here we have to mention

That varchar Size also lets say 30 size You can give

Although we will see these things in implementation But you will get an overview That what all we can do with alter command

So add column or columns Which means even if you want to add more then 2 columns Then through one command only

Through alter command You can do it simply By adding bracket over here

You can write multiple columns Then remove a column Let's say that in future we have requirement

That I don't want this particular column Then what you will do Will you delete full table

No Means, let's say You have a big database

In which there are 30-40 columns And there are lakhs of rows There are lakhs of tuples

So even if by mistake you deleted the table Then this is a blunder mistake Because, in any organization

For any company The most important asset What is it?

Data Even more than employees The most important asses for all the companies

In today's time What is it? data So the data

It is not so that anyone will delete Any one will add No

For that proper privileges are required So if we are working as an administrator Then if it comes to us that a particular column

We have to remove Let's say Over here the column is address

The one which we just added Or take any other column Let's say age

Tomorrow if get that remove age So to remove age What we will do

And to remove column also We use alter command So to add, remove, modify the datatype

Let's say today I feel that That my Id is sufficient in int data type But after that my number of employees

Increased a lot So instead of number what do I want That it should be combination of character and number

In this way So to do this You will have to make varchar

So varchar means At present you have made this as int So varchar means

You will have to modify what Datatype And when you have to modify datatype

Then we again use alter command Even the length of datatype Let's say at present

You have given name as size 20 Varchar 20 But tomorrow if you feel that

The name of employees in the company are very big Then again you can increase that length By using the

Alter command Then add and remove the constraint Means primary key

Foreign key Not null Check default

If you want to add all these constraints After the table is created Then we do it with what?

With alter command Remove also And rename column

Column name, let's say If you think Id is fine Tomorrow you think that

Id is not fine It should be roll number over here Or it should be registration number

So if you want to change such type of names Or even full student table If you want to change the name of the table

Let's say right now name is student Tomorrow you want to make it student detail To do this type of changes

To do renaming again Alter command is used So these are the multiple ways

Through which you can Thorough Alter command you can perform all these task So come we will see its implementation

Come we will start with implementation First of all I want to tell you Over here I have written create table command

And we are creating a table over here Employee And I have added 2 columns Id and Name in it

So first of all we will run this And over here we have created a table Employee

Because when we had seed that Alter command We use alter command

After creating the table Means that table is already there In that if we want to do any changes

Then what is the first change that we saw Alter table tablename And first of all suppose we want to add

A new column Let's say alter table tablename Employee

Add, we will add a new column With name of dress So Address, along with that

It is necessary for you to give the datatype So over here we have mentioned datatype as varchar And we will select this and run

So see the table is altered Means already in this table a column address has came So first of all we will check

Describe If we do employee then over here With desc command you get to see the whole schema of table

So over here you see the address It is already created in that Now Id name was already there

Now we have also inserted address in this So first of all what we have seen that If we want to insert a new column in the table

Then we can insert it in this way Now second thing what we can do We can drop in this Suppose we want to drop column

We will drop address only Which we have just inserted Se what we have to do for that

Alter table tablename Drop column With drop you have to remember

You definitely have to write column word And along with that Column name

Like if want to drop the address Now you need not mention datatype Because compiler already knows it that

It's datatype will be varchar Or whatever we have given in this Because if we are dropping, tha at that time

You need not mention the datatype So see what is written Table dropped

This is actually over here flaw of the software Table is not dropped If you do describe table name

And you run then see With Id and name, employee table exists But the address that was here earlier

That address has now been dropped We have dropped one column over here So you can insert multiple columns

You can also drop multiple columns What is the third thing that we can do Alter table tablename

Let's say employee And we want to modify Datatype

Let's say Id, what is the datatype of Id, number So I want to change it To let's say to varchar

So when we change it to varchar Then varchar and it is necessary to specify the length We gave the length and we are running this

So se what is written Table altered So we will check it

We again selected describe We are running, then see Id and Name is coming

And now what is the Id Varchar2 Oracle by default works with the name of varchar2

The by default datatype in this is varchar2 So according to oracle Even if you are giving simple varchar

Then also it will consider this as varchar2 only So first it was number integer Now we have changed it to varchar

Even its length Like what length we have given 10

So if tomorrow you want to change this You want to make any change in schema Let's say you want to make its length 30

Then you just have to write it again in this command Modify Column name

Varchar, whatever is its datatype And 30 So see now we will again select this and run

So see what has came Over here the length mentioned is 30 so in this way

We can add, drop, modify column datatype And along with that the length Next what we can do in this

Rename Alter table tablename Employee

And what we have done over here Rename what? column Let's say over here we are renaming

Column word is necessary to write over here also So rename column, let's say we are changing Id Id to let's say we are converting it into roll number

Whatever name you want to change you can change So Id is the old name And roll number is the new name

so select this And we will run this also So see what is written

Table altered So along with that we will check it Describe table name

And see we are running this then see First Id was coming Now what is coming over here?

Roll number is coming So in this way we can add, drop Modify and along with that you can rename

So alter table Tablename Employee We will also rename it second time Rename to let's say

Emp Any small name We will give over here

So as we run this So it is showing that name is already by used by existing What does that mean

That table with this name already exists So give it some new name Let's say Employee11 Anything over here we can give

So see table is Altered What have we done?

Alter table tablename rename to emp, it means First what we had done in this command We had changed column name

Now we have changed table name only Employee rename to new name Which means new name of table

So now see what we have done Describe desc employee So as we run it

What is it showing? Object to be described Could not be found

Which means, nothing exists with the name employee And obviously When we have changed the name

Then see over here The new name that we had kept, what was it? Emp11

So I will give this emp11 over here And now I will run See what comes over here

Roll number That varchar2 30 which we done chages in this So all that changes as it is exists

But table name What we have changed? Emp11

So this major, what we can do is With alter Add, drop, modify, rename column

Or rename table Other then that one more thing that we can do That is constraints

Alter table tablename Let's say if I want to give primary key Then add primary key

To Id, but Id does not exist over here So let's say roll number I want to make primary key

So roll number You have to carefully add column name So as we have added column name over here

Add primary key And as we are running it Then see this is showing

Table or view does not exist Ok over here employee name We had converted to emp11

So this actually exists with this name over here So see table is altered Now over here primary key is added

Other then primary key We can add foreign key, not null all these constraints So see this nullable over here

This is ticked which means Null value can go into name But null values cannot come in roll number

Because we have made this as primary key But this primary key, foreign key, not null Default, check all these constraints

When we will discuss in constraints Over there we will see them in detail Now in alter table this is the main thing that you have remember

That in the table We can add, drop, modify, rename All these major work

That we can do Through alter command So this is the full concept of alter

Thank you