Sakinol Cheat Sheet (DRAFT) by Sakinol

CREATE TABLE students
  (id INT, name STRING, value DOUBLE);

CREATE TABLE IF NOT EXISTS students 
  (id INT, name STRING, value DOUBLE)

INSERT INTO students
VALUES 
  (4, "Ted", 4.7),
  (5, "Tiffany", 5.5),
  (6, "Vini", 6.3)

UPDATE students 
SET value = value + 1
WHERE name LIKE "T%"

DELETE FROM students 
WHERE value > 6

CREATE OR REPLACE TEMP VIEW updates(id, name, value, type) AS VALUES
  (2, "Omar", 15.2, "update"),
  (3, "", null, "delete"),
  (7, "Blue", 7.7, "insert"),
  (11, "Diya", 8.8, "update");
MERGE INTO students b
USING updates u
ON b.id=u.id
WHEN MATCHED AND u.type = "update"
  THEN UPDATE SET *
WHEN MATCHED AND u.type = "delete"
  THEN DELETE
WHEN NOT MATCHED AND u.type = "insert"
  THEN INSERT *

DROP TABLE students

DESCRIBE EXTENDED students;
DESCRIBE DETAIL students;
DESCRIBE HISTORY students;

OPTIMIZE students 
ZORDER BY id;

SELECT * 
FROM students VERSION AS OF 3

RESTORE TABLE students TO VERSION AS OF 8 

VACUUM students RETAIN 0 HOURS -- remove all history

SET spark.databricks.delta.retentionDurationCheck.enabled = false;