Database Tutorial Oracle, SQLplus/PLSQL

Commands, Syntax, Examples – easily described

Oracle SQL 9i Introduction and OCA guide...CLICK HERE !!!

Data is the most important element of any organization. Loss of essential data may sometimes result in loss of business or even losing your job. So it is highly crucial for any organization to store and manage its data in a way that is easily accessible, and at the same time secure.

A database is a collection of data stored on the disk of a database server.

DBMS stands for DataBase Management System. It is a collection of data stored in a disc or a network. The data is stored in a very efficient manner which is easy to access and maintain.

RDBMS stands for Relational Database Management System. The data in a database is stored in a very efficient way, such that it is easy to access and maintain. In a RDBMS, every data in the database is designed in such a way that they can be related to one another i.e. there exist a relation among the data. That is why it is called RDBMS. Oracle is a RDBMS

It is difficult to distinguish between DBMS and RDBMS. You can say that RDBMS is a type of DBMS. DBMS is a very old term, while RDBMS is newer compared to it. In some cases a DBMS may contain data in the form of flat files. A RDBMS will always store the data in the form of tables.

In order to access and manipulate the data existing on a database, Oracle has provided two languages – SQL and PLSQL.

SQL stands for Structured Query Language or Standard Query Language. It can be used to access and manipulate data in the database.  We can create, modify, delete, insert data into the database using SQL. SQL statements are independent queries i.e., they do not have any impact or effect on each other.

PL/SQL stands for Procedural Language/ Structured Query Language or Procedural Language extension Structured Query Language.
It is combination SQL statements and technical functionality of a programming language.
PL/SQL enables us to group together SQL statements to form blocks of codes, apply programming features to these blocks and run it as a whole.

Both SQL and PLSQL are very important components of the database.

Every data stored in the database is organized in the form of rows and columns. The vertical structure are called columns where as the horizontal structure are known as rows.

For example – Above we have a table named EMPLOYEE which stores NAME, LOCATION, DEPARTMENT and DESIGNATION of all employees in our organization

In our example NAME, LOCATION, DEPARTMENT and DESIGNATION are the table fields or columns. The string ‘Alex’ reside on row1 column1. The string ‘Wales’ reside on row2 column 2. The string ‘Sr. Manager’ reside on row2 column 4 and so on.

Tables, Views, Synonyms, Procedures, Indexes etc are called database objects.

A database object is an item which is used to store or refer to or manipulate data.
A table is a set of data on a particular topic. For example we can create a table which will store data about Students – NAME, GRADE, DEPARTMENT etc.
A view is a logical representation of data from one or more table.
Synonyms are alternative names given to a table.
Procedures are block of codes that can be used to perform certain actions on the data.
Indexes act as performance enhancing mechanism.
Whether your aim is to become a software engineer or a database administrator, directly or indirectly you will have to interact with the database. So it is very important to have a good understanding on the various concepts of a database.

A DBA has to make sure that the database is always up and available; and also secure. Performing Backup and Recovery, Creation of users, providing proper access to users etc are some of the important activities of a database administrator.

A good developer or software engineer may have to write hundreds of queries, scripts or procedures in their daily activity. Having a good grip on SQL and PLSQL will make their job much easier.
In this tutorial we will discuss about basics of Database Administration, SQL and PLSQL.

SQL Samples

Examples of SELECT

Examples of INSERT

Examples of UPDATE

Examples of  ALTER

Examples of MERGE INTO

Examples of CASE & DECODE

Examples of CREATE


Examples of JOINS


Examples of SUBQUERIES

Examples of INDEX

Examples of SYNONYM

Examples of SEQUENCE

Examples of VIEWS

PLSQL Samples

Examples of PLSQL Block

Examples of PLSQL Iterative statement

Examples of PLSQL Conditional statement

Examples of PLSQL Records

Examples of PLSQL Cursors

Examples of PLSQL Triggers

Examples of PLSQL Procedures

Examples of PLSQL Functions

Examples of PLSQL Parameters (IN,OUT, IN OUT)

Examples PLSQL Varrays

Examples PLSQL Nested Table

Examples PLSQL Associative Array/Index by Table