Func Part..3

FUNCTIONS CONTINUES..

GENERAL FUNCTIONS::

The General functions are mainly used for Process the "NULL" Values.

There are following four types of General functions are there::

1.NVL()
  --It's having two arguments
2.NVL2()
  --It's having three arguments
3.NULLIF()
  --It's having two arguments
4.COALESCE()
  --It's having "n" number of arguments.

1.NVL()::(Argument1,Argument2)

The NVL() function used for if the first argument having NULL value then it's return the second argument value.

Example::

SELECT NVL(Commission_PCT,'0') From employees;
O/P---> -
-
1.2
3.3
-
2.NVL2()::(Argument1,Argument2,Argument3)

The NVL2() function used for if the first argument having "NULL" value then it's return the third argument value.

Example::

SELECT NVL2(Commission_Pct,'0','1') From employees
O/P--O/P---> 1
1
2.2
3.3
1
3.NULLIF()::(Argument1,Argument2)

The NULLIF() function used for return,If the two argument having same values then its return "NULL",else it print
the "first" argument value.

Example::

SELECT NULLIF (1,,1)
,(2,2)
,(1,2)
,(2,7)
,(7,9)
FROM dual;

O/P-------->>> -
-
1
2
7
4.COALESCE()::(1,2,3,....n)

The COALESCE() function returns the first "non null Value" from the Passed arguments.

Examples:

SELECT COALESCE(NULL,NULL,NULL,NULL,NULL,89,NULL,NULL,99,100,101) from dual;
O/P--89

II.MULTI ROW FUNCTIONS
======================
GROUP FUNCTIONS or AGGREGATE FUNCTIONS::

1.MIN()
2.MAX()
3.SUM()
4.AVG()
5.COUNT()

Example Table::

-----------------------------------------------
Name Dept_Id Salary
-----------------------------------------------
n1 10 25000
n2 20 45000
n3 20 17000
n4 30 13500
n5 30 73000
n6 30 72000
n7 40 1700
n8 40 2900
------------------------------------------------

1.MIN()::

The MIN() function is mainly used for display the Minimum of Value in the selected column.

Example::
SELECT MIN(Salary) FROM employees;
O/P--1700

2.MAX()::

The MAX() function is used for return the maximun of value in the selected column.

Example::

SELECT MAX(Salary) From employees;
O/P---73000
3.SUM()::

The SUM() function is used for returning the sum(Addition of selected column) selected column from the table.

Example::

SELECT SUM(1000 + 2000 + 3000) From dual;
O/P---6000
4.AVG()::

The AVG() function is used for display the value like (Add the Values and/divid the Number Of columns or Number of row)

Example::

SELECT ROUND(AVG(salary)) from employees;
O/P---6462

5.COUNT()::

The COUNT() function is used for Count the number in the selected rows.

Example::

SELECT COUNT(Salary) FROM employees;
O/P--107

GROUP BY & HAVING::

When ever use the Group function Must use the GROUP BY Clause for the non group function columns.

USING GROUP BY::

SELECT First_Name,COUNT(Salary) FROM employees;
GROUP BY First_Name;
O/P--Steven   2
    Neena    1
USING HAVING::

*When ever we want to check the condition while using the Group function case we must use the "HAVING",do not use the
"WHERE" condition.
*We can use "HAVING" with out GROUP BY


Example::

SELECT First_Name,COUNT(Salary) FROM employees
GROUP BY First_Name
HAVING Count(Salary)>1;

O/P---Steven  2
     Peter   3
     John    3
USING ORDER BY While Use GROUP BY & HAVING::

If the "ORDER BY" Comes in the query ,It Should place after the "HAVING" statement.
Example::

SELECT First_Name,COUNT(Salary) FROM employees
GROUP BY First_Name
HAVING Count(Salary)>1
ORDER BY First_Name Asc;

O/P--- Alexander 2
David 3
James 2

DECODE & CASE::


*Decode is a Function
*CASE is a expression
*Decode produce the OUTPUT based on If the next ..next value satisfy the condition else it print the Final Value.
*In Case we can used Oracle Operators "LIKE,BETWEEN AND,=,<,>,<>"
*Compare to Decode , CASE is fast because it followes ANSII Standards.
*DECODE is an SQL Standard but CASE is an SQL and PL/SQL Standard.

Example Using DECODE::

SELECT DECODE('Infycle','Infycle','YES','NO')"EXAMPLE" FROM dual;
O/P---YES
SELECT DECODE('Infycle','infycle','YES','NO') "EXAMPLE" FROM dual;
O/P---NO
SELECT DECODE('Infycle','Infycle_Chennai','YES_1','Infycle','YES_2','NO') "EXAMPLE" FROM dual;
O/P---YES_2
SELECT DECODE('Infycle','Infycle_Chennai','YES_1','InFyCle','YES_2','NO MATCH') "EXAMPLE" FROM dual;
O/P---NO MATCH

Example Using CASE::


SELECT FIRST_NAME,DEPARTMENT_ID,
CASE DEPARTMENT_ID
WHEN 10
THEN 'ADMIN'
WHEN 20
THEN 'PURCHASE'
WHEN 30
THEN 'SALES'
WHEN 50
THEN 'HR'
ELSE 'OTHERS'
END
FROM employees WHERE DEPARTMENT_ID in (10,20,30,50);

Read More

Func Part..2

FUNCTION CONTINUES

NUMBER FUNCTIONS::

1.ROUND()
2.TRUNC()
3.MOD()
4.POWER()

1.ROUND()::

The ROUND() Function mainly used for Round of the Decimal Values Or Round of the Values based on the Position Which is
Passed in the argument.

Example::

**Using Second Argument As Postitive Values--ROUND(Arg1,Positive Value)::

SELECT ROUND(324.5456,2) From Dual;
O/P--324.54
SELECT ROUND(324.5456,3) From Dual;
O/P--324.546
SELECT ROUND(324.5,1) From Dual;
O/P--325
SELECT ROUND(324.5,0) From Dual;
O/P--325
SELECT ROUND(340.5456) From Dual;
O/P--340

**Using Second Argument As Negative Values--ROUND(Arg1,Negative Values)::

SELECT ROUND(324.5456,-1) From Dual;
O/P--320
SELECT ROUND(324.5456,-2) From Dual;
O/P--300
SELECT ROUND(1324.5,-3) From Dual;
O/P--1000
SELECT ROUND(326.5,-1) From Dual;
O/P--330
SELECT ROUND(374.5456,-2) From Dual;
O/P--400
SELECT ROUND(2674.5456,-3) From Dual;
O/P--3000
TRUNC()::

The TRUNC() function mainly used for "Truncate" or "Remove" the Values(From Position) based on the Position
we mentioned in the second argument.

SELECT TRUNC(324.5456,2) From Dual;
O/P--324.54
SELECT TRUNC(324.5456,3) From Dual;
O/P--324.545
SELECT TRUNC(324.5,1) From Dual;
O/P--324.1
SELECT TRUNC(324.5,0) From Dual;
O/P--324
SELECT TRUNC(340.5456) From Dual;
O/P--340

MOD()::

The MOD() function mainly used for Divid the Argument1/Arg2 and Returns the remainter value of the Statement.

Example::

SELECT MOD(3,2) FROM dual;
O/P--1

POWER()::

The POWER() function mainly used for multiply the argumnet1 with arg2(Number Of Times) and ruturn the Output

SELECT POWER(3,4) FROM dual;
O/P--3*3*3*3====>81

DATE FUNCTION::


There are four DATE functions are there.
1.NONTHS_BETWEEN()
2.ADD_MONTHS()
3.NEXT_DAY()
4.LAST_DATE()

"SYSDATE" is an inbuilt Pseudo function ,It mainly used for Getting Current Server date.(01-Apr-16)

SELECT SYSDATE from dual:
O/P--01-Apr-16
SELECT SYSDATE+10 from dual;
O/P--10-Apr-16
SELECT SYSDATE-10 from dual;
O/P--20-Mar-16
*We can Perform Add ,Sub with "SYSDATE"

Condition::

1.Date + Number = Date
2.Date - Number = Date
3.Date - Date = Number
4.Date + Date = NOT POSSIBE

(DATE)-(DATE) Example::

We Can Subtract One date from another Date using
 TO_Date("Your Date","Format DD-Mon-YY")

Example::


SELECT TO_DATE('05-Apr-16','DD-Mon-YY') - TO_DATE('01-Apr-16','DD-Mon-YY') as "RESULT" FROM dual;
O/P--4

i)MONTHS_BETWEEN()::

The MONTHS_BETWEEN() is mainly used for return the number of months between from Month(Arg1) to till Month(Arg2)

SELECT MONTH_BETWEEN(SYSDATE,'01-Jun-15') FROM dual;
SELECT ROUND(MONTHS_BETWEEN(SYSDATE,'01-Jun-15')) FROM dual;
O/P--10

ii)ADD_MONTHS()

The ADD_MONTHS() increment the the Month(Arg1,Increment Month(+ Or -)

SELECT ADD_MONTHS(SYSDATE,3) FROM dual;
O/P--05-JUL-16
SELECT ADD_MONTHS(SYSDATE,-7) FROM dual;
O/P--05-SEP-15

NEXT_DAY()::

The NEXT_DAY() function is used for return the next date From(Argument 1).

SELECT NEXT_DAY(SYSDATE,'Thursday') FROM Dual;
O/P --NEXT Thursday Date--->14-Apr-16
SELECT NEXT_DAY(NEXT_DAY(SYSDATE,'FRIDAY'),'FRIDAY') FROM dual;
O/P ---Next 2nd Friday----->15-Apr-16

LAST_DAY()::

The LAST_DAY() function is used for returning the last day (Date) of the Month(Passed in the query)

SELECT LAST_DAY(SYSDATE) from dual;
O/P-->30-Apr-16

TYPE FUNCTIONS::

There are following three types types function are there,
1.TO_NUMBER()
2.TO_DATE()
3.TO_CHAR()


1.TO_NUMBER()::

TO_NUMBER() function is mainly used for converts a Character String into Number.

SELECT 5 + '5' FROM dual;--------Its an Implicit Conversion
O/P----10
SELECT 5 + TO_NUMBER('5') From dual;
O/P--->10

2.TO_DATE()::

The TO_DATE() function is mainly used for Converting the char & Other Unknown date format into a Oracle Standard DATE format.

For example--21-January-95 its a date format but oracle does not recognize the format, So in this case we can used TO_DATE()

SELECT TO_DATE('21-January-95','DD-Month-YY') From dual;
O/P---21-JAN-95

3.TO_CHAR()::

The TO_CHAR() function is mainly used for convert and displaying the Data base date format into used needed format.

SELECT TO_CHAR(SYSDATE,'DD Month Year') from dual;
O/P--->07 April Twenty Sixteen

3)i) Using ROMAN CHARACTER::

SELECT TO_CHAR(8,'RM') FROM dual;
O/P---viii

Example::
SELECT 'Due Date is' || TO_CHAR(SYSDATE,'DDspth month year') FROM dual;

O/P---Due Date is SEVENTH april twenty sixteen

Read More

Func Part..1

FUNCTIONS

FUNCTIONS::

1.Single Row Functions
2.Multi Row Functions

1.Single Row Functions::

If we input 'n' number of input and it produce 'n' number of Output.(n->n)

Types in Single Row Function::

1.Case Functions
   i)Case Manipulation
  ii)Character Manipulation

2.Number Functions
3.Date Functions
4.Type Functions
5.General Functions

1.Case Functions:

i)Case Manipulation:

There are three types of case manipulations are there,
1.UPPER()
2.LOWER()
3.INITCAP()

UPPER():

The UPPER() functions changes the selected colunn into UPPER CASE

Example::

SELECT UPPER(First_Name) FROM employees;
O/P---Arul Is table value after Using Upper the O/P is ARUL.

LOWER()::

The LOWER() functions changes the selected colunn into LOWER CASE

Example::

SELECT LOWER(First_Name) FROM employees;
O/P---Arul Is table value after Using LOWER the O/P is arul.

INITCAP()::

The INITCAP() function used for Changing the First Character into CAPS(UPPER)

SELECT INITCAP(First_Name) FROM employees;

ii)Character Manipulation::

1.LENGTH()
2.REVERSE()
3.CONCAT()
4.SUBSTR()
5.INSTR()
6.LPAD()  & RPAD()
7.TRIM() & LTRIM()  &  RTRIM()
8.REPLACE()
9.TRANSLATE()

LENGTH()::

The LENGTH() function is used for count the number of Char in the selected column including "space"

Select First_Name,LENGTH(First_Name) from employees;

O/P---Arul.....4

Using WHERE with LENGTH()::

SELECT * FROM employees where LENGTH(First_Name)=5;

REVERSE()::

The REVERSE() function is used for REVERSE the selected column "string"

SELECT REVERSE(First_Name) from employees;
  O/P--Arul.....lurA

CONCAT()::

The CONCAT() function is used for joining the two column.

SELECT CONCAT(First_Name,Salary) from employees;

O/P is----Arul15000

SUBSTR()::

The SUBSTR() function id used for returning the Values from Position to Count Position(From_Position,Number_Of_Pos)
                       ------------------------------------------------------------
SELECT First_Name,SUBSTR(First_Name,1,2) from employees;
          --Arul-----O/P is----Arul...Ar

SELECT First_Name,SUBSTR(First_Name,3,3) from employees;
         --Arulxa--O/P is-----Arulxa..lxa

SELECT First_Name,SUBSTR(First_Name,2) from employees;
         --Arulxa--O/P is-----Arulxa..rulxa

SELECT First_Name,SUBSTR(First_Name,-2,2) from employees;
         --Arulxa--O/P is-----Arulxa..xa(-2 Reverse 2 char count and next Print 2 Last Char)

SELECT First_Name,SUBSTR(1000-1,-2,2) from dual;
        --999,,,,,O/P Is 99

SELECT * from employees where SUBSTR(Hire_Date,-2,2)=89;
         --O/P Is 01-Jan-89)

INSTR()::

It returns the Position of the Given Char in the Selected Column

select first_name,INSTR(First_Name,('a')) from employees;
         ---arul----O/P is-----1

select first_name,INSTR(First_Name,('a')),INSTR(First_Name,('a'),1,2) from employees;
---arulxa----O/P is--1--6

LPAD() & RPAD()::

 The both functions mainly used for alignment.


SELECT First_Name,LPAD(First_Name,10,'0') FROM employees;
O/P is (If salary is 25000 -----0000025000)

SELECT First_Name,RPAD(First_Name,10,'$') FROM employees;
O/P is (If salary is 25000 -----25000$$$$$)

TRIM()::

The TRIM() function returns the vlues with out any space before and after the "String".

SELECT LENGTH(TRIM('arul   ')) from dual;
SELECT LENGTH(TRIM('arul')) from dual;
  Both return the same value---4

LTRIM()::

The LTRIM() mainly used for the TRIM the left values Which we pass in the condition(Like "0")

SELECT LTRIM('0000012334000','0') from dual
O/P Is------------1234000

RTRIM()::

The RTRIM() mainly used for the TRIM the Right values Which we pass in the condition(Like "0")

SELECT LTRIM('0000012334000','0') from dual
O/P Is------------000001234


REPLACE()::(String Based)

The REPLACE() function used to Replace the "String Based(Whic means continues character or Word)" values
which is mentioned in the query.

SELECT First_Name,REPLACE(First_Name,'ul','xy') from employees;
O/P---arul------arxy

TRANSLATE()::(Character Based)::

The TRANSLATE() function mainly used for Change the "Single Char"  which is Passed in the query.
SELECT TRANSLATE('WELCOME','E','L') FROM dual;
                                    ------
Translate the 'Char E' into "L".

O/P Is --WELCOME--------WLLCOML.

Read More

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