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");