VIEWING DATA IN THE TABLES

Once data has been inserted into a table, the next most logical operation would be to view what has been inserted. The SELECT SQL verb is used to achieve this. The SELECT command is used to retrieve rows selected from one or more tables.

All Rows And All Columns

In order to view global table data the syntax is:

SELECT TO  FROM TableName;

Here, ColumnNamel to ColumnNameN represents table column names.

Syntax:

SELECT * FROM ;

Example : Show all employee first name, middle name, last name, Department, Designation and Manager Number.

SELECT * FROM EMP_MSTR;

Oracle allows the use of the Meta Character Asterisk (*), this is expanded by Oracle to mean all rows and all columns in the table.

The Oracle Server parses and compiles the SQL query, executes it and retrieves data from all rows/columns from the table. 

Filtering Table Data

While viewing data from a table it is rare that all the data from the table will be required each Hence, SQL provides a method of filtering. table data that is not required.

The ways of filtering table data are:

Ø  Selected columns and all rows

Ø  Selected rows and all columns

Ø  Selected columns and selected rows

Selected Columns And All Rows

The retrieval of specific columns from a table can be done as shown below:

Syntax:

SELECT , FROM ;

Example:

Show the first name and the last name of the bank employees

SELECT FNAME, LNAME FROM EMP_MSTR;

Output:

FNAME          LNAME

Chetan             Bhatt

Vimal              Patel

Vishal              Shah

Jay                   Yadav

4 rows selected.

Selected Rows And All Columns

If information of a particular client is to be retrieved from a table, its retrieval must be based on a specific condition.

The SELECT statement used until now displayed all rows. This is because there was no condition set that informed Oracle about how to choose a specific set of rows (or a specific row) from any table. Oracle provides the option of using a WHERE Clause in an SQL query to apply a filter on the rows retrieved.

When a where clause is added to the SQL query, the Oracle engine compares each record in the table with condition specified in the where clause. The Oracle engine displays only those records that satisfy the specified condition.

Syntax:

SELECT * FROM WHERE ;

Here, is always written as

Example:

Display the employee details of the department named Admin

SELECT * FROM EMP_MSTR WHERE DEPT = ‘Admin’;

Output:

FNAME          MNAME                     LNAME          DEPT              DESIG            MNGR_NO

-------------------------------------------------------------------------------------------------------

Mitesh             Hasmukhbhai              Yadav                         Admin Clerk                   103

1  rows selected.

Selected Columns And Selected Rows

To view a specific set of rows and columns from a table the syntax will be as follows:

Syntax:

SELECT , FROM WHERE ;

Example :

List the Employee First Name and Manager numbers of the Department Sales.

SELECT FNAME,MNGR_NO FROM EMP_MSTR WHERE DEPT = ‘Sales’;

Output:

FNAME                      MNGR_NO

--------------------------------------

Chetan                                     5

1 rows selected.