Ceil Function

In Oracle/PLSQL, the ceil function returns the smallest integer value that is greater than or equal to a number.
The syntax for the ceil function is: ceil( number ) number is the value used to find the smallest integer value.

 For Example
ceil(32.65) would return 33
ceil(32) would return 32
ceil(-32.65) would return -32
ceil(-32) would return -32

AVG FUNCTION

This SQL tutorial explains how to use the SQL AVG function with syntax and examples.

DESCRIPTION

The SQL AVG function is used to return the average of an expression in a SELECT statement.

SYNTAX

The syntax for the SQL AVG function is:

SELECT AVG(aggregate_expression)
FROM tables
[WHERE conditions];

OR the syntax for the AVG function when grouping the results by one or more columns is:

SELECT expression1, expression2, ... expression_n,
       AVG(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

Parameters or Arguments

expression1, expression2, ... expression_n

Expressions that are not encapsulated within the AVG function and must be included in the GROUP BY clause at the end of the SQL statement.

aggregate_expression

This is the column or expression that will be averaged.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. These are conditions that must be met for the records to be selected.

EXAMPLE - WITH SINGLE EXPRESSION

For example, you might wish to know how the average cost of all products that are in the Clothing category.

SELECT AVG(cost) AS "Average Cost"
FROM products
WHERE category = 'Clothing';

In this SQL AVG Function example, we've aliased the AVG(cost) expression as "Average Cost". As a result, "Average Cost" will display as the field name when the result set is returned.

EXAMPLE - USING SQL DISTINCT

You can use the SQL DISTINCT clause within the AVG function. For example, the SELECT statement below returns the combined average cost of unique cost values where the category is Clothing.

SELECT AVG(DISTINCT cost) AS "Average Cost"
FROM products
WHERE category = 'Clothing';

If there were two cost values of $25, only one of these values would be used in the AVG function calculation.

EXAMPLE - USING FORMULA

The expression contained within the AVG function does not need to be a single field. You could also use a formula. For example, you might want the average profit for a product. Average profit is calculated as sale_price less cost.

SELECT AVG(sale_price - cost) AS "Average Profit"
FROM products;

You might also want to perform a mathematical operation within the AVG function. For example, you might determine the average commission as 10% of sale_price.

SELECT AVG(sale_price * 0.10) AS "Average Commission"
FROM products;

EXAMPLE - USING SQL GROUP BY

In some cases, you will be required to use the SQL GROUP BY clause with the AVG function.

For example, you could also use the AVG function to return the name of the department and the average sales (in the associated department).

SELECT department, AVG(sales) AS "Average Sales"
FROM order_details
WHERE department > 10
GROUP BY department;

Because you have listed one column in your SELECT statement that is not encapsulated in the AVG function, you must use theGROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

MAX FUNCTION

This SQL tutorial explains how to use the SQL MAX function with syntax and examples.

DESCRIPTION

The SQL MAX function is used to return the maximum value of an expression in a SELECT statement.

SYNTAX

The syntax for the SQL MAX function is:

SELECT MAX(aggregate_expression)
FROM tables
[WHERE conditions];

OR the syntax for the MAX function when grouping the results by one or more columns is:

SELECT expression1, expression2, ... expression_n,
       MAX(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

Parameters or Arguments

expression1, expression2, ... expression_n

Expressions that are not encapsulated within the MAX function and must be included in the GROUP BY clause at the end of the SQL statement.

aggregate_expression

This is the column or expression from which the maximum value will be returned.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. These are conditions that must be met for the records to be selected.

EXAMPLE - WITH SINGLE EXPRESSION

The simplest way to use the SQL MAX function would be to return a single field that calculates the MAX value.

For example, you might wish to know the maximum salary of all employees.

SELECT MAX(salary) AS "Highest salary"
FROM employees;

In this SQL MAX function example, we've aliased the MAX(salary) field as "Highest salary". As a result, "Highest salary" will display as the field name when the result set is returned.

EXAMPLE - USING SQL GROUP BY CLAUSE

In some cases, you will be required to use the SQL GROUP BY clause with the SQL MAX function.

For example, you could also use the SQL MAX function to return the name of each department and the maximum salary in the department.

SELECT department, MAX(salary) AS "Highest salary"
FROM employees
GROUP BY department;

Because you have listed one column in your SQL SELECT statement that is not encapsulated in the MAX function, you must use the SQL GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

MIN FUNCTION

This SQL tutorial explains how to use the SQL MIN function with syntax and examples.

DESCRIPTION

The SQL MIN function is used to return the minimum value of an expression in a SELECT statement.

SYNTAX

The syntax for the SQL MIN function is:

SELECT MIN(aggregate_expression)
FROM tables
[WHERE conditions];

OR the syntax for the MIN function when grouping the results by one or more columns is:

SELECT expression1, expression2, ... expression_n,
       MIN(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

Parameters or Arguments

expression1, expression2, ... expression_n

Expressions that are not encapsulated within the MIN function and must be included in the GROUP BY clause at the end of the SQL statement.

aggregate_expression

This is the column or expression from which the minimum value will be returned.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. These are conditions that must be met for the records to be selected.

EXAMPLE - WITH SINGLE EXPRESSION

The simplest way to use the SQL MIN function would be to return a single field that calculates the MIN value.

For example, you might wish to know the minimum salary of all employees.

SELECT MIN(salary) AS "Lowest salary"
FROM employees;

In this SQL MIN function example, we've aliased the MIN(salary) field as "Lowest salary". As a result, "Lowest salary" will display as the field name when the result set is returned.

EXAMPLE - USING SQL GROUP BY

In some cases, you will be required to use the SQL GROUP BY clause with the SQL MIN function.

For example, you could also use the SQL MIN function to return the name of each department and the minimum salary in the department.

SELECT department, MIN(salary) AS "Lowest salary"
FROM employees
GROUP BY department;

Because you have listed one column in your SQL SELECT statement that is not encapsulated in the SQL MIN function, you must use the SQL GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section

SUM FUNCTION

This SQL tutorial explains how to use the SQL SUM function with syntax and examples.

DESCRIPTION

The SQL SUM function is used to return the sum of an expression in a SELECT statement.

SYNTAX

The syntax for the SQL SUM function is:

SELECT SUM(aggregate_expression)
FROM tables
[WHERE conditions];

OR the syntax for the SUM function when grouping the results by one or more columns is:

SELECT expression1, expression2, ... expression_n,
       SUM(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

Parameters or Arguments

expression1, expression2, ... expression_n

Expressions that are not encapsulated within the SUM function and must be included in the GROUP BY clause at the end of the SQL statement.

aggregate_expression

This is the column or expression that will be summed.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. These are conditions that must be met for the records to be selected.

EXAMPLE - WITH SINGLE EXPRESSION

For example, you might wish to know how the combined total salary of all employees whose salary is above $25,000 / year.

SELECT SUM(salary) AS "Total Salary"
FROM employees
WHERE salary > 25000;

In this SQL SUM Function example, we've aliased the SUM(salary) expression as "Total Salary". As a result, "Total Salary" will display as the field name when the result set is returned.

EXAMPLE - USING SQL DISTINCT

You can use the SQL DISTINCT clause within the SQL SUM function. For example, the SQL SELECT statement below returns the combined total salary of unique salary values where the salary is above $25,000 / year.

SELECT SUM(DISTINCT salary) AS "Total Salary"
FROM employees
WHERE salary > 25000;

If there were two salaries of $30,000/year, only one of these values would be used in the SQL SUM function.

EXAMPLE - USING FORMULA

The expression contained within the SQL SUM function does not need to be a single field. You could also use a formula. For example, you might want the net income for a business. Net Income is calculated as total income less total expenses.

SELECT SUM(income - expenses) AS "Net Income"
FROM gl_transactions;

You might also want to perform a mathematical operation within the SQL SUM function. For example, you might determine total commission as 10% of total sales.

SELECT SUM(sales * 0.10) AS "Commission"
FROM order_details;

EXAMPLE - USING SQL GROUP BY

In some cases, you will be required to use the SQL GROUP BY clause with the SQL SUM function.

For example, you could also use the SQL SUM function to return the name of the department and the total sales (in the associated department).

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department;

Because you have listed one column in your SQL SELECT statement that is not encapsulated in the SQL SUM function, you must use the SQL GROUP BY clause. The department field must, therefore, be listed in the SQL GROUP BY section.

COUNT FUNCTION

This SQL tutorial explains how to use the SQL COUNT function with syntax, examples, and practice exercises.

DESCRIPTION

The SQL COUNT function is used to count the number of rows returned in a SELECT statement.

SYNTAX

The syntax for the SQL COUNT function is:

SELECT COUNT(aggregate_expression)
FROM tables
[WHERE conditions];

OR the syntax for the COUNT function when grouping the results by one or more columns is:

SELECT expression1, expression2, ... expression_n,
       COUNT(aggregate_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

Parameters or Arguments

expression1, expression2, ... expression_n

Expressions that are not encapsulated within the COUNT function and must be included in the GROUP BY clause at the end of the SQL statement.

aggregate_expression

This is the column or expression whose non-null values will be counted.

tables

The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.

WHERE conditions

Optional. These are conditions that must be met for the records to be selected.

ONLY INCLUDES NOT NULL VALUES

Not everyone realizes this, but the SQL COUNT function will only include the records in the count where the value of expressionin COUNT(expression) is NOT NULL. When expression contains a NULL value, it is not included in the COUNT calculations.

Let's look at a SQL COUNT function example that demonstrates how NULL values are evaluated by the COUNT function.

For example, if you have the following table called suppliers:

supplier_id	supplier_name	state
1	IBM	CA
2	Microsoft
3	NVIDIA

And if you ran the following SQL SELECT statement that uses the SQL COUNT function:

SELECT COUNT(supplier_id)
FROM suppliers;

Result: 3

This SQL COUNT example will return 3 since all supplier_id values in the query's result set are NOT NULL.

However, if you ran the next SQL SELECT statement that uses the SQL COUNT function:

SELECT COUNT(state) 
FROM suppliers;

Result: 1

This SQL COUNT example will only return 1, since only one state value in the query's result set is NOT NULL. That would be the first row where the state = 'CA'. It is the only row that is included in the COUNT function calculation.

EXAMPLE - WITH SINGLE EXPRESSION

The simplest way to use the SQL COUNT function would be to return a single field that returns the COUNT of something.

For example, you might wish to know how many employees have a salary that is above $25,000 / year.

SELECT COUNT(*) AS "Number of employees"
FROM employees
WHERE salary > 25000;

In this SQL COUNT function example, we've aliased the COUNT(*) expression as "Number of employees". As a result, "Number of employees" will display as the field name when the result set is returned.

EXAMPLE - USING SQL DISTINCT CLAUSE

You can use the SQL DISTINCT clause within the SQL COUNT function.

For example, the SQL statement below returns the number of unique departments where at least one employee makes over $25,000 / year.

SELECT COUNT(DISTINCT department) AS "Unique departments"
FROM employees
WHERE salary > 25000;

Again, the COUNT(DISTINCT department) field is aliased as "Unique departments". This is the field name that will display in the result set.

EXAMPLE - USING SQL GROUP BY CLAUSE

In some cases, you will be required to use the SQL GROUP BY clause with the SQL COUNT function.

For example, you could use the SQL COUNT function to return the name of the department and the number of employees (in the associated department) that make over $25,000 / year.

SELECT department, COUNT(*) AS "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Because you have listed one column in your SQL SELECT statement that is not encapsulated in the SQL COUNT function, you must use the SQL GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

TIP: PERFORMANCE TUNING WITH SQL COUNT

Since the SQL COUNT function will return the same results regardless of what NOT NULL field(s) you include as the SQL COUNT function parameters (ie: within the parentheses), you can change the syntax of the SQL COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields.

For example, based on the example above, the following syntax would result in better performance:

SELECT department, COUNT(1) AS "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Now, the SQL COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.

PRACTICE EXERCISE #1:

Based on the employees table populated with the following data, count the number of employees whose salary is over $55,000 per year.

CREATE TABLE employees
( employee_number int NOT NULL,
  employee_name char(50) NOT NULL,
  salary int,
  CONSTRAINT employees_pk PRIMARY KEY (employee_number)
);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1001, 'John Smith', 62000);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1002, 'Jane Anderson', 57500);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1003, 'Brad Everest', 71000);

INSERT INTO employees (employee_number, employee_name, salary)
VALUES (1004, 'Jack Horvath', 42000);

Solution for Practice Exercise #1:

Although inefficient in terms of performance, the following SQL SELECT statement would return the number of employees whose salary is over $55,000 per year.

SELECT COUNT(*) AS "Number of employees"
FROM employees
WHERE salary > 55000;

It would return the following result set:

Number of employees
3

A more efficient implementation of the same solution would be the following SQL SELECT statement:

SELECT COUNT(1) AS "Number of employees"
FROM employees
WHERE salary > 55000;

Now, the SQL COUNT function does not need to retrieve all of the fields from the table (ie: employee_number, employee_name, and salary), but rather whenever the condition is met, it will retrieve the numeric value of 1. Thus, increasing the performance of the SQL statement.

PRACTICE EXERCISE #2:

Based on the suppliers table populated with the following data, count the number of distinct cities in the suppliers table:

CREATE TABLE suppliers
( supplier_id int NOT NULL,
  supplier_name char(50) NOT NULL,
  city char(50),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_id)
);

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5001, 'Microsoft', 'New York');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5002, 'IBM', 'Chicago');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5003, 'Red Hat', 'Detroit');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5004, 'NVIDIA', 'New York');

INSERT INTO suppliers (supplier_id, supplier_name, city)
VALUES (5005, 'NVIDIA', 'LA');

Solution for Practice Exercise #2:

The following SQL SELECT statement would return the number of distinct cities in the suppliers table:

SELECT COUNT(DISTINCT city) AS "Distinct Cities"
FROM suppliers;

It would return the following result set:

Distinct Cities
4

PRACTICE EXERCISE #3:

Based on the customers table populated with the following data, count the number of distinct cities for each customer_name in the customers table:

CREATE TABLE customers
( customer_id int NOT NULL,
  customer_name char(50) NOT NULL,
  city char(50),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7001, 'Microsoft', 'New York');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7002, 'IBM', 'Chicago');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7003, 'Red Hat', 'Detroit');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7004, 'Red Hat', 'New York');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7005, 'Red Hat', 'San Francisco');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7006, 'NVIDIA', 'New York');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7007, 'NVIDIA', 'LA');

INSERT INTO customers (customer_id, customer_name, city)
VALUES (7008, 'NVIDIA', 'LA');

Solution for Practice Exercise #3:

The following SQL SELECT statement would return the number of distinct cities for each customer_name in the customerstable:

SELECT customer_name, COUNT(DISTINCT city) AS "Distinct Cities"
FROM customers
GROUP BY customer_name;

It would return the following result set:

CUSTOMER_NAME	Distinct Cities
IBM	1
Microsoft	1
NVIDIA	2
Red Hat	3