SQL: MIN Function

The MIN function returns the minimum value of an expression.

The syntax for the MIN function is:

SELECT MIN(expression )
FROM tables
WHERE predicates;

Simple Example

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

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

In this 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 GROUP BY

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

For example, you could also use the 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 SELECT statement that is not encapsulated in the MIN function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

SQL: MAX Function

The MAX function returns the maximum value of an expression.

The syntax for the MAX function is:

SELECT MAX(expression )
FROM tables
WHERE predicates;

Simple Example

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

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

In this 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 GROUP BY

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

For example, you could also use the 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 SELECT statement that is not encapsulated in the MAX function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

Frequently Asked Questions

---

Question: I'm trying to pull some info out of a table. To simplify, let's say the table (report_history) has 4 columns:

user_name, report_job_id, report_name, report_run_date.

Each time a report is run in Oracle, a record is written to this table noting the above info. What I am trying to do is pull from this table when the last time each distinct report was run and who ran it last.

My initial query:

SELECT report_name, max(report_run_date)
FROM report_history
GROUP BY report_name

runs fine. However, it does not provide the name of the user who ran the report.

Adding user_name to both the select list and to the group by clause returns multiple lines for each report; the results show the last time each person ran each report in question. (i.e. User1 ran Report 1 on 01-JUL-03, User2 ran Report1 on 01-AUG-03). I don't want that....I just want to know who ran a particular report the last time it was run.

Any suggestions?

Answer: This is where things get a bit complicated. The SQL statement below will return the results that you want:

SELECT rh.user_name, rh.report_name, rh.report_run_date
FROM report_history rh,
(SELECT max(report_run_date) as maxdate, report_name
FROM report_history
GROUP BY report_name) maxresults
WHERE rh.report_name = maxresults.report_name
AND rh.report_run_date= maxresults.maxdate;

Let's take a few moments to explain what we've done.

First, we've aliased the first instance of the report_history table as rh.

Second, we've included two components in our FROM clause. The first is the table called report_history (aliased as rh). The second is a select statement:

(SELECT max(report_run_date) as maxdate, report_name
FROM report_history
GROUP BY report_name) maxresults

We've aliased the max(report_run_date) as maxdate and we've aliased the entire result set as maxresults.

Now, that we've created this select statement within our FROM clause, Oracle will let us join these results against our original report_history table. So we've joined the report_name and report_run_date fields between the tables called rh and maxresults. This allows us to retrieve the report_name, max(report_run_date) as well as the user_name.

---

Question: I need help in an SQL query. I have a table in Oracle called orders which has the following fields: order_no, customer, and amount.

I need a query that will return the customer who has ordered the highest total amount.

Answer: The following SQL should return the customer with the highest total amount in the orders table.

select query1.* from
(SELECT customer, Sum(orders.amount) AS total_amt
FROM orders
GROUP BY orders.customer) query1,

(select max(query2.total_amt) as highest_amt
from (SELECT customer, Sum(orders.amount) AS total_amt
FROM orders
GROUP BY orders.customer) query2) query3
where query1.total_amt = query3.highest_amt;

This SQL statement will summarize the total orders for each customer and then return the customer with the highest total orders. This syntax is optimized for Oracle and may not work for other database technologies.

---

Question: I'm trying to retrieve some info from an Oracle database. I've got a table named Scoring with two fields - Name and Score. What I want to get is the highest score from the table and the name of the player.

Answer: The following SQL should work:

SELECT Name, Score
FROM Scoring
WHERE Score = (select Max(Score) from Scoring);

---

Question: I need help in an SQL query. I have a table in Oracle called cust_order which has the following fields: OrderNo, Customer_id, Order_Date, and Amount.

I would like to find the customer_id, who has Highest order count.

I tried with following query.

SELECT MAX(COUNT(*)) FROM CUST_ORDER GROUP BY CUSTOMER_ID;

This gives me the max Count, But, I can't get the CUSTOMER_ID. Can you help me please?

Answer: The following SQL should return the customer with the highest order count in the cust_order table.

select query1.* from
(SELECT Customer_id, Count(*) AS order_count
FROM cust_order
GROUP BY cust_order.Customer_id) query1,

(select max(query2.order_count) as highest_count
from (SELECT Customer_id, Count(*) AS order_count
FROM cust_order
GROUP BY cust_order.Customer_id) query2) query3
where query1.order_count = query3.highest_count;

This SQL statement will summarize the total orders for each customer and then return the customer with the highest order count. This syntax is optimized for Oracle and may not work for other database technologies.

SQL: COUNT Function

The COUNT function returns the number of rows in a query.

The syntax for the COUNT function is:

SELECT COUNT(expression)
FROM tables
WHERE predicates;

Note:

The COUNT function will only count those records in which the field in the brackets is NOT NULL.

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

Supplier_ID	Supplier_Name	State
1	IBM	CA
2	Microsoft
3	NVIDIA

The result for this query will return 3.

Select COUNT(Supplier_ID) from suppliers;

While the result for the next query will only return 1, since there is only one row in the suppliers table where the State field is NOT NULL.

Select COUNT(State) from suppliers;

Simple Example

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 example, we've aliased the count(*) field as "Number of employees". As a result, "Number of employees" will display as the field name when the result set is returned.

Example using DISTINCT

You can use the DISTINCT clause within the 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 GROUP BY

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

For example, you could use the 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 SELECT statement that is not encapsulated in the COUNT function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

TIP: Performance Tuning

Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the 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 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	number(10)	not null,
	employee_name	varchar2(50)	not null,
	salary	number(6),
	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:

Although inefficient in terms of performance, the following SQL 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 statement:

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

Now, the 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	number(10)	not null,
	supplier_name	varchar2(50)	not null,
	city	varchar2(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:

The following SQL 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	number(10)	not null,
	customer_name	varchar2(50)	not null,
	city	varchar2(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:

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

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

SQL: SUM Function

The SUM function returns the summed value of an expression.

The syntax for the SUM function is:

SELECT SUM(expression )
FROM tables
WHERE predicates;

expression can be a numeric field or formula.

Simple Example

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 example, we've aliased the sum(salary) field as "Total Salary". As a result, "Total Salary" will display as the field name when the result set is returned.

Example using DISTINCT

You can use the DISTINCT clause within the SUM function. For example, the SQL 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 SUM function.

Example using a Formula

The expression contained within the 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 a 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 GROUP BY

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

For example, you could also use the 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 SELECT statement that is not encapsulated in the SUM function, you must use a GROUP BY clause. The department field must, therefore, be listed in the GROUP BY section.

SQL: DISTINCT Clause

The DISTINCT clause allows you to remove duplicates from the result set. The DISTINCT clause can only be used with select statements.

The syntax for the DISTINCT clause is:

SELECT DISTINCT columns
FROM tables
WHERE predicates;

Example #1

Let's take a look at a very simple example.

SELECT DISTINCT city
FROM suppliers;

This SQL statement would return all unique cities from the suppliers table.

Example #2

The DISTINCT clause can be used with more than one field.

For example:

SELECT DISTINCT city, state
FROM suppliers;

This select statement would return each unique city and state combination. In this case, the distinct applies to each field listed after the DISTINCT keyword.

SQL: SELECT Statement

The SELECT statement allows you to retrieve records from one or more tables in your database.

The syntax for the SELECT statement is:

SELECT columns
FROM tables
WHERE predicates;

Example #1

Let's take a look at how to select all fields from a table.

SELECT *
FROM suppliers
WHERE city = 'Newark';

In our example, we've used * to signify that we wish to view all fields from the suppliers table where the supplier resides in Newark.

Example #2

You can also choose to select individual fields as opposed to all fields in the table.

For example:

SELECT name, city, state
FROM suppliers
WHERE supplier_id > 1000;

This select statement would return all name, city, and state values from the suppliers table where the supplier_id value is greater than 1000.

Example #3

You can also use the select statement to retrieve fields from multiple tables.

SELECT orders.order_id, suppliers.name
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

The result set would display the order_id and suppier name fields where the supplier_id value existed in both the suppliers and orders table.

SQL: Data Types

The following is a list of general SQL datatypes that may not be supported by all relational databases.

Data Type	Syntax	Explanation (if applicable)
integer	integer
smallint	smallint
numeric	numeric(p,s)	Where p is a precision value; s is a scale value. For example, numeric(6,2) is a number that has 4 digits before the decimal and 2 digits after the decimal.
decimal	decimal(p,s)	Where p is a precision value; s is a scale value.
real	real	Single-precision floating point number
double precision	double precision	Double-precision floating point number
float	float(p)	Where p is a precision value.
character	char(x)	Where x is the number of characters to store. This data type is space padded to fill the number of characters specified.
character varying	varchar2(x)	Where x is the number of characters to store. This data type does NOT space pad.
bit	bit(x)	Where x is the number of bits to store.
bit varying	bit varying(x)	Where x is the number of bits to store. The length can vary up to x.
date	date	Stores year, month, and day values.
time	time	Stores the hour, minute, and second values.
timestamp	timestamp	Stores year, month, day, hour, minute, and second values.
time with time zone	time with time zone	Exactly the same as time, but also stores an offset from UTC of the time specified.
timestamp with time zone	timestamp with time zone	Exactly the same as timestamp, but also stores an offset from UTC of the time specified.
year-month interval		Contains a year value, a month value, or both.
day-time interval		Contains a day value, an hour value, a minute value, and/or a second value.