SQL
FUNCTIONS
LOWER function:
- The lower function converts all letters in the specified string to lowercase.
1
|
SQL>
SELECT 'ORACLE' String,LOWER('ORACLE') Lower FROM DUAL;
|
Output:
- If the
all employee names( Ename)and job columns was retrieved from the emp table
by the below SQL, the value returned would be lower case only, and others
columns data will display the normally.
1
|
SQL>
SELECT LOWER(Ename) Ename,LOWER(Job) Job,Sal,HireDate FROM Emp;
|
Output:
UPPER Function:
·
The
Oracle UPPER function is used to convert strings to all upper case (i.e.
capitals).
1
|
SQL>
SELECT 'oracle' String,UPPER('oracle') Upper FROM DUAL;
|
Output:
INITCAP Function:
·
INITCAP returns char, with the first letter of each word in uppercase,
all other letters in lowercase.
1
|
SQL>
SELECT 'oracle corporation' String, INITCAP('oracle corporation') InitCap FROM DUAl;
|
Output:
CONCAT Function:
Oracle CONCAT( ) function only
allows two arguments — only two strings can be put together at a time using
this function. However, it is possible to concatenate more than two strings at
a time in Oracle using ‘||’.
1
2
|
SQL> SELECT 'Oracle' String1,'Corporation' String2,
CONCAT('Oracle', 'Corporation') Concat FROM DUAL;
|
Output:
SUBSTR Function:
·
The substr functions allows you to extract a substring from
a string.
Syntax:
Substr(string,start_position,[length])
·
string is the source string.
·
start_position is the position for extraction. The first
position in the string is always 1.
·
length is optional. It is the number of characters
to extract. If this parameter is omitted, substr will return the
entire string.
NOTE:
·
If start_position is 0, then substr treats start_position as
1 (ie: the first position in the
string).
·
If start_position is a positive number, then substr starts from the beginning of the string.
·
If start_position is a negative number, then substr starts from the end of the string and
counts backwards.
·
If length is a negative number, then substr will return a NULL value.
Examples:
·
The below
Query returns 5 characters starting in position 0.
Output:
·
The below
query returns 3 characters starting in position 4 in job column from emp table
Output:
LENGTH Function:
·
The length function returns the length of the
specified string.
·
Now we
want to select the length of the values in the “ORACLE” column below.
1
|
SELECT
'ORACLE' String, LENGTH('ORACLE') Length FROM DUAL;
|
Output:
INSTR Function:
·
The instr function returns the location of a substring
in a string.
Syntax:
Instr(string1,string2[,start_position[,nth_appearance]])
·
string1 is the string to search.
·
string2 is the substring to search for in string1.
·
start_position is the position in string1 where the search will start. This argument
is optional. If omitted, it defaults to 1. The first position in the string is
1. If the start_position is negative,
the function counts backstart_position number
of characters from the end of string1 and
then searches towards the beginning of string1.
·
nth_appearance is the nth appearance of string2. This is optional. If omitted, it defaults to
1.
Note: If string2 is not found in string1, then the instr Oracle
function will return 0.
Examples:
1
|
SELECT
'STRING' Original,INSTR ('STRING' , 'R') InString FROM DUAL;
|
Output: 
1
|
SQL>SELECT 'STRING'
Original,INSTR('STRING' , 'RIN', 2) InString FROM DUAL;
|
Output:
Padding Functions LPAD, RPAD:
LPAD:
The lpad function pads the left-side of a string with a
specific set of characters (when string1 is
not null).
The following example
left-pads a string with the characters “*” and “.”.
1
|
SQL>SELECT
'Page 1' MyString, LPAD('Page 1', 15, '*.') LPadded FROM DUAL;
|
Output:
RPAD:
The rpad function pads the right-side of a string with
a specific set of characters (when string1 is not null).
The following example right-pads
a string with the characters “*” and ”.”
1
|
SQL>
SELECT 'Page 1' String1, RPAD('Page 1' , 15, '*.') RPad FROM DUAL;
|
Output:
Trim Functions LTRIM, RTRIM, TRIM:
LTRIM:
The ltrim function removes all specified characters
from the left-hand side of a string.The ltrim function may
appear to remove patterns, but this is not the case as
demonstrated in the following example.
1
2
|
SQL>SELECT
'xyzXxyLAST WORD' OrgStr,LTRIM('xyzXxyLAST WORD', 'xy') Ltrim
FROM
DUAL;
|
It actually removes the
individual occurrences of ‘x’ and ‘y’, as opposed to the pattern of ’xy’.
Output:
RTRIM:
The rtrim function removes all specified characters
from the right-hand side of a string.The rtrim function may
appear to remove patterns, but this is not the case as
demonstrated in the following example.
1
|
SELECT
'BROWNINGyxXxy' String,RTRIM('BROWNINGyxXxy', 'xy') Rtrim FROM DUAL;
|
It actually removes the
individual occurrences of ‘x’and ‘y’, as opposed to the pattern of ‘xy’.
·
Output:
TRIM:The trim function removes all specified characters
either from the beginning or the ending of a string.
The following query removes the character ”S” from a string.
TRIM:The trim function removes all specified characters
either from the beginning or the ending of a string.
The following query removes the character ”S” from a string.
1
|
SQL>SELECT
'MITHSS' String,TRIM('S' FROM 'MITHSS') Trimmed FROM DUAL;
|
Output:
Replace Function:
The replace function replaces a sequence of characters
in a string with another set of characters.The following example replaces
occurrences of J with BL:
1
|
SELECT
'JACK AND JUE' OrgStr, REPLACE('JACK AND JUE' , 'J', 'BL') Replace FROM DUAL;
|
Output:
Translate Function:
The translate function replaces a sequence of
characters in a string with another set of characters. However, it replaces a
single character at a time. For example, it will replace the 1st character in
the string_to_replace with the 1st character in
the replacement_string. Then it will replace the 2nd
character in the string_to_replace with the 2nd
character in the replacement_string, and so on.
1
2
|
SQL>
SELECT Ename, Job,TRANSLATE(Job, 'P', ' ') Translate
FROM Emp WHERE Job = 'PRESIDENT';
|
Output:
ASCII Functions:
The ascii function returns the NUMBER code that
represents the specified character.
1
|
SQL>SELECT
'Ascii Code'||CHR(67)||CHR(65)||CHR(84)||CHR(10) Sample FROM DUAL;
|
Output:
SIGN Function:
The sign function returns a value indicating the sign
of a number.
1
|
SQL>
SELECT Ename, Sal, Comm FROM Emp WHERE SIGN(Sal - Comm) = -1;
|
Output:
ADD MONTHS Function:
The add_months function returns a date plus n months.
1
2
|
SQL>SELECT
SYSDATE ToDay, ADD_MONTHS(SYSDATE, 3) "3 Months",
ADD_MONTHS(SYSDATE, -3) "-3 Months" FROM DUAL;
|
Output:
MONTHS BETWEEN Function:
The months_between function returns the number of
months between date1 and date2.
1
|
SQL>SELECT
Ename, Sal, SYSDATE Today, HireDate,MONTHS_BETWEEN(SYSDATE, HireDate) "Months"FROM Emp;
|
Output:
NEXT DAY Function:
The next_day function returns the first weekday that
is greater than a date.
1
2
3
4
5
6
|
SQL>SELECT
SYSDATE Today,
NEXT_DAY(SYSDATE, 'FRI') "Friday",
NEXT_DAY(SYSDATE, 6) "Friday" ,
NEXT_DAY(SYSDATE, 'FRIDAY') "Friday" ,
NEXT_DAY(SYSDATE, 'WED') "Wednesday"
FROM
DUAL;
|
Output:
LAST DAY Function:
The last_day function returns the last day of the
month based on a date value.
1
|
SQL>SELECT
SYSDATE Today, LAST_DAY(SYSDATE) LastDay FROM DUAL;
|
Output:
ROUND and TRUNC Functions:
TRUNC and ROUND function looks
similar but not exactly.
ROUND function used to round the
number to the nearest while TRUNC used to truncate/delete the number from some
position. Some cases both returns same result.
1
|
SQL>SELECT
SYSDATE Today,ROUND(SYSDATE) Round,TRUNC(SYSDATE) Trunc FROM DUAL;
|
Output:
TO CHAR Function:
The TO_CHAR function converts a
DATETIME, number, or NTEXT expression to a TEXT expression in a specified
format. This function is typically used to format output data.
1
2
|
SQL>SELECT
Ename, Sal, Comm,TO_CHAR(Sal - Comm, '9999MI') Diff FROM Emp
WHERE Comm IS NOT NULL
|
Output:
SYSDATE using different Parameters:
The sysdate function returns the current system date
and time on your local database.
1
2
3
4
|
SQL>SELECT
Ename, Sal,HireDate||TO_CHAR(HireDate, ' B.C.') HireDate,
TO_CHAR(SYSDATE, 'P.M.') Meridian,
TO_CHAR(SYSDATE, ' B.C.')
FROM Emp;
|
Output:
TO NUMBER Function:
The to_number function converts a string to a number.
1
2
3
4
5
|
SQL>SELECT
'ALL ORACLE APPS' OrgName,
TRANSLATE('ALL ORACLE APPS',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'1234567890!@#$%^&*()-=_+;,.') EncryptedName
FROM
DUAL;
|
Output:
Related posts:
Group
Functions
Average, Sum, Distinct, Maximum, Minimum, Count:
Average:
The Avg function returns
the average value of an expression.
1
|
SQL>SELECT
AVG(Sal) FROM Emp;
|
Output: 
Sum:
The SQL SUM function returns the summed value of an
expression.
1
|
SQL>SELECT
SUM(Sal), SUM(Comm) FROM Emp;
|
Output:
Maxmimum and Minimum:
The SQL MAX and MIN function returns the maximum
and minimum value of an expression.
1
|
SQL>SELECT
MAX(HireDate), MIN(HireDate) FROM Emp;
|
Output:
Count:
The SQL COUNT function returns the number of rows
in a query.
1
|
SQL>SELECT
COUNT(MGR) FROM Emp;
|
Output:
Group By; Group By with Having; Group By with
Where, Having and Order By:
Group By:
The SQL GROUP BY clause can be used in an SQL
SELECT statement to collect data across multiple records and group the
results by one or more columns.
1
|
SQL>SELECT
Deptno FROM Emp GROUP BY Deptno;
|
Output:
Group By with Having:
- Oracle
GROUP BY HAVING can be used to limit the returned rows after the
grouping. With Oracle GROUP BY HAVING, the HAVING clause is applied
after the GROUP BY has taken place.
- Oracle
GROUP BY HAVING will group values that have a particular value. Oracle
GROUP BY HAVING can be used in conjunction with other logical functions
such as MIN, MAX, COUNT, and SUM.
- With
Oracle GROUP BY HAVING, the HAVING clause filters rows after the grouping
with the Oracle GROUP BY clause.An example of Oracle GROUP BY HAVING using
the MAX function can be seen below:
1
2
3
4
|
SQL>SELECT
Deptno, AVG(Sal)
FROM Emp
GROUP
BY Deptno
HAVING
MAX(Sal) > 2900;
|
Output:
Group By with Where:
1
2
3
4
|
SQL>SELECT
Deptno, SUM(Sal)
FROM
Emp
WHERE Deptno <> 10
GROUP BY Deptno;
|
Output:
Having clause:
The SQL HAVING clause is used in
combination with the SQL Group By Clause. It can be used in an SQL
Select Statement to filter the records that a SQL GROUP BY returns.
1
2
3
4
5
|
SQL>SELECT Deptno, MIN(Sal), MAX(Sal)
FROM
Emp
WHERE Job = 'CLERK'
GROUP BY Deptno
HAVING MIN(Sal) < 1000;
|
Output:
Order By:
The SQL ORDER BY clause allows
you to sort the records in your result set. The SQL ORDER BY clause can only be
used in SQL Select Statement.
1
2
3
4
5
6
|
SQL>SELECT Job, SUM(Sal) Payroll
FROM Emp
WHERE
Job NOT LIKE 'SALES%'
GROUP
BY Job
HAVING
SUM(Sal) > 5000
ORDER
BY SUM(Sal);
|
Output:
Related posts:
SQL
Practice – 1
If we want to retrieve all of the information about
all of the customers in the Employees table, we could use the asterisk (*) as a
shortcut for all of the columns, and our query looks like
1
|
Select
* from emp;
|
Output:
If we want only specific columns (as is usually the
case), we can/should explicitly specify them in a comma-separated list, as in
1
|
Select
ename,sal,hiredate,job from emp;
|
Output:
We want to select only the distinct values from the
column named “deptno” from the emp table
1
|
select
distinct deptno from emp;
|
Ouput:
We want to select only the employees whose working
as “Manager” from the emp table.
1
|
Select
ename,sal,deptno,job from emp where job= ‘MANAGER’;
|
Ouput:
We could also use the NOT operator, to fetch those rows that are not
between the specified columns.
1
|
select
* from emp where not(job <> ‘SALESMAN’ AND Deptno <> 30);
|
Ouput:
To get a list of employees who were hired on or
after a given date, you would write like this
1
|
Select
ename,sal,deptno,hiredate from emp where hiredate> ’03-DEC-81’;
|
Ouput:
We wanted to see if the employees name is”FORD” and
” ALLEN”, we would write
1
|
Select
ename,sal,job from emp where ename in(‘FORD’,’ALLEN’);
|
Ouput:
This query retrieves data only from those rows of
the emp table where the Comm is NULL.
1
|
Select
ename,sal,comm from emp where comm is null;
|
Output:
To select employees whose name begins with
‘S’
1
|
Select ename,job from emp where ename LIKE ‘S%’;
|
Output:
It is also possible to select the employees does
NOT contain the pattern “S” from the “emp” table, by using the NOT keyword.
1
|
Select
ename,job from emp where ename NOT LIKE ‘S%’;
|
Ouput:
If you want to sort the emp table by the name, the
query would be like,
1
|
Select ename,sal,deptno,job from emp ORDER By Ename;
|
Ouput:
By default, the ORDER BY Clause sorts data in
ascending order. If you want to sort the data in descending order, you must
explicitly specify it as shown below
1
|
Select
ename,sal,deptno,job from emp order by deptno,sal,ename desc;
|
Ouput:
If you want to sort the emp table by the
sal+nvl(comm.,0), the query would be like,
1
|
Select
ename,sal,comm,sal+nvl(comm,0) totalsal from emp order by sal+nvl(comm,0);
|
Ouput:
NOTE:The columns specified in ORDER BY clause should be
one of the columns selected in the SELECT column list.
You can represent the columns in the ORDER BY
clause by specifying the position of a column in the SELECT list, instead of
writing the column name.
The above query can also be written as given below,
1
|
Select
ename,sal,comm,sal+nvl(comm,0) totsal from emp order by 4;
|
Ouput:
SQL
FUNCTIONS
LOWER function:
- The lower function converts all letters in the
specified string to lowercase.
1
|
SQL>
SELECT 'ORACLE' String,LOWER('ORACLE') Lower FROM DUAL;
|
Output:
- If the
all employee names( Ename)and job columns was retrieved from the emp table
by the below SQL, the value returned would be lower case only, and others
columns data will display the normally.
1
|
SQL>
SELECT LOWER(Ename) Ename,LOWER(Job) Job,Sal,HireDate FROM Emp;
|
Output:
UPPER Function:
·
The
Oracle UPPER function is used to convert strings to all upper case (i.e.
capitals).
1
|
SQL>
SELECT 'oracle' String,UPPER('oracle') Upper FROM DUAL;
|
Output:
INITCAP Function:
·
INITCAP returns char, with the first letter of each word in uppercase,
all other letters in lowercase.
1
|
SQL>
SELECT 'oracle corporation' String, INITCAP('oracle corporation') InitCap FROM DUAl;
|
Output:
CONCAT Function:
Oracle CONCAT( ) function only
allows two arguments — only two strings can be put together at a time using
this function. However, it is possible to concatenate more than two strings at
a time in Oracle using ‘||’.
1
2
|
SQL> SELECT 'Oracle' String1,'Corporation' String2,
CONCAT('Oracle', 'Corporation') Concat FROM DUAL;
|
Output:
SUBSTR Function:
·
The substr functions allows you to extract a substring from
a string.
Syntax:
Substr(string,start_position,[length])
·
string is the source string.
·
start_position is the position for extraction. The first
position in the string is always 1.
·
length is optional. It is the number of characters
to extract. If this parameter is omitted, substr will return the
entire string.
NOTE:
·
If start_position is 0, then substr treats start_position as
1 (ie: the first position in the
string).
·
If start_position is a positive number, then substr starts from the beginning of the string.
·
If start_position is a negative number, then substr starts from the end of the string and
counts backwards.
·
If length is a negative number, then substr will return a NULL value.
Examples:
·
The below
Query returns 5 characters starting in position 0.
Output:
·
The below
query returns 3 characters starting in position 4 in job column from emp table
Output:
LENGTH Function:
·
The length function returns the length of the
specified string.
·
Now we
want to select the length of the values in the “ORACLE” column below.
1
|
SELECT
'ORACLE' String, LENGTH('ORACLE') Length FROM DUAL;
|
Output:
INSTR Function:
·
The instr function returns the location of a substring
in a string.
Syntax:
Instr(string1,string2[,start_position[,nth_appearance]])
·
string1 is the string to search.
·
string2 is the substring to search for in string1.
·
start_position is the position in string1 where the search will start. This argument
is optional. If omitted, it defaults to 1. The first position in the string is
1. If the start_position is negative,
the function counts backstart_position number
of characters from the end of string1 and
then searches towards the beginning of string1.
·
nth_appearance is the nth appearance of string2. This is optional. If omitted, it defaults to
1.
Note: If string2 is not found in string1, then the instr Oracle
function will return 0.
Examples:
1
|
SELECT
'STRING' Original,INSTR ('STRING' , 'R') InString FROM DUAL;
|
Output:
1
|
SQL>SELECT 'STRING'
Original,INSTR('STRING' , 'RIN', 2) InString FROM DUAL;
|
Output:
Padding Functions LPAD, RPAD:
LPAD:
The lpad function pads the left-side of a string with a
specific set of characters (when string1 is
not null).
The following example
left-pads a string with the characters “*” and “.”.
1
|
SQL>SELECT
'Page 1' MyString, LPAD('Page 1', 15, '*.') LPadded FROM DUAL;
|
Output:
RPAD:
The rpad function pads the right-side of a string with
a specific set of characters (when string1 is not null).
The following example right-pads
a string with the characters “*” and ”.”
1
|
SQL>
SELECT 'Page 1' String1, RPAD('Page 1' , 15, '*.') RPad FROM DUAL;
|
Output:
Trim Functions LTRIM, RTRIM, TRIM:
LTRIM:
The ltrim function removes all specified characters
from the left-hand side of a string.The ltrim function may
appear to remove patterns, but this is not the case as
demonstrated in the following example.
1
2
|
SQL>SELECT
'xyzXxyLAST WORD' OrgStr,LTRIM('xyzXxyLAST WORD', 'xy') Ltrim
FROM
DUAL;
|
It actually removes the
individual occurrences of ‘x’ and ‘y’, as opposed to the pattern of ’xy’.
Output:
RTRIM:
The rtrim function removes all specified characters
from the right-hand side of a string.The rtrim function may
appear to remove patterns, but this is not the case as
demonstrated in the following example.
1
|
SELECT
'BROWNINGyxXxy' String,RTRIM('BROWNINGyxXxy', 'xy') Rtrim FROM DUAL;
|
It actually removes the
individual occurrences of ‘x’and ‘y’, as opposed to the pattern of ‘xy’.
·
Output:TRIM:The trim function removes all specified characters
either from the beginning or the ending of a string.
The following query removes the character ”S” from a string.
TRIM:The trim function removes all specified characters
either from the beginning or the ending of a string.
The following query removes the character ”S” from a string.
1
|
SQL>SELECT
'MITHSS' String,TRIM('S' FROM 'MITHSS') Trimmed FROM DUAL;
|
Output:
Replace Function:
The replace function replaces a sequence of characters
in a string with another set of characters.The following example replaces
occurrences of J with BL:
1
|
SELECT
'JACK AND JUE' OrgStr, REPLACE('JACK AND JUE' , 'J', 'BL') Replace FROM DUAL;
|
Output:
Translate Function:
The translate function replaces a sequence of
characters in a string with another set of characters. However, it replaces a
single character at a time. For example, it will replace the 1st character in
the string_to_replace with the 1st character in
the replacement_string. Then it will replace the 2nd
character in the string_to_replace with the 2nd
character in the replacement_string, and so on.
1
2
|
SQL>
SELECT Ename, Job,TRANSLATE(Job, 'P', ' ') Translate
FROM Emp WHERE Job = 'PRESIDENT';
|
Output:
ASCII Functions:
The ascii function returns the NUMBER code that
represents the specified character.
1
|
SQL>SELECT
'Ascii Code'||CHR(67)||CHR(65)||CHR(84)||CHR(10) Sample FROM DUAL;
|
Output:
SIGN Function:
The sign function returns a value indicating the sign
of a number.
1
|
SQL>
SELECT Ename, Sal, Comm FROM Emp WHERE SIGN(Sal - Comm) = -1;
|
Output:
ADD MONTHS Function:
The add_months function returns a date plus n months.
1
2
|
SQL>SELECT
SYSDATE ToDay, ADD_MONTHS(SYSDATE, 3) "3 Months",
ADD_MONTHS(SYSDATE, -3) "-3 Months" FROM DUAL;
|
Output:
MONTHS BETWEEN Function:
The months_between function returns the number of
months between date1 and date2.
1
|
SQL>SELECT
Ename, Sal, SYSDATE Today, HireDate,MONTHS_BETWEEN(SYSDATE, HireDate) "Months"FROM Emp;
|
Output:
NEXT DAY Function:
The next_day function returns the first weekday that
is greater than a date.
1
2
3
4
5
6
|
SQL>SELECT
SYSDATE Today,
NEXT_DAY(SYSDATE, 'FRI') "Friday",
NEXT_DAY(SYSDATE, 6) "Friday" ,
NEXT_DAY(SYSDATE, 'FRIDAY') "Friday" ,
NEXT_DAY(SYSDATE, 'WED') "Wednesday"
FROM
DUAL;
|
Output:
LAST DAY Function:
The last_day function returns the last day of the
month based on a date value.
1
|
SQL>SELECT
SYSDATE Today, LAST_DAY(SYSDATE) LastDay FROM DUAL;
|
Output:
ROUND and TRUNC Functions:
TRUNC and ROUND function looks
similar but not exactly.
ROUND function used to round the
number to the nearest while TRUNC used to truncate/delete the number from some
position. Some cases both returns same result.
1
|
SQL>SELECT
SYSDATE Today,ROUND(SYSDATE) Round,TRUNC(SYSDATE) Trunc FROM DUAL;
|
Output:
TO CHAR Function:
The TO_CHAR function converts a
DATETIME, number, or NTEXT expression to a TEXT expression in a specified
format. This function is typically used to format output data.
1
2
|
SQL>SELECT
Ename, Sal, Comm,TO_CHAR(Sal - Comm, '9999MI') Diff FROM Emp
WHERE Comm IS NOT NULL
|
Output:
SYSDATE using different Parameters:
The sysdate function returns the current system date
and time on your local database.
1
2
3
4
|
SQL>SELECT
Ename, Sal,HireDate||TO_CHAR(HireDate, ' B.C.') HireDate,
TO_CHAR(SYSDATE, 'P.M.') Meridian,
TO_CHAR(SYSDATE, ' B.C.')
FROM Emp;
|
Output:
TO NUMBER Function:
The to_number function converts a string to a number.
1
2
3
4
5
|
SQL>SELECT
'ALL ORACLE APPS' OrgName,
TRANSLATE('ALL ORACLE APPS',
'ABCDEFGHIJKLMNOPQRSTUVWXYZ',
'1234567890!@#$%^&*()-=_+;,.') EncryptedName
FROM
DUAL;
|
Output:
Related posts:
Group
Functions
Average, Sum, Distinct, Maximum, Minimum, Count:
Average:
The Avg function returns
the average value of an expression.
1
|
SQL>SELECT
AVG(Sal) FROM Emp;
|
Output:
Sum:
The SQL SUM function returns the summed value of an
expression.
1
|
SQL>SELECT
SUM(Sal), SUM(Comm) FROM Emp;
|
Output:
Maxmimum and Minimum:
The SQL MAX and MIN function returns the maximum
and minimum value of an expression.
1
|
SQL>SELECT
MAX(HireDate), MIN(HireDate) FROM Emp;
|
Output:
Count:
The SQL COUNT function returns the number of rows
in a query.
1
|
SQL>SELECT
COUNT(MGR) FROM Emp;
|
Output:
Group By; Group By with Having; Group By with
Where, Having and Order By:
Group By:
The SQL GROUP BY clause can be used in an SQL
SELECT statement to collect data across multiple records and group the
results by one or more columns.
1
|
SQL>SELECT
Deptno FROM Emp GROUP BY Deptno;
|
Output:
Group By with Having:
- Oracle
GROUP BY HAVING can be used to limit the returned rows after the
grouping. With Oracle GROUP BY HAVING, the HAVING clause is applied
after the GROUP BY has taken place.
- Oracle
GROUP BY HAVING will group values that have a particular value. Oracle
GROUP BY HAVING can be used in conjunction with other logical functions
such as MIN, MAX, COUNT, and SUM.
- With
Oracle GROUP BY HAVING, the HAVING clause filters rows after the grouping
with the Oracle GROUP BY clause.An example of Oracle GROUP BY HAVING using
the MAX function can be seen below:
1
2
3
4
|
SQL>SELECT
Deptno, AVG(Sal)
FROM Emp
GROUP
BY Deptno
HAVING
MAX(Sal) > 2900;
|
Output:
Group By with Where:
1
2
3
4
|
SQL>SELECT
Deptno, SUM(Sal)
FROM
Emp
WHERE Deptno <> 10
GROUP BY Deptno;
|
Output:
Having clause:
The SQL HAVING clause is used in
combination with the SQL Group By Clause. It can be used in an SQL
Select Statement to filter the records that a SQL GROUP BY returns.
1
2
3
4
5
|
SQL>SELECT Deptno, MIN(Sal), MAX(Sal)
FROM
Emp
WHERE Job = 'CLERK'
GROUP BY Deptno
HAVING MIN(Sal) < 1000;
|
Output:
Order By:
The SQL ORDER BY clause allows
you to sort the records in your result set. The SQL ORDER BY clause can only be
used in SQL Select Statement.
1
2
3
4
5
6
|
SQL>SELECT Job, SUM(Sal) Payroll
FROM Emp
WHERE
Job NOT LIKE 'SALES%'
GROUP
BY Job
HAVING
SUM(Sal) > 5000
ORDER
BY SUM(Sal);
|
Output:
Related posts:
SQL
Practice – 1
If we want to retrieve all of the information about
all of the customers in the Employees table, we could use the asterisk (*) as a
shortcut for all of the columns, and our query looks like
1
|
Select
* from emp;
|
Output:
If we want only specific columns (as is usually the
case), we can/should explicitly specify them in a comma-separated list, as in
1
|
Select
ename,sal,hiredate,job from emp;
|
Output:
We want to select only the distinct values from the
column named “deptno” from the emp table
1
|
select
distinct deptno from emp;
|
Ouput:
We want to select only the employees whose working
as “Manager” from the emp table.
1
|
Select
ename,sal,deptno,job from emp where job= ‘MANAGER’;
|
Ouput:
We could also use the NOT operator, to fetch those rows that are not
between the specified columns.
1
|
select
* from emp where not(job <> ‘SALESMAN’ AND Deptno <> 30);
|
Ouput:
To get a list of employees who were hired on or
after a given date, you would write like this
1
|
Select
ename,sal,deptno,hiredate from emp where hiredate> ’03-DEC-81’;
|
Ouput:
We wanted to see if the employees name is”FORD” and
” ALLEN”, we would write
1
|
Select
ename,sal,job from emp where ename in(‘FORD’,’ALLEN’);
|
Ouput:
This query retrieves data only from those rows of
the emp table where the Comm is NULL.
1
|
Select
ename,sal,comm from emp where comm is null;
|
Output:
To select employees whose name begins with
‘S’
1
|
Select ename,job from emp where ename LIKE ‘S%’;
|
Output:
It is also possible to select the employees does
NOT contain the pattern “S” from the “emp” table, by using the NOT keyword.
1
|
Select
ename,job from emp where ename NOT LIKE ‘S%’;
|
Ouput:
If you want to sort the emp table by the name, the
query would be like,
1
|
Select ename,sal,deptno,job from emp ORDER By Ename;
|
Ouput:
By default, the ORDER BY Clause sorts data in
ascending order. If you want to sort the data in descending order, you must
explicitly specify it as shown below
1
|
Select
ename,sal,deptno,job from emp order by deptno,sal,ename desc;
|
Ouput:
If you want to sort the emp table by the
sal+nvl(comm.,0), the query would be like,
1
|
Select
ename,sal,comm,sal+nvl(comm,0) totalsal from emp order by sal+nvl(comm,0);
|
Ouput:
NOTE:The columns specified in ORDER BY clause should be
one of the columns selected in the SELECT column list.
You can represent the columns in the ORDER BY
clause by specifying the position of a column in the SELECT list, instead of
writing the column name.
The above query can also be written as given below,
1
|
Select
ename,sal,comm,sal+nvl(comm,0) totsal from emp order by 4;
|
Ouput:
No comments:
Post a Comment