SQL Tables and Queries
SA Engine includes a built-in main-memory local database that can be queried with the standard database query language SQL. The main use of the local database is for storing models, parameters and other meta-data. It is a relational database extended to permit objects of many different types to be stored and queried.
In this section we show how the local database can be used as a regular main-memory relational database managed by SQL statements.
Creating tables​
The create table statement creates a new database table.
Examples:
Suppose we are going to store data about departments and their
properties. The first thing to do is to define a new table named
Department having the two columns id and name using the
create table statement:
create table Department(id Integer, // Identifier of departments
name Charstring, // Name of department
unique(id), // Department identifiers are unique
unique(name) // Department names are unique
)
The above create table is simpler than SQL's create
table statement,
in that columns are specified using OSQL types. SQL data types are
translated to the corresponding OSQL types.
Let's create another table Person with columns id, name,
income, and dept:
create table Person(id Integer, // Unique identifier of persons
name Charstring, // Name of person
income Real, // Income of person
dept Integer, // Department id for person
unique(id))
We create a third table Account with columns id, owner,
and balance:
create table Account(id Integer, // Unique identifier of accounts
owner Integer, // Id of person owning account
balance Real, // Current balance
unique(id))
Populating tables​
The insert into statement populates the database by inserting rows into tables.
Examples:
Add three rows to the table Department:
insert into Department values (1, 'Toys'),
(2, 'Food'),
(3, 'Tools')
Populate the Person table with seven rows:
insert into Person values(1,"Kalle", 100.0, 2),
(2,"Ville", 360.0, 2),
(3,"Ulla", 300.0, 3),
(4,"Mats", 400.0, 1),
(5,"Anita", 585.0, 1),
(6,"Magnus", 500.0, 1),
(7,"Eva", 401.0, 3)
Create accounts and assign them to the persons owning the accounts:
insert into Account values(1,2,150.0),
(2,1,200.0),
(3,2,400.0),
(4,1,85.0),
(5,2,70.0),
(6,3,10.0),
(7,5,500.0),
(8,6,75.0),
(9,3,95.0)
Queries​
SQL queries retrieve data from tables based on search conditions using the select statement.
Examples:
Get all rows in the table Department:
select * from Department
Get the names and incomes of all persons in the database:
select name, income
from Person
Get the names of persons working in the department named 'Toys':
select p.name
from Person p, Department d
where p.dept = d.id
and d.name = 'Toys'
We use the table alias names p and d as shortcuts for the tables
Person and Department, respectively. This is recommended to avoid
ambiguous column names from different tables when combining more than
one table in the from clause. The dot operator (.) uniquely
identifies a column in a table.
Get the balances of the accounts of the person named 'Ville':
select a.balance
from Account a, Person p
where p.name = 'Ville'
and a.owner = p.id
Get the names and account balances of all persons in the 'Toys' department:
select p.name, a.balance
from Person p, Department d, Account a
where p.dept = d.id
and d.name = 'Toys'
and a.owner = p.id
Pattern matching​
We may filter strings by matching them against a pattern by using
the SQL operator like:
Example:
Get the names of the departments that begins with a T
select name
from Department w
where name like 'T%'
Here the two department names 'Toys' and 'Tools'are matching the
pattern 'T%', A % matches any sequence of characters while a _
matches a single character.
Sorting the query result​
The order by
keyword in a select statement specifies the ordering of the result
rows.
Get the names and incomes of all persons ordered decreasingly by income:
select name, income
from Person
order by income desc
Get the two highest paid persons:
select name, income
from Person
order by income desc
limit 2
The keyword limit restricts the number of rows (or tuples) to return
from a select statement. It is not standard SQL so different vendors
use different terms for this.
Get the names and incomes of those persons earning more than 400 ordered by their names:
select name, income
from Person
where income > 400
order by name
Aggregate functions​
Aggregate functions compute a single value from a bag of values in a query.
Examples:
How many persons are there in the database?
select count(*) from Person
The function count is an aggregate
function,
which takes a bag (i.e. set with duplicates) of values as argument and
returns a single value.
What is the total sum of the incomes of all persons?
select sum(income) from Person
sum is an aggregate function too.
What is average income of all persons?
select avg(income) from Person
select avg(income) from Person
What is the median income?
select median(income) from Person
The aggregate function median is not supported by standard SQL.
Count the number of accounts per person in 'Food' department:
select p.name, count(a.id)
from Person p, Department d, Account a
where d.name = 'Food'
and p.dept = d.id
and a.owner = p.id
group by p.name
Compute the total balances of accounts per person in 'Food' department:
select p.name, sum(a.balance)
from Person p, Department d, Account a
where d.name = 'Food'
and p.dept = d.id
and a.owner = p.id
group by p.name
Get the total balances of all accounts per person:
select p.name, sum(a.balance)
from Person p, Account a
where a.owner = p.id
group by p.name
Get decreasingly ordered total balances of each person in the database:
select p.name, sum(a.balance)
from Person p, Account a
where a.owner = p.id
group by p.name
order by sum(a.balance) desc
What are the lowest incomes in each department?
select d.name, min(p.income)
from Department d, Person p
where p.dept = d.id
group by d.name
order by min(p.income)
Get the two departments with highest average incomes:
select d.name, avg(p.income)
from Department d, Person p
where p.dept = d.id
group by d.name
order by avg(p.income) desc
limit 2
For each person get the name, department name, income, and total balance, ordered by total balance decreasingly:
select p.name, d.name, p.income, sum(a.balance)
from Department d, Person p, Account a
where a.owner = p.id
and p.dept = d.id
group by p.name, d.name, p.income
order by sum(a.balance) desc
Updating tables​
The update statement
changes rows in a table based on some condition.
Examples:
Change the income of Kalle to 150:
update Person set income=150 where name = 'Kalle'
Increase the income with 10% for all persons whose names begin with the letter 'A':
update Person set income=income * 1.1 where name like 'A%'
Copying tables​
The insert
into
statement can be used for copying data between tables.
Example:
Copy data about those persons whose incomes are larger
than 350 from table Person into a new table HighEarners:
create table HighEarners(id Integer,
name Charstring,
income Real,
department Integer,
unique(id))
insert into HighEarners select * from Person where income > 350
select * from HighEarners
Removing tables​
Tables are removed by the drop table statement.
Example:
drop table Account
The next tutorial shows how to develop models, undo changes, and save the database of models on disk.