Introduction of SQL commands and Different Data types in Oracle(SQL)

Different types of DataTypes and SQL Commands: 

In this session I am going to explain about different types of dataTypes and basic introduction of SQL commands.

 Structured query language(SQL) is a database language to perform the certain operations on database. Sql uses certain commands like Create, Drop, Insert etc.. to carry out the required operations.

These SQL commands are mainly categorized into four types.

1. DDL commands ( Data definition language )

2.DML Commands ( Data Manipulation language )

3.DQL Commands ( Data query language OR Data retrieval language )

4.TCL commands ( Transaction control language )

5.DCL Commands ( Date control Language)

Before Knowing about SQL Commands, We will know about DataTypes. So that we can easily understand about SQL commands.

What is the purpose of Data type:-  Data type is used to identifies the type of data with in table column.

Oracle having following data types.

    1. Number (P,S) and  Number(P)

    2.Char and Varchar2(max size)

    3.Date

1) Number (P,S):-  It is used to store fixed and floating point numbers.

P= Precision (Before point digit number)

S=Scale ( After point digit number)

example : 100.12

                  Here 100 is the precision and  12 is the scale.

Syntax : column name number(P,S)

Example query:

SQL> Create table ABC(SNO number(3,2)); --- Create command is used to create table or other objects.

SQL> Insert into ABC values(100.12); --- Insert command is used to insert data into table.

SQL> Select * from ABC; --- Select command is used to select the data from table.

Output:  SNO :     100.12

 

We have created a table with precision 3 and scale 2.  So while inserting to table if you pass precision more than 3 digit number then system will through error.

Error: Value larger than specified precision allowed for column

I will explain In-Detailed about the create, insert and select commands in my upcoming sessions.

2) Char :-

It is used to store fixed length alphanumeric data type. The maximum size of char is 2000 bytes and by default is one byte.

syntax: column name  Char(size)

> When we are trying to store the less data than the specified size to character data type, Then oracle system automatically append spaces in place of remaining bytes at the end of actual data. This is called as "Blank padded mechanism".

Example: name : Krishna (actual size is 10), Krishna length is 7 So remaining will append as blank.

Varchar2(max size) : Varchar is the advanced data type of Char. Varchar also stores alphanumeric data. This data type stores 4000 bytes.

Syntax : Column name varchar2(max size)

> Varchar is bit different compared to char data type. Because if we are storing less value than the actual size of column then by default oracle system doesn't append spaces. This is called as "not a blank padded mechanism".

And the same way Varchar(max size) data type.  Varchar stores maximum 2000 bytes and varchar2 stores 4000 bytes of data.

3) Date :-

It is used to store the date in oracle date format.

Syntax: column name date.

> In oracle by default date format is  DD-MON-YY.

Example: 12-JUL-20.

I will explain about SQL commands In-Detailed in my next Post.

                                            Thank you.