This Oracle tutorial explains how to use the Oracle SELECT statement with syntax, examples, and practice exercises.
The Oracle SELECT statement is used to retrieve records from one or more tables in an Oracle database.
The syntax for the SELECT statement in Oracle/PLSQL is:
Parameters or Arguments
The columns or calculations that you wish to retrieve.
The tables that you wish to retrieve records from. There must be at least one table listed in the FROM clause.
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.
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.
SELECT home_id, home_type, bathrooms
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
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,
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:
WHERE last_name = 'Smith'
AND contact_id >= 1000
AND contact_id <= 2000;
Or you could write the solution using the BETWEEN clause as follows:
WHERE last_name = 'Smith'
AND contact_id BETWEEN 1000 AND 2000;