Introduction

Oracle SQL Introduction

           An Oracle Database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to solving the problems of information management

I.Technical Topics::
1.Introduction
2.Different Types Of Tracks in Oracle
3.SQL
4.PL/SQL
5.Advance Topics

I.(1)Introduction:

Software:

i)Front End
ii)Back End

Meaning::

In Software,the fornt end indicate the "User Interface"(UI),and the back en indicates the Data Access layer or Data Base(DB).

Sample Fornt end and their Corresponding Backend Tools:

JAVA(Owner Oracle) - Oracle Data Base(Oracle)
MS .NET(Microsoft) - SQL Data Base(Micro Soft)
PHP(Open Source) - MYSQL(Oracle)
IBM - DB2(IBM)
---------------------------------------------------------------------

Versions Of Oracle:

7,8,8i,9i,10g,11g,12c(12c is  a current version)

"i" Indicates - Internet
"g" Indicates - grid
"c" Indicates - Cloud

Standards In Oracle:

i)Nothing is "Case Sensitive" in Oracle except "DATA"(Which Means the Row and column values in Tables

Example 1:

SELECT * FORM EMploYEEs;

*It Provide the All the records from the table "employees"

Exapme 2:

Consider in the employee table the Employee Name is "Arul"

SELECT * FORM employees where First_Name='arul';
SELECT * FORM employees where First_Name='Arul';

*The First Query is not return the Value the second one did becuase the Data's are case sensitive

ii)Date is FORMAT Sensitive

Format Of Date is    -  "DD-MON-YY"
Example     - 01-Jan-99

iii)We can type the queries in multiple lines

Example:

SELECT First_Name
, Employee_Id
, Salary
, Hire_Date
FORM Employees;

Schema::

Schema is nothing but its an User.

There are five types of schema's available

1.Table
2.View
3.Sequence
4.Synonyms
5.Index

1.Table:

It is an basic unit of storage and the table contains set of rows and Columns.

2.View:

It is an Logical representation of subset of records.

3.Sequence::

It is an numeric Value generator(It generates the Unique Values)
(e.g. 1,2,3.......100...etc...)

4.Synonyms::

It is used to provide the alternate name to the Object(Table).

5.Index:

It is used for improving the performance of the table.


(I.2)Different Types Of Tracks in Oracle::

1.Oracle Development
2.Oracle Administration

1.Oracle Developer:

The Developer works on the versions 7,8,8i,9i,10g,11g,12c with using SQL and PL/SQL.

2.Oracle Administration:

The Administrator is Works on the server side like,

1.User Privileges
2.Restrict/grant User management
3.Memory Management
4.Recovery Form Corruption
5.Recovery From Suspect
6.Analysing the data Base Performance
7.Data Base Tuning
8.Data Base Mirroring
9.Backup Maintenance
10.Schedule Fixing

                   
(I.3)SQL(Structured Query Language):

SQL is a Standard Programming language or Command for Getting or retrieving the Data's From the "Data Base".

SELECT & FROM Key Word::

Syntax--->SELECT * FORM table_Name;

Select * Form employees;
  The Query returns the whole values from the table.

Different Types Of Tables::

i)User Table
ii)Data Dictionary Tables

i)User Tables:

It contains only information about the users,and  It Created and maintained by User.
(E.g SELECT * FORM employees;)

ii)Data Dictionary Tables::

It Contains only Information about the Data base,and It Created and maintained by Database.

(e.g SELECT * FROM user_Tables;
The User tables Contains the Only the User Crearet tables
    SELECT * FROM All_Objects;
The All_Obects table contains all the System tables)

Selecting Particular Columns in a Table:


Syntax:

Select Col1,Col2,Col3 From table_Name;

Example:

Select Employee_Id,First_Name,Salary from employee;

Using Arithmetic Operators::

+,   -    ,*   ,    /

Preference Wise   /,    *   ,   +   ,   -

Example:

Select Employee_Id,First_Name,Salary,Salary*12 from employees;
Select Employee_Id,First_Name,Salary,12*(Salary+100) from employees;

"()" is called as "BODMAS"

Alias::

*It is used for renaming the Column Heading
*It is mostly used with expressions.

Possibilities Using "Alias Conditions":

*The "AS" Key word for using next the selected column(It No need to specify in all the places)
*The expression "" used for giving the alies name.(No Need to specify in all the place but if the alias
name Come with Space it must come)

Examples:

SELECT First_Name AS "NAME" FROM employees; --Using As Keyword
SELECT First_Name "NAME" FROM employees; --Using Without As and With Expression
SELECT First_Name NAME FROM employees; --Without expression and AS
SELECT First_Name "NA_ME" FROM employees;
SELECT First_Name NA_ME FROM employees;
SELECT First_Name NA ME FROM employees; --Sample Error query
(This above one return the Error like "ORA-00923: FROM keyword not found where expected")
                                      -------------------------------------------------
SELECT First_Name AS "NA ME" FROM employees; --Expression Query for Space word in Alias Name

NULL::


*NULL is an Unassigned and Unknown value.(Each NULL is a Unique Value)
*We can't perform the arithmetic operation with NULL Column if happens it return the Null Value
NULL != 0(0 is an value)
NULL != SPACE(One Space is Contain One Char)
NULL != SPECIAL CHARACTER
NULL = NULL(Each NULL is a Unique Value)

Example:

SELECT First_Name,Salary,12*Commission_Pct FROM employees;



CONCAT(PIPE(||) Operator)::


The PIPE || Operator is mainly used for concatenate the column and produce the value in a single column.

SELECT First_Name || Salary from employees;
  O/P is ---Arul15000

SELECT First_Name ||' '|| Salary from employees;
  O/P is ---Arul 15000
SELECT First_Name ||' '||'Salary is '|| Salary from employees;
  O/P is ---Arul Salary is 15000



q'[' Operator or  De-Limiters or Quoting Mechanism ::

The De-limiters mainly used for Print the Single quotes in between the strings.

Select First_Name ||q'['s Salary is ]'|| Salary from Employees;

O/P Is --- Steven's Salary is 24000


DISTINCT and UNIQUE::

DISTINCT and UNIQUE both are mainly used for suppresse the duplicate rows in a tables.

Example:

SELECT DISTINCT Department_Id from employees;
SELECT UNIQUE Department_Id from employees;

Both Query results are same.


DESCRIBE(DESC)::

The DESCRIBE or DESC provides the Complete information( likePrimary Key,Index,Data type,..) about the Oject or table.
DESCRIBE employees;
DESC employees;


DUAL::

*Dual is an table is used for process our own data's
*Owner of the dual table is "SYS"
*Dual table column datatype is "VARCHAR2"
*Heading is "DUMMY"
*Default Value is "X"

SELECT * FROM dual;
SELECT "Welcome to Oracle Blog" from dual;
It returns the Values as == >  "Welcome to Oracle Blog"
SELECT 5*5 from dual;
It return the Value as  ==>  25


Specifying the Schemas(User) with table::

Select * from hr.employees

*Here the "hr" indicate the schema(User)

                                    ----------Introduction End-----------