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 <TX_ID>’ 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 <TX_ID>’ 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 <TX_ID>’ command, it’s then possible to reset to the latest with

  1. USE SNAPSHOT BEFORE TX <LATEST_TX_ID>;