Chapter 2. Simple Select-From-Where Queries

Table of Contents

SQLBasicExamples Script
Running the SQL Shell
Selecting columns
Renaming columns
Computing new columns
Identifying tables and their columns
Filtering results
Ordering Results
Limiting and Offsetting Results
Using External Variables

In this section we walk you through examples of queries over simple, relational data. If you want to follow along with the examples, get the Examples download from http://www.oracle.com/technetwork/database/database-technologies/nosqldb/downloads/index.html and run the SQLBasicExamples script found in the sql folder. This creates the table and imports the data used.

SQLBasicExamples Script

The script SQLBasicExamples creates the following table:

create table Users (
  id integer,
  firstname string,
  lastname string,
  age integer,
  income integer,
  primary key (id)
); 

The script also populates the Users table with the following rows (shown here in JSON format):

{
  "id":1,
  "firstname":"David",
  "lastname":"Morrison",
  "age":25,
  "income":100000,
}

{
  "id":2,
  "firstname":"John",
  "lastname":"Anderson",
  "age":35,
  "income":100000,
}

{
  "id":3,
  "firstname":"John",
  "lastname":"Morgan",
  "age":38,
  "income":null,
}

{
  "id":4,
  "firstname":"Peter",
  "lastname":"Smith",
  "age":38,
  "income":80000,
}

{
  "id":5,
  "firstname":"Dana",
  "lastname":"Scully",
  "age":47,
  "income":400000,
} 

You run the SQLBasicExamples script using the load command:

> cd <installdir>/examples/sql
> java -jar <KVHOME>/lib/sql.jar -helper-hosts <host>:<port> \
-store <storename> load \
-file <KVHOME>/examples/sql/SQLBasicExamples.cli