While working with Oracle database, in day to day life,we use many types of SQL commands. In fact SQL commands are really universal in nature. Today, let us learn some of the most commonly used SQL commands in Oracle.

Commands

Creating a table in Oracle

For creating a table in Oracle you can use the below given command:

Syntax:

CREATETABLE tablename (columnname datatype(size), columnname datatype(size),)

Creating a table from  a table in Oracle

For Creating a table from a table you can use the below give SQL command

Syntax:

CREATETABLE tablename,

[(columnname,columnname)]

       AS SELECT columnname, columnname FROM tablename;

Note: If the source table from which the Target  is being created, has records in it then the target table is populated with these records as well.

Inserting of Data into Tables in Oracle

For inserting data or information to your ORACLE tables you can use:

Syntax

INSERT INTO tablename

[(columnname, columnname)]

VALUES (expression, expression);

Note : The character expressions must be in single quotes.

Inserting data into a table from another Table

For inserting data from another table to your ORACLE table of your database you can use the below give syntax:

Syntax

INSERT INTO tablename

SELECT columnname,columnname,

      FROM tablename;

Inserting Selected data from another Table 

In many cases you  need to insert selected data from another table to your table. For this you can use :

Syntax

INSERT INTO tablename

       SELECT columnname, columnname FROM tablename WHERE column = expression

Updating the Contents of a table

For updating the contents of your Oracle database table you can use the below given syntax.

Syntax

UPDATE tablename

       SET columnname = expression, columnname = expression…

              WHERE columnname = expression;

Deleting all rows from Oracle database tables

For deleting all the rows from the Oracle database tables you can use:

Syntax

DELETE from tablename;

Deleting a specific number of rows

For deleting a specific number of rows from your ORACLE database you can use;

Syntax

DELETE from tablename WHERE search condition;

THE SELECT COMMAND IN ORACLE

Global data extract:

For extracting the data from the tables you can use:

Syntax

SELECT * FROM tablename;

Retrieving specific columns from a table : 

For retrieving a particular column from a table use:

Syntax

SELECT columnname, columnname

FROM tablename

Elimination of Duplicate from the Select Statement:

For eliminating all duplicate data from the selected columns of the database tables use:

Syntax

SELECT DISTINCT columnname,columnname

FROM tablename;

Sorting of data in table in Oracle

If you want to sort the data in a table you can use :

Syntax

SELECT columnname, columnname

FROM tablename

ORDER BY columnname,columnname

Selecting a data set from table data:

If you want to select a data set from table data then you can use the following command:

Syntax

SELECT columnname,columnname

FROM tablename

where search condition;

Note : In the search condition all standard operators such as logic, arithmetic, predicates etc can be used.

Modifying the Structure of Tables in Oracle:

For modifying the structure of  tables in Oracle you can take the advantage of the ALTER command:-

Syntax

ALTER TABLE tablename

ADD (newcolumnname datatype(size), newcolumnname datatype(size)… );

Restrictions : Using the alter table clause you cannot perform the following tasks:

• Change the name of the table.
• Change the name of the column.
• Drop a column.
• Decrease the size of a column if table data exists.

Note : Oracle will not allow constraints defined using the alter table, if the date in the table, violets such constraints.

Example : The Primary Key constraints on the column that has duplicate values will not be allowed.

Removing/Deleting/Dropping Tables in Oracle:

For removing or in other words you can say dropping tables in Oracle you can use the below given syntax:

Syntax

DROP TABLE tablename;