Wednesday, March 21, 2018

Oracle Insert,Select,Update & Delete Table Operations

In Oracle database management system we perform four main operations on table Insert,Select,Update and  Delete. These all are DML Operations i.e. Data Manipulation Language operations. So COMMIT command is required after executing above command on table. 

Oracle Insert Query
Oracle Create New Record in table is performed using Insert Query on table

Syntax of insert query mentioned below,

INSERT INTO Table_Name (Column1,Column2, ........ ColumnN)VALUES(Value1,Value2,..........ValueN);

Above insert query create new record in the table and insert Value1 in Column1, Value2 in Column2 and so on.


Oracle Select Query i.e. Read Record
Oracle Select query select multiple rows from table

Syntax of select query mentioned below,
SELECT *  FROM Table_Name 

Above select query read all record from Table_Name

SELECT * FROM Table_Name WHERE Column1 = Value

Above select command returns records from Table_Name,who satisfy the condition written next to where command


Oracle Update Query 
Oracle Update query update existing record from table who satisfy where clause conditions 

Syntax of update query mentioned below,
UPDATE Table_Name SET Column_Name1 = Value1,Column_Name2=Value2 WHERE Column_Name = Value

Above update command update existing column values to new values using SET keyword.


Oracle Delete Query
Oracle delete query delete records from table.

Syntax of delete query mentioned below,

DELETE FROM Table_Name WHERE Column_Name = Value

Above delete command delete records from Table_Name who satisfying condition next to where clause.


So after executing above commands COMMIT command  is need to execute for commit all changes in database.

Read More

Wednesday, March 14, 2018

Oracle Database: DROP, TRUNCATE, DELETE Operations

Oracle Database Drop and Truncate are DDL  (Data Definition Language) Command while Delete is DML (Data Manipulation Language) Command. We will learn about each of them in detail below,

Drop:   Drop oracle command is used to remove complete table from database. As drop is DDL so commit operation not required after drop query.


Syntax : Drop Table Table_Name [Purge]; 
Table_Name can be any table name that you want to delete from database. Purge is optional keyword.

Drop Table have purge option. Purge is used to delete table permanently from database. If you have used purge with drop table then that table directly deleted from database can not recovered if required.Table delete with drop and purge will not stored into recycle bean.


Truncate :  Truncate Oracle Command is used to remove all rows i.e. all record from table. This command not delete table definition from database.Truncate is DDL command so commit not required after truncate oracle table. Truncate is faster than Delete. Where clause can not be used with Truncate command.

Syntax : Truncate Table Table_Name


Delete : Delete Oracle Command is used to remove all, single, multiple rows i.e. all record from table. This command not delete table definition from database.Delete is DML command so commit required after Delete command to save changes permanently.  Delete can be used with where clause.
Delete is time consuming and slow compared to truncate.

Syntax :  Delete Table Table_Name; Commit;

Delete multiple Rows/Records base on some criteria:
Syntax : Delete from Table_Name [where Condition]


Click Here for Oracle Database Insert,Select,Update & Delete Table Operations.

Read More

Sunday, March 11, 2018

Oracle Database ALTER Table

Hello In this post we are going to learn ALTER oracle command.

Oracle ALTER keyword is used to change properties of existing oracle object i.e. Table.

Oracle ALTER Table is used to add,modify,delete existing column of Table. We can change Database name using Oracle ALTER table keyword.

Oracle Syntax Add Single Column & Multiple Columns To Exiting Table as shown below

ALTER Table Table_Name
ADD Column_Name Datatype



ALTER Table Table_Name
ADD 
(Column_Name1 Datatype,
Column_Name2 Datatype,
Column_Name3 Datatype,
.....
.....
Column_NameN Datatype   
)

Above Add keyword is used to add new column to an existing table.



Oracle Syntax Modify Single Column & Multiple Columns To Exiting Table as shown below

ALTER Table Table_Name
MODIFY Column_Name Datatype



ALTER Table Table_Name
MODIFY 
(Column_Name1 Datatype,
Column_Name2 Datatype,
Column_Name3 Datatype,
.....
.....
Column_NameN Datatype   
)

Above Modify keyword is used to modify existing column from an existing table.



Oracle Syntax Drop Columns of an Exiting Table as shown below

ALTER Table Table_Name
DROP COLUMN Column_Name

Above Drop keyword is used to delete column of an Table.



Oracle Syntax Rename Columns of an Exiting Table as shown below

ALTER Table Table_Name
RENAME COLUMN Column_Name_Old TO Column_Name_New

Above Rename Column keyword is used to rename existing column name Column_Name_Old  to new name Column_Name_New.



Oracle Syntax Rename Existing Table Name as shown below

ALTER Table Table_Name_Old
RENAME TO Table_Name_New

Above Rename keyword is used to change existing table name Table_Name_Old to Table_Name_New.


Read More

Saturday, March 10, 2018

Oracle Database Create Table

Oracle Create table command used to create table in oracle database.

Table is main oracle entity used to store data in oracle database.


Oracle Create Table Syntax:
In Oracle create table syntax user need to specify Table name and Columns like below,

Create Table Table_Name
{
Column_Name1 DataType,
Column_Name2 DataType,
Column_Name3 DataType,
......
......
Column_NameN DataType
};

In above create table syntax Table_Name can be any name that user want to name table. Column_Name1  can be any name that user wants to give to its column.Every column must  have Datatype. Datatype can be anything i.e. varchar,Number,date etc.We will detaily discuss about oracle datatype in upcoming posts.

We can also specify oracle column value should be null or not by specify NULL or NOT NULL in create table command as like below,

We can specify column NULL like below in create table query then value of the column can be null

Create Table Table_Name
{
Column_Name1 DataType NULL,
Column_Name2 DataType NULL,
Column_Name3 DataType NULL,
......
......
Column_NameN DataType NULL
};

So if we specify column NULL like above then we have to compulsory specify value to column while inserting record.


We can specify column NOT NULL like below in create table query then value of the column can not be null.

Create Table Table_Name
{
Column_Name1 DataType NOT NULL,
Column_Name2 DataType  NOT NULL,
Column_Name3 DataType NOT NULL,
......
......
Column_NameN DataType NOT NULL
};

So if we specify column NOT NULL like above then we can exclude column or set blank value to column while inserting record.

Create Primary Key :
Primary key is used to uniquely identify the record from table. Primary key should not be NULL.

We can specify column as primary key like below,
Constraint Constraint_Name PRIMARY KEY(Column_Name)

Here Constraint_Name can be anything and Column_Name must be table column that we have to assign as primary key.

So create table command look like below,

Create Table Table_Name
{
Column_Name1 DataType NOT NULL,
Column_Name2 DataType  NOT NULL,
Column_Name3 DataType NOT NULL,
......
......
Column_NameN DataType NOT NULL
Constraint Constraint_Name PRIMARY KEY(Column_Name1)
};


Click Here for ALTER TABLE ORACLE article.

Read More

Friday, March 2, 2018

Oracle Database Introduction

Oracle is Relational Database (RDBMS) used to Store and Retrieve Data in the form of Table Structure.

Oracle used SQL (Structured Query Language) language for create, read, update and delete operations to read and write data to and from oracle database tables.

Oracle is mostly used by big companies in their enterprise applications .

Oracle have different editions depend upon type of industry and its application. We need to select appropriate edition for our use. These editions are as mentioned below

Oracle Enterprise Edition- Most robust package include all advance features i.e. security also
Oracle Standard Edition - Base Functionalities for the user
Oracle Express Edition - Free Edition
Oracle Lite - For Mobile Devices

For more information & download Oracle Database visit below link
https://www.oracle.com/database/index.html

Click Here to learn how to create table in oracle database.

Read More