Oracle SQL Commands :

In previous articles i have explained about different examples of SQL queries.I explained the SQL server queries examples as well.In this article i would like to give you different important Oracle SQL Commands with real life examples.Oracle SQL Commands not only contains different SQL Statements but also PL SQL statements.I would like to start with simple system table commands to complex sql and pl sql codes which are used in real world industry.There are following important Oracle SQL commands which are really very useful :

Part 1 : Oracle System Table Commands  :

In this section i would like to give you some most important Oracle SQL commands which are based on system tables.

Example 1 :  How to Find table information about specific table.

Select table_name,Owner from All_tables order by table_name,owner;

The above query will give you the information about all the tables on the database.

Example 2 : How do you fetch different users on database.

SELECT Username FROM All_Users ORDER BY Username;

The above query will give you information about the specific users on database

Example 3 : How to find constraint information on specified database.

SELECT Table_Name, Constraint_Name FROM User_Constraints;

Example 4 : How to check the procedure code from specific procedure.

 SELECT * FROM User_Source

WHERE Type=’PROCEDURE’

AND NAME IN (‘SP_CONNECTED_AGG’,’SP_UNCONNECTED_AGG’);

Example 5 : How to Find index information from the specific database.

Select  * from USER_INDEXES;

Select * from ALL_IND_COLS where Index_name=’Name of Index’;

Part 2 : Oracle Basic SQL Queries

In this section i would like to give you the basic sql queries examples.

Example 1 : Create table in multiple ways in SQL
In this section I would like to give you one simple example of create table in SQL.

Create table Teacher

(Emp_No Number(10),

Teacher_name Varchar2(50));

Example 2 : How to insert data in table

INSERT INTO Teacher

Values (101,’Amit’);

Example 3 :What is query to update data in table.

Update Teacher

set name=’Rohit’ where Emp_No=101;

The above query will update the Teacher table where Employee_No is 101.

Example 4 :Delete record from table.

Delete from Teacher;

The above statement will delete the data from Teacher table.

Example 5 : Alter table and add new column

Alter table Teacher

Add Grade Varchar2(1);

The above command will add new column in Teacher table.

These are some most important basic SQL Queries.

Part 3 : Subqueries in SQL

In this section i would like to give you some important examples of subqueries.

Example 1 : Single Row Sub-query example

Select Employee_No,Employee_Name from Employee

where Salary=(Select max(Salary) from Employee);

Example 2 : Multiple Row Sub-query example

Select Employee_No,Employee_Name from Employee

where Department_Name in

(Select Department_Name from Employee where Department_name in (‘OBIEE’,Oracle’));

Example 3 : Correlated Sub-query

Select * from Employee E where Not exist

(Select Department_no From Department D where E.Employee_id=D.Employee_ID);

Example 4 : Nested Sub-query

Select * from Employee

where Employee_No Exist

(Select * from Employee

where Department_Name=

(Select Department_Name from Employee where Department_Name=’OBIEE’));

Example 5 : Scalar Sub-query

Select D.Department_name(Select Count(E.Department_name) From Employee E Where E.Department_no=D.Department_no;

These are some most important queries for Scalar subqueries.

Part 4 : Other Important Examples

In this section i would like to give you some more examples of Oracle. I would like to give you these examples in different links.

Example 1 : Some PL SQL Examples

Example 2 : System tables in Oracle

Example 3 : Some Important Complex SQL Queries in Oracle

These are some important Oracle SQL Commands which are important in real life. If you like this article on Oracle SQL Commands or if you have any questions regarding the Oracle SQL Commands article kindly comment in comments section.