Tech Honey

The #1 Website for Oracle PL/SQL, OA Framework and HTML

SQL STDDEV/Standard Deviation Function

by

SQL STDDEV FunctionSQL Standard Deviation or SQL STDDEV Function returns the standard deviation of available records. Oracle Standard Deviation or Oracle STDDEV Function can accept a column name or formula as parameter for Standard Deviation calculation.

SQL Standard Deviation Function Syntax

SELECT STDDEV(numeric column / formula)
FROM table_name
WHERE conditions;

SQL Standard Deviation 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 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Oracle STDDEV Function application is shown below.

SQL Standard Deviation – SQL STDDEV Simple Usage Example

SQL Standard Deviation query below will return the standard deviation of salaries of all employees.

SELECT STDDEV(salary) "SQL STDDEV"
FROM employee;

Above Oracle Standard Deviation query returns ‘9055.38513813742’ as standard deviation of all salaries in ‘employee’ table.

Note: We have aliased STDDEV(salary) as SQL STDDEV.

Oracle Standard Deviation – Oracle STDDEV Using Formula Example

SQL Standard Deviation query below calculates the Standard Deviation of Salary*Commission for ’employee’ table.

SELECT STDDEV(salary*(commission/100)) "Oracle STDDEV"
FROM employee;

Above Oracle Standard Deviation query returns ‘1913.98362932741’ as standard deviation of all salary*(commission/100) of ‘employee’ table.

Note: We have aliased STDDEV(salary*(commission/100)) as Oracle STDDEV.

SQL VARIANCE Function

by

Oracle VARIANCE FunctionSQL VARIANCE Function returns variance of available records. Oracle VARIANCE Function accepts column/formula as parameter for Variance calculation.

SQL VARIANCE Function Syntax

SELECT VARIANCE(numeric column / formula)
FROM table_name
WHERE conditions;

SQL VARIANCE 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 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

SQL VARIANCE examples are mentioned below

SQL VARIANCE Function – Simple Usage

A simple usage of Oracle VARIANCE Function will be to fetch the variance of a table column.

For example, below SQL VARIANCE query returns variance of salary of employees

SELECT VARIANCE(salary) "Oracle VARIANCE"
FROM employee;

The above Oracle VARIANCE query returns ‘82000000’ as variance of salaries in ‘employee’ table.

Note: We have aliased VARIANCE(salary) as Oracle VARIANCE.

SQL VARIANCE Function – Using Formula Example

Oracle VARIANCE Function accepts formula also as parameter.

For example, the Oracle VARIANCE query returns variance of salary*(commission/100) from employee table.

SELECT salary*(commission/100) "SQL VARIANCE"
FROM employee;

Above Oracle VARIANCE query returns ‘3663333.33333333’ as variance of salary*(commission/100) from employee table.

Note: We have aliased salary*(commission/100) as “SQL VARIANCE”.

SQL AVG/Average Function

by

SQL AVG FunctionOracle SQL Average Function or SQL AVG Function returns average of available records. Oracle Average Function or Oracle AVG Function accepts column/formula as parameter.

SQL Average Function Syntax

SELECT AVG(numeric column / formula)
FROM table_name
WHERE conditions;

SQL Average 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 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

We will learn using Oracle SQL AVG Function below.

SQL Average Function – Oracle AVG Simple Usage

Oracle AVG Function query below returns the average of salaries of all employees from ’employee’ table.

SELECT AVG(salary) "Oracle SQL Average"
FROM employee;

Above Oracle Average Function query returns ‘24000’ as the average of salaries from employee table.

Note: We have aliased AVG(Salary) as Oracle SQL Average.

SQL Average Function – SQL AVG Using Formula Example

Oracle SQL Average Function accepts formula for calculation.

The SQL AVG Function query below return Average of Salary*(commission/100) of ‘Sales’ department.

SELECT AVG(salary*(commission/100)) "SQL AVG"
FROM employee
WHERE department  = 'Sales';

Above Oracle Average Function query returns ‘2100’ as the average of Salary*(commission/100) of ‘Sales’ department from ’employee’ table.

Note: We have aliased AVG(salary*(commission/100)) as SQL AVG.

SQL Average Function – Using SQL Group By Clause

SQL AVG Function can be used with the SQL Group By Clause.

For example, the Oracle AVG Function query below return the average salary in each department.

SELECT department, AVG(salary) Total_Salary
FROM employee
GROUP BY department;

Above Oracle SQL Average Function query returns following data:

Department Average_Salary
Sales 21000
IT 24000
Support 30000

As we can understand that by using SQL GROUP BY Clause with Oracle Average Function we can fetch average salary of employee(s) in every unique department.

SQL SUM Function

by

SQL SUM FunctionSQL SUM Function returns total or summed up value of the available records.Oracle SUM Function can accept a column or field of database table or a formula as parameter.

SQL SUM Function Syntax

SELECT SUM(numeric column / formula)
FROM table_name
WHERE conditions;

SQL SUM 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 IT 20
104 Emp D 30000 Support
105 Emp E 32000 Sales 10

Now we will see what Oracle SUM Function does when used in different scenarios

SQL SUM Function – SQL SELECT SUM Example

Suppose we wish to view total ‘Salary’ of all employees from ‘employee’ table.

SQL SELECT SUM query below will return total salary given to all employees.

 SELECT SUM(salary) Total_Salary
 FROM employee;

SQL SELECT SUM Function query above returns ‘120,000’ because total salaries of all employees is ‘120,000’.

Note: SQL SELECT SUM Function query above uses “Total_Salary” as alias name for SUM(Salary) query result column.

SQL SUM Function -Using Formula Example

SQL SUM Function can also accept a formula as parameter for summing up values.

For example, Oracle SUM Function query below returns total commission of employees from ‘Sales’ department.

 SELECT SUM(salary*(commission/100)) Commission_Amount
 FROM employee
 WHERE department  = 'Sales';

SQL SUM Function query above returns ‘4200’ as commission of ‘Sales’ department.

Note: Oracle SUM Function query above also uses the SQL WHERE Clause.

SQL SUM Function -SQL SUM Group By Example

SQL SUM Function can be used with SQL GROUP BY Clause to form SQL SUM Group By query.

For example, SQL SUM Group By Function query below returns total salary for each department.

 SELECT department, SUM(salary) Total_Salary
 FROM employee
 GROUP BY department;

SQL SUM Group By query above returns following data:

Department Total_Salary
Sales 42000
IT 48000
Support 30000

By using the SQL Group By Clause we have fetched the total salary of every unique department.

INDEX in Oracle SQL – PLSQL

by

In Oracle SQL /PLSQL an INDEX is basically a performance tuning method which allows us to retrieve or fetch the desired records faster.
An INDEX will create an entry for each value that is stored in the indexed columns.

Syntax for creating an INDEX in Oracle SQL / PLSQL is:

CREATE [UNIQUE] INDEX index_name
ON TABLE table_name (column_name1,column_name2, . . . ,column_nameN)
[COMPUTE STATISTICS];

  • The keyword “UNIQUE” indicates that value / combination of values in the indexed column should be unique.
  • COMPUTE STATISTICS keyword tells Oracle to collect the statistics when the index is being created, these statistics are then used by the Oracle Optimizer to choose the best plan for the execution of any SQL statement.

Example:

CREATE INDEX employee_index
ON TABLE employee (employee_id);

Here we have created an index named ‘employee_index’ on the ‘employee’ table it contains only one column ‘employee_id’.

We can create indexes with more than one columns as shown below:

CREATE INDEX employee_index
ON TABLE employee (employee_id, employee_name);

Here we have created an index named ‘employee_index’ on the ‘employee’ table it contains two columns ‘employee_id’ and ‘employee_name’.

Creating Function Based Index:

In Oracle we can not only create indexes based on columns but we can also create indexes based on functions.

Syntax for creating a FUNCTION BASED INDEX in Oracle SQL / PLSQL is:

CREATE [UNIQUE] INDEX index_name
ON TABLE table_name (function_name1,function_name2, . . . . function_nameN)
[COMPUTE STATISTICS];

Example:

CREATE INDEX emp_name_index
ON TABLE employee (UPPER(employee_name));

Here we have created an index named ‘emp_name_index’ based on the uppercase evaluation of the ‘employee_name’ column in the ‘employee’ table’.

Renaming an INDEX:

The syntax to RENAME an INDEX in Oracle SQL / PLSQL is:

ALTER INDEX index_name
RENAME TO new_index_name;

Example:

ALTER INDEX employee_index
RENAME TO emp_index;

Above statement will rename the ’employee_index’ to ’emp_index’.

Rebuilding an INDEX to compute statistics:

If we forget to COMPUTE STATISTICS while creating an INDEX we can REBUILD the index again to compute the statistics:

Syntax to REBUILD the INDEX in Oracle SQL / PLSQL is:

ALTER INDEX index_name
REBUILD COMPUTE STATISTICS;

Example:

ALTER INDEX employee_index
REBUILD COMPUTE STATISTICS;

In the above statement we are collecting the statistics for the ‘employee_index’.

Dropping an INDEX:

Syntax for dropping an INDEX in Oracle SQL / PLSQL is:

DROP INDEX index_name;

Example:

DROP INDEX employee_index;