How to use SELECT Statement in Oracle? With Example

SELECT Statement in Oracle

The most common Structured Query Language (SQL) command for retrieving data from tables is the SELECT statement, which forms the basis of data retrieval in Oracle databases. All relational databases employ SQL as their primary access tool, which enables users to create, query, and modify database objects and data. Oracle was the first business to release a product that made use of SQL, an English-based language that doesn’t require programming knowledge and has a clear structure, grammar, and syntax.

Because the SELECT statement operates at a logical level, you specify the criteria to filter data, and Oracle obtains all rows that satisfy the criteria in a single step, saving you from having to worry about retrieval techniques or physical storage. Oracle typically follows the ANSI/ISO standard SQL syntax, therefore the majority of the SQL examples it provides work with any database management system that supports the language.

Learn the Statement: The Basics

The SELECT and FROM keywords will be used in every Oracle query you run because they are essential parts of any data retrieval statement. The FROM keyword designates the table or tables that contain the columns you want to view, while the SELECT keyword tells Oracle which columns you want to see.

Consider a simple NEWSPAPER table which might look like this:

NEWSPAPER Table Example:

FEATURE         S       PAGE
--------------- - ----------
National News   A          1
Sports          D          1
Editorials      A         12
Business        E          1
Weather         C          2

You can use the asterisk (*) wildcard with the SELECT statement to get all of the data from a table.

Code Example: Selecting All Columns

SELECT *
FROM NEWSPAPER;

Output:

FEATURE         S       PAGE
--------------- - ----------
National News   A          1
Sports          D          1
Editorials      A         12
Business        E          1
Weather         C          2
Births          F          7
Bridge          B          2
Classified      F          8
Comics          C          4
Doctor Is In    F          6
Modern Life     B          1
Movies          B          4
Obituaries      F          6
Television      B          7

Each column and row from the NEWSPAPER table is returned by this query.

Specifying Data: Choose Specific Columns

Frequently, you will just need to view specific columns in a table. After the SELECT keyword, you can list the appropriate column names, separated by commas, in place of *.

Code Example: Selecting Specific Columns

SELECT Feature, Page
FROM NEWSPAPER;

Output:

FEATURE                 PAGE
-------------------- ----------
National News           1
Sports                  1
Editorials             12
Business                1
Weather                 2
Births                  7
Bridge                  2
Classified              8
Comics                  4
Doctor Is In            6
Modern Life             1
Movies                  4
Obituaries              6
Television              7

Column Aliases

When working with column expressions (calculated or virtual columns), you can use column aliases, which are temporary alternate names, to improve readability. These aliases are especially helpful in SQL*Plus for improving the readability of report output. To define an alias, you use the AS keyword, however this is frequently optional (for example, using DaysOut in an expression).

An alias can be used to name this new column, for example, if you’re calculating DaysOut from ReturnedDate - CheckoutDate:

Code Example: Using a Column Alias for a Calculation

SELECT Name, Title, CheckoutDate, ReturnedDate, ReturnedDate-CheckoutDate AS DaysOut
FROM BOOKSHELF_CHECKOUT
ORDER BY Name, CheckoutDate;

In this case, the calculated column ReturnedDate-CheckoutDate is represented by the alias DaysOut.

Formatting Output with SQL*Plus

When you run a SQL SELECT command, SQLPlus, Oracle’s interactive query tool, makes default assumptions about how to display the data. SQLPlus by default:

• Changes all column heads to capitalised letters.
• Sets the width of a column to the database’s specified length. For instance, the width of a CHAR(1) column will be shown as one character.

Raw query output may become less understandable as a result of these default settings. If a column called Section is defined as CHAR(1), for instance, SQL*Plus may show its heading as just S.

These settings can be changed, and SQL*Plus instructions allow you to personalise the output format. For this, the COLUMN command is an effective tool that lets you modify the format and heading of any column in a SELECT statement.

Code Example: Formatting a Column with SQL*Plus Let’s format the cust_last_name and sum(amount_sold) columns for a report.

SET LINESIZE 70
TTITLE 'Customer Sales Report | Utah Region'
COLUMN cust_last_name FORMAT a12 WRAP HEADING 'Last | Name'
COLUMN cust_city FORMAT a15 HEADING 'City'
COLUMN sum(amount_sold) FORMAT $999,999.99 WRAP HEADING 'Total | Sales'
SELECT cust_last_name, cust_city, sum(amount_sold)
FROM customers NATURAL JOIN sales
WHERE cust_state_province = 'UT'
GROUP BY cust_last_name, cust_city;

Output:

Mon Jan 12                                             page    1
                                Customer Sales Report
                                Utah Region
Last                               Total
Name        City                   Sales
------------ --------------- ------------
Vankirk      Farmington        $19,279.94
Campbell     Farmington            $11.99
Robbinette   Farmington        $11,167.65
CONFIDENTIAL REPORT
9 rows selected.

As an illustration:

1. SET LINESIZE 70 sets the maximum line width to 70 characters.
2. TTITLE sets the top title for each page of the report, supporting multiple lines with |.
3. COLUMN cust_last_name FORMAT a12 WRAP HEADING 'Last | Name' sets the cust_last_name column to alphanumeric (a) format with a width of 12, allows wrapping, and defines a two-line heading.
4. COLUMN sum(amount_sold) FORMAT $999,999.99 WRAP HEADING 'Total | Sales' formats the sum(amount_sold) column as currency with wrapping and a custom heading.

Additional helpful SQL*Plus formatting commands include BTITLE (bottom title for each page) and SET PAGESIZE (maximum lines per page). It is also possible to save the formatted output to a file using the SPOOL command.

In Oracle, the SELECT statement is your main tool for retrieving data because it lets you be very specific about the information you need and how you want it displayed. Its clauses and SQL*Plus formatting commands allow you to customise the query results for individual reporting needs and clarity.