Oracle SQL Introduction

Oracle SQL Introduction

WHERE Condition:

A table contains the lacks of records but if we want any "specific record" from the table,in this case we use "WHERE".

Example:

SELECT * FROM employees where Employee_Id='100';
It return the value  who all are having the id only 100.

Conditional Operators::

=,<,>,<=,>=,<>,!=,^=(The last 3 oprataors are same (Not equal operators))

Examples::

SELECT First_Name,Salary FROM employees where salary=5000;
              --Produce the output equal to 5000 salary employees

SELECT First_Name,Salary FROM employees where salary>5000;
              --Produce the output Greter than 5000 salary employees

SELECT First_Name,Salary FROM employees where salary<5000;
              --Produce the output less than 5000 salary employees

SELECT First_Name,Salary FROM employees where salary>=5000;
              --Produce the output greater then or equal to 5000 salary employees

SELECT First_Name,Salary FROM employees where salary<=5000;
             --Produce the output less than or equal to 5000  salary employees

SELECT First_Name,Salary FROM employees where salary<>5000;
            --Produce the output not equal(with out 5000) to 5000  salary employees

LOGICAL OPERATORS::

i)AND
ii)OR
iii)NOT

AND::

The AND operator is return the OUTPUT when the Condition is True.

Example::

SELECT * FROM employees where employee_Id=10 and First_Name='Arul';

10 Arul 15000 01-Jan-99
---     ----
The query returns the output if the AND operations True.

OR::

The OR Operator is return the output if the any one of the condition satisfy before or after the OR operator.

Example::

SELECT * FROM employees where employee_Id=10 OR First_Name='Steven';
The above query is return the Output the employee Id condition as well as the First Name(If available in table)

ORACLE OPERATORS::

1.IN and NOT IN
2.BETWEEN ..AND --NOT BETWEEN... AND
3.LIKE and NOT LIKE
4.IS NULL and IS NOT NULL

IN and NOT IN::

IN condition is used for the return the Output when the Inside (Values satisfy the condition)

Example:

SELECT * FROM employees where Employee_Id IN(10,100);

The query return the values who all are having the Id 10 & 100.

NOT IN is reacted Opposite to the IN Condition
SELECT * FROM employees where Employee_Id NOT IN(10,100);

The query not return the values who all are having the Id 10 & 100 and return the remaining values.

BETWEEN...AND -----NOT BETWEEN ...AND

The BETWEEN....AND used for When we use any range or [from] to [to date] case we use this.

Example::

SELECT * FROM employees where salary BETWEEN 5000 and 1000;
SELECT * FROM employees where Hire_Date BETWEEN '01-Jan-89' and '31-DEC-89';

The NOT BETWEEN....AND reacts opposite to the BETWEEN...AND.

Example::

SELECT * FROM employees where salary NOT BETWEEN 5000 and 1000;
SELECT * FROM employees where Hire_Date NOT BETWEEN '01-Jan-89' and '31-DEC-89';

LIKE and NOT LIKE::

LIKE is Otherwise Known as "Pattern Matching" and "Wildcard Search".
and mainly used for search the String Using From [Beginning Char] or [end Char] or [Containg Character] Or
[Starting and ending Char].

LIKE OPERATORS:


1.% -- Something or Nothing
2._ -- one Char Indication



Examples::

%

SELECT * FROM employees where First_Name like 'St%';
            --Return the Values Start with "St"

SELECT * FROM employees where First_Name like '%en';
            --Return the Values end with "en"

SELECT * FROM employees where First_Name like 'S%n';
            --Return the Values Start with "S" and end with "n"

SELECT * FROM employees where First_Name like '%tev%';
            --Return the Values the Word "man" Contain continuously.
_

SELECT * FROM employees where First_Name like '_t%';
           --Return the Values the char "t" contain second position from beginning

SELECT * FROM employees where First_Name like '%v_n';
           --Return the Values end with "n" and reverse the 3rd char having "v"

SELECT * FROM employees where First_Name like '_____';
           --This return the values which are having 5 character.

Printing _ (Underscore Operator)  in Output::

Example:

Consider "arul_xavier@gmail.com" is a table value

SELECT * FROM email where email_Id like '____$%_' ESCAPE '$';

IS NULL & IS NOT NULL::

The IS NULL is used for returning the NULL Values in the selected column and the IS NOT NULL is used for returning the NOT NULL Values in the Selected column.

Example:

SELECT * FORM employees where Commission_Pct IS NULL
SELECT * FORM employees where Commission_Pct IS NOT NULL

ORDER BY ::

The ORDER BY mainly used for sorting the selected output values
  There are two types of sorting available

1.ASC
2.DESC

Example::

SELECT * FROM employees Order By Salary;
         --If we are not give any key word it will sort ASC by default.(1,2,..)

SELECT * FROM employees Order By Salary ASC;
         --It return the values by ascending.(1,2,...)

SELECT * FROM employees Order By Salary DESC;
         --It return the values by descending.(100,99....9,8,...1)

SELECT * FROM employees Order By 3 DESC;
         --It return the values by descending based on Column "3"

SELECT Emp_id,12*salary a FROM employees where Order By a DESC;
         --Return the value(Multiply salary with 12 and return by DESC
   Based on Alias Name(a).

SELECT Employee_Id,First_Name,Salary,Salary*2 FROM employees Order By           employee_Id,Salary*2 DESC;

  (It Return the employee_Id Asc then Ascending value the it Descending the salary*12 Column.)

                                             --------------Session - 2 End------------------

Read More

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-----------

Read More