function

SQL MAX Function

October 21, 2012 by techhoneyadmin

SQL MAX function returns the highest or maximum value out of the available records/values. Oracle MAX Function is opposite of SQL MIN Function.

SQL MAX Function Syntax

SELECT MAX(expression)
FROM table_name
WHERE conditions;

SQL MAX Function Examples

Let’s take an example for understanding the SQL MAX Function:

Suppose we have a table named “Employee” with the data as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Now we will see usage of SQL MAX Function in different scenarios

SQL MAX Function – SQL SELECT MAX Usage

The simplest way of using the SQL MAX Function is to fetch the maximum value from a column of table

For example, with the below Oracle MAX Function query will fetch maximum value of commission.

SELECT MAX(commission) as "MAX Commission"
FROM employee;

The above SQL SELECT MAX query will return result as “20” as that’s the highest commission available in the “Employee” table.

Note: We have aliased the MAX(commission) field as “MAX Commission”, hence once the query is run “MAX Commission” is displayed as field name of result from the Oracle MAX Function query.

NULL Values are not considered by the SQL MAX Function.

SQL MAX Function – Using SQL WHERE Clause Example

SQL MAX Function can be used in SQL SELECT Statement having WHERE Clause.

For example, below SQL SELECT MAX query will return maximum salary in ‘Sales’ department.

SELECT MAX(salary) as "Maximum Salary"
FROM employee
WHERE department  = 'Sales';

The above Oracle MAX Function query returns “32000” as maximum salary for ‘Sales’ department.
Also note that we have aliased the MAX(salary) field as “Maximum Salary”.

SQL MAX Function – Using SQL GROUP BY Clause Example

SQL MAX Function can be used in SQL SELECT Statement having SQL GROUP BY Clause.

For example, the Oracle MAX Function query below will return department name and the maximum salary being given in each department.

SELECT department as "Department Name"
       ,MAX(salary) "Maximum Salary"
FROM employee
GROUP BY department;

The data returned by the above SQL SELECT MAX query will be:

DEPARTMENT NAME	MAXIMUM SALARY
Sales	32000
IT	28000
Support	30000

Here notice that by using the SQL SELECT MAX query with SQL GROUP BY Clause we have retrieved the maximum salaries of every unique department along with the department name.

Oracle/SQL MIN Function

October 21, 2012 by techhoneyadmin

SQL MIN function returns least or minimum value out of available records/values. Oracle MIN Function is opposite of the SQL MAX Function.

SQL MIN Function Syntax

SELECT MIN(expression)
FROM table_name
WHERE conditions;

SQL MIN Function Examples

Let’s take an example for understanding the Oracle MIN Function

Suppose we have a table named “Employee” with the data as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	IT	20
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Now we will see what the SQL MIN Function does when used in different scenarios

SQL MIN Function – Simple Usage

The simplest way to use SQL MIN Function is to fetch the minimum value from a column of a table

For example, with the below Oracle MIN Function query we will be able to fetch the minimum value of commission.

SELECT MIN(commission) as "MIN Commission"
FROM employee;

The above MIN SQL Function query will return result as “10” as that’s the lowest commission available in the “Employee” table.

Also note that we have aliased the MIN(commission) field as “MIN Commission”, hence once the query is run “MIN Commission” will be displayed as the field name of the result from the Oracle MIN Function query.

NULL values are not considered by the MIN SQL Function.

SQL MIN Function – SQL WHERE Clause Example

SQL MIN Function can be used in SQL SELECT Statement having SQL WHERE Clause.

For example, below SQL MIN query returns minimum salary in ‘Sales’ department.

SELECT MIN(salary) as "Minimum Salary"
FROM employee
WHERE department  = 'Sales';

The above Oracle MIN Function query returns ‘10000’ as minimum salary in ‘Sales’ department.

Also note that we have aliased the MIN(salary) field as “Minimum Salary”.

SQL MIN Function – SQL GROUP BY Clause Example

SQL MIN Function can be used in SQL SELECT Statement having SQL GROUP BY Clause.

For example, Oracle MIN Function query below, shows department name and minimum salary of each department.

SELECT department "Department Name"
,MIN(salary) "Minimum Salary"
FROM employee
GROUP BY department;

The data returned by above MIN SQL Function query will be:

Departement Name	Minimum Salary
Sales	10000
IT	20000
Support	30000

Here notice that by using Oracle MIN Function with SQL GROUP BY Clause we have retrieved minimum salaries of every unique department along with the department name.

SQL COUNT Function

October 21, 2012 by techhoneyadmin

SQL COUNT Function is used to return number of rows fetched by a query. Oracle COUNT Function can be used to count number of rows that matches criteria set in SQL WHERE Clause.Oracle COUNT Function can be used as SQL SELECT COUNT, SQL COUNT DISTINCT, SQL COUNT Group By Statements.

SQL COUNT Function Syntax

SELECT COUNT(expression)
FROM table_name
WHERE conditions;

SQL COUNT Function Examples

Suppose we have a table named “Employee” with the data as shown below.

Employee_ID	Employee_Name	Salary	Department	Commission
101	Emp A	10000	Sales	10
102	Emp B	20000	IT	20
103	Emp C	28000	Marketing	15
104	Emp D	30000	Support
105	Emp E	32000	Sales	10

Oracle COUNT Function application in different scenarios.

SQL COUNT Function – SQL SELECT COUNT (SQL COUNT Rows) Example

SQL COUNT Function can be used on single column of table as SQL SELECT COUNT Statement.

For example, SQL SELECT COUNT query below will count number of employees from ’employee’ table.

SELECT COUNT(employee_name)
FROM employee;

SQL SELECT COUNT query above returns ‘5’ as there are 5 employees in ’employee’ table.

SQL COUNT Function – SQL WHERE Clause Example

Oracle COUNT Function can be used with SQL WHERE Clause.

For example, suppose we wish to view number of employees getting salary more than ‘21000’.

Oracle COUNT Function query below will fetch the number of employees having salary more than ‘21000’.

SELECT COUNT(*)
FROM employee
WHERE salary > 21000;

Oracle COUNT Function query above returns count of employees earning more than ‘21000’, which in our case is 3.

SQL COUNT Function – SQL COUNT DISTINCT Example

SQL COUNT DISTINCT query is use to view count of unique result.

For this we have to combine the Oracle COUNT Function with the SQL DISTINCT Clause.

For example, SQL COUNT DISTINCT query below will return the number of unique departments.

SELECT COUNT(DISTINCT(department))
FROM employee;

SQL COUNT DISTINCT query above returns ‘4’ as number of unique departments is ‘4’.

The department ‘Sales’ occurs twice in employee table and hence been counted only once.

SQL COUNT(*) Function – SQL COUNT GROUP BY Example

SQL COUNT Group By query is used to count records group wise.

For this we have to combine Oracle COUNT Function with the SQL GROUP BY Clause.

For example, the SQL COUNT Group By query below will return the department name and number of employees in each department.

SELECT department
       ,COUNT(*)
FROM employee
GROUP BY department;

SQL COUNT Group By query above returns the following data:

DEPARTMENT	*COUNT()**
Sales	2
IT	1
Marketing	1
Support	1