ORACLE/PLSQL: OR CONDITION

This Oracle tutorial explains how to use the Oracle OR condition with syntax and examples.

DESCRIPTION

The Oracle OR Condition is used to test multiple conditions where records are returned when any one of the conditions are met. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

The syntax for the Oracle OR Condition is:

WHERE condition1
OR condition2
...
OR condition_n;

Parameters or Arguments

condition1, condition2, condition_n are any of the conditions that must be met for the records to be selected.

NOTE

The Oracle OR condition allows you to test 2 or more conditions.
The Oracle OR condition requires that any of the conditions (ie: condition1, condition2, condition_n) be must be met for the record to be included in the result set.

EXAMPLE - WITH SELECT STATEMENT

The first Oracle OR condition example that we'll take a look at involves an Oracle SELECT statement with 2 conditions:

SELECT *
FROM customers
WHERE state = 'California'
OR available_credit > 500;

This Oracle OR condition example would return all customers that reside in either the state of California or haveavailable_credit greater than 500. Because the * is used in the SELECT statement, all fields from the customers table would appear in the result set.

EXAMPLE - WITH SELECT STATEMENT (3 CONDITIONS)

The next Oracle OR example looks at an Oracle SELECT statement with 3 conditions. If any of these conditions is met, the record will be included in the result set.

SELECT supplier_id
FROM suppliers
WHERE supplier_name = 'IBM'
OR city = 'New York'
OR offices > 5;

This Oracle OR condition example would return all supplier_id values where the supplier's name is either IBM, city is New York, or offices is greater than 5.

EXAMPLE - WITH INSERT STATEMENT

The Oracle OR condition can be used in the Oracle INSERT statement.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city = 'New York'
OR city = 'Newark';

This Oracle OR example would insert into the suppliers table, all account_no and name records from the customers table that reside in either New York or Newark.

EXAMPLE - WITH UPDATE STATEMENT

The Oracle OR condition can be used in the Oracle UPDATE statement.

For example:

UPDATE suppliers
SET supplier_name = 'Apple'
WHERE supplier_name = 'RIM'
OR available_products < 10;

This Oracle OR condition example would update all supplier_name values in the suppliers table to Apple where thesupplier_name was RIM or its availabe_products was less than 10.

EXAMPLE - WITH DELETE STATEMENT

The Oracle OR condition can be used in the Oracle DELETE statement.

For example:

DELETE FROM suppliers
WHERE supplier_name = 'HP'
OR employees >= 60;

This Oracle OR condition example would delete all suppliers from the suppliers table whose supplier_name was HP or its employees was greater than or equal to 60.

ORACLE/PLSQL: AND & OR CONDITIONS

This Oracle tutorial explains how to use the AND condition and the OR condition together in an Oracle query with syntax and examples.

DESCRIPTION

The Oracle AND condition and OR condition can be combined in a SELECT, INSERT, UPDATE, or DELETE statement.

When combining these conditions, it is important to use brackets so that the database knows what order to evaluate each condition. (Just like when you were learning the order of operations in Math class!)

SYNTAX

The syntax for the Oracle AND condition and OR condition together is:

WHERE condition1
AND condition2
...
OR condition_n;

Parameters or Arguments

condition1, condition2, condition_n are the conditions that are evaluated to determine if the records will be selected.

NOTE

The Oracle AND & OR conditions allow you to test multiple conditions.
Don't forget the order of operation brackets!

EXAMPLE - WITH SELECT STATEMENT

Let's look at an example that combines the AND and OR conditions in a SELECT statement.

For example:

SELECT *
FROM suppliers
WHERE (state = 'California' AND supplier_name = 'IBM')
OR (supplier_id < 5000);

This AND & OR example would return all suppliers that reside in the state of California whose supplier_name is IBM and all suppliers whose supplier_id is less than 5000. The brackets determine the order that the AND and OR conditions are evaluated. Just like you learned in the order of operations in Math class!

The next example takes a look at a more complex statement.

For example:

SELECT supplier_id
FROM suppliers
WHERE (supplier_name = 'IBM')
OR (supplier_name = 'Apple' AND state = 'Florida')
OR (supplier_name = 'Best Buy' AND status = 'Active' AND state = 'California');

This AND & OR example would return all supplier_id values where the supplier_name is IBM OR the supplier_name is Apple and the state is Florida OR the supplier_name is Best Buy, the status is Active and the state is California.

EXAMPLE - WITH INSERT STATEMENT

This next AND & OR example demonstrates how the AND condition and OR condition can be combined in the INSERT statement.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, customer_name
FROM customers
WHERE (customer_name = 'Apple' OR customer_name = 'Samsung')
AND customer_id > 20;

This Oracle AND and OR example would insert into the suppliers table, all account_no and customer_name records from thecustomers table whose customer_name is either Apple or Samsung and where the customer_id is greater than 20.

EXAMPLE - WITH UPDATE STATEMENT

This AND & OR example shows how the AND and OR conditions can be used in the UPDATE statement.

For example:

UPDATE suppliers
SET supplier_name = 'Samsung'
WHERE supplier_name = 'RIM'
AND (state = 'California' OR state = 'Florida');

This Oracle AND & OR condition example would update all supplier_name values in the suppliers table to Samsung where the supplier_name was RIM and resides in either the state of California or Florida.

EXAMPLE - WITH DELETE STATEMENT

Finally, this last AND & OR example demonstrates how the AND and OR conditions can be used in the DELETE statement.

For example:

DELETE FROM suppliers
WHERE state = 'Florida'
AND (product = 'PC computers' OR supplier_name = 'Dell');

This Oracle AND and OR condition example would delete all records from the suppliers table whose state was Florida and either the product was PC computers or the supplier name was Dell.

ORACLE/PLSQL: DISTINCT CLAUSE

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

DESCRIPTION

The Oracle DISTINCT clause is used to remove duplicates from the result set. The DISTINCT clause can only be used withSELECT statements.

SYNTAX

The syntax for the Oracle DISTINCT clause is:

SELECT DISTINCT expressions
FROM tables
WHERE conditions;

Parameters or Arguments

expressions 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

When only one expression is provided in the DISTINCT clause, the query will return the unique values for that expression.
When more than one expression is provided in the DISTINCT clause, the query will retrieve unique combinations for the expressions listed.
In Oracle, the DISTINCT clause doesn't ignore NULL values. So when using the DISTINCT clause in your SQL statement, your result set will include NULL as a distinct value.

EXAMPLE - WITH SINGLE EXPRESSION

Let's look at the simplest Oracle DISTINCT clause example. We can use the Oracle DISTINCT clause to return a single field that removes the duplicates from the result set.

For example:

SELECT DISTINCT state
FROM customers
WHERE last_name = 'Smith';

This Oracle DISTINCT example would return all unique state values from the customers table where the customer'slast_name is 'Smith'.

EXAMPLE - WITH MULTIPLE EXPRESSIONS

Let's look at how you might use the Oracle DISTINCT clause to remove duplicates from more than one field in your SELECT statement.

For example:

SELECT DISTINCT city, state
FROM customers
WHERE total_orders > 10
ORDER BY city;

This Oracle DISTINCT clause example would return each unique city and state combination from the customers table where the total_orders is greater than 10. The results are sorted in ascending order by city.

In this case, the DISTINCT applies to each field listed after the DISTINCT keyword, and therefore returns distinct combinations.

ORACLE/PLSQL: IN CONDITION

This Oracle tutorial explains how to use the Oracle IN condition with syntax and examples.

DESCRIPTION

The Oracle IN condition is used to help reduce the need to use multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

The syntax for the Oracle IN condition is:

expression IN (value1, value2, .... value_n);

Parameters or Arguments

expression is a value to test.

value1, value2..., or value_n are the values to test against expression.

NOTE

The Oracle IN condition will return the records where expression is value1, value2..., or value_n.
The Oracle IN condition is also called the Oracle IN operator.

EXAMPLE - WITH CHARACTER

Let's look at an Oracle IN condition example using character values.

The following is an Oracle SELECT statement that uses the IN condition to compare character values:

SELECT *
FROM customers
WHERE customer_name IN ('IBM', 'Hewlett Packard', 'Microsoft');

This Oracle IN condition example would return all rows where the customer_name is either IBM, Hewlett Packard, or Microsoft. Because the * is used in the SELECT, all fields from the customers table would appear in the result set.

The above IN example is equivalent to the following SELECT statement:

SELECT *
FROM customers
WHERE customer_name = 'IBM'
OR customer_name = 'Hewlett Packard'
OR customer_name = 'Microsoft';

As you can see, using the Oracle IN condition makes the statement easier to read and more efficient.

EXAMPLE - WITH NUMERIC

Next, let's look at an Oracle IN condition example using numeric values.

For example:

SELECT *
FROM orders
WHERE order_id IN (10000, 10001, 10003, 10005);

This Oracle IN condition example would return all orders where the order_id is either 10000, 10001, 10003, or 10005.

The above IN example is equivalent to the following SELECT statement:

SELECT *
FROM orders
WHERE order_id = 10000
OR order_id = 10001
OR order_id = 10003
OR order_id = 10005;

EXAMPLE - USING NOT OPERATOR

Finally, let's look at an IN condition example using the Oracle NOT operator.

For example:

SELECT *
FROM customers
WHERE customer_name NOT IN ( 'IBM', 'Hewlett Packard', 'Microsoft');

This Oracle IN condition example would return all rows where the customer_name is not IBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

ORACLE/PLSQL: IS NULL CONDITION

This Oracle tutorial explains how to use the Oracle IS NULL condition with syntax and examples.

DESCRIPTION

The Oracle IS NULL condition is used to test for a NULL value. You can use the Oracle IS NULL condition in either a SQL statement or in a block of PLSQL code.

SYNTAX

The syntax for the Oracle IS NULL Condition is:

expression IS NULL

Parameters or Arguments

expression is the value to test.

NOTE

If expression is a NULL value, the condition evaluates to TRUE.
If expression is not a NULL value, the condition evaluates to FALSE.

EXAMPLE - WITH SELECT STATEMENT

Here is an example of how to use the Oracle IS NULL condition in a SELECT statement:

SELECT *
FROM suppliers
WHERE supplier_name IS NULL;

This Oracle IS NULL example will return all records from the suppliers table where the supplier_name contains a null value.

EXAMPLE - WITH INSERT STATEMENT

Here is an example of how to use the Oracle IS NULL condition in an INSERT statement:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE city IS NULL;

This Oracle IS NULL example will insert records into the suppliers table where the city contains a null value.

EXAMPLE - WITH UPDATE STATEMENT

Here is an example of how to use the Oracle IS NULL condition in an UPDATE statement:

UPDATE suppliers
SET name = 'Apple'
WHERE name IS NULL;

This Oracle IS NULL example will update records in the suppliers table where the name contains a null value.

EXAMPLE - WITH DELETE STATEMENT

Here is an example of how to use the Oracle IS NULL condition in a DELETE statement:

DELETE FROM suppliers
WHERE supplier_name IS NULL;

This Oracle IS NULL example will delete all records from the suppliers table where the supplier_name contains a null value.

EXAMPLE - USING PLSQL CODE

You can use the Oracle IS NULL condition in PLSQL to check if a value is null.

For example,

IF Lvalue IS NULL then

   ...

END IF;

If Lvalue contains a null value, the "IF" expression will evaluate to TRUE.

This Oracle tutorial explains how to test for a value that is not null.

ORACLE/PLSQL: IS NOT NULL CONDITION

This Oracle tutorial explains how to use the Oracle IS NOT NULL condition with syntax and examples.

DESCRIPTION

The Oracle IS NOT NULL condition is used to test for a NOT NULL value. You can use the Oracle IS NOT NULL condition in either a SQL statement or in a block of PLSQL code.

SYNTAX

The syntax for the Oracle IS NOT NULL Condition is:

expression IS NOT NULL

Parameters or Arguments

expression is the value to test.

NOTE

If expression is NOT a NULL value, the condition evaluates to TRUE.
If expression is a NULL value, the condition evaluates to FALSE.

EXAMPLE - WITH SELECT STATEMENT

Here is an example of how to use the Oracle IS NOT NULL condition in a SELECT statement:

SELECT *
FROM customers
WHERE customer_name IS NOT NULL;

This Oracle IS NOT NULL example will return all records from the customers table where the customer_name does not contain a null value.

EXAMPLE - WITH INSERT STATEMENT

Here is an example of how to use the Oracle IS NOT NULL condition in an INSERT statement:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE account_no IS NOT NULL;

This Oracle IS NOT NULL example will insert records into the suppliers table where the account_no does not contain a null value in the customers table.

EXAMPLE - WITH UPDATE STATEMENT

Here is an example of how to use the Oracle IS NOT NULL condition in an UPDATE statement:

UPDATE customers
SET status = 'Active'
WHERE customer_name IS NOT NULL;

This Oracle IS NOT NULL example will update records in the customers table where the customer_name does not contain a null value.

EXAMPLE - WITH DELETE STATEMENT

Here is an example of how to use the Oracle IS NOT NULL condition in a DELETE statement:

DELETE FROM customers
WHERE status IS NOT NULL;

This Oracle IS NOT NULL example will delete all records from the customers table where the status does not contain a null value.

EXAMPLE - USING PLSQL CODE

You can use the Oracle IS NOT NULL condition in PLSQL to check if a value is not null.

For example:

IF Lvalue IS NOT NULL then

   ...

END IF;

If Lvalue does not contain a null value, the "IF" expression will evaluate to TRUE.

This Oracle tutorial explains how to test for a value that is null.

ORACLE/PLSQL: LIKE CONDITION

This Oracle tutorial explains how to use the Oracle LIKE condition (to perform pattern matching) with syntax, examples, and practice exercises.

DESCRIPTION

The Oracle LIKE condition allows wildcards to be used in the WHERE clause of a SELECT, INSERT, UPDATE, orDELETE statement. This allows you to perform pattern matching.

SYNTAX

The syntax for the Oracle LIKE Condition is:

expression LIKE pattern [ ESCAPE 'escape_character' ]

Parameters or Arguments

expression is a character expression such as a column or field.

pattern is a character expression that contains pattern matching. The patterns that you can choose from are:

Wildcard	Explanation
%	Allows you to match any string of any length (including zero length)
_	Allows you to match on a single character

escape_character is optional. It allows you to test for literal instances of a wildcard character such as % or _.

NOTE

See also the Oracle REGEXP_LIKE condition.

EXAMPLE - USING % WILDCARD (PERCENT SIGN WILDCARD)

The first Oracle LIKE example that we will look at involves using the % wildcard (percent sign wildcard).

Let's explain how the % wildcard works in the Oracle LIKE condition. We want to find all of the customers whose last_namebegins with 'Ap'.

SELECT last_name
FROM customers
WHERE last_name LIKE 'Ap%';

You can also using the % wildcard multiple times within the same string. For example,

SELECT last_name
FROM customers
WHERE last_name LIKE '%er%';

In this Oracle LIKE condition example, we are looking for all customers whose last_name contains the characters 'er'.

EXAMPLE - USING _ WILDCARD (UNDERSCORE WILDCARD)

Next, let's explain how the _ wildcard (underscore wildcard) works in the Oracle LIKE condition. Remember that _ wildcard is looking for only one character.

For example:

SELECT supplier_name
FROM suppliers
WHERE supplier_name LIKE 'Sm_th';

This Oracle LIKE condition example would return all suppliers whose supplier_name is 5 characters long, where the first two characters is 'Sm' and the last two characters is 'th'. For example, it could return suppliers whose supplier_name is 'Smith', 'Smyth', 'Smath', 'Smeth', etc.

Here is another example:

SELECT *
FROM suppliers
WHERE account_number LIKE '92314_';

You might find that you are looking for an account number, but you only have 5 of the 6 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return suppliers whose account numbers are:

923140, 923141, 923142, 923143, 923144, 923145, 923146, 923147, 923148, 923149

EXAMPLE - USING NOT OPERATOR

Next, let's look at how you would use the Oracle NOT Operator with wildcards.

Let's use the % wilcard with the NOT Operator. You could also use the Oracle LIKE condition to find suppliers whose name does not start with 'T'.

For example:

SELECT supplier_name
FROM suppliers
WHERE supplier_name NOT LIKE 'W%';

By placing the NOT Operator in front of the Oracle LIKE condition, you are able to retrieve all suppliers whose supplier_namedoes not start with 'W'.

EXAMPLE - USING ESCAPE CHARACTERS

It is important to understand how to "Escape Characters" when pattern matching. These examples deal specifically with escaping characters in Oracle.

Let's say you wanted to search for a % or a _ character in the Oracle LIKE condition. You can do this using an Escape character.

Please note that you can only define an escape character as a single character (length of 1).

For example:

SELECT *
FROM suppliers
WHERE supplier_name LIKE 'Water!%' ESCAPE '!';

This Oracle LIKE condition example identifies the ! character as an escape character. This statement will return all suppliers whose name is Water%.

Here is another more complicated example using escape characters in the Oracle LIKE condition.

SELECT *
FROM suppliers
WHERE supplier_name LIKE 'H%!%' ESCAPE '!';

This Oracle LIKE condition example returns all suppliers whose name starts with H and ends in %. For example, it would return a value such as 'Hello%'.

You can also use the escape character with the _ character in the Oracle LIKE condition.

For example:

SELECT *
FROM suppliers
WHERE supplier_name LIKE 'H%!_' ESCAPE '!';

This Oracle LIKE condition example returns all suppliers whose name starts with H and ends in _. For example, it would return a value such as 'Hello_'.

FREQUENTLY ASKED QUESTIONS

Question: How do you incorporate the Oracle UPPER function with the Oracle LIKE condition? I'm trying to query against a free text field for all records containing the word "test". The problem is that it can be entered in the following ways: TEST, Test, or test.

Answer: To answer this question, let's look at an example.

Let's say that we have a suppliers table with a field called supplier_name that contains the values TEST, Test, or test.

If we wanted to find all records containing the word "test", regardless of whether it was stored as TEST, Test, or test, we could run either of the following SELECT statements:

SELECT *
FROM suppliers
WHERE UPPER(supplier_name) LIKE ('TEST%');

SELECT *
FROM suppliers
WHERE UPPER(supplier_name) LIKE UPPER('test%')

These SELECT statements use a combination of the Oracle UPPER function and the LIKE condition to return all of the records where the supplier_name field contains the word "test", regardless of whether it was stored as TEST, Test, or test.

PRACTICE EXERCISE #1:

Based on the employees table populated with the following data, find all records whose employee_name ends with the letter "h".

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 for Practice Exercise #1:

The following SELECT statement uses the Oracle LIKE condition to return the records whose employee_name ends with the letter "h".

SELECT *
FROM employees
WHERE employee_name LIKE '%h';

It would return the following result set:

EMPLOYEE_NUMBER	EMPLOYEE_NAME	SALARY
1001	John Smith	62000
1004	Jack Horvath	42000

PRACTICE EXERCISE #2:

Based on the employees table populated with the following data, find all records whose employee_name contains the letter "s".

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 for Practice Exercise #2:

The following Oracle SELECT statement would use the Oracle LIKE condition to return the records whose employee_namecontains the letter "s".

SELECT *
FROM employees
WHERE employee_name LIKE '%s%';

It would return the following result set:

EMPLOYEE_NUMBER	EMPLOYEE_NAME	SALARY
1002	Jane Anderson	57500
1003	Brad Everest	71000

PRACTICE EXERCISE #3:

Based on the suppliers table populated with the following data, find all records whose supplier_id is 4 digits and starts with "500".

CREATE TABLE suppliers
( supplier_id varchar2(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 ('5008', 'Microsoft', 'New York');

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

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

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

Solution for Practice Exercise #3:

The following Oracle SELECT statement would use the Oracle LIKE condition to return the records whose supplier_id is 4 digits and starts with "500".

SELECT *
FROM suppliers
WHERE supplier_id LIKE '500_';

It would return the following result set:

SUPPLIER_ID	SUPPLIER_NAME	CITY
5008	Microsoft	New York
5009	IBM	Chicago

ORACLE/PLSQL: REGEXP_LIKE CONDITION

This Oracle tutorial explains how to use the Oracle REGEXP_LIKE condition (to perform regular expression matching) with syntax and examples.

* Not to be confused with the LIKE condition which performs simple pattern matching.

DESCRIPTION

The Oracle REGEXP_LIKE condition allows you to perform regular expression matching in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement

SYNTAX

The syntax for the Oracle REGEXP_LIKE Condition is:

REGEXP_LIKE ( expression, pattern [, match_parameter ] )

Parameters or Arguments

expression is a character expression such as a column or field. It can be a VARCHAR2, CHAR, NVARCHAR2, NCHAR, CLOB or NCLOB data type.

pattern is the regular expression matching information. It can be a combination of the following:

Value	Description
^	Matches the beginning of a string. If used with a match_parameter of 'm', it matches the start of a line anywhere within expression.
$	Matches the end of a string. If used with a match_parameter of 'm', it matches the end of a line anywhere withinexpression.
*	Matches zero or more occurrences.
+	Matches one of more occurrences.
?	Matches zero or one occurrence.
.	Matches any character except NULL.
\|	Used like an "OR" to specify more than one alternative.
[ ]	Used to specify a matching list where you are trying to match any one of the characters in the list.
[^ ]	Used to specify a nonmatching list where you are trying to match any character except for the ones in the list.
( )	Used to group expressions as a subexpression.
{m}	Matches m times.
{m,}	Matches at least m times.
{m,n}	Matches at least m times, but no more than n times.
\n	n is a number between 1 and 9. Matches the nth subexpression found within ( ) before encountering \n.
[..]	Matches one collation element that can be more than one character.
[::]	Matches character classes.
[==]	Matches equivalence classes.
\d	Matches a digit character.
\D	Matches a nondigit character.
\w	Matches a word character.
\W	Matches a nonword character.
\s	Matches a whitespace character.
\S	matches a non-whitespace character.
\A	Matches the beginning of a string or matches at the end of a string before a newline character.
\Z	Matches at the end of a string.
*?	Matches the preceding pattern zero or more occurrences.
+?	Matches the preceding pattern one or more occurrences.
??	Matches the preceding pattern zero or one occurrence.
{n}?	Matches the preceding pattern n times.
{n,}?	Matches the preceding pattern at least n times.
{n,m}?	Matches the preceding pattern at least n times, but not more than m times.

match_parameter is optional. It allows you to modify the matching behavior for the REGEXP_LIKE condition. It can be a combination of the following:

Value	Description
'c'	Perform case-sensitive matching.
'i'	Perform case-insensitive matching.
'n'	Allows the period character (.) to match the newline character. By default, the period is a wildcard.
'm'	expression is assumed to have multiple lines, where ^ is the start of a line and $ is the end of a line, regardless of the position of those characters in expression. By default, expression is assumed to be a single line.
'x'	Whitespace characters are ignored. By default, whitespace characters are matched like any other character.

NOTE

The REGEXP_LIKE condition uses the input character set to evaluate strings.
If you specify match_parameter values that conflict, the REGEXP_LIKE condition will use the last value to break the conflict.
If the match_parameter is omitted, the REGEXP_LIKE condition will use the case-sensitivity as determined by the NLS_SORT parameter.
See also the Oracle LIKE condition.

EXAMPLE - MATCH ON MORE THAN ONE ALTERNATIVE

The first Oracle REGEXP_LIKE condition example that we will look at involves using the | pattern.

Let's explain how the | pattern works in the Oracle REGEXP_LIKE condition. For example:

SELECT last_name
FROM contacts
WHERE REGEXP_LIKE (last_name, 'Anders(o|e|a)n');

This REGEXP_LIKE example will return all contacts whose last_name is either Anderson, Andersen, or Andersan. The | pattern tells us to look for the letter "o", "e", or "a".

EXAMPLE - MATCH ON BEGINNING

Next, let's use the REGEXP_LIKE condition to match on the beginning of a string. For example:

SELECT last_name
FROM contacts
WHERE REGEXP_LIKE (last_name, '^A(*)');

This REGEXP_LIKE example will return all contacts whose last_name starts with 'A'.

EXAMPLE - MATCH ON END

Next, let's use the REGEXP_LIKE condition to match on the end of a string. For example:

SELECT last_name
FROM contacts
WHERE REGEXP_LIKE (last_name, '(*)n$');

This REGEXP_LIKE example will return all contacts whose last_name ends with 'n'.

ORACLE/PLSQL: NOT CONDITION

This Oracle tutorial explains how to use the Oracle NOT condition with syntax and examples.

DESCRIPTION

The Oracle NOT Condition (also called the NOT Operator) is used to negate a condition in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

The syntax for the Oracle NOT Condition is:

NOT condition

Parameters or Arguments

condition is the condition to negate.

NOTE

The Oracle NOT condition requires that the opposite of the condition be must be met for the record to be included in the result set.

EXAMPLE - COMBINE WITH IN CONDITION

The Oracle NOT condition can be combined with the IN Condition.

For example:

SELECT *
FROM customers
WHERE customer_name NOT IN ( 'IBM', 'Hewlett Packard', 'Microsoft' );

This Oracle NOT example would return all rows from the customers table where the customer_name is not IBM, Hewlett Packard, or Microsoft. Sometimes, it is more efficient to list the values that you do not want, as opposed to the values that you do want.

EXAMPLE - COMBINE WITH IS NULL CONDITION

The Oracle NOT condition can also be combined with the IS NULL Condition.

For example,

SELECT *
FROM customers
WHERE last_name IS NOT NULL;

This Oracle NOT example would return all records from the customers table where the last_name does not contain a NULL value.

EXAMPLE - COMBINE WITH LIKE CONDITION

The Oracle NOT condition can also be combined with the LIKE Condition.

For example:

SELECT customer_name
FROM customers
WHERE customer_name NOT LIKE 'S%';

By placing the Oracle NOT Operator in front of the LIKE condition, you are able to retrieve all customers whosecustomer_name does not start with 'S'.

EXAMPLE - COMBINE WITH BETWEEN CONDITION

The Oracle NOT condition can also be combined with the BETWEEN Condition. Here is an example of how you would combine the NOT Operator with the BETWEEN Condition.

For example:

SELECT *
FROM customers
WHERE customer_id NOT BETWEEN 4000 AND 4100;

This Oracle NOT example would return all rows where the customer_id was NOT between 4000 and 4100, inclusive. It would be equivalent to the following Oracle SELECT statement:

SELECT *
FROM customers
WHERE customer_id < 4000
OR customer_id > 4100;

EXAMPLE - COMBINE WITH EXISTS CONDITION

The Oracle NOT condition can also be combined with the EXISTS Condition.

For example,

SELECT *
FROM suppliers
WHERE NOT EXISTS (SELECT * 
                  FROM orders
                  WHERE suppliers.supplier_id = orders.supplier_id);

This Oracle NOT example would return all records from the suppliers table where there are no records in the orders table for the given supplier_id.

ORACLE/PLSQL: ALIASES

This Oracle tutorial explains how to use Oracle ALIASES (temporary names for columns or tables) with syntax and examples.

DESCRIPTION

Oracle ALIASES can be used to create a temporary name for columns or tables.

COLUMN ALIASES are used to make column headings in your result set easier to read.
TABLE ALIASES are used to shorten your SQL to make it easier to read or when you are performing a self join (ie: listing the same table more than once in the FROM clause).

SYNTAX

The syntax to ALIAS A COLUMN in Oracle/PLSQL is:

column_name AS alias_name

The syntax to ALIAS A TABLE in Oracle/PLSQL is:

table_name alias_name

Parameters or Arguments

column_name is the original name of the column that you wish to alias.

table_name is the original name of the table that you wish to alias.

alias_name is the temporary name to assign.

NOTE

If the alias_name contains spaces, you must enclose the alias_name in quotes.
It is acceptable to use spaces when you are aliasing a column name. However, it is not generally good practice to use spaces when you are aliasing a table name.
The alias_name is only valid within the scope of the SQL statement.

EXAMPLE - ALIAS A COLUMN

Generally, aliases are used to make the column headings in your result set easier to read. For example, when concatenating fields together, you might alias the result.

For example:

SELECT contact_id, first_name || last_name AS NAME
FROM contacts
WHERE last_name = 'Anderson';

In this example, we've aliased the second column (ie: first_name and last_name concatenated) as NAME. As a result, NAME will display as the heading for the second column when the result set is returned. Because our alias_name did not include any spaces, we are not required to enclose the alias_name in quotes.

However, it would have been perfectly acceptable to write this example using quotes as follows:

SELECT contact_id, first_name || last_name AS "NAME"
FROM contacts
WHERE last_name = 'Anderson';

Next, let's look at an example where we are required to enclose the alias_name in quotes.

For example:

SELECT contact_id, first_name || last_name AS "CONTACT NAME"
FROM contacts
WHERE last_name = 'Anderson';

In this example, we've aliased the second column (ie: first_name and last_name concatenated) as "CONTACT NAME". Since there are spaces in this alias_name, "CONTACT NAME" must be enclosed in quotes.

EXAMPLE - ALIAS A TABLE

When you create an alias on a table, it is either because you plan to list the same table name more than once in the FROM clause (ie: self join), or you want to shorten the table name to make the SQL statement shorter and easier to read.

Let's look at an example of how to alias a table name in Oracle/PLSQL.

For example:

SELECT p.product_id, p.product_name, categories.category_name
FROM products p
INNER JOIN categories
ON p.category_id = categories.category_id
ORDER BY p.product_name ASC, categories.category_name ASC;

In this example, we've created an alias for the products table called p. Now within this SQL statement, we can refer to theproducts table as p.

When creating table aliases, it is not necessary to create aliases for all of the tables listed in the FROM clause. You can choose to create aliases on any or all of the tables.

For example, we could modify our example above and create an alias for the categories table as well.

SELECT p.product_id, p.product_name, c.category_name
FROM products p
INNER JOIN categories c
ON p.category_id = c.category_id
ORDER BY p.product_name ASC, c.category_name ASC;

Now we have an alias for categories table called c as well as the alias for the products table called p.

ORACLE/PLSQL: JOINS

This Oracle tutorial explains how to use JOINS (inner and outer) in Oracle with syntax, visual illustrations, and examples.

DESCRIPTION

Oracle JOINS are used to retrieve data from multiple tables. An Oracle JOIN is performed whenever two or more tables are joined in a SQL statement.

There are 4 different types of Oracle joins:

Oracle INNER JOIN (or sometimes called simple join)
Oracle LEFT OUTER JOIN (or sometimes called LEFT JOIN)
Oracle RIGHT OUTER JOIN (or sometimes called RIGHT JOIN)
Oracle FULL OUTER JOIN (or sometimes called FULL JOIN)

So let's discuss Oracle JOIN syntax, look at visual illustrations of Oracle JOINS, and explore Oracle JOIN examples.

INNER JOIN (SIMPLE JOIN)

Chances are, you've already written a statement that uses an Oracle INNER JOIN. It is the most common type of join. Oracle INNER JOINS return all rows from multiple tables where the join condition is met.

Syntax

The syntax for the Oracle INNER JOIN is:

SELECT columns
FROM table1 
INNER JOIN table2
ON table1.column = table2.column;

Visual Illustration

In this visual diagram, the Oracle INNER JOIN returns the shaded area:

The Oracle INNER JOIN would return the records where table1 and table2 intersect.

Example

Here is an example of an Oracle INNER JOIN:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers 
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This Oracle INNER JOIN example would return all rows from the suppliers and orders tables where there is a matching supplier_id value in both the suppliers and orders tables.

Let's look at some data to explain how the INNER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have another table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2003/05/12
500126	10001	2003/05/13
500127	10004	2003/05/14

If we run the Oracle SELECT statement (that contains an INNER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id	name	order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13

The rows for Microsoft and NVIDIA from the supplier table would be omitted, since the supplier_id's 10002 and 10003 do not exist in both tables. The row for 500127 (order_id) from the orders table would be omitted, since the supplier_id 10004 does not exist in the suppliers table.

Old Syntax

As a final note, it is worth mentioning that the Oracle INNER JOIN example above could be rewritten using the older implicit syntax as follows (but we still recommend using the INNER JOIN keyword syntax):

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id;

LEFT OUTER JOIN

Another type of join is called an Oracle LEFT OUTER JOIN. This type of join returns all rows from the LEFT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the Oracle LEFT OUTER JOIN is:

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the LEFT OUTER JOIN keywords are replaced with LEFT JOIN.

Visual Illustration

In this visual diagram, the Oracle LEFT OUTER JOIN returns the shaded area:

The Oracle LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect withtable1.

Example

Here is an example of an Oracle LEFT OUTER JOIN:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This LEFT OUTER JOIN example would return all rows from the suppliers table and only those rows from the orders table where the joined fields are equal.

If a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as >

in the result set.

Let's look at some data to explain how LEFT OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2003/05/12
500126	10001	2003/05/13

If we run the SELECT statement (that contains a LEFT OUTER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
LEFT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id	supplier_name	order_date
10000	IBM	2003/05/12
10001	Hewlett Packard	2003/05/13
10002	Microsoft
10003	NVIDIA

The rows for Microsoft and NVIDIA would be included because a LEFT OUTER JOIN was used. However, you will notice that the order_date field for those records contains a value.

Old Syntax

As a final note, it is worth mentioning that the LEFT OUTER JOIN example above could be rewritten using the older implicit syntax that utilizes the outer join operator (+) as follows (but we still recommend using the LEFT OUTER JOIN keyword syntax):

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id(+);

RIGHT OUTER JOIN

Another type of join is called an Oracle RIGHT OUTER JOIN. This type of join returns all rows from the RIGHT-hand table specified in the ON condition and only those rows from the other table where the joined fields are equal (join condition is met).

Syntax

The syntax for the Oracle RIGHT OUTER JOIN is:

SELECT columns
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the RIGHT OUTER JOIN keywords are replaced with RIGHT JOIN.

Visual Illustration

In this visual diagram, the Oracle RIGHT OUTER JOIN returns the shaded area:

The Oracle RIGHT OUTER JOIN would return the all records from table2 and only those records from table1 that intersect with table2.

Example

Here is an example of an Oracle RIGHT OUTER JOIN:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This RIGHT OUTER JOIN example would return all rows from the orders table and only those rows from the suppliers table where the joined fields are equal.

If a supplier_id value in the orders table does not exist in the suppliers table, all fields in the suppliers table will display as >

in the result set.

Let's look at some data to explain how RIGHT OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

supplier_id	supplier_name
10000	Apple
10001	Google

We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2013/08/12
500126	10001	2013/08/13
500127	10002	2013/08/14

If we run the SELECT statement (that contains a RIGHT OUTER JOIN) below:

SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers
RIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

order_id	order_date	supplier_name
500125	2013/08/12	Apple
500126	2013/08/13	Google
500127	2013/08/14

The row for 500127 (order_id) would be included because a RIGHT OUTER JOIN was used. However, you will notice that the supplier_name field for that record contains a value.

Old Syntax

As a final note, it is worth mentioning that the RIGHT OUTER JOIN example above could be rewritten using the older implicit syntax that utilizes the outer join operator (+) as follows (but we still recommend using the RIGHT OUTER JOIN keyword syntax):

SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers, orders
WHERE suppliers.supplier_id(+) = orders.supplier_id;

FULL OUTER JOIN

Another type of join is called an Oracle FULL OUTER JOIN. This type of join returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in place where the join condition is not met.

Syntax

The syntax for the Oracle FULL OUTER JOIN is:

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

In some databases, the FULL OUTER JOIN keywords are replaced with FULL JOIN.

Visual Illustration

In this visual diagram, the Oracle FULL OUTER JOIN returns the shaded area:

The Oracle FULL OUTER JOIN would return the all records from both table1 and table2.

Example

Here is an example of an Oracle FULL OUTER JOIN:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This FULL OUTER JOIN example would return all rows from the suppliers table and all rows from the orders table and whenever the join condition is not met, would be extended to those fields in the result set.

If a supplier_id value in the suppliers table does not exist in the orders table, all fields in the orders table will display as >

in the result set. If a supplier_id value in the orders table does not exist in the suppliers table, all fields in the suppliers table will display as in the result set.

Let's look at some data to explain how FULL OUTER JOINS work:

We have a table called suppliers with two fields (supplier_id and supplier_name). It contains the following data:

supplier_id	supplier_name
10000	IBM
10001	Hewlett Packard
10002	Microsoft
10003	NVIDIA

We have a second table called orders with three fields (order_id, supplier_id, and order_date). It contains the following data:

order_id	supplier_id	order_date
500125	10000	2013/08/12
500126	10001	2013/08/13
500127	10004	2013/08/14

If we run the SELECT statement (that contains a FULL OUTER JOIN) below:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers
FULL OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

Our result set would look like this:

supplier_id	supplier_name	order_date
10000	IBM	2013/08/12
10001	Hewlett Packard	2013/08/13
10002	Microsoft
10003	NVIDIA
		2013/08/14

The rows for Microsoft and NVIDIA would be included because a FULL OUTER JOIN was used. However, you will notice that the order_date field for those records contains a value.

The row for supplier_id 10004 would be also included because a FULL OUTER JOIN was used. However, you will notice that the supplier_id and supplier_name field for those records contain a value.

Old Syntax

As a final note, it is worth mentioning that the FULL OUTER JOIN example above could not have been written in the old syntax without using a UNION query

ORACLE/PLSQL: BETWEEN CONDITION

This Oracle tutorial explains how to use the Oracle BETWEEN condition with syntax and examples.

DESCRIPTION

The Oracle BETWEEN Condition is used to retrieve values within a range in a SELECT, INSERT, UPDATE, or DELETEstatement.

SYNTAX

The syntax for the Oracle BETWEEN Condition is:

expression BETWEEN value1 AND value2;

Parameters or Arguments

expression is a column or calculation.

value1 and value2 create an inclusive range that expression is compared to.

NOTE

The Oracle BETWEEN Condition will return the records where expression is within the range of value1 and value2(inclusive).

EXAMPLE - WITH NUMERIC

Let's look at some Oracle BETWEEN condition examples using numeric values. The following numeric example uses the BETWEEN condition to retrieve values within a numeric range.

For example:

SELECT *
FROM customers
WHERE customer_id BETWEEN 4000 AND 4999;

This Oracle BETWEEN example would return all rows from the customers table where the customer_id is between 4000 and 4999 (inclusive). It is equivalent to the following SELECT statement:

SELECT *
FROM customers
WHERE customer_id >= 4000
AND customer_id <= 4999;

EXAMPLE - WITH DATE

Next, let's look at how you would use the Oracle BETWEEN condition with Dates. The following date example uses the BETWEEN condition to retrieve values within a date range.

For example:

SELECT *
FROM order_details
WHERE order_date BETWEEN TO_DATE ('2014/02/01', 'yyyy/mm/dd')
AND TO_DATE ('2014/02/28', 'yyyy/mm/dd');

This Oracle BETWEEN condition example would return all records from the order_details table where the order_date is between Feb 1, 2014 and Feb 28, 2014 (inclusive). It would be equivalent to the following SELECT statement:

SELECT *
FROM order_details
WHERE order_date >= TO_DATE('2014/02/01', 'yyyy/mm/dd')
AND order_date <= TO_DATE('2014/02/28','yyyy/mm/dd');

EXAMPLE - USING NOT OPERATOR

The Oracle BETWEEN condition can also be combined with the Oracle NOT operator. Here is an example of how you would combine the BETWEEN condition with the NOT Operator.

For example:

SELECT *
FROM customers
WHERE customer_id NOT BETWEEN 3000 AND 3500;

This Oracle BETWEEN example would return all rows from the customers table where the customer_id was NOT between 3000 and 3500, inclusive. It would be equivalent to the following SELECT statement:

SELECT *
FROM customers
WHERE customer_id < 3000
OR customer_id > 3500;

ORACLE/PLSQL: INSERT STATEMENT

This Oracle tutorial explains how to use the Oracle INSERT statement with syntax and examples. We've also added some practice exercises that you can try for yourself.

DESCRIPTION

The Oracle INSERT statement is used to insert a single record or multiple records into a table in Oracle.

SYNTAX

The syntax for the Oracle INSERT statement when inserting a single record using the VALUES keyword is:

INSERT INTO table
(column1, column2, ... )
VALUES
(expression1, expression2, ... );

Or the syntax for the Oracle INSERT statement when inserting multiple records using a SELECT statement is:

INSERT INTO table
(column1, column2, ... )
SELECT expression1, expression2, ...
FROM source_table
WHERE conditions;

Parameters or Arguments

table is the table to insert the records into.

column1, column2 are the columns in the table to insert values.

expression1, expression2 are the values to assign to the columns in the table. So column1 would be assigned the value ofexpression1, column2 would be assigned the value of expression2, and so on.

source_table is the source table when inserting data from another table.

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

NOTE

When inserting records into a table using the Oracle INSERT statement, you must provide a value for every NOT NULL column.
You can omit a column from the Oracle INSERT statement if the column allows NULL values.

EXAMPLE - USING VALUES KEYWORD

The simplest way to create an Oracle INSERT query to list the values using the VALUES keyword.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
VALUES
(5000, 'Apple');

This Oracle INSERT statement would result in one record being inserted into the suppliers table. This new record would have a supplier_id of 5000 and a supplier_name of 'Apple'.

EXAMPLE - USING SELECT STATEMENT

You can also create more complicated Oracle INSERT statements using SELECT statements.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT account_no, name
FROM customers
WHERE customer_id > 5000;

By placing a SELECT statement within the INSERT statement, you can perform multiples inserts quickly.

With this type of insert, you may wish to check for the number of rows being inserted. You can determine the number of rows that will be inserted by running the following Oracle SELECT statement before performing the insert.

SELECT count(*)
FROM customers
WHERE customer_id > 5000;

FREQUENTLY ASKED QUESTIONS

Question: I am setting up a database with clients. I know that you use the Oracle INSERT statement to insert information in the database, but how do I make sure that I do not enter the same client information again?

Answer: You can make sure that you do not insert duplicate information by using the EXISTS condition.

For example, if you had a table named clients with a primary key of client_id, you could use the following Oracle INSERT statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT supplier_id, supplier_name, 'advertising'
FROM suppliers
WHERE NOT EXISTS (SELECT *
                  FROM clients
                  WHERE clients.client_id = suppliers.supplier_id);

This Oracle INSERT statement inserts multiple records with a subselect.

If you wanted to insert a single record, you could use the following Oracle INSERT statement:

INSERT INTO clients
(client_id, client_name, client_type)
SELECT 10345, 'IBM', 'advertising'
FROM dual
WHERE NOT EXISTS (SELECT *
                  FROM clients
                  WHERE clients.client_id = 10345);

The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.

Question: How can I insert multiple rows of explicit data in one INSERT command in Oracle?

Answer: The following is an example of how you might insert 3 rows into the suppliers table in Oracle, using an Oracle INSERT statement:

INSERT ALL
  INTO suppliers (supplier_id, supplier_name) VALUES (1000, 'IBM')
  INTO suppliers (supplier_id, supplier_name) VALUES (2000, 'Microsoft')
  INTO suppliers (supplier_id, supplier_name) VALUES (3000, 'Google')
SELECT * FROM dual;

PRACTICE EXERCISE #1:

Based on the contacts table, insert a contact record whose contact_id is 1000, last_name is Smith, first_name is Jane, and address is 10 Somewhere St.:

CREATE TABLE contacts
( contact_id number(10) not null,
  last_name varchar2(50) not null,
  first_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(20),
  zip_code varchar2(10),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

Solution for Practice Exercise #1:

The following Oracle INSERT statement would insert this record into the employees table:

INSERT INTO contacts
(contact_id, last_name, first_name, address)
VALUES
(1000, 'Smith', 'Jane', '10 Somewhere St.');

PRACTICE EXERCISE #2:

Based on the contacts and customers table, insert into the contacts table all customers who reside in the state of 'Florida'.

CREATE TABLE contacts
( contact_id number(10) not null,
  last_name varchar2(50) not null,
  first_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(20),
  zip_code varchar2(10),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

CREATE TABLE customers
( customer_id number(10) not null,
  last_name varchar2(50) not null,
  first_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(20),
  zip_code varchar2(10),
  CONSTRAINT customers_pk PRIMARY KEY (customer_id)
);

Solution for Practice Exercise #2:

The following Oracle INSERT statement would insert this record into the suppliers table:

INSERT INTO contacts
(contact_id, last_name, first_name, address, city, state, zip_code)
SELECT customer_id, last_name, first_name, address, city, state, zip_code
FROM customers
WHERE state = 'Florida';

Since the number of fields in the contacts and customers table are the same and the fields are listed in the same order, you could write the solution as follows (though it is generally better practice to list the column names in case the table definitions change):

INSERT INTO contacts
SELECT *
FROM customers
WHERE state = 'Florida';

ORACLE/PLSQL: UPDATE STATEMENT

This Oracle tutorial explains how to use the Oracle UPDATE statement with syntax, examples, and practice exercises.

DESCRIPTION

The Oracle UPDATE statement is used to update existing records in a table in an Oracle database. There are 2 syntaxes for an update query in Oracle depending on whether you are performing a traditional update or updating one table with data from another table.

SYNTAX

The syntax for the Oracle UPDATE statement when updating one table is:

UPDATE table
SET column1 = expression1,
    column2 = expression2,
    ...
WHERE conditions;

The syntax for the Oracle UPDATE statement when updating one table with data from another table is:

UPDATE table1
SET column1 = (SELECT expression1
               FROM table2
               WHERE conditions)
WHERE conditions;

Parameters or Arguments

column1, column2 are the columns that you wish to update.

expression1, expression2 are the new values to assign to the column1, column2. So column1 would be assigned the value ofexpression1, column2 would be assigned the value of expression2, and so on.

conditions are the conditions that must be met for the update to execute.

EXAMPLE - UPDATE SINGLE COLUMN

Let's look at a very simple Oracle UPDATE query example.

UPDATE customers
SET last_name = 'Anderson'
WHERE customer_id = 5000;

This Oracle UPDATE example would update the last_name to 'Anderson' in the customers table where the customer_id is 5000.

EXAMPLE - UPDATE MULTIPLE COLUMNS

Let's look at an Oracle UPDATE example where you might want to update more than one column with a single UPDATE statement.

UPDATE customers
SET state = 'California',
    customer_rep = 32
WHERE customer_id > 100;

When you wish to update multiple columns, you can do this by separating the column/value pairs with commas.

This Oracle UPDATE statement example would update the state to 'California' and the customer_rep to 32 where thecustomer_id is greater than 100.

EXAMPLE - UPDATE TABLE WITH DATA FROM ANOTHER TABLE

Let's look at an Oracle UPDATE example that shows how to update a table with data from another table.

UPDATE customers
SET c_details = (SELECT contract_date
                 FROM suppliers
                 WHERE suppliers.supplier_name = customers.customer_name)
WHERE customer_id < 1000;

This UPDATE example would update only the customers table for all records where the customer_id is less than 1000. When the supplier_name from the suppliers table matches the customer_name from the customers table, the contract_date from the suppliers table would be copied to the c_details field in the customers table.

EXAMPLE - USING EXISTS CLAUSE

You can also perform more complicated updates in Oracle.

You may wish to update records in one table based on values in another table. Since you can't list more than one table in the Oracle UPDATE statement, you can use the Oracle EXISTS clause.

For example:

UPDATE suppliers
SET supplier_name = (SELECT customers.customer_name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.customer_name
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

In this Oracle UPDATE example, whenever a supplier_id matched a customer_id value, the supplier_name would be overwritten to the customer_name from the customers table.

PRACTICE EXERCISE #1:

Based on the suppliers table populated with the following data, update the city to "San Francisco" for all records whosesupplier_name is "IBM".

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', 'Chicago');

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');

Solution for Practice Exercise #1:

The following UPDATE statement would perform this update in Oracle.

UPDATE suppliers
SET city = 'San Francisco'
WHERE supplier_name = 'IBM';

The suppliers table would now look like this:

SUPPLIER_ID	SUPPLIER_NAME	CITY
5001	Microsoft	Chicago
5002	IBM	San Francisco
5003	Red Hat	Detroit
5004	NVIDIA	New York

PRACTICE EXERCISE #2:

Based on the suppliers and customers table populated with the following data, update the city in the suppliers table with thecity in the customers table when the supplier_name in the suppliers table matches the customer_name in the customerstable.

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 (5005, 'NVIDIA', 'LA');


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', 'San Francisco');

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

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

Solution for Practice Exercise #2:

The following UPDATE statement would perform this update in Oracle.

UPDATE suppliers
SET city = (SELECT customers.city
            FROM customers
            WHERE customers.customer_name = suppliers.supplier_name)
WHERE EXISTS (SELECT customers.city
              FROM customers
              WHERE customers.customer_name = suppliers.supplier_name);

The suppliers table would now look like this:

SUPPLIER_ID	SUPPLIER_NAME	CITY
5001	Microsoft	San Francisco
5002	IBM	Toronto
5003	Red Hat	Newark
5004	NVIDIA	LA

ORACLE/PLSQL: DELETE STATEMENT

This Oracle tutorial explains how to use the Oracle DELETE statement with syntax, examples, and practice exercises.

DESCRIPTION

The Oracle DELETE statement is used to delete a single record or multiple records from a table in Oracle.

SYNTAX

The syntax for the Oracle DELETE statement is:

DELETE FROM table
WHERE conditions;

Parameters or Arguments

table is the table that you wish to delete records from.

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

NOTE

You do not need to list fields in the Oracle DELETE statement since you are deleting the entire row from the table.

EXAMPLE - USING ONE CONDITION

Let's look at a simple Oracle DELETE query example, where we just have one condition in the DELETE statement.

For example:

DELETE FROM customers
WHERE last_name = 'Smith';

This Oracle DELETE example would delete all records from the customers table where the last_name is Smith.

You may wish to check for the number of rows that will be deleted. You can determine the number of rows that will be deleted by running the following Oracle SELECT statement before performing the delete.

SELECT count(*)
FROM customers
WHERE last_name = 'Smith';

EXAMPLE - USING TWO CONDITIONS

Let's look at an Oracle DELETE example, where we just have two conditions in the DELETE statement.

For example:

DELETE FROM customers
WHERE last_name = 'Anderson'
AND customer_id > 25;

This Oracle DELETE example would delete all records from the customers table where the last_name is 'Anderson' and thecustomer_id is greater than 25.

SELECT count(*)
FROM customers
WHERE last_name = 'Anderson'
AND customer_id > 25;

EXAMPLE - USING EXISTS CLAUSE

You can also perform more complicated deletes.

You may wish to delete records in one table based on values in another table. Since you can't list more than one table in the Oracle FROM clause when you are performing a delete, you can use the Oracle EXISTS clause.

For example:

DELETE FROM suppliers
WHERE EXISTS
  ( SELECT customers.customer_name
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id
    AND customer_id > 25 );

This Oracle DELETE example would delete all records in the suppliers table where there is a record in the customers table whose customer_id is greater than 25, and the customer_id matches the supplier_id.

If you wish to determine the number of rows that will be deleted, you can run the following Oracle SELECT statement beforeperforming the delete.

SELECT COUNT(*) FROM suppliers
WHERE EXISTS
  ( SELECT customers.customer_name
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id
    AND customer_id > 25 );

FREQUENTLY ASKED QUESTIONS

Question: How would I write an Oracle DELETE statement to delete all records in TableA whose data in field1 & field2 DO NOT match the data in fieldx & fieldz of TableB?

Answer: You could try something like this for your Oracle DELETE statement:

DELETE FROM TableA
WHERE NOT EXISTS
  ( SELECT *
    FROM TableB
     WHERE TableA.field1 = TableB.fieldx
     AND TableA.field2 = TableB.fieldz );

PRACTICE EXERCISE #1:

Based on the contacts table, delete all records from the contacts table who reside in the City of 'Las Vegas' and whosefirst_name is 'Jane'.

CREATE TABLE contacts
( contact_id number(10) not null,
  last_name varchar2(50) not null,
  first_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(2),
  zip_code varchar2(10),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

Solution for Practice Exercise #1:

The following Oracle DELETE statement would delete these records from the contacts table:

DELETE FROM contacts
WHERE city = 'Las Vegas'
AND first_name = 'Jane';

PRACTICE EXERCISE #2:

Based on the contacts table, delete all records from the contacts table whose contact_id is greater than or equal to 5000 and less than 6000.

CREATE TABLE contacts
( contact_id number(10) not null,
  last_name varchar2(50) not null,
  first_name varchar2(50) not null,
  address varchar2(50),
  city varchar2(50),
  state varchar2(2),
  zip_code varchar2(10),
  CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);

Solution for Practice Exercise #2:

The following Oracle DELETE statement would delete these records from the contacts table:

DELETE FROM contacts
WHERE contact_id >= 5000
AND contact_id < 6000.

Or you could write the solution using the BETWEEN clause as follows:

DELETE FROM contacts
WHERE contact_id BETWEEN 5000 AND 5999;

ORACLE/PLSQL: EXISTS CONDITION

This Oracle tutorial explains how to use the Oracle EXISTS condition with syntax and examples.

The Oracle EXISTS condition is used in combination with a subquery and is considered "to be met" if the subquery returns at least one row. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

The syntax for the Oracle EXISTS condition is:

WHERE EXISTS ( subquery );

Parameters or Arguments

subquery is a SELECT statement.

NOTE

Oracle SQL statements that use the Oracle EXISTS Condition are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use the EXISTS Condition.

EXAMPLE - WITH SELECT STATEMENT

Let's look at a simple example.

The following is a SELECT statement that uses the EXISTS condition:

SELECT *
FROM customers
WHERE EXISTS (SELECT *
              FROM order_details
              WHERE customers.customer_id = order_details.customer_id);

This Oracle EXISTS condition example will return all records from the customers table where there is at least one record in the order_details table with the matching customer_id.

EXAMPLE - WITH SELECT STATEMENT USING NOT EXISTS

The Oracle EXISTS condition can also be combined with the NOT operator.

For example,

SELECT *
FROM customers
WHERE NOT EXISTS (SELECT *
                  FROM order_details
                  WHERE customers.customer_id = order_details.customer_id);

This Oracle EXISTS example will return all records from the customers table where there are no records in the order_detailstable for the given customer_id.

EXAMPLE - WITH INSERT STATEMENT

The following is an example of an INSERT statement that uses the EXISTS condition:

INSERT INTO contacts
(contact_id, contact_name)
SELECT supplier_id, supplier_name
FROM suppliers
WHERE EXISTS (SELECT *
              FROM order_details
              WHERE suppliers.supplier_id = order_details.supplier_id);

EXAMPLE - WITH UPDATE STATEMENT

The following is an example of an UPDATE statement that uses the EXISTS condition:

UPDATE suppliers
SET supplier_name = (SELECT customers.name
                     FROM customers
                     WHERE customers.customer_id = suppliers.supplier_id)
WHERE EXISTS (SELECT customers.name
              FROM customers
              WHERE customers.customer_id = suppliers.supplier_id);

EXAMPLE - WITH DELETE STATEMENT

The following is an example of a DELETE statement that uses the EXISTS condition:

DELETE FROM suppliers
WHERE EXISTS (SELECT *
              FROM order_details
              WHERE suppliers.supplier_id = order_details.supplier_id);

ORACLE/PLSQL: GROUP BY CLAUSE

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

DESCRIPTION

The Oracle GROUP BY Clause is used in a SELECT statement to collect data across multiple records and group the results by one or more columns.

SYNTAX

The syntax for the Oracle GROUP BY Clause is:

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

Parameters or Arguments

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

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

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.

EXAMPLE - USING SUM FUNCTION

Let's look at an Oracle GROUP BY query example that uses the SUM function.

This Oracle GROUP BY example uses the SUM function to return the name of the product and the total sales (for the product).

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

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

EXAMPLE - USING COUNT FUNCTION

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

This GROUP BY example uses the COUNT function to return the category and the number of suppliers (in that category) that have over 45 available_products.

SELECT category, COUNT(*) AS "Number of suppliers"
FROM suppliers
WHERE available_products > 45
GROUP BY category;

EXAMPLE - USING MIN FUNCTION

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

This GROUP BY example uses 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;

EXAMPLE - USING MAX FUNCTION

Finally, let's look at how we could use the GROUP BY clause with the MAX function.

This GROUP BY example uses 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;

Discover Millions of Products your daily Need

Amazon::::Discover Millions of Products your daily Need

Amway Products in Hyderabad. All India Distributors (Certified Dietitian / Nutrition Advisor )

Amway Products in Kakinada. All India Distributors ( Certified Dietitian / Nutrition Advisor)

Monday, December 1, 2014

Oracle Tutorial 2

ORACLE/PLSQL: OR CONDITION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

EXAMPLE - WITH SELECT STATEMENT

EXAMPLE - WITH SELECT STATEMENT (3 CONDITIONS)

EXAMPLE - WITH INSERT STATEMENT

EXAMPLE - WITH UPDATE STATEMENT

EXAMPLE - WITH DELETE STATEMENT

ORACLE/PLSQL: AND & OR CONDITIONS

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

EXAMPLE - WITH SELECT STATEMENT

EXAMPLE - WITH INSERT STATEMENT

EXAMPLE - WITH UPDATE STATEMENT

EXAMPLE - WITH DELETE STATEMENT

ORACLE/PLSQL: DISTINCT CLAUSE

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

EXAMPLE - WITH SINGLE EXPRESSION

EXAMPLE - WITH MULTIPLE EXPRESSIONS

ORACLE/PLSQL: IN CONDITION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

EXAMPLE - WITH CHARACTER

EXAMPLE - WITH NUMERIC

EXAMPLE - USING NOT OPERATOR

ORACLE/PLSQL: IS NULL CONDITION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

EXAMPLE - WITH SELECT STATEMENT

EXAMPLE - WITH INSERT STATEMENT

EXAMPLE - WITH UPDATE STATEMENT

EXAMPLE - WITH DELETE STATEMENT

EXAMPLE - USING PLSQL CODE

ORACLE/PLSQL: IS NOT NULL CONDITION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

EXAMPLE - WITH SELECT STATEMENT

EXAMPLE - WITH INSERT STATEMENT

EXAMPLE - WITH UPDATE STATEMENT

EXAMPLE - WITH DELETE STATEMENT

EXAMPLE - USING PLSQL CODE

ORACLE/PLSQL: LIKE CONDITION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

EXAMPLE - USING % WILDCARD (PERCENT SIGN WILDCARD)

EXAMPLE - USING _ WILDCARD (UNDERSCORE WILDCARD)

EXAMPLE - USING NOT OPERATOR

EXAMPLE - USING ESCAPE CHARACTERS

FREQUENTLY ASKED QUESTIONS

PRACTICE EXERCISE #1:

Solution for Practice Exercise #1:

PRACTICE EXERCISE #2:

Solution for Practice Exercise #2:

PRACTICE EXERCISE #3:

Solution for Practice Exercise #3:

ORACLE/PLSQL: REGEXP_LIKE CONDITION

DESCRIPTION

SYNTAX

Parameters or Arguments