SQL Reference

Data types

  • INTEGER
  • BOOLEAN
  • VARCHAR
  • BLOB
  • TIMESTAMP

Creating tables


1. CREATE TABLE IF NOT EXISTS customers (id INTEGER, customer_name VARCHAR, email VARCHAR, address VARCHAR, city VARCHAR, ip VARCHAR, country VARCHAR, age INTEGER, active BOOLEAN, PRIMARY KEY id);
2. CREATE TABLE IF NOT EXISTS products (id INTEGER, product VARCHAR, price VARCHAR, PRIMARY KEY id);
3. CREATE TABLE IF NOT EXISTS orders (id INTEGER, customerid INTEGER, productid INTEGER, PRIMARY KEY id);

Indexes


1. CREATE INDEX ON customers(customer_name);

Inserting or updating data


1. INSERT INTO customers (id, customer_name, email, address, city, ip, country, age, active) values (1, 'Isidro Behnen', 'ibehnen0@mail.ru', 'ibehnen0@chronoengine.com', 'Arvika', '2.124.67.107', 'SE', 24, true);
2. INSERT INTO products (id, product, price) values (1, 'Juice - V8, Tomato', '$4.04');

UPSERT will update the value if a row with the same primary key already exists:


1. UPSERT INTO customers (id, customer_name, email, address, city, ip, country, age, active) values (1, 'Isidro Behnen', 'ibehnen0@mail.ru', 'ibehnen0@chronoengine.com', 'Arvika', '2.124.67.108', 'SE', 24, true);
2. UPSERT INTO customers (id, customer_name, email, address, city, ip, country) values (2, 'Claudianus Boldt', 'cboldt1@adobe.com', 'cboldt1@elpais.com', 'Kimhae', '125.89.31.130', 'KR');
3. UPSERT INTO products (id, product, price) values (2, 'Grapes - Red', '$5.03');
4. UPSERT INTO orders (id, customerID, productID) values (1, 1, 2);

Querying


1. SELECT id, customer_name, ip FROM customers;
2. SELECT id, customer_name, email FROM customers WHERE country = 'SE' AND city = 'Arvika';
3. SELECT id, customer_name FROM customers ORDER BY customer_name ASC;
4. SELECT COUNT() FROM orders INNER JOIN customers ON orders.productid = customers.id;
5. SELECT COUNT() FROM orders INNER JOIN customers ON orders.productid = customers.id WHERE orders.productid = 2;
6. SELECT * FROM customers GROUP BY country;
7. SELECT product FROM products WHERE product LIKE 'J';
8. SELECT id, product FROM products WHERE (id > 0 AND NOT products.id >= 10) AND (products.product LIKE 'J');

Parameters


1. SELECT c.id, c.customer_name AS name, active FROM (customers AS c) WHERE id <= 3 AND active = true;

Aggregations

  • COUNT
  • SUM
  • MAX
  • MIN
  • AVG

1. SELECT COUNT() AS c, SUM(age), MIN(age), MAX(age), AVG(age) FROM customers;
2. SELECT active, COUNT() as c, MIN(age), MAX(age) FROM customers GROUP BY active HAVING COUNT() > 0 ORDER BY active DESC;
3. SELECT active, COUNT() as c, MIN(age), MAX(age) FROM customers GROUP BY active HAVING COUNT() > 0 ORDER BY customer_name DESC;

Transactions


1. BEGIN TRANSACTION; UPSERT INTO customers (id, age) VALUES (1, 25); UPSERT INTO products (id, price) VALUES (2, '$5.76'); COMMIT;

Time travel

Time travel could be achieved in two ways

by adding the ‘BEFORE TX ’ within the table name

1. # latest data
2. SELECT id, customer_name, ip as name FROM customers;

4. # past data
5. SELECT id, customer_name, ip FROM (customers BEFORE TX 5);
or using the ‘USE SNAPSHOT BEFORE TX ’ command that will influence all the following commands

1. # latest data
2. SELECT id, customer_name as name FROM customers;

4. # past data
5. USE SNAPSHOT BEFORE TX 5;
6. SELECT id, customer_name, ip FROM customers;

After using the ‘USE SNAPSHOT BEFORE TX ’ command, it’s then possible to reset to the latest with


1. USE SNAPSHOT BEFORE TX <LATEST_TX_ID>;