Oracle Tutorial 1

ORACLE/PLSQL: SELECT STATEMENT

DESCRIPTION

The Oracle SELECT statement is used to retrieve records from one or more tables in an Oracle database.

SYNTAX

The syntax for the Oracle SELECT statement is:

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

EXAMPLE - SELECT ALL FIELDS FROM ONE TABLE

Let's look at how to use an Oracle SELECT query to select all fields from a table.

SELECT *
FROM homes
WHERE bathrooms >= 2
ORDER BY home_type ASC;

In this Oracle SELECT statement example, we've used * to signify that we wish to select all fields from the homes table where the number of bathrooms is greater than or equal to 2. The result set is sorted by home_type in ascending order.

EXAMPLE - SELECT INDIVIDUAL FIELDS FROM ONE TABLE

You can also use the Oracle SELECT statement to select individual fields from the table, as opposed to all fields from the table.

For example:

SELECT home_id, home_type, bathrooms
FROM homes
WHERE home_id < 500
AND home_type = 'two-storey'
ORDER BY home_type ASC, bathrooms DESC;

This Oracle SELECT example would return only the home_id, home_type, and bathrooms fields from the homes table where the home_id is less than 500 and the home_type is 'two-storey'. The results are sorted by home_type in ascending order and then bathrooms in descending order.

EXAMPLE - SELECT FIELDS FROM MULTIPLE TABLES

You can also use the Oracle SELECT statement to retrieve fields from multiple tables by using a join.

SELECT homes.home_id, customers.customer_name
FROM customers
INNER JOIN homes
ON customers.customer_id = homes.customer_id
ORDER BY home_id;

This Oracle SELECT example joins two tables together to gives us a result set that displays the home_id andcustomer_name fields where the customer_id value matches in both the customers and homes table. The results are sorted by home_id in ascending order.

PRACTICE EXERCISE #1:

Based on the contacts table below, select all fields from the contacts table whose last_name is 'Smith', contact_id is greater than or equal 1000 and contact_id is less than or equal to 2000 (no sorting is required):

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 SELECT statement would select these records from the employees table:

SELECT *
FROM contacts
WHERE last_name = 'Smith'
AND contact_id >= 1000
AND contact_id <= 2000;

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

SELECT *
FROM contacts
WHERE last_name = 'Smith'
AND contact_id BETWEEN 1000 AND 2000;

ORACLE/PLSQL: FROM CLAUSE

This Oracle tutorial explains how to use the FROM clause in Oracle/PLSQL with syntax and examples.

DESCRIPTION

The Oracle/PLSQL FROM clause is used to list the tables and any join information required for the Oracle query.

SYNTAX

The syntax for the FROM Clause in Oracle/PLSQL is:

FROM table1
[ { INNER JOIN
  | LEFT [OUTER] JOIN
  | RIGHT [OUTER] JOIN
  | FULL [OUTER] JOIN } table2
ON table1.column1 = table2.column1 ]

Parameters or Arguments

table1 and table2 are the tables used in the SQL statement. The two tables are joined based on table1.column1 = table2.column1.

NOTE

• There must be at least one table listed in the FROM clause in Oracle/PLSQL.
• If there are two or more tables listed in the FROM clause, these tables are generally joined in the FROM clause using INNER or OUTER joins. Although the tables can also be joined using the old syntax in the WHERE clause, we recommend using new standards and including your join information in the FROM clause. See Oracle joins for more information.

EXAMPLE - WITH ONE TABLE

It is difficult to explain the syntax for the Oracle FROM clause, so let's look at some examples.

We'll start by looking at how to use the FROM clause with only a single table.

For example:

SELECT *
FROM homes
WHERE bathrooms >= 2
ORDER BY home_type ASC;

In this Oracle FROM clause example, we've used the FROM clause to list the table called homes. There are no joins performed since we are only using one table.

EXAMPLE - TWO TABLES WITH INNER JOIN

Let's look at how to use the FROM clause with two tables and an INNER JOIN.

For example:

SELECT homes.home_id, customers.last_name, customers.first_name
FROM customers
INNER JOIN homes
ON customers.customer_id = homes.customer_id
ORDER BY home_id;

This Oracle FROM clause example uses the FROM clause to list two tables - customers and homes. And we are using the FROM clause to specify an INNER JOIN between the customers and homes tables based on the customer_id column in both tables.

EXAMPLE - TWO TABLES WITH OUTER JOIN

Let's look at how to use the FROM clause when we join two tables together using an OUTER JOIN. In this case, we will look at the LEFT OUTER JOIN.

For example:

SELECT customers.customer_id, contacts.last_name, contacts.first_name
FROM customers
LEFT OUTER JOIN contacts
ON customers.customer_id = contacts.contact_id
WHERE customers.last_name = 'Smith';

This Oracle FROM clause example uses the FROM clause to list two tables - customers and contacts. And we are using the FROM clause to specify a LEFT OUTER JOIN between the customers and contacts tables based on the customer_idcolumn in both tables.

ORACLE/PLSQL: WHERE CLAUSE

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

DESCRIPTION

The Oracle WHERE clause is used to filter the results from a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

The syntax for the Oracle WHERE Clause is:

WHERE conditions;

Parameters or Arguments

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

EXAMPLE - WITH SINGLE CONDITION

It is difficult to explain the syntax for the Oracle WHERE clause, so let's look at some examples.

SELECT *
FROM customers
WHERE last_name = 'Anderson';

In this Oracle WHERE clause example, we've used the WHERE clause to filter our results from the customers table. The SELECT statement above would return all rows from the customers table where the last_name is Anderson. Because the * is used in the SELECT, all fields from the customers table would appear in the result set.

EXAMPLE - USING AND CONDITION

SELECT *
FROM suppliers
WHERE state = 'California'
AND supplier_id <= 750;

This Oracle WHERE clause example uses the WHERE clause to define multiple conditions. In this case, this SELECT statement uses the AND Condition to return all suppliers that are located in the state of California and whose supplier_id is less than or equal to 750.

EXAMPLE - USING OR CONDITION

SELECT supplier_id
FROM suppliers
WHERE supplier_name = 'Apple'
OR supplier_name = 'Microsoft';

This Oracle WHERE clause example uses the WHERE clause to define multiple conditions, but instead of using the AND Condition, it uses the OR Condition. In this case, this SELECT statement would return all supplier_id values where thesupplier_name is Apple or Microsoft.

EXAMPLE - COMBINING AND & OR CONDITIONS

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

This Oracle WHERE clause example uses the WHERE clause to define multiple conditions, but it combines the AND Condition and the OR Condition. This example would return all suppliers that reside in the state of Florida and whosesupplier_name is IBM as well as all suppliers whose supplier_id is greater 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!

EXAMPLE - JOINING TABLES

SELECT suppliers.suppler_name, orders.order_id
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
WHERE suppliers.state = 'California';

This Oracle WHERE clause example uses the WHERE clause to join multiple tables together in a single SELECT statement. This SELECT statement would return all supplier_name and order_id values where there is a matching record in the suppliersand orders tables based on supplier_id, and where the supplier's state is California.

ORACLE/PLSQL: ORDER BY CLAUSE

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

DESCRIPTION

The Oracle ORDER BY clause is used to sort the records in your result set. The ORDER BY clause can only be used inSELECT statements.

SYNTAX

The syntax for the Oracle ORDER BY clause is:

SELECT expressions
FROM tables
WHERE conditions
ORDER BY expression [ ASC | DESC ];

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.

ASC is optional. It sorts the result set in ascending order by expression (default, if no modifier is provider).

DESC is optional. It sorts the result set in descending order by expression.

NOTE

• If the ASC or DESC modifier is not provided in the ORDER BY clause, the results will be sorted by expression in ascending order (which is equivalent to "ORDER BY expression ASC").

EXAMPLE - SORTING WITHOUT USING ASC/DESC ATTRIBUTE

The Oracle ORDER BY clause can be used without specifying the ASC or DESC value. When this attribute is omitted from the ORDER BY clause, the sort order is defaulted to ASC or ascending order.

For example:

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY supplier_city;

This Oracle ORDER BY example would return all records sorted by the supplier_city field in ascending order and would be equivalent to the following ORDER BY clause:

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY supplier_city ASC;

Most programmers omit the ASC attribute if sorting in ascending order.

EXAMPLE - SORTING IN DESCENDING ORDER

When sorting your result set in descending order, you use the DESC attribute in your ORDER BY clause as follows:

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY supplier_city DESC;

This Oracle ORDER BY example would return all records sorted by the supplier_city field in descending order.

EXAMPLE - SORTING BY RELATIVE POSITION

You can also use the Oracle ORDER BY clause to sort by relative position in the result set, where the first field in the result set is 1. The next field is 2, and so on.

For example:

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY 1 DESC;

This Oracle ORDER BY would return all records sorted by the supplier_city field in descending order, since the supplier_cityfield is in position #1 in the result set and would be equivalent to the following ORDER BY clause:

SELECT supplier_city
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY supplier_city DESC;

EXAMPLE - USING BOTH ASC AND DESC ATTRIBUTES

When sorting your result set using the Oracle ORDER BY clause, you can use the ASC and DESC attributes in a singleSELECT statement.

For example:

SELECT supplier_city, supplier_state
FROM suppliers
WHERE supplier_name = 'Microsoft'
ORDER BY supplier_city DESC, supplier_state ASC;

This Oracle ORDER BY would return all records sorted by the supplier_city field in descending order, with a secondary sort by supplier_state in ascending order.

ORACLE/PLSQL: AND CONDITION

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

DESCRIPTION

The Oracle AND Condition (also called the AND Operator) is used to test for two or more conditions in a SELECT, INSERT, UPDATE, or DELETE statement.

SYNTAX

The syntax for the Oracle AND Condition is:

WHERE condition1
AND condition2
...
AND condition_n;

Parameters or Arguments

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

NOTE

• The Oracle AND condition allows you to test 2 or more conditions.
• The Oracle AND condition requires that all 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 AND condition query involves a SELECT statement with 2 conditions.

For example:

SELECT *
FROM customers
WHERE state = 'Florida'
AND customer_id > 5000;

This Oracle AND example would return all customers that reside in the state of Florida and have a customer_id > 5000. Because the * is used in the SELECT statement, all fields from the customers table would appear in the result set.

EXAMPLE - JOINING TABLES

Our next Oracle AND example shows how the AND condition can be used to join multiple tables in a SELECT statement.

For example:

SELECT orders.order_id, suppliers.supplier_name
FROM suppliers, orders
WHERE suppliers.supplier_id = orders.supplier_id
AND suppliers.supplier_name = 'Microsoft';

Though the above SQL works just fine, you would more traditionally write this SQL as follows using a proper INNER JOIN.

For example:

SELECT orders.order_id, suppliers.supplier_name
FROM suppliers
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id
WHERE suppliers.supplier_name = 'Microsoft';

This Oracle AND condition example would return all rows where the supplier_name is Microsoft. And the suppliers and orderstables are joined on supplier_id. You will notice that all of the fields are prefixed with the table names (ie: orders.order_id). This is required to eliminate any ambiguity as to which field is being referenced; as the same field name can exist in both thesuppliers and the orders tables.

In this case, the result set would only display the order_id and supplier_name fields (as listed in the first part of the SELECT statement.).

EXAMPLE - WITH INSERT STATEMENT

This next Oracle AND example demonstrates how the AND condition can be used in the INSERT statement.

For example:

INSERT INTO suppliers
(supplier_id, supplier_name)
SELECT customer_id, customer_name
FROM customers
WHERE customer_name = 'Microsoft'
AND customer_id <= 1000;

This Oracle AND condition example would insert into the suppliers table, all customer_id and customer_name records from the customers table whose customer_name is Microsoft and have a customer_id less than or equal to 1000.

EXAMPLE - WITH UPDATE STATEMENT

This Oracle AND condition example shows how the AND condition can be used in the UPDATE statement.

For example:

UPDATE suppliers
SET supplier_name = 'Apple'
WHERE supplier_name = 'RIM'
AND offices = 8;

This Oracle AND condition example would update all supplier_name values in the suppliers table to Apple where thesupplier_name was RIM and had 8 offices.

EXAMPLE - WITH DELETE STATEMENT

Finally, this last Oracle AND example demonstrates how the AND condition can be used in the DELETE statement.

For example:

DELETE FROM suppliers
WHERE supplier_name = 'Apple'
AND product = 'iPod';

This Oracle AND condition example would delete all records from the suppliers table whose supplier_name was Apple and product was iPod.

Learn more about joining tables in Oracle.