This SQL tutorial demonstrates how to create a basic SELECT statement in Oracle, and provides explanations, examples and exercises. For this lesson’s exercises, use this link.

This tutorial is a part of several posts explaining how to write basic queries in Oracle. To read additional posts regarding this subject, please use the following links:

• Writing Basic SQL SELECT statements
• SQL Arithmetic Operators
• SQL Column Aliases
• SQL String Concatenation
• SQL DISTINCT keyword

---

Basic Select Statement

the syntax for a basic SELECT statement in Oracle is:

SELECT *
FROM table_name

For example :

SELECT *
FROM products

In this example we displayed all columns of data in Products table.

The Oracle SELECT clause:

• Let you choose what you want to display.
• The asterisk sign (*) indicates that you want to select all fields contained in this table.

The Oracle FROM clause:

• Let you specify from which table you want to retrieve all of these fields.
• A table’s name always appears after the FROM keyword.

 

Selecting Specific Columns

the syntax for selecting specific columns in Oracle is:

SELECT column_name, column_name, column_name ..
FROM table_name

For example :

SELECT product_id , product_name
FROM products

The example above retrieves specific columns.

The Oracle SELECT clause:

• Let you choose what columns you want to display (“Projection”).
• After the Oracle SELECT keyword, specify the names of the columns that you would like to retrieve, separated by comma (,).
• You can specify as many columns as you want; you can even specify the same column more than once.
• The columns appear in the order selected.

The Oracle FROM clause:

• Let you specify from which table you want to display these columns.
• A table’s name always appears after the Oracle FROM keyword.

 

General Guidelines

• In each SQL statement, Oracle SELECT and FROM clauses are mandatory. Without both of them, SQL statements are not valid (there is no point in seeking to display data without indicating what should be retrieved and from where).

-- SQL Statement 1 - not valid (FROM clause is missing)
SELECT product_name, unit_price, supplier_id

-- SQL Statement 2 - not valid (SELECT clause is missing)
FROM products 

-- SQL Statement 3 - valid
SELECT product_name, unit_price, supplier_id
FROM products

• The order of the Oracle SELECT and FROM clauses cannot be changed, the Oracle SELECT clause will always be listed first; you cannot write an Oracle SELECT statement that begins with a FROM clause.

-- Not a valid SQL statement
FROM products
SELECT product_name, product_id, unit_price

• It is possible to specify a column’s name multiple times; the data of this column will simply appear again and again, according to the number of times the column was specified.

SELECT product_id, product_name, unit_price, supplier_id
FROM products

• To enhance readability – even though the Oracle SQL syntax is neither case-sensitive, nor sensitive to spaces or line breaks, ensure writing in an orderly manner: write the keywords in capital letters, names of columns/tables in small letters, insert a line break after each command and indents when required.