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.
The data in a database is stored in a very efficient way, such that it is easy to access and maintain. In a Relational Database Management System, 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

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 CONSTRAINTS
Examples of JOINS
Examples of DELETE, DROP & TRUNCATE
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