Basic SQL SELECT statement

SELECT statement

Capabilities of SQL SELECT Statements

A SELECT statement retrieves information from the database. With a SELECT statement, you can
use the following capabilities:

• Projection: Select the columns in a table that are returned by a query. Select as few or as many
                    of the columns as required.
• Selection:  Select the rows in a table that are returned by a query. Various criteria can be used to
                    restrict the rows that are retrieved.
• Joining:     Bring together data that is stored in different tables by specifying the link between
                    them.                                                    

  

In its simplest form, a SELECT statement must include the following:
• A SELECT clause, which specifies the columns to be displayed
• A FROM clause, which identifies the table containing the columns that are listed in the SELECT

clause

                    SELECT *|{[DISTINCT] column|expression [alias],...}  FROM     table;

In the syntax:

                            SELECT                            is a list of one or more columns

                            *                                         selects all columns

                            DISTINCT                         suppresses duplicates

                            alias                                    gives the selected columns different headings

                            column|expression              selects the named column or the expression

                            FROM table                       specifies the table containing the columns

Note: In this tutorial I have used oracle HR sample schema and sql developer tool for querying the sql commands.

You need to install oracle 11g express edition which you can download  Oracle express edition 

And already I have posted  How to unlock hr schema in oracle express edition which guides you to unlock the sample HR schema from the oracle database.

Selecting All Columns

  

                                                       SELECT * FROM departments;

You can display all columns of data in a table by following the SELECT keyword with an asterisk

(*). In the example in the slide, the department table contains four columns: DEPARTMENT_ID,

DEPARTMENT_NAME, MANAGER_ID, and LOCATION_ID. The table contains eight rows, one for each department.

Writing SQL Statements

SQL statements are not case-sensitive.

SQL statements can be entered on one or more lines.

Keywords cannot be abbreviated or split across lines.

Clauses are usually placed on separate lines.

Indents are used to enhance readability.

In SQL Developer, SQL statements can optionally be terminated by a semicolon (;). 

Semicolons are required when you execute multiple SQL statements.

In SQL*Plus, you are required to end each SQL statement with a semicolon (;).

Executing SQL Statement

In SQL Developer, click the Run Script icon or press [F5] to run the command or commands in the

SQL Worksheet. You can also click the Execute Statement icon or press [F9] to run a SQL statement

in the SQL Worksheet. The Execute Statement icon executes the statement at the mouse pointer in

the Enter SQL Statement box while the Run Script icon executes all the statements in the Enter SQL

Statement box. The Execute Statement icon displays the output of the query on the Results tabbed

page while the Run Script icon emulates the SQL*Plus display and shows the output on the Script

Output tabbed page.

In SQL*Plus, terminate the SQL statement with a semicolon, and then press [Enter] to run the

command.