Oracle / PLSQL: SELECT Statement

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

Description

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

Syntax

The syntax for the SELECT statement in Oracle/PLSQL is:

SELECT expressions
FROM tables
[WHERE conditions];

Parameters or Arguments

expressions

The columns or calculations that you wish to retrieve. Use * if you wish to select all columns.

tables

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

WHERE conditions

Optional. The conditions that must be met for the records to be selected. If no conditions are provided, then all records will 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 and customer_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;