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.

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:

0

0

  

Related posts:

DDL Alter, Drop, Rename, Truncate

CONVERSION FUNCTIONS

ABSTRACT DATA TYPES

OBJECT VIEWS AND METHODS

PARTITIONS in Oracle Database

     

This entry was posted in Oracle, Oracle Scripts For Practice on March 25, 2013 by All Oracle Apps.

GROUP FUNCTIONS

Leave a reply

                                            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:

+21

0

  

Related posts:

Aliases

Multiple Inserts in SQL

GROUP BY AND HAVING

INDEXES

DATABASE TRIGGERS

     

This entry was posted in Oracle, Oracle Scripts For Practice on March 25, 2013 by All Oracle Apps.

SQL Practice 1

Leave a reply

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.

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:

0

0

  

Related posts:

DDL Alter, Drop, Rename, Truncate

CONVERSION FUNCTIONS

ABSTRACT DATA TYPES

OBJECT VIEWS AND METHODS

PARTITIONS in Oracle Database

     

This entry was posted in Oracle, Oracle Scripts For Practice on March 25, 2013 by All Oracle Apps.

GROUP FUNCTIONS

Leave a reply

                                            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:

+21

0

  

Related posts:

Aliases

Multiple Inserts in SQL

GROUP BY AND HAVING

INDEXES

DATABASE TRIGGERS

     

This entry was posted in Oracle, Oracle Scripts For Practice on March 25, 2013 by All Oracle Apps.

SQL Practice 1

Leave a reply

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: