Selecting data from a table

Syntax
Example 1
Example 2
Example 3
Example 4

Syntax

The following syntax shows how to use the select keyword to retrieve data from a table.

select                   comma_separated_column_list_or_wildcard 
from                     database_name.table_name
[ where                  conditional_test ]
[ order by               field_name [asc|desc] ];

The * symbol can be used as a wildcard in a select statement to return all the columns of the table. Alternatively a comma-separated list of columns can be specified.

If you specify an order by clause, then results are returned in ascending order by default. NULL values are returned first when the results are in ascending order. Ordering of results in descending order is the exact opposite of the ordering of results in ascending order.

Example 1

The following example shows how to use the select statement within the OQL Service Provider to query the staff.managers table (the following example output is abbreviated).

|phoenix:1.> select * from staff.managers;
|phoenix:2.> go
.....
{
        EmployeeID=1;
        Name='Matt';
        Department='Development';
        Gender='M';
        Age=28;
}
{
        EmployeeID=2;
....
....
}
( 5 record(s) : Transaction complete )

Example 2

The following example shows a select statement that retrieves only specific fields from the staff.managers table.

|phoenix:1.> select Name, Gender from staff.managers;
|phoenix:2.> go
.....
{
        Name='Matt';
        Gender='M';
}
{
        Name='Irene';
        Gender='F';
}
{
        Name='Ernie';
....
....
}
( 5 record(s) : Transaction complete )

Example 3

The following example uses a where clause to restrict the results.

|phoenix:1.> select EmployeeID, Name from staff.managers
|phoenix:2.> where Department = "Marketing";
|phoenix:3.> go
.
{
        EmployeeID=4;
        Name='John';
}
( 1 record(s) : Transaction complete )

Example 4

The following example shows how to use a select DISTINCT keyword to retrieve a single row for each type of data; for example a single row for each department.

|phoenix:1.> select DISTINCT Department from staff.managers
|phoenix:2.> go
.
{
        Department='Development';
}
{
        Department='Marketing';
}
{
        Department='Sales';
}
( 3 record(s) : Transaction complete )