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 .