Using SELECT Statement in Oracle

This article is written about how to use the Oracle SELECT declaration with syntax, examples, and exercise exercises.

Description

The Oracle SELECT assertion is used to retrieve archives from one or extra tables in an Oracle database.

Syntax

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

SELECT expressions
FROM tables
[WHERE conditions];

Parameters or Arguments

expressions The columns or calculations that you desire to retrieve. Use * if you wish to choose all columns. tables The tables that you desire to retrieve records from. There have to be at least one table listed in the FROM clause. WHERE conditions Optional. The conditions that need to be met for the data to be selected. If no stipulations are provided, then all information will be selected.

Example – Select all fields from one table

Let’s seem to be at how to use an Oracle SELECT question to pick out all fields from a table.

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

In this Oracle SELECT assertion example, we’ve got used * to signify that we want to select all fields from the residences desk where the range of bathrooms is larger than or equal to 2 The end result set is sorted by means of home_type in ascending order.

Example – Select individual fields from one table

You can additionally use the Oracle SELECT announcement to choose person fields from the table, as antagonistic 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 lavatories fields from the properties desk where the home_id is less than five hundred and the home_type is ‘two-storey’. The effects are sorted with the aid of home_type in ascending order and then toilets in descending order.

Example – Select fields from multiple tables

You can additionally use the Oracle SELECT declaration to retrieve fields from a couple of tables with the aid of the use of 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 instance joins two tables together to offers us a result set that shows the home_id and customer_name fields the place the customer_id value suits in both the clients and residences table. The results are sorted by using home_id in ascending order.

Practice Exercise #1:

Based on the contacts desk below, pick all fields from the contacts table whose last_name is ‘Smith’, contact_id is increased than or equal one thousand 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 declaration would pick out these documents from the personnel table:

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

Or you could write the solution the usage of the BETWEEN clause as follows:

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