Structured query language (SQL) is a programming language for storing and processing information in a relational database.

Space and Time is an ANSI-standard, ACID-compliant data warehouse, which means it accepts standard SQL, the same as any other relational database. If you need to brush-up on writing SQL, check out this resource.

You can run SQL against:

• Your own tables in Space and Time, containing off-chain data that you've ingested
• Tables containing relational, realtime blockchain data that we've indexed from major chains

Types of SQL commands

There are three SQL commands you'll need to know to use Space and Time:

DQL

Data query language (DQL) is a type of SQL command used to retrieve data from the database. In other words, it's used to run queries.

DQL is only associated with one command: SELECT. The syntax for SELECT commands is as follows:

SELECT column1, column2, ...  
FROM table_name;

Here's an example of a DQL command you might run in Space and Time:

SELECT EXCHANGE_NAME, COUNT(*)  
FROM ETH.DEX_TRADE  
GROUP BY EXCHANGE_NAME  
ORDER BY 2 DESC

DDL

Data definition language (DDL) changes the structure of the table. In other words, it's used to configure your table.

DDL is associated with the following commands:

• CREATE: used to create a new table in the database. The syntax for the CREATE command is as follows:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

Here's an example of a CREATE statement you might run in Space and Time:

CREATE TABLE My_User_Wallets (
  User_Wallet_Address VARCHAR PRIMARY KEY,  
  User_Subscription VARCHAR
) WITH "publicKey=4c4d31237894198ab4174f8b49f9e9dc370737bcc4e741897ba56e86ffb5fa2f,accessType=public_write"

• DROP: used to delete both the structure and record stored in the table. The syntax for the DROP command is as follows:

DROP TABLE table_name;

Here's an example of a DROP statement you might run in Space and Time:

DROP TABLE My_User_Wallets;

DML

Data manipulation language (DML) modifies the database. In other words, it's used to change the data in your table.

DML is associated with the following commands:

• INSERT: used to insert data into the row of a table. The syntax for the INSERT command is as follows:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Here's an example of an INSERT statement you might run in Space and Time:

INSERT INTO My_User_Wallets (User_Wallet_Address, User_Subscription)  
VALUES ('0x456008396BFdd64159998cE362b8D650FFd6F28b', 'premium')

• UPDATE: used to update or modify the value of a column in the table. The syntax for the UPDATE command is as follows:

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Here's an example of an UPDATE statement you might run in Space and Time:

UPDATE My_User_Wallets  
SET User_Subscription = 'premium'  
WHERE User_Wallet_Address = '0x456008396BFdd64159998cE362b8D650FFd6F28b' 

• DELETE: used to delete existing records in a table. The syntax for the DELETE command is as follows:

DELETE FROM table_name WHERE condition;

Here's an example of a DELETE statement you might run in Space and Time:

DELETE FROM My_User_Wallets WHERE User_Subscription = 'expired'