Alter Table SQL

Alter Table SQL

In this part of sql tutorial  , we will see the use of  alter table command of sql. After going through this part of sql tutorial , readers will be able to add columns to existing tables as well as they will be able to alter the existing columns of the tables. Sometimes after the creation of a table, need of addition of a new column exists, in that case Alter table command is helpful.

Before we discuss Alter Table command in SQL  , lets us see why to add columns in an existing table. Sometimes while deciding columns of a table, we forget to include a particular field, in that case we can add new columns to the existing tables. 

In programming , sometimes need of a new columns in existing tables arises at a later stage.

In SQL, we can add a new column in an existing table as well as we can change the specifications of the existing columns.

For video Tutorial on Alter Table command click here.

Suppose you have a table named employee in database as given below-

Eocde      Ename   Ecity

Ecode	Ename	Ecity

After creating this table, need of a new column named zip code arises. You can add this new column using Alter table command of SQL.

A table in SQL can be altered by using alter table command. By using  alter table command , we can add  new columns to an existing table. We can also change specification of the existing columns of a table using Alter Table command.

Using alter table command, one can add new columns as well as can change the specifications of the existing columns. You can change the data types of the existing columns; one can change the field width of the existing columns.

Add Column SQL

Alter Table Command

A table in SQL can be altered by using alter table command. By using  alter table command , we can add new columns to an existing table. We can also change specification of the existing columns of a table using Alter Table command.

Whenever you will try to add column in sql, you have to take care of data.

Suppose you have a table named employee as given below –

Ecode	Ename	Ecity
E001	JOE	FLORDA
E002	JIM	SYDNEY

If you add a new column named zip code in the above table, structure of the above table will be like this-

Ecode	Ename	Ecity	Zip code
E001	JOE	FLORDA
E002	JIM	SYDNEY

Here you can see Zip code column is having no values, and then what will the values of Zip code in case of these two entered records.

In that case you have to use update command to add values to these two already entered records.

 How to Add Column in sql table using Alter Table command

To add new columns in an existing table, we can use add option of alter table command.

The general syntax of alter table command with add option is given below:

Alter table <table-name> add( new column specification);

Suppose we want to add zipcode column in employee table.

SQL>Alter table employee add(zipcode number(10));

This will add a new column zipcode to employee table.

Modifying existing columns of a table

To modify specification of existing columns of a table ,we can use modify option of  alter table command.

The general syntax for modifying existing columns is given below

Alter table <table-name> modify( column new_specification);

Suppose we wants to change filed width of ecode column from 4 to 6.

SQL>Alter Table employee modify( ecode char(6));

Note: Be careful while changing column specifications, this is because when you to change the data type of an existing column, if the table is having data that time, there may be some issues of data type mismatch. Also in case of column width alteration, if you try to reduce the width of an existing column , if that column is having values, in that case truncation of data may happen.

ORACLE SQL Update COMMAND

ORACLE SQL Update COMMAND

How to Update Rows in Oracle SQL 

About the author of this Article
Author of this article is having more than 20 years experience of using Databases and SQL.He had also authored a Book on Oracle.

SQL Update Table Command 

SQL Update Table Command is used to update existing records or rows of a database table. SQL Update Table Command is applied when some changes happens in records. Suppose in case of an employee if he changes his city ,then his record in database must be updated. Another example could be for annual increments in salaries of all the employees, we need update command.

The general syntax of SQL Update Table Command is given below

 Update table name set column name=new value;

SQL Update Table Query

SQL Update Statement Example

Lets have a look on sql update statement example. 

Suppose you want to change present city of employee named Adam to Chandigarh.

SQL>Update employee set ecity=’Chandigarh’ where ename=’Adam’;

Here employee is name of the table to be updated.

SQL Update Where clause

In SQL Update Where Clause, WHERE is optional. If you do not supply WHERE keyword in SQL Update Where Clause then , the update statement will update all the rows of the table.

SQL Update Where clause

Suppose you supply following update statement.

SQL>Update employee set ecity='Nagpur';

In this case cities of all the employees will be updated to Nagpur.

Few more examples to illustrate the use of SQL Update Where Clause.

Change city of employees whose name starts from letter N to NAGPUR
SQL> update employee set ecity='NAGPUR' where ename like 'N%';
Change city of employees whose name doses not starts from letter N

SQL> update employee set ecity='NAGPUR' where ename not like 'N%';

Increment salaries of all the employees by Rs.1000

SQL> update employee set salary=salary+1000;

Increment salaries of all the employees by 10%

SQL> update employee set salary=salary+(Salary*10/100);

Increment salaries of all those employees by 10% whose city is Nagpur

SQL> update employee set salary=salary+(Salary*10/100) where ecity='Nagpur';

How to Update Multiple Records in SQL

To update  multiple records in SQL, if we omit where condition in update statement, then all the rows will be affected by update statement.

How to Update Multiple Records in SQL

Example to show how to update multiple records in SQL : 

update employee set ecity='New Delhi';

in this case city of all the employees will be updated to New Delhi

To update multiple rows, we use where condition for a group of rows too.

Example to show how to update multiple records in SQL :

update employee set ecity='New Delhi' where salary>50000;

In this case city of those employees whose salary is greater than 50000 will be updated to New Delhi.

SQL Update Table Query

You can also use arithmetic operators in update, like increment the salary of all the employees by 100.

The update statement will be like this:

Update employee set salary=salary+100;

SQL Update Multiple Rows at Once

You can update multiple rows at once in SQL. We already discussed about this, to update multiple rows at once, there are many ways.

Suppose you wants to update all the rows of a table using a single update statement.

SQL Update Multiple Rows at Once

In that case don't provide where condition in update statement.

Suppose you supply following update statement at sql prompt

SQL> Update employee set salary=50000;

The above statement will make salaries of all the employees to 50000.

So this was one of the way to update multiple rows at once.

Multiple rows can also be updated at once if the where condition selects multiple rows.

lets us have one MORE example to have an idea of this.

sql> Update employee set salary=50000 where ecity='NAGPUR';

In this case salaried of all those employees will be set to 50000 whose living city is NAGPUR. There is always a possibility that there could be multiple records having living city as NAGPUR.

Similarly you can update any record or any number of records.

We hope all your doubts are cleared about SQL Update Statement after reading this post. We will appreciate social media share of this post by you. This will encourage us to arrange more stuff for you like this in future too.

Datatypes in Sql Oracle

Data Types in ORACLE SQL

There are following datatypes in SQL Oracle.

Numeric data types SQL

• NUMBER datatype in sql

The Number data type is useful when you wants to assign a numeric value to a variable. 

Character data types

• CHAR
• NCHAR
• NVARCHAR2
• CLOB
• NCLOB
• VARCHAR2 and VARCHAR
• LONG

Binary data types

• BLOB
• LONG RAW
• BFILE
• RAW

SQL Insert Command

SQL Insert Command 

In this post SQL Insert Command , we will discuss about inserting rows in sql  tables. The basic purpose of SQL Insert Command is to insert data into the tables. After creating tables , we need to insert data into tables.

SQL Insert Command 

Before we learn SQL Insert Command , We first have to create tables in sql database. If the tables are already created, then before you enter data into a SQL  database table, you have to know the name of the table as well as the various columns of the table. Along with names of the columns, we you have to be familiar with the data types of the various columns of the table. 

How to know the name of the various columns and their respective data types?

Is this possible to know the name and data types of various columns of the table, yes this is quite simple to know the name of the columns and their data types. This can be done by using desc table command. 

Desc Table Command

The Desc Table command of SQL is to describe the structure of the table.

For example if we supply command like this : Desc employee;

This will display the whole structure of the employee table. It will display the name of the columns along with column specifications.

When you came to know the names of various columns and their data types, their widths and constraints, we can add rows to the table.

While inserting rows into tables, you have to take care with number of columns and their data types.

Now we will see how we can use SQL Insert Command 

To  add a row in a table in sql, we have to use insert into command. The use of insert into command is quite simple.

SQL Insert Command – To insert data in tables we have to use Insert into Command.  With the help of insert into command, we can add rows in a table.

The General Syntax of Insert into Command 

Insert into <table name> values (value1, value 2,…);

Here table name is the name of the table in which you wants to add rows.

Suppose the name of the table is employee, the insert statement will be like this;

Insert into employee values (101, ‘SAM’);

After execution of the statement, the following message will be displayed

I row(s) created.

Here, you have to be care full, 101 will be added to first column of the table and SAM will be added to the second column of the table.

It is necessary to take care of data types of while inserting rows in a table.                   

With one insert into command, you can add only one row in a table. 

For Multiple rows , we have to use insert into command multiple times.

Few more examples of Insert into Command

Given a table named employee with columns ecode->char(4), ename->varchar2(30), ecity->varchar2(30)

Insert into employee values (‘E001’,’SAM’,’Florida’);

Here the first value E001 will be stored in first column of the table, second value SAM will be stored in second column of the table and third value Florida will be stored in  the third column of the table.
In this case, user must be aware about the sequence of the columns of employee table. If by mistake he supply insert into command as Insert into employee values (‘SAM’,’E001’,’Florida’);

In this case employee name will be stored in employee code column and employee code will be stored in employee name column.

To avoid such problems, supply insert into like this:

Insert into employee(ecode, ename, ecity ) values (‘E001’,’SAM’,’Florida’);

This will add a row in database table, in this case E001 will be stored in ecode, SAM will be stored in ename and Florida will be stored in ecity column of the employee table.

Main thing here is , you donot have to remember the sequence of columns in the table. You can supply the above insert into command as : Insert into employee(ename, ecode, ecity ) values (‘SAM’,’E001’,’Florida’);

Here you can see, we had altered the sequence of values, we had written SAM as first value and E001 as second value, we had also altered the sequence of column names in the above statement, ename is given first and then ecode.

Inserting multiple rows using SQL Insert Command 

To insert multiple rows in a table,  we have to supply insert into command as

Insert into employee values (&eocde, &ename,&ecity);

When you supply this command, the following output will be generate.

Enter Value for Ecode :

Here you have to supply value for ecode like ‘E001’

After this you will get –

Enter Value for Ename :

Here you have to supply value for ename like ‘SAM’

After this you will get –

Enter Value for Ecity :

Here you have to supply value for ecity like ‘Florida’

After this following message will be generated –

1 row(s) created.

After this sql prompt will come.

Sql>

Here you have to write run

Sql>run;

Run command of sql executes the last executable statement stored in Sql buffer.

In this case it is : Insert into employee values (&eocde, &ename,&ecity);

Again you have to supply new values, you can repeat this step for as many times as you want.

 Summary: This is all about SQL Insert Command .