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