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
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 "publicKey=4c4d31237894198ab4174f8b49f9e9dc370737bcc4e741897ba56e86ffb5fa2f,accessType=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