SQL Commands
What can I do with SQL?
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
Try it out!
Hop over to our API Reference page to try these commands out on our SQL API.
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 theCREATE
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 "public_key=4c4d31237894198ab4174f8b49f9e9dc370737bcc4e741897ba56e86ffb5fa2f,access_type=public_write"
DROP
: used to delete both the structure and record stored in the table. The syntax for theDROP
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 theINSERT
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 theUPDATE
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 theDELETE
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'
Updated 4 months ago