SQL Select Statement

A SELECT statement in SQL  is used to  retrieve data from the database. Using a SELECT statement, you can do the following

• Projection: You can use the projection capability in SQL to choose the columns in a table that you want returned by your query. You can choose as few or as many columns of the table as you require.

Example

SQL>SELECT  EMP_NAME,EMP_JOB FROM EMPLOYEES;

EMP_NAME          EMP_SSN

—————-          ————

PETER                        183653

PAUL                         184578

MARY                       182345

CHRIS                        189786

JOHN                         1845598

• Selection: You can use the selection capability in SQL to choose the rows in a table that you want returned by a query. You can use various criteria to restrict the rows that you see.

Example

SQL>SELECT * FROM EMPLOYEES WHERE EMP_SSN=183653;

EMP_NO   EMP_NAME   EMP_JOB  EMP_SSN EMP_SAL

———       ————-       ———–   ————  ————

0111            PETER              Manager     183653        10000

• Joining: You can use the join capability in SQL to bring together data that is stored in different tables by creating a link between them.

Basic SELECT Statement

Syntax

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

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 specifies the table containing the columns listed in the SELECT clause

In the syntax:

SELECT >>   is a list of one or more columns
* >>  selects all columns
DISTINCT >> suppresses duplicates

column|expression >>  selects the named column or the expression
alias >> gives selected columns different headings
FROM table >> specifies the table containing the columns

Selecting All Columns

Syntax

SELECT * FROM table_name;

You can display all columns of data in a table by following the SELECT keyword with an asterisk (*).You can also display all columns in the table by listing all the columns after the SELECT keyword.But if the table has more columns it is very tedious job to write all the column names.

For example, to display complete data in EMPLOYEE table you use the following statement

SQL>SELECT * FROM EMPLOYEES;

Selecting Specific Columns

Syntax

SELECT column-name_1,column_name_2….column_name_n

FROM table_name;

You can use the SELECT statement to display specific columns of the table by specifying the column names, separated by commas.In the SELECT clause, specify the columns that you want, in the order in whic h you want them to appear in the output.

For example, to display Employee SSN,Employee job From EMPLOYEES table, you use the following statement:

SQL>SELECT  EMP_NAME,EMP_JOB FROM EMPLOYEES;

EMP_NAME          EMP_SSN

—————-          ————

PETER                        183653

PAUL                         184578

MARY                       182345

CHRIS                        189786

JOHN                         1845598