Learn to perform basic SQL operations such as creating tables, inserting data, deleting data, and dropping Oracle NoSQL Database tables.

You use the CREATE TABLE statement to create a new table in Oracle NoSQL Database. Every table must have one or more fields designated as the primary key. This designation occurs at the time of table creation and can't be changed later. The primary key value for each row must be unique and the associated type must be one of the following: INTEGER, LONG, FLOAT, DOUBLE, NUMBER, STRING, ENUM, BOOLEAN, or TIMESTAMP.

CREATE TABLE IF NOT EXISTS Users(
id integer,
firstname string,
lastname string,
age integer,
income integer,
primary key (id)
);

The CREATE TABLE statement above defines a Users table, which contains information about the users. It includes the id field as the primary key column. The other columns firstname, lastname, age, and income represent various user-related data.

You can use the IF NOT EXISTS clause to conditionally create a table if it does not already exist. This avoids unnecessary errors when the table with the same qualified name and schema is already present in the Oracle NoSQL Database.

It is possible to provide several other options while creating a table, a few of which are covered in further topics. For information on all the available table creation options, see Create Table.

SHOW TABLES;

tables
  SYS$IndexStatsLease
  SYS$MRTableAgentStat
  SYS$MRTableInfo
  SYS$MRTableInitCheckpoint
  SYS$PartitionStatsLease
  SYS$SGAttributesTable
  SYS$StreamRequest
  SYS$StreamResponse
  SYS$TableMetadata
  SYS$TableStatsIndex
  SYS$TableStatsPartition
  SYS$TopologyHistory
  Users

The output displays both system-created and user-created tables. The system-created tables are created during the installation of Oracle NoSQL Database.

DESCRIBE TABLE Users;

=== Information ===
 +-------+-----+----------------+-------+----------------+----------+--------+----------+---------+---------+-------------+
 | name  | ttl | beforeImageTTL | owner | jsonCollection | sysTable | parent | children | regions | indexes | description |
 +-------+-----+----------------+-------+----------------+----------+--------+----------+---------+---------+-------------+
 | Users |     |                |       | N              | N        |        |          |         |         |             |
 +-------+-----+----------------+-------+----------------+----------+--------+----------+---------+---------+-------------+

 === Fields ===
 +----+-----------+---------+----------+---------+----------+------------+----------+
 | id |   name    |  type   | nullable | default | shardKey | primaryKey | identity |
 +----+-----------+---------+----------+---------+----------+------------+----------+
 |  1 | id        | Integer | N        | NULL    | Y        | Y          |          |
 +----+-----------+---------+----------+---------+----------+------------+----------+
 |  2 | firstname | String  | Y        | NULL    |          |            |          |
 +----+-----------+---------+----------+---------+----------+------------+----------+
 |  3 | lastname  | String  | Y        | NULL    |          |            |          |
 +----+-----------+---------+----------+---------+----------+------------+----------+
 |  4 | age       | Integer | Y        | NULL    |          |            |          |
 +----+-----------+---------+----------+---------+----------+------------+----------+
 |  5 | income    | Integer | Y        | NULL    |          |            |          |
 +----+-----------+---------+----------+---------+----------+------------+----------+

INSERT INTO Users VALUES (10, "John", "Smith", 22, 45000);
INSERT INTO Users VALUES (20, "Jane", "Smith", 23, 55000);

With the above statements, you insert two rows to the Users table. To insert data into only some of the table columns, specify the column names explicitly in the INSERT statement. For more details, see Adding Table Rows using INSERT and UPSERT.

DELETE FROM Users WHERE age < 25 RETURNING firstName, lastName;

The query above deletes the rows with age field value less than 25 from the Users table.

{"firstName":"Jane","lastName":"Smith"}
{"firstName":"John","lastName":"Smith"}

2 rows returned

DROP TABLE  IF EXISTS Users;

If the named table does not exist, then the DROP TABLE statement fails. To avoid errors, you can use it with IF EXISTS clause.

Table Hierarchies

Oracle NoSQL Database allows tables to exist in a parent-child relationship. You use the CREATE TABLE statement to create a table as a child of another table, which can then be the parent of a new table. The topmost table that does not have any other parent is called the root table. The immediate predecessors of a table are called its parent tables and the immediate successors of a table are called its child tables. You use the composite name while creating/inserting rows into the child table, where you specify the parent table followed by a period (.) before the child table name. To understand the significance of creating a parent-child structure, see Hierarchical tables.

A child table inherits the primary key columns of its parent table. This is done implicitly, without including the parent columns in the create table statement of the child table. All tables in the hierarchy have the same shard key columns, which are specified in the create table statement of the root table.

CREATE TABLE Users.empDetails(
empId integer,
dept string,
city string,
state string,
zip integer
primary key (empId)
);

The empDetails table contains the user's employment details. The empDetails table automatically includes the id column, which is the primary key of the Users table. The empDetails table's primary key columns are id and empId.

You can't drop a parent table before its child tables are dropped.