First steps with SQream DB

This tutorial explains the basics of SQream DB SQL.

Preparing for this tutorial

This tutorial assumes you already have a SQream DB cluster running.

Tip

This tutorial makes use of the built-in command-line client, which can be found in the bin directory of your SQream DB installation, under the name sqream sql.

Creating your first table

When starting SQream SQL, after entering your password, you are presented with the SQL shell.

Tip

  • To exit the shell, type \q or Ctrl-d.
  • A new SQream DB cluster contains a database named master. We will start with this database.
$ sqream sql --port=5000 --username=rhendricks -d master
Password:

Interactive client mode
To quit, use ^D or \q.

master=> _

To create a table, we will use the CREATE TABLE syntax, with a table name and some column specifications.

For example,

CREATE TABLE cool_animals (
   id INT NOT NULL,
   name VARCHAR(20),
   weight INT
);

Tip

Statements and queries are standard SQL, followed by a semicolon (;).

If the table already exists and you want to drop the current table and create a new one, you can add OR REPLACE after the CREATE keyword.

CREATE OR REPLACE TABLE cool_animals (
   id INT NOT NULL,
   name VARCHAR(20),
   weight INT
);

You can ask SQream DB to list the full, verbose CREATE TABLE statement for any table, by using the GET_DDL function, with the table name.

master=> SELECT GET_DDL('cool_animals');
create table "public"."cool_animals" (
"id" int not null,
"name" varchar(20),
"weight" int
);

If you are done with this table, you can use DROP TABLE to remove the table and all of its data.

master=> DROP TABLE cool_animals;

executed

Listing tables

To see the tables in the current database, we will query the catalog

master=> SELECT table_name FROM sqream_catalog.tables;
cool_animals

1 rows

Inserting rows

Inserting rows into a table can be performed with the INSERT statement. The statement includes the table name, an optional list of column names, and column values listed in the same order as the column names:

master=> INSERT INTO cool_animals VALUES (1, 'Dog', 7);

executed

To change the order of values, specify the column order:

master=> INSERT INTO cool_animals(weight, id, name) VALUES (3, 2, 'Possum');

executed

To insert multiple rows, use sets of parentheses:

master=> INSERT INTO cool_animals VALUES
      (3, 'Cat', 5),
      (4, 'Elephant', 6500),
      (5, 'Rhinoceros', 2100);

executed

When you leave out columns that have a default value (including default NULL value) the default value is used.

master=> INSERT INTO cool_animals (id) VALUES (6);

executed
master=> INSERT INTO cool_animals (id) VALUES (6);

executed
master=> SELECT * FROM cool_animals;
1,Dog                 ,7
2,Possum              ,3
3,Cat                 ,5
4,Elephant            ,6500
5,Rhinoceros          ,2100
6,\N,\N

6 rows

Note

Null row values are represented as \N

Queries

For querying, use the SELECT keyword, followed by a list of columns and values to be returned, and the table to get the data from.

master=> SELECT id, name, weight FROM cool_animals;
1,Dog                 ,7
2,Possum              ,3
3,Cat                 ,5
4,Elephant            ,6500
5,Rhinoceros          ,2100
6,\N,\N

6 rows

To get all columns without specifying them, use the star operator *:

master=> SELECT * FROM cool_animals;
1,Dog                 ,7
2,Possum              ,3
3,Cat                 ,5
4,Elephant            ,6500
5,Rhinoceros          ,2100
6,\N,\N

6 rows

To get the number of values in a table without getting the full result set, use COUNT(*):

master=> SELECT COUNT(*) FROM cool_animals;
6

1 row

Filter results by adding a WHERE clause and specifying the filter condition:

master=> SELECT id, name, weight FROM cool_animals WHERE weight > 1000;
4,Elephant            ,6500
5,Rhinoceros          ,2100

2 rows

Sort the results by adding an ORDER BY clause, and specifying ascending (ASC) or descending (DESC) order:

master=> SELECT * FROM cool_animals ORDER BY weight DESC;
4,Elephant            ,6500
5,Rhinoceros          ,2100
1,Dog                 ,7
3,Cat                 ,5
2,Possum              ,3
6,\N,\N

6 rows

Filter null rows by adding a filter IS NOT NULL:

master=> SELECT * FROM cool_animals WHERE weight IS NOT NULL ORDER BY weight DESC;
4,Elephant            ,6500
5,Rhinoceros          ,2100
1,Dog                 ,7
3,Cat                 ,5
2,Possum              ,3

5 rows

Deleting rows

To delete rows in a table selectively, use the DELETE command, with a table name and a WHERE clause to specify which rows are to be deleted:

master=> DELETE FROM cool_animals WHERE weight is null;

executed
master=> SELECT  * FROM cool_animals;
1,Dog                 ,7
2,Possum              ,3
3,Cat                 ,5
4,Elephant            ,6500
5,Rhinoceros          ,2100

5 rows

To delete all rows in a table, use the TRUNCATE command followed by the table name:

master=> TRUNCATE TABLE cool_animals;

executed

Note

While TRUNCATE deletes data from disk immediately, DELETE does not physically remove the deleted rows. For more information on removing the rows from disk, see DELETE.

What’s next?