It concatenates the first character value to the second character value.
It accepts only two parameters accept.
It returns the character data type.

Syntax :

CONCAT( Column1/Expr1, Column2/Expr2)

Examples :
1.
SELECT
'Oracle' String1,
'Corporation' String2,
CONCAT('Oracle','Corporation') Concatenated string
FROM DUAL;

2.
SELECT Ename, Job, CONCAT(Ename, Job) Concat
FROM Emp
WHERE Deptno= 10;

3.
SELECT
CONCAT('The Employee Name is', INITCAP(Ename)) Info
FROM Emp
WHERE Deptno IN(10,30);

4.
SELECT
CONCAT(CONCAT(INITCAP(Ename),'is a '), Job) Job
FROM Emp
WHERE Deptno IN(10,20);

It converts the alpha character values to upper case.
The return value has the same data type as the argument CHAR.

Syntax :

UPPER(Column/Expression)

Examples :

1.
SELECT
'oracle corporation' String,
UPPER('oracle corporation') Upper
FROM DUAL;

2.
SELECT Ename, UPPER('my information') Lower
FROM Emp;

3.
SELECT Ename, UPPER(Ename), LOWER(Ename)
FROM Emp
WHERE Job= 'MANAGER';

4.
SELECT 'The'||Ename||'" s Designation is '|| Job Employee
FROM  Emp
WHERE Job= UPPER('manager');


5.
SELECT Ename, Job
FROM Emp
WHERE Job=UPPER('Manager');

6.
SELECT Ename, Job, Sal, Sal*12
FROM Emp
WHERE Job= UPPER(LOWER('MANAGER'));

7.
SELECT UPPER('The '||Ename||'"s Basic salary is rupees'|| Sal)
FROM Emp
WHERE Job IN('MANAGER', UPPER('clerk'));
ORDER By Sal DESC;

It converts the alpha character values into uppercase for the first letter of each word, keeping all other loetters in lower case.
Words are delimited by white spaces are characters that are not alphanumeric.

Syntax :

INITCAP(Column/Expression)

Examples :

1.
SELECT
'oracle corporation' string,
INITCAP('oracle corporation') initcap
FROM DUAL;

2.
SELECT Empno, INITCAP(Ename), Deptno
FROM EMP
WHERE = UPPER('blake');

3.
SELECT Ename, UPPER(Ename), LOWER(Ename), INITCAP(Ename)
FROM Emp;

4.
SELECT
'The job title for '||INITCAP(Ename)|| ' is '||LOWER(Job)
FROM Emp;

It converts alpha character values to lower case.
The return value has the same data types as argument CHAR type (CHAR or VARCHAR2).

Syntax :  

LOWER(Column/Expression)

Examples :  
1.
SELECT
'ORACLE CORPORATION' String,
LOWER('ORACLE CORPORATION') Lower
FROM DUAL;

2.
SELECT Ename, LOWER('MY INFORMATION') Lower
FROM Emp;

3.
SELECT Ename, LOWER(Ename) Lower
FROM Emp
WHERE Job= 'MANAGER';

4.
SELECT 'The'||Ename||'" s Designation is '|| Job Employee
FROM  Emp
WHERE LOWER(Job)= 'manager';

They are functions that return CHARACTER values, unless stated.
They return the data type VARCHAR2, limited to a length of 4000 bytes.
If the return values length exceeds, Then the return value is truncated, without an error.
The functions are categorized as

• Case Conversion Functions.
• Character Manipulation Functions.

Case Conversion Functions :

These functions are used to convert the casing of the existing character from one type to another.

• LOWER Function
• UPPER Function
• INITCAP Function

SQL functions are built into ORACLE and are available for use in various appropriate SQL statements.
1. The SQL functions can be used to

• Perform calculations on data.
• Modify individual data items.
• Manipulate output for groups of rows.
• Format dates and numbers for display.
• Convert column data types.

2. SQL functions may accept arguments are always return a value, and can be nested.
3. If an SQL function is called with a NULL argument, then a NULL is returned.


SQL FUNCTION TYPES :

SQL identifies two types of functions

• SINGLE row functions.
• MULTIPLE row functions.

SINGLE ROW FUNCTIONS :

• These functions return a single result for every row of a queried table or view.

MULTIPLE ROW FUNCTIONS :               

1. These functions manipulate groups of rows and return one result per group of rows
2. The single row functions can appear in

• SELECT list.
• WHERE clause and ORDER BY clause.
• START WITH clause.
• CONNECT BY clause.

3. The single row functions are categorized as

• CHARACTER functions.
• NUMBER functions.
• DATE  functions.
• CONVERSION functions.

4. SINGLE row functions are used to manipulate date items.
5. They can accept one or more arguments and return one value for each row returned by the query.
6. The argument for a single row function can be

• User supplied constant.
• Variable value.
• Column name.
• Expression.

Syntax :

FUNCTIONNAME(Column / Expr, [Arg1,Arg2,...])

Single Row Functions Features :

•  Acts on each row returned in the query.
• Returns one result per row.
• May return a data value of  a different data type than that referenced.
• May expect one or more arguments.
• Can be used in select, where and order by clauses.
• It can be nested.

Specification Behaviour Of Functions :

Character Functions :

• Accept character input and can return both character and number values.

Number Functions :             

• Accept numeric input and return numeric values.

Date Functions :  

• Operate on values of date date type and can return both date and number.

Conversion Function :  

• Convert a value from one data type to another data type.

General Function :

• NVL - Operates on NULL values.
• DECODE - Operates on any data type and can return any data type.

At www.oracletutorialz.blogspot.com, the privacy of our visitors is of extreme importance to us. This privacy policy document outlines the types of personal information is received and collected by www.oracletutorialz.blogspot.com and how it is used.

Log Files
Like many other Web sites, www.oracletutorialz.blogspot.com makes use of log files. The information inside the log files includes internet protocol ( IP ) addresses, type of browser, Internet Service Provider ( ISP ), date/time stamp, referring/exit pages, and number of clicks to analyze trends, administer the site, track user’s movement around the site, and gather demographic information. IP addresses, and other such information are not linked to any information that is personally identifiable.

Cookies and Web Beacons
www.oracletutorialz.blogspot.com does use cookies to store information about visitors preferences, record user-specific information on which pages the user access or visit, customize Web page content based on visitors browser type or other information that the visitor sends via their browser.

Some of our advertising partners may use cookies and web beacons on our site.
These third-party ad servers or ad networks use technology to the advertisements and links that appear on www.oracletutorialz.blogspot.com send directly to your browsers. They automatically receive your IP address when this occurs. Other technologies ( such as cookies, JavaScript, or Web Beacons ) may also be used by the third-party ad networks to measure the effectiveness of their advertisements and / or to personalize the advertising content that you see.

www.oracletutorialz.blogspot.com has no access to or control over these cookies that are used by third-party advertisers.

You should consult the respective privacy policies of these third-party ad servers for more detailed information on their practices as well as for instructions about how to opt-out of certain practices. www.oracletutorialz.blogspot.com's privacy policy does not apply to, and we cannot control the activities of, such other advertisers or web sites.

If you wish to disable cookies, you may do so through your individual browser options. More detailed information about cookie management with specific web browsers can be found at the browsers' respective websites.

If you require any more information or have any questions about our privacy policy, please feel free to contact us by email at guduruphanikumar@gmail.com.

Ordering Information :

1. The order of rows returned in the result of a query undefined.
2. The ORDER BY clause can be used to sort the rows in the required order.
3. The ORDER BY clause should be the last clause in the order of all clauses in the SELECT statement.
4. An expression or an alias can be specified to ORDER BY clause for sorting.
5. Default ordering of data is ascending

• Number -> 0-9
• Dates     -> Earliest - Latest
• Strings   ->  A-Z
• NULLs -> Last.

Syntax :
 
SELECT [DISTINCT] [*] {Column1 [Alias],.....}
FROM Table_Name
[WHERE Condition(s)]
[ORDER BY {Column, Expr}[ASC?DESC]];

• The default ordering upon a column is ASCENDING, to change the default ordering DESC should be used after the column name.
• Sorting can be implemented on column Aliases, and can also be implemented upon multiple columns.
• The controversy of sorting is broken only when there arises a conflict of consistency upon the data in a column.

Examples :

1.
SELECT Ename, Job, Deptno, Hiredate
FROM Emp
ORDER BY Hiredate;

2.
SELECT Ename, Job, Deptno, Hiredate
FROM Emp
ORDER BY Hiredate DESC;


3.
SELECT Ename, Job, Sal
FROM Emp
WHERE Job = 'MANAGER'
ORDER BY Sal;

4.
SELECT Ename , Job, Sal
FROM Emp
WHERE Sal >=2500
ORDER BY Job, Ename DESC;

5.
SELECT Empno, Ename, Sal, Sal * 12 Annsal
FROM Emp
ORDER BY Annsal;;

6.
SELECT Empno, Ename, Sal
FROM Emp
ORDER BY Deptno, Sal, Hiredate;

7.
SELECT Empno, Ename, Sal
FROM Emp
WHERE Sal >=2000
ORDER BY BY Hiredate, Sal DESC;

SQL*PLUS Operators  

1. BETWEEN...AND...Operator
2. IN Operator
3. IS NULL Operator
4. LIKE Operator

1. BETWEEN...AND...Operator :

• This operator is used to display rows based on a range of values.
• The declared range is inclusive.
• The lower limit should be declared first
• The negation of this operator is NOT BETWEEN...AND...

Examples :

1.
SELECT Ename, Sal, Job
FROM Emp
WHERE Sal BETWEEN 1000 AND 1500;
2.
SELECT Ename, Sal, Job
FROM Emp
WHERE Sal  NOT BETWEEN 1000 AND 1500;

3.
SELECT Ename, Sal, Job
FROM Emp
WHERE Job BETWEEN 'MANAGER'  AND 'SALESMAN' ;

4.
SELECT Ename, Sal, Job
FROM Emp
WHERE Job NOT BETWEEN 'MANAGER'  AND 'SALESMAN' ;

5.
SELECT Ename, Sal, Job,Hiredate
FROM Emp
WHERE Hiredate BETWEEN '18-FEB-1981'  AND '20-JUN-1983' ;
6.

SELECT Ename, Sal, Job,Hiredate
FROM Emp
WHERE Hiredate NOT BETWEEN '18-FEB-1981'  AND '20-JUN-1983' ;

2. IN Operator :

The operator is used to test for values in a specified list.
The operator can be used upon any data type.
The negation of the operator is NOT IN.

Examples :

1.
SELECT Ename, Sal, Job
FROM Emp
WHERE Ename IN('FORD' , 'ALLEN');

2.
SELECT Ename, Sal, Job
FROM Emp
WHERE Ename NOT IN('FORD' , 'ALLEN');

3.
SELECT Ename, Sal, Deptno
FROM Emp
WHERE Deptno IN(10 , 30 );

4.
SELECT Ename, Sal, Deptno
FROM Emp
WHERE Deptno NOT IN(10 , 30 );

5.
SELECT Ename, Sal, Hiredate
FROM Emp
WHERE Hiredate IN( '20-FEB-1981' , '09-JUN-1981' );

6.

SELECT Ename, Sal, Hiredate
FROM Emp
WHERE Hiredate NOT IN( '20-FEB-1981' , '09-JUN-1981' );

3. IS NULL Operator :

• The operator tests for NULL values.
• It is the only operator that can be used to test for NULL'S.
  
• The negation is IS NOT NULL.

Examples :

1.
SELECT Ename,Deptno, Comm
FROM Emp
WHERE Comm IS NULL;
2.

SELECT Ename, Deptno, Job, MGR
FROM Emp
WHERE MGR IS NULL;

3.

SELECT Ename, Deptno, Comm

FROM Emp
WHERE Comm IS NOT NULL;
4.

SELECT Ename, Deptno, Job, MGR
FROM Emp
WHERE MGR IS NOT NULL;

4. LIKE Operator :

• The LIKE operator is used to search for a matching character patterns.
• The character pattern matching operation is referred as a WILD CARD SEARCH.
• The available WILD CARD in oracle are      

•                     % - Used to represent any sequence of zero or more characters.       
•                    _ -  Represents any single character, only at that position.

• The WILD CARD symbols can be used in any combination with literal characters.
• For finding exact match for '%' and '_' the ESCAPE option has to be used along with '\' symbol.

Examples :

1.
SELECT Ename, Job
FROM Emp
WHERE Ename LIKE 'S%';

2.
SELECT Ename, Job
FROM Emp
WHERE Ename NOT LIKE 'S%';

3.
SELECT Ename, Job
FROM Emp
WHERE Ename LIKE '_A%';

4.
SELECT Ename, Job
FROM Emp
WHERE Ename NOT LIKE '_A%';

5.
SELECT Ename, Job
FROM Emp
WHERE Ename LIKE 'SM%';
6.
SELECT Ename, Job
FROM Emp
WHERE Ename NOT LIKE 'SM%';

7.
SELECT Ename, Job
FROM Emp
WHERE  'SM%' LIKE Ename;

8.
SELECT Ename,Hiredate
FROM Emp
WHERE Hiredate LIKE '%-FEB-1981';

9.
SELECT Ename,Hiredate
FROM Emp
WHERE Hiredate LIKE '03-%-1981';

10.
SELECT *
FROM Dept
WHERE Dname LIKE '%\_%' ESCAPE '\';

Rules Of Operator Precedence

1. The default precedence order is

• All comparison operators
• Not operator
• And operator
• Or operator

2.  The precedence can be controlled using parenthesis.

Examples :

1.
SELECT Ename, Deptno, Job, Sal
FROM Emp
WHERE Deptno = 10 OR Job = 'SALESMAN' AND Sal > 2500 OR Sal <1500;

2.
SELECT Ename, Deptno, Job, Sal
FROM Emp
WHERE Deptno = 10 OR ( Deptno = 20 AND Job = 'SALESMAN' ) AND (Sal > 2500 OR Sal <1500);

The logical operators combine the results of two component conditions to produce a single result.

The logical operators provide by oracle are

• Logical Conjunction Operator - AND
• Logical Disjunction Operator - OR
• Logical Negation Operator - NOT

AND Operator :

• It returns TRUE if both or all component conditions are TRUE.
• It returns FALSE if either is FALSE, else returns unknown.
  

Truth table :

AND	TRUE	FALSE	NULL
TRUE	T	F	NULL
FALSE	F	F	F
NULL	NULL	F	NULL

Examples :

1.

SELECT Ename, Sal, Deptno, Job

FROM Emp

WHERE Deptno=20 AND Job='MANAGER';

2.

SELECT Empno, Ename, Sal, Job

FROM Emp

WHERE Sal>=1100 AND Job='CLERK';

3.

SELECT Ename, Sal, Job

FROM Emp

WHERE Sal>=1500 AND Sal>5000;

4.

SELECT Ename, Sal, Job

FROM Emp

WHERE Deptno=10 AND Job='CLERK';

5.

SELECT Ename, Sal, Job

FROM Emp

WHERE ( Sal>=1500 AND Sal<=5000) AND Job='MANAGER';

OR Operator :

• It returns TRUE if either of the component condition is true.
• It returns FALSE if both are FALSE, else returns unknown.

Truth table :

OR	TRUE	FALSE	NULL
TRUE	T	T	T
FALSE	T	F	NULL
NULL	T	NULL	NULL

Examples :

1.

SELECT Ename, Sal, Deptno, Job

FROM Emp

WHERE Deptno=20 OR Job='MANAGER';

2.

SELECT Empno, Ename, Sal, Job

FROM Emp

WHERE Sal>=1100 OR Job='CLERK';

3.

SELECT Ename, Sal, Job

FROM Emp

WHERE Sal>=1500 OR Sal>5000;

4.

SELECT Ename, Sal, Job

FROM Emp

WHERE Deptno=10 OR Deptno=20;

5.

SELECT Ename, Sal, Job

FROM Emp

WHERE ( Sal>=1500 OR Sal<=5000) OR Job='MANAGER';

6.

SELECT Ename, Sal, Deptno, Job

FROM Emp

WHERE Job='CLERK' OR Job='MANAGER';

NOT Operator :

• It returns TRUE if the following condition is FALSE.
• It returns FALSE if the following condition is TRUE.
• If the condition is unknown, it returns unknown.

Truth table :

NOT	TRUE	FALSE	NULL
NOT	F	F	NULL

Examples :

1.

SELECT Ename, Sal, Job

FROM Emp

WHERE NOT Job='MANAGER';

2.

SELECT Ename, Sal, Hiredate

FROM Emp

WHERE NOT Hiredate='20-FEB-81';

3.

SELECT Ename, Sal, Job

FROM Emp

WHERE NOT Sal>5000;

4.

SELECT Ename, Sal, Job,Deptno

FROM Emp

WHERE NOT Job='SALESMAN' AND Deptno=30;