Oracle Tutorial 3

ORACLE/PLSQL: COUNT FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL COUNT function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL COUNT function returns the count of an expression.

SYNTAX

The syntax for the Oracle/PLSQL COUNT function is:

SELECT COUNT( expression )
FROM tables
WHERE conditions;

Parameters or Arguments

expression can be a field or formula.

ONLY INCLUDES NOT NULL VALUES

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

Let's look at a 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 SELECT statement that uses the COUNT function:

SELECT COUNT(supplier_id)
FROM suppliers;

This 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 SELECT statement that uses the COUNT function:

SELECT COUNT(state) 
FROM suppliers;

This 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.

APPLIES TO

The COUNT function can be used in the following versions of Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE - WITH SINGLE FIELD

Let's look at some Oracle COUNT function examples and explore how to use the COUNT function in Oracle/PLSQL.

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

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

In this 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 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 $55,000 / year.

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

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 the GROUP BY clause with the COUNT function.

For example, you could also use the COUNT function to return the name of the department and the number of employees (in the associated department) that are in the state of 'CA'.

SELECT department, COUNT(*) AS "Number of employees"
FROM employees
WHERE state = 'CA'
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.

ORACLE/PLSQL: SUM FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL SUM function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL SUM function returns the summed value of an expression.

SYNTAX

The syntax for the Oracle/PLSQL SUM function is:

SELECT SUM( expression )
FROM tables
WHERE conditions;

Parameters or Arguments

expression can be a numeric field or formula.

APPLIES TO

The SUM function can be used in the following versions of Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE - WITH SINGLE FIELD

Let's look at some Oracle SUM function examples and explore how to use the SUM function in Oracle/PLSQL.

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

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

In this 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 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 $50,000 / year.

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

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

EXAMPLE - USING 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 to calculate the total commission.

SELECT SUM(sales * 0.05) AS "Total Commission"
FROM orders;

EXAMPLE - USING GROUP BY

In some cases, you will be required to use the 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.

ORACLE/PLSQL: MAX FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL MAX function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL MAX function returns the maximum value of an expression.

SYNTAX

The syntax for the Oracle/PLSQL MAX function is:

SELECT MAX( expression )
FROM tables
WHERE conditions;

Parameters or Arguments

expression can be a numeric field or formula.

APPLIES TO

The MAX function can be used in the following versions of Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE - WITH SINGLE FIELD

Let's look at some Oracle MAX function examples and explore how to use the MAX function in Oracle/PLSQL.

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

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

In this MAX function example, we've aliased the MAX(salary) expression 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 the GROUP BY clause with the MAX function.

For example, you could also use the MAX function to return the name of the 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, and 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 SELECT 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 with a 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 SELECT 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 SELECT statement should work:

SELECT Name, Score
FROM Scoring
WHERE Score = (SELECT MAX(Score) FROM Scoring);

---

Question: I need help in a 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 SELECT statement 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 SELECT 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.

---

Question: I'm trying to get the employee with the maximum salary from department 30, but I need to display the employee's full information. I've tried the following query, but it returns the result from both department 30 and 80:

SELECT *
FROM employees
WHERE salary = (SELECT MAX(salary)
                FROM employees
                WHERE department_id=30);

Answer: The SQL SELECT statement that you have written will first determine the maximum salary for department 30, but then you select all employees that have this salary. In your case, you must have 2 employees (one in department 30 and another in department 80) that have this same salary. You need to make sure that you are refining your query results to only return employees from department 30.

Try using this SQL SELECT statement:

SELECT *
FROM employees
WHERE department_id=30
AND salary = (SELECT MAX(salary)
              FROM employees
              WHERE department_id=30);

This will return the employee information for only the employee in department 30 that has the highest salary.

ORACLE/PLSQL: MIN FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL MIN function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL MIN function returns the minimum value of an expression.

SYNTAX

The syntax for the Oracle/PLSQL MIN function is:

SELECT MIN( expression )
FROM tables
WHERE conditions;

Parameters or Arguments

expression can be a numeric field or formula.

APPLIES TO

The MIN function can be used in the following versions of Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE - WITH SINGLE FIELD

Let's look at some Oracle MIN function examples and explore how to use the MIN function in Oracle/PLSQL.

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

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

In this MIN function example, we've aliased the MIN(salary) expression 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 the GROUP BY clause with the MIN function.

For example, you could also use the MIN function to return the name of the 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.

ORACLE/PLSQL: AVG FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL AVG function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL AVG function returns the average value of an expression.

SYNTAX

The syntax for the Oracle/PLSQL AVG function is:

SELECT AVG( expression )
FROM tables
WHERE conditions;

Parameters or Arguments

expression can be a numeric field or formula.

APPLIES TO

The AVG function can be used in the following versions of Oracle/PLSQL:

• Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE - WITH SINGLE FIELD

Let's look at some Oracle AVG function examples and explore how to use the AVG function in Oracle/PLSQL.

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

SELECT AVG(salary) AS "Avg Salary"
FROM employees
WHERE salary > 25000;

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

EXAMPLE - USING DISTINCT

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

SELECT AVG(DISTINCT salary) AS "Avg 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 AVG function.

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 commission.

SELECT AVG(sales * 0.10) AS "Average Commission"
FROM orders;

EXAMPLE - USING GROUP BY

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

SELECT department, AVG(sales) AS "Avg sales"
FROM order_details
GROUP BY department;

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

ORACLE/PLSQL: HAVING CLAUSE

This Oracle tutorial explains how to use the Oracle HAVING clause with syntax and examples.

DESCRIPTION

The Oracle HAVING Clause is used in combination with the GROUP BY Clause to restrict the groups of returned rows to only those whose the condition is TRUE.

SYNTAX

The syntax for the Oracle HAVING Clause is:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n
HAVING condition;

Parameters or Arguments

aggregate_function can be a function such as SUM, COUNT, MIN, MAX, or AVG functions.

expression1, expression2, ... expression_n are expressions that are not encapsulated within an aggregate function and must be included in the GROUP BY Clause.

condition is the condition that is used to restrict the groups of returned rows. Only those groups whose condition evaluates to TRUE will be included in the result set.

EXAMPLE - USING SUM FUNCTION

Let's look at an Oracle HAVING clause example that uses the SUM function.

You could also use the SUM function to return the name of the department and the total sales (in the associated department). The Oracle HAVING clause will filter the results so that only departments with sales greater than $25,000 will be returned.

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

EXAMPLE - USING COUNT FUNCTION

Let's look at how we could use the HAVING clause with the COUNT function.

You could use the COUNT function to return the name of the department and the number of employees (in the associated department) that make under $49,500 / year. The Oracle HAVING clause will filter the results so that only departments with more than 10 employees will be returned.

SELECT department, COUNT(*) AS "Number of employees"
FROM employees
WHERE salary < 49500
GROUP BY department
HAVING COUNT(*) > 10;

EXAMPLE - USING MIN FUNCTION

Let's next look at how we could use the HAVING clause with the MIN function.

You could also use the MIN function to return the name of each department and the minimum salary in the department. The Oracle HAVING clause will return only those departments where the minimum salary is less than $42,000.

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

EXAMPLE - USING MAX FUNCTION

Finally, let's look at how we could use the HAVING 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. The Oracle HAVING clause will return only those departments whose maximum salary is greater than $45,000.

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

ORACLE/PLSQL: UNION OPERATOR

This Oracle tutorial explains how to use the Oracle UNION operator with syntax and examples.

DESCRIPTION

The Oracle UNION operator is used to combine the result sets of 2 or more Oracle SELECT statements. It removes duplicate rows between the various SELECT statements.

Each SELECT statement within the UNION operator must have the same number of fields in the result sets with similar data types.

SYNTAX

The syntax for the Oracle UNION operator is:

SELECT expression1, expression2, ... expression_n
FROM tables
WHERE conditions
UNION
SELECT expression1, expression2, ... expression_n
FROM tables
WHERE conditions;

Parameters or Arguments

expression1, expression2, expression_n are the columns or calculations that you wish to retrieve.

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

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

NOTE

• There must be same number of expressions in both SELECT statements.

EXAMPLE - RETURNS SINGLE FIELD

The following is an example of the Oracle UNION operator that returns one field from multiple SELECT statements (and both fields have the same data type):

SELECT supplier_id
FROM suppliers
UNION
SELECT supplier_id
FROM order_details;

In this Oracle UNION operator example, if a supplier_id appeared in both the suppliers and order_details table, it would appear once in your result set. The Oracle UNION operator removes duplicates. If you do not wish to remove duplicates, try using the Oracle UNION ALL operator.

EXAMPLE - USING ORDER BY

The Oracle UNION operator can use the ORDER BY clause to order the results of the query.

For example:

SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_id <= 500
UNION
SELECT company_id, company_name
FROM companies
WHERE company_name = 'Apple'
ORDER BY 2;

In this Oracle UNION operator, since the column names are different between the two SELECT statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".

The supplier_name / company_name fields are in position #2 in the result set.

FREQUENTLY ASKED QUESTIONS

Question: I need to compare two dates and return the count of a field based on the date values. For example, I have a date field in a table called last updated date. I have to check if trunc(last_updated_date >= trunc(sysdate-13).

Answer: Since you are using the Oracle COUNT function which is an aggregate function, we'd recommend using the Oracle UNION operator. For example, you could try the following:

SELECT a.code AS Code, a.name AS Name, COUNT(b.Ncode)
FROM cdmaster a, nmmaster b
WHERE a.code = b.code
AND a.status = 1
AND b.status = 1
AND b.Ncode <> 'a10'
AND TRUNC(last_updated_date) <= TRUNC(sysdate-13)
GROUP BY a.code, a.name
UNION
SELECT a.code AS Code, a.name AS Name, COUNT(b.Ncode)
FROM cdmaster a, nmmaster b
WHERE a.code = b.code
AND a.status = 1
AND b.status = 1
AND b.Ncode <> 'a10'
AND TRUNC(last_updated_date) > TRUNC(sysdate-13)
GROUP BY a.code, a.name;

The Oracle UNION operator allows you to perform a count based on one set of criteria.

TRUNC(last_updated_date) <= TRUNC(sysdate-13)

As well as perform a count based on another set of criteria.

TRUNC(last_updated_date) > TRUNC(sysdate-13)

ORACLE/PLSQL: UNION ALL OPERATOR

This Oracle tutorial explains how to use the Oracle UNION ALL operator with syntax and examples.

DESCRIPTION

The Oracle UNION ALL operator is used to combine the result sets of 2 or more SELECT statements. It returns all rows from the query (even if the row exists in more than one of the SELECT statements).

Each SELECT statement within the Oracle UNION ALL operator must have the same number of fields in the result sets with similar data types.

SYNTAX

The syntax for the Oracle UNION ALL operator is:

SELECT expression1, expression2, ... expression_n
FROM tables
WHERE conditions
UNION ALL
SELECT expression1, expression2, ... expression_n
FROM tables
WHERE conditions;

Parameters or Arguments

expression1, expression2, expression_n are the columns or calculations that you wish to retrieve.

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

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

NOTE

• There must be same number of expressions in both SELECT statements.

EXAMPLE - RETURN SINGLE FIELD

The following is an example of the Oracle UNION ALL operator that returns one field from multiple SELECT statements (and both fields have the same data type):

SELECT supplier_id
FROM suppliers
UNION ALL
SELECT supplier_id
FROM orders;

This Oracle UNION ALL operator would return a supplier_id multiple times in your result set if the supplier_id appeared in both the suppliers and orders table. The Oracle UNION ALL operator does not remove duplicates. If you wish to remove duplicates, try using the Oracle UNION operator.

EXAMPLE - USING ORDER BY

The Oracle UNION ALL operator can use the Oracle ORDER BY clause to order the results of the query.

For example:

SELECT supplier_id, supplier_name
FROM suppliers
WHERE state = 'California'
UNION ALL
SELECT company_id, company_name
FROM companies
WHERE company_id > 1000
ORDER BY 2;

In this Oracle UNION ALL operator, since the column names are different between the two SELECT statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".

The supplier_name / company_name fields are in position #2 in the result set.

ORACLE/PLSQL: INTERSECT OPERATOR

This Oracle tutorial explains how to use the Oracle INTERSECT operator with syntax and examples.

DESCRIPTION

The Oracle INTERSECT operator is used to return the results of 2 or more SELECT statements. However, it only returns the rows selected by all queries. If a record exists in one query and not in the other, it will be omitted from the INTERSECT results.

Each SELECT statement within the Oracle INTERSECT must have the same number of fields in the result sets with similar data types.

SYNTAX

The syntax for the Oracle INTERSECT operator is:

SELECT field1, field2, ... field_n
FROM tables
INTERSECT
SELECT field1, field2, ... field_n
FROM tables;

EXAMPLE - WITH SINGLE FIELD

The following is an Oracle INTERSECT example that returns one field with the same data type:

SELECT supplier_id
FROM suppliers
INTERSECT
SELECT supplier_id
FROM orders;

In this INTERSECT example, if a supplier_id appeared in both the suppliers and orders table, it would appear in your result set.

EXAMPLE - USING ORDER BY

The following is an INTERSECT example that uses an ORDER BY clause:

SELECT supplier_id, supplier_name
FROM suppliers
WHERE supplier_id > 500
INTERSECT
SELECT company_id, company_name
FROM companies
WHERE company_name in ( 'Apple', 'Microsoft', 'Oracle' )
ORDER BY 2;

Since the column names are different between the two SELECT statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results bysupplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".

The supplier_name / company_name fields are in position #2 in the result set.

ORACLE/PLSQL: MINUS OPERATOR

This Oracle tutorial explains how to use the Oracle MINUS operator with syntax and examples.

DESCRIPTION

The Oracle MINUS operator is used to return all rows in the first SELECT statement that are not returned in the second SELECT statement.

Each SELECT statement must have the same number of fields in the result sets with similar data types.

SYNTAX

The syntax for the Oracle MINUS operator is:

SELECT expression1, expression2, ... expression_n
FROM tables
MINUS
SELECT expression1, expression2, ... expression_n
FROM tables;

EXAMPLE - WITH SINGLE EXPRESSION

The following is an Oracle MINUS operator example that returns one field with the same data type:

SELECT supplier_id
FROM suppliers
MINUS
SELECT supplier_id
FROM orders;

This Oracle MINUS example returns all supplier_id values that are in the suppliers table and not in the orders table. What this means is that if a supplier_id value existed in the suppliers table and also existed in the orders table, the supplier_id value would not appear in this result set.

EXAMPLE - USING ORDER BY

The following is a MINUS operator example that uses an ORDER BY clause:

SELECT supplier_id, supplier_name
FROM suppliers
WHERE state = 'Florida'
MINUS
SELECT company_id, company_name
FROM companies
WHERE company_id <= 400
ORDER BY 2;

In this MINUS example, since the column names are different between the two SELECT statements, it is more advantageous to reference the columns in the ORDER BY clause by their position in the result set. In this example, we've sorted the results by supplier_name / company_name in ascending order, as denoted by the "ORDER BY 2".

The supplier_name / company_name fields are in position #2 in the result set.

ORACLE/PLSQL: SUBQUERIES

This Oracle tutorial explains how to use Oracle subqueries with syntax and examples.

WHAT IS A SUBQUERY IN ORACLE?

In Oracle, a subquery is a query within a query. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.

WHERE CLAUSE

Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.

For example:

SELECT * 
FROM all_tables tabs
WHERE tabs.table_name IN (SELECT cols.table_name
                          FROM all_tab_columns cols
                          WHERE cols.column_name = 'SUPPLIER_ID');

Limitations

Oracle allows up to 255 levels of subqueries in the WHERE clause.

FROM CLAUSE

A subquery can also be found in the FROM clause. These are called inline views.

For example:

SELECT suppliers.name, subquery1.total_amt
FROM suppliers,
 (SELECT supplier_id, SUM(orders.amount) AS total_amt
  FROM orders
  GROUP BY supplier_id) subquery1
WHERE subquery1.supplier_id = suppliers.supplier_id;

In this example, we've created a subquery in the FROM clause as follows:

(SELECT supplier_id, SUM(orders.amount) AS total_amt
 FROM orders
 GROUP BY supplier_id) subquery1

This subquery has been aliased with the name subquery1. This will be the name used to reference this subquery or any of its fields.

Limitations

Oracle allows an unlimited number of subqueries in the FROM clause.

SELECT CLAUSE

A subquery can also be found in the SELECT clause.

For example:

SELECT tbls.owner, tbls.table_name,
  (SELECT COUNT(column_name) AS total_columns
   FROM all_tab_columns cols
   WHERE cols.owner = tbls.owner
   AND cols.table_name = tbls.table_name) subquery2
FROM all_tables tbls;

In this example, we've created a subquery in the SELECT clause as follows:

(SELECT COUNT(column_name) AS total_columns
 FROM all_tab_columns cols
 WHERE cols.owner = tbls.owner
 AND cols.table_name = tbls.table_name) subquery2

The subquery has been aliased with the name subquery2. This will be the name used to reference this subquery or any of its fields.

The trick to placing a subquery in the select clause is that the subquery must return a single value. This is why an aggregate function such as SUM function, COUNT function, MIN function, or MAX function is commonly used in the subquery.

ORACLE/PLSQL: DATA TYPES

The following is a list of datatypes available in Oracle/PLSQL, which includes character, numeric, date/time, LOB and rowid datatypes.

CHARACTER DATATYPES

The following are the Character Datatypes in Oracle/PLSQL:

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation
char(size)	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Where size is the number of characters to store. Fixed-length strings. Space padded.
nchar(size)	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Where size is the number of characters to store. Fixed-length NLS string Space padded.
nvarchar2(size)	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Where size is the number of characters to store. Variable-length NLS string.
varchar2(size)	Maximum size of 4000 bytes. Maximum size of 32KB in PLSQL.	Maximum size of 4000 bytes. Maximum size of 32KB in PLSQL.	Maximum size of 4000 bytes. Maximum size of 32KB in PLSQL.	Where size is the number of characters to store. Variable-length string.
long	Maximum size of 2GB.	Maximum size of 2GB.	Maximum size of 2GB.	Variable-length strings. (backward compatible)
raw	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Variable-length binary strings
long raw	Maximum size of 2GB.	Maximum size of 2GB.	Maximum size of 2GB.	Variable-length binary strings. (backward compatible)

NUMERIC DATATYPES

The following are the Numeric Datatypes in Oracle/PLSQL:

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation
number(p,s)	Precision can range from 1 to 38. Scale can range from -84 to 127.	Precision can range from 1 to 38. Scale can range from -84 to 127.	Precision can range from 1 to 38. Scale can range from -84 to 127.	Where p is the precision and s is the scale. For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
numeric(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, numeric(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
float
dec(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, dec(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
decimal(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, decimal(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
integer
int
smallint
real
double precision

DATE/TIME DATATYPES

The following are the Date/Time Datatypes in Oracle/PLSQL:

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation
date	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.
timestamp (fractional seconds precision)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds. For example: timestamp(6)
timestamp (fractional seconds precision) with time zone	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone displacement value. For example: timestamp(5) with time zone
timestamp (fractional seconds precision) with local time zone	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone expressed as the session time zone. For example: timestamp(4) with local time zone
interval year (year precision) to month	year precision is the number of digits in the year. (default is 2)	year precision is the number of digits in the year. (default is 2)	year precision is the number of digits in the year. (default is 2)	Time period stored in years and months. For example: interval year(4) to month
interval day (day precision) to second (fractional seconds precision)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	Time period stored in days, hours, minutes, and seconds. For example: interval day(2) to second(6)

LARGE OBJECT (LOB) DATATYPES

The following are the LOB Datatypes in Oracle/PLSQL:

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation
bfile	Maximum file size of 4GB.	Maximum file size of 232-1 bytes.	Maximum file size of 264-1 bytes.	File locators that point to a binary file on the server file system (outside the database).
blob	Store up to 4GB of binary data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).	Stores unstructured binary large objects.
clob	Store up to 4GB of character data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.	Stores single-byte and multi-byte character data.
nclob	Store up to 4GB of character text data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data.	Stores unicode data.

ROWID DATATYPES

The following are the Rowid Datatypes in Oracle/PLSQL:

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation
rowid	The format of the rowid is: BBBBBBB.RRRR.FFFFF Where BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.	The format of the rowid is: BBBBBBB.RRRR.FFFFF Where BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.	The format of the rowid is: BBBBBBB.RRRR.FFFFF Where BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.	Fixed-length binary data. Every record in the database has a physical address or rowid.
urowid(size)				Universal rowid. Where size is optional.