SELECT Statement in SQL with examples

Read this Oracle Tutorial Point article to know how to Query / fetch data from the table will use the SELECT Statement in SQL in Oracle Database. To know more about the SELECT Statement, continue reading this article.

SELECT Statement:
The SELECT statement is used to retrieve data from one or more than one table, object tables, views, object views or management views.

Prerequisites:
The user must have the SELECT privileges on the defined object. The SELECT ANY TABLE allows selecting the data from any recognized object.

Efficiency of SQL SELECT Statement:
The SELECT statement can be used to select or retrieve data from the object using any of the following criteria.

• SELECTION: Selection chooses the rows in a table that are expected to return by a query.
• PROJECTION: Projection chooses the columns in a table that are expected to return by a query.
• JOIN: Join chooses the data in from one or more than one table by creating a link between them.

Basic SELECT Syntax:

SELECT [DISTINCT] {* , Column [Alias],- - -} FROM TABLE;

SELECT --> Defined Columns FROM --> Declared Tables.

• SELECT : Specifies a list of Column (one/more)
• DISTINCT : Suppresses Duplicates
• * -- Indicates Select all columns
• COLUMN : Select the named column
• Alias : Provide selected columns in different columns
• FROM Table: Specifies the table containing the columns.

Writing SQL Statements to SELECT or RETRIEVE DATA FROM TABLES: Sample table used,

Table1 - EMP:

Column Names          Data Types
------------         ----------
ENO                     NUMBER
ENAME                   VARCHAR
DNO                     NUMBER
JOB                     VARCHAR
SAL                     NUMBER
COMM                    NUMBER
MGR                     NUMBER
HIREDATE                DATE

Table2 - DEPT:

Column Names             Data Types
--------------          ---------------
DNO                     NUMBER
DNAME                   VARCHAR
LOC                     VARCHAR

Table3 - SALGRADE:

Column Names             Data Types
--------------          ---------------
HISAL                   NUMBER
LOSAL                   NUMBER
GRADE                   NUMBER

Retrieving Data from ALL columns in the table:

SQL> SELECT * FROM EMP;       

SQL> SELECT * FROM DEPT;      

SQL> SELECT * FROM SALGRADE;     

In the above the * is a projection operator. It shows data from all the columns defined in the table with all records. The data is displayed in a table format.

Retrieving Data from particular columns:

SQL> SELECT ENO,ENAME,SAL FROM EMP;

SQL> SELECT ENAME,JOB,SAL,DNO FROM EMP;

SQL> SELECT ENO,ENAME,SAL FROM EMP;

SQL> SELECT DNO,DNAME,LOC FROM DEPT;

SQL> SELECT HISAL,LOSAL,GRADE FROM SALGRADE;

The column names need not be in the same order as exists in table. The columns should be separated by using comma (,). The column names can be separated into different lines within the SQL Buffer. The casing of column names is not important.

Column Heading Defaults:
The default conclusion of the data after it is retrieved from the table is,

• LEFT: Character and Date data
• RIGHT:Numeric Data

By default the data will be shown in UPPERcase. The character and date column headings can be truncated. But NUMBER cannot be truncated.