AMI Cheat Sheet

AMI Cheat Sheet

For this demonstration we need to include company.db and university.db sqlite databases as datasources into AMI. For better comprehension, the examples should be executed in the order presented

CREATE

CREATE TABLE BusinessTeam (name string, job string, salary int);

CREATE PUBLIC TABLE ManagementTeam (name string);

CREATE TABLE IF NOT EXISTS OpenJobs (job string, id int);

CREATE TEMP TABLE BonusJobs (job string, id int, bonus double);

CREATE TABLE Graduates (name string, degree string);

CREATE AS

CREATE TABLE Team AS USE ds = "company" EXECUTE SELECT * from Employees;

CREATE TABLE SoftwareTeam AS SELECT name, job, salary FROM Team WHERE job IN ("Programming", "Testing", "DevOps");

CREATE TABLE AnalyzeBudget AS ANALYZE name, job, salary, avg(win.salary) AS avgSalary, median(win.salary) AS medianSalary, salary - median(win.salary) AS diff FROM SoftwareTeam WINDOW win ON true;

CREATE TABLE PrepareBudget AS PREPARE name, job, salary, stack(salary), norm(salary) FROM SoftwareTeam ORDER BY salary ASC PARTITION BY job;

CREATE TABLE DescTable AS DESCRIBE TABLE OpenJobs;

CREATE TABLE ShowTable AS SHOW TEMP TABLES;

CREATE PROCEDURE PrefixFind OFTYPE AMISCRIPT USE arguments="string prefix, string col, string tb" script="SELECT * FROM ${tb} WHERE ${col} ~~ \"^${prefix}\"";

CREATE TABLE PrefixTeam AS CALL PrefixFind("Jo", "name", "Team");

INSERT VALUES

INSERT INTO OpenJobs VALUES ("Programming", 1),("Sales", 2), ("Management", 3), ("Finance", 4), ("DevOps", 5), ("Fax", -1) LIMIT 5;

Double discountFactor1 = 1.03, discountFactor2 = 1.05, discountFactor3 = 1.09, discountFactor6 = 1.08;

Double bonusAmount1 = 25, bonusAmount2 = 20, bonusAmount3 = 30, bonusAmount6 = 15;

INSERT INTO BonusJobs VALUES ("Programming", 1, power(bonusAmount1, discountFactor1)), ("Sales", 2, power(bonusAmount2, discountFactor2)), ("Management", 3, power(bonusAmount3, discountFactor3)), ("Machine Learning,AI", 6, power(bonusAmount6, discountFactor6));

INSERT INTO Graduates VALUES ("Jeff", "Programming"), ("Peter", "Machine Learning"), ("Jennifer", "Finance"), ("Michelle", "Sales");

INSERT [FROM]

INSERT INTO Team FROM SELECT name, degree, 80 FROM Graduates;

INSERT INTO ManagementTeam FROM SELECT name from Team WHERE job == "Management";

INSERT INTO BusinessTeam FROM SELECT name, job, salary FROM Team WHERE job IN ("Management", "Sales", "Operations");

INSERT INTO Team USE ds = "university" EXECUTE SELECT name, subject, 2 * salary FROM Professors;

INSERT INTO AnalyzeBudget FROM ANALYZE name, job, salary, avg(win.salary) AS avgSalary, median(win.salary) AS medianSalary, salary - median(win.salary) AS diff FROM BusinessTeam WINDOW win ON true;

INSERT INTO PrepareBudget FROM PREPARE name, job, salary, stack(salary), norm(salary) FROM BusinessTeam ORDER BY salary ASC PARTITION BY job;

INSERT INTO DescTable FROM DESCRIBE TABLE Team;

INSERT INTO ShowTable FROM SHOW PUBLIC TABLES;

INSERT INTO PrefixTeam FROM CALL PrefixFind("Ma", "name", "Team");

ALTER

ALTER TABLE Team MODIFY salary AS salary BigInteger;

ALTER TABLE Graduates RENAME degree TO profession;

ALTER TABLE Graduates ADD income int BEFORE profession;

ALTER TABLE Graduates DROP income;

ALTER TABLE Graduates ADD income int;