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’));
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.