Tutorial

Start the sa.amos console top loob by running the executable sa.amos in the bin folder of the downloaded system. Cut and paste the examples below and observe the effects.

Documentation of function sqrt()

doc("sqrt");

Documentation of all functions whose names contain the string max:

doc(apropos("max"));

Source code of all functions whose names contain the string max

sourcecode(apropos("max"));

What system version am I running?

system_version();

What folder is my current working direcory?

pwd();

What is the home folder of the system I am currently running?

startup_dir();

Call built-in arithmetic functions

1+2+3;

sqrt(1+2+3);

max(1,2);

Vector arithmetics

{1,2,3}+{4,5,6};

{1,2,3}*{4,5,6};

5*{1,2,3};

{1,2,3}/4;

{1,2,3}.*{4,5,6};

{1,2,3}./{4,5,6};

String functions

"abra" + "kadabra";

upper("abrakadabra");

substring("abrakadabra",2,4);

Define your own type Person with a property name

create type Department properties
  (name Charstring);

Create three department objects. Set property name:

Bind interface variables :d1, :d2, and :d3 to the created departments

create Department (name) instances :d1 ("Toys"), :d2 ("Food"), :d3 ("Tools");

Get the names of all departments

select name(d) from Department d;

What is the name of the department in variable :d1?

name(:d1);

Get the objects representing the departments

select d from Department d;

Create type 'Person' with property functions 'name', 'dept', and 'income'

create type Person properties
  (name Charstring,
   dept Department,
   income Number);

Create some persons and assign them to departments

create Person(name, income, dept) instances
  :p1 ("Maja",    100, :d1),
  :p2 ("Bill",    200, :d2),
  :p3 ("Bull",    300, :d3),
  :p4 ("Pelle",   400, :d1),
  :p5 ("Måns",    500, :d2),
  :p6 ("Olle",    500, :d1),
  :p7 ("Birgitta",600, :d3),
  :p8 ("Murre",   700, :d1);

Get the income of the person in variable :p3

income(:p3);

Get the names and incomes of all persons in the database

select name(p), income(p) from Person p;

Get the names of persons working in department named 'Toys'

select name(p)
  from Person p
 where name(dept(p)) = "Toys";

Get the incomes of the persons ordered decreasingly by income

select name(p), income(p)
  from Person p
 order by income(p) desc;

Get the two highest paid persons

select name(p), income(p)
  from Person p
 order by income(p) desc
 limit 2;

Get the incomes of all persons earning more that 400 ordered by their names

select income(p)
  from Person p
 where income(p) > 400
 order by name(p);

How many persons are there in the database?

count(select p from Person p);

What is the total sum of the incomes of all persons?

sum(select income(p) from Person p);

What is average of the incomes of all persons?

avg(select income(p) from Person p);

What is the standard deviation of the incomes of all persons?

stdev(select income(p) from Person p);

Create type 'Account' with properties 'id', 'owner', and 'balance'

create type Account properties
  (id Number,
   owner Person,
   balance Number);

Create some accounts and assign them to persons

create account(id, owner, balance) instances
  (1,:p1, 150),
  (2,:p1, 200),
  (3,:p2, 400),
  (4,:p2,  85),
  (5,:p2,  70),
  (6,:p3,  10),
  (7,:p5, 500),
  (8,:p6,  75),
  (9,:p6,  95),
  (10,:p7,105),
  (11,:p8, 90);

Get the balances of the accounts of the person named 'Bill'

select balance(a)
  from Account a
 where name(owner(a)) = "Bill";

Get the names and account balances of all persons in the 'Toys' department

select name(p), balance(a)
  from Person p, Account a
 where name(dept(p)) = "Toys"
   and owner(a) = p;

Count the number of accounts per person in 'Toys' department

select name(p), count(a)
  from Person p, Account a
 where name(dept(p)) = "Toys"
   and owner(a) = p
 group by name(p);

Compute total balance of accounts per person in 'Toys' department

select name(p), sum(balance(a))
  from Person p, Account a
 where name(dept(p)) = "Toys"
   and owner(a) = p
 group by name(p);

Get the total balances for all persons in the database

select name(p), sum(balance(a))
  from Person p, Account a
 where owner(a) = p
 group by name(p);

Get the average incomes and standard deviations per department without showing the departments' names

select avg(income(p)), stdev(income(p))
  from Department d, Person p
 where dept(p)=d
 group by d;

Get decreasingly ordered total balances of each person in the database

select name(p), sum(balance(a))
  from Person p, Account a
 where owner(a) = p
 group by name(p)
 order by sum(balance(a)) desc;

What are the lowest incomes in each department?

select name(d), min(income(p))
  from Department d, Person p
 where dept(p) = d
 group by name(d)
 order by min(income(p));

Get the two departments with highest average incomes with standard deviations

select name(d), avg(income(p)), stdev(income(p))
  from Department d, Person p
 where dept(p) = d
 group by name(d)
 order by avg(income(p)) desc
 limit 2;

For each person get the name, department name, income, and total balance, ordered by total balance decreasingly

select name(p), name(d), income(p), sum(balance(a))
  from Department d, Person p, Account a
 where owner(a) = p
   and dept(p) = d
 group by name(p), name(d), income(p)
 order by sum(balance(a)) desc;

User defined derived functions

The following function finds the incomes higher than a given threshold. It is a function returning a bag (set with duplicates) of numbers

create function higherIncomes (Number thres) -> Bag of Number
  as select income(p)
       from Person p
      where income(p)>thres;

Call function highIncomes()

higherIncomes(500);

Function highIncomesPers() returns both names and incomes. It is a function returning a bag of tuples (pairs)

create function higherIncomePers (Number thres)
                                -> Bag of (Charstring nm, Number inc)
  as select name(p), inc
       from Number inc, Person p
      where income(p)=inc
        and inc > thres;

Call tuple valued function: Get persons earning more than 100

higherIncomePers(500);

The following function returns the k highest paid persons

create function highestIncomePers(Number k)
                                -> Bag of (Charstring nm, Number inc)
  as select name(p), income(p)
        from Person p
      order by income(p) desc
      limit k;

Get the tho highest earners

highestIncomePers(2);

Collections

Bags

The function iota() returns a bag of integers in an interval

iota(2,6);

You can assign a variable to a bag valued function

set :b = iota(1,10);
:b;

Use in() to extract values from bag

in(:b);

Alternatively 'in' operator

select x from Number x where x in :b;

Aggregate functions over bags

set :b = iota(1,100000);

count(:b);
sum(:b);
avg(:b);
stdev(:b);
maxagg(:b);
minagg(:b);

sum(select n
      from Number n
     where n in :b
     limit 20);

Aggregation over bags

set :b2 = iota(1,30);

sum(select x from Number x where x in :b2 and x>7);

set :b3 = (select x from Number x where x in :b2 and x > 7);

sum(:b3);

avg(:b3);

Vectors (ordered collections)

Set interface variable :v0 to value of query forming a VECTOR {1,2,3,4,5,6,7,8,9,10} by using vselect instead of select

set :v0 = vselect i from Number i where i in iota(1,10) order by i;
:v0;

Set :v to the square root of elements in :v0

set :v = vselect sqrt(i) from Number i where i in :v0 order by i;
:v;

Basic vector aggregation

dim(:v);
vsum(:v);
vavg(:v);
vstdev(:v);

The 'in' operator converts vector to bag

select n
  from Number n
 where n in {1,2,3}
   and n >= 2;

Represent coordinates as 2D vectors

set :c1 = {1,2};
set :c2 = {3,4};

Euclidean coordinate distance

euclid(:c1, :c2);

Vectors can hold objects of any datatype

{1,3,{3,4}};
{1,"a",{"b","c"}};

Save database in file mydb.dmp

save "mydb.dmp";

After saving the interface variables are lost

:b;

Quit sa.amos

quit;

Run sa.amos again with the save database

sa.amos mydb.dmp

Inspect the database again

higherIncomePers(100);