%%%%% Page 7: First Steps %%%%%

scidb.py initall mydb
scidb.py startall mydb
scidb.py status mydb
scidb.py stopall mydb

%%%%% Page 8:iquery %%%%%

set lang AFL;
set lang AQL;
help;
iqeury -q "statement"
iquery -aq "list('arrays');"
iquery -f "filename" -r "filename"
iqeury -af "filename" -r "filename"

%%%%% Page 12: Example: Create Array %%%%%

CREATE ARRAY actors <actor:string, name:string, surname:string, year:int16> [i=0:*];

set lang AFL; list('arrays', false);
set lang AFL; show(actors);
set lang AFL; scan(actors);
set lang AFL; project(actors, actor, name, surname);
set lang AFL; rename(actors, newActors);

set lang AQL; SELECT * FROM show(actors);
set lang AQL; SELECT * FROM actors;
set lang AQL; SELECT actor, name, surname FROM actors;
set lang AQL; SELECT * INTO newActors FROM actors;

%%%%% Page 13: Exercise 1 %%%%%

set lang AQL; CREATE ARRAY movies <movie:string, title:string, year:int16, rating:int8, length:int16> [j=0:3];

set lang AQL; CREATE ARRAY roles <actor:string, role:string, movie:string, award:string> [k];

%%%%% Page 15: Example: Loading Data %%%%%

set lang AQL; LOAD actors FROM '/home/scidb/actors.scidb';
set lang AQL; LOAD movies FROM '/home/scidb/movies.scidb';
set lang AQL; LOAD roles FROM '/home/scidb/roles.scidb';

set lang AFL; load(actors, '/home/scidb/actors.scidb');
set lang AFL; load(movies, '/home/scidb/movies.scidb');
set lang AFL; load(roles, '/home/scidb/roles.scidb');

%%%%% Page 16: Insert Value %%%%%

set lang AFL; store(filter(actors, year >= 1966), youngActors);

%%%%% Page 17: Exercise 2 %%%%%

% Wrong solution
set lang AQL; INSERT INTO actors '[("geislerova", "Anna", "Geislerova", 1976)]';

% Fix
set lang AQL; INSERT INTO actors '[("trojan", "Ivan", "Trojan", 1964)]';

% Correct solution
set lang AQL; INSERT INTO actors '[(),(),(),(),("geislerova", "Anna", "Geislerova", 1976)]';

set lang AFFL; insert(build(actors, '[(),(),(),(),("geislerova", "Anna", "Geislerova", 1976)]', true), actors);

%%%%% Page 18: Update Value %%%%%

set lang AQL; UPDATE movies SET rating = rating +10, length = length - 20 WHERE rating < 70;

set lang AQL; SELECT * FROM versions(actors);
set lang AFL; list('arrays', true);
set lang AQL; SELECT * FROM actors@1;
set lang AFL; scan(actors@datetime('...'));

%%%%% Page 18: Apply Operator %%%%%

set lang AFL; apply(actors, fullname, name + ' ' + surname);

%%%%% Page 19: Exercise 3 %%%%%

set lang AFL; store(apply(actors, age, 2020 - year), actorsWithAge);

%%%%% Page 23: Query Structure %%%%%

set lang AFL; list('types');
set lang AFL; list('operators');
set lang AFL; list('functions');
set lang AFL; list('aggregates');

%%%%% Page 25: Array Joins %%%%%

set lang AQL; SELECT * FROM actors, roles;
set lang AQL; SELECT * FROM cross_join(actors, roles);
set lang AQL; SELECT * INTO actorsRoles FROM actors JOIN roles ON actors.actor = roles.actor;

%%%%% Page 26: Filtering Operators %%%%%

set lang AFL; filter(list('operators'),regex(name,'(.*)q(.*)'));

%%%%% Page 28: Exercise 4 %%%%%

set lang AQL; SELECT name, surname FROM actors WHERE name = 'Ivan';
set lang AQL; SELECT * FROM between(actors, 0, 1);
set lang AQL; SELECT * FROM actorsRoles WHERE i=0;
set lang AQL; SELECT * FROM slice(actorsRoles, k, 3);

set lang AFL; project(filter(actors, name='Ivan'), name, surname);
set lang AFL; between(actors, 0, 1);
set lang AFL; slice(actorsRoles, i, 0);
set lang AFL; slice(actorsRoles, k, 3);

%%%%% Page 31: Example: Aggregating Operators and Functions %%%%%

set lang AQL; SELECT avg(year), count(award), count(*), min(year), max(year), sum(year) FROM actorsRoles;

set lang AFL; aggregate(actorsRoles, avg(year), count(award), count(*), min(year), max(year), sum(year));

set lang AQL; SELECT max(year) FROM actorsRoles GROUP BY i;

set lang AFL; aggregate(actorsRoles, max(year), i);

set lang AQL; SELECT sum(year) AS sumYear FROM actorsRoles REGRID AS (PARTITION BY i 2, k 2);

set lang AFL; regrid(actorsRoles, 2, 2, sum(year) AS sumYear);

set lang AQL; SELECT sum(year) FROM actorsRoles WINDOW AS (PARTITION BY i 1 PRECEDING AND 3 FOLLOWING, k 1 PRECEDING AND 2 FOLLOWING);

set lang AFL; window(actorsRoles, 1, 3, 1, 2, sum(year));

%%%%% Page 32: Exercise 5: Nested Subqueries %%%%%

set lang AQL; SELECT min(actor) AS actor, count(*), count(award) FROM (SELECT * FROM actorsRoles WHERE year > 1964) GROUP BY i;

set lang AQL; SELECT min(actor) AS actor, count(*), count(award) FROM (SELECT * FROM actorsRoles WHERE year > 1964 ORDER BY actor DESC) GROUP BY n;

set lang AFL; aggregate(filter(actorsRoles, year > 1964), min(actor) AS actor, count(*), count(award), i);

set lang AFL; aggregate(sort(filter(actorsRoles, year > 1964), actor desc), min(actor) AS actor, count(*), count(award), n);