#batch res/sampledata/batch/SBQL_Examples_4_LINQ.cli
$encoding = utf-8
\
add module test
{
type AddressType is record { // declaration of type
city:string;
street:string;
house: integer;
}
class EmpClass // declaration
of class
{
instance Emp : {
name : string;
address : AddressType [0..1];
//absent data allowed
salary :
real[0..1]; //absent data
allowed
job : string;
worksIn : ref DeptClass reverse employs; // declaration of bidirectional pointer
attribute
}
}
class DeptClass
{
instance Dept : {
employs : ref EmpClass [0..*] reverse worksIn; // declaration of bidirectional pointer
attribute
boss : ref EmpClass[0..1]; //pending absent data allowed
name : string;
budget : real;
location :
string [1..*]; //multiple values
}
}
Emp : EmpClass [0..*]; //
declaration of persistent collection of data
Dept : DeptClass [0..*]; // declaration of persistent collection of
data
init(){
department : Dept; //
declaration of local pointer variable
employee : Emp; // declaration of local pointer
variable
department := create permanent Dept("Production"
as name, //creation of persistent
object and assingnment to pointer variable
"
employee := create
permanent Emp("Poe" as name,
"boss" as job,
("
(ref
department) as worksIn, 1500 as salary);
department :<< boss(ref employee); //creating and inserting pointer into
object (persistently)
create permanent Emp("Smith"
as name, "boss" as job, //creation of persistent
object
("Makaha" as city, "
(ref
department) as worksIn, 2200 as salary);
department := create permanent Dept("HR" as name,
"
employee := create permanent Emp("Bert"
as name, "boss" as job,
("
(ref
department) as worksIn, 2200 as salary);
department :<< boss(ref employee);
create permanent Emp("Daniels"
as name, "clerk" as job,
("
(ref
department) as worksIn, 1200 as salary);
create permanent Emp("Ripper"
as name, "clerk" as job,
("
(ref
department) as worksIn, 3300 as salary);
}
}
.
compile test
cm test
\
set output default
set autoderef off
init();
\
#----------------------------------------------------------------------------------------------------
#1. Get departments together with the
average salaries of their employees:
(Dept as d)
join avg(d.employs.Emp.salary);
# Possibly dynamic type error
if some department has no employees or
# when all employees in some
department have no salary
\
Dept join avg(employs.Emp.salary);
# Possibly dynamic type
error, as before
\
Dept join (if
exists(employs.Emp.salary)
then avg(employs.Emp.salary)
else 0.0);
\
Dept join ((employs.Emp) groupas de).(if exists(de.salary) then avg(de.salary) else 0.0);
\
#----------------------------------------------------------------------------------------------------
#2. Get name, salary and department name
for employees earning less than 2222
(Emp where salary < 2222).(name,
salary, worksIn.Dept.name);
# Possibly dynamic type error
if some employee has no salary
\
(Emp where forsome
(salary as s) (s < 2222)).
(name, (if exists(salary) then deref(salary)
else 0.0), worksIn.Dept.name);
\
#----------------------------------------------------------------------------------------------------
#3. Get names of employees working for the
department managed by Bert.
(Emp where (worksIn.Dept.boss.Emp.name)
= "Bert").name;
\
#----------------------------------------------------------------------------------------------------
#4. Get the name of Poe's boss:
(Emp where name = "Poe").worksIn.Dept.boss.Emp.name;
\
#----------------------------------------------------------------------------------------------------
#5.Names and cities of employees working in
departments managed by Bert:
(Dept where (boss.Emp.name) = "Bert").employs.Emp.(name, (deref(address.city)
union
("No address" where not exists(address))));
\
(Dept where (boss.Emp.name) = "Bert").employs.Emp.(name as name, ((deref(address.city) union
("No address" where not exists(address)))) as city) as empcity;
\
#----------------------------------------------------------------------------------------------------
#6.Get the minimal, average and maximal number of
employees in departments:
min(Dept.count(employs)), avg(Dept.count(employs)), max(Dept.count(employs));
\
((Dept.count(employs))
groupas counts).(min(counts), avg(counts),
max(counts));
\
#----------------------------------------------------------------------------------------------------
#7. For each department get its name and
the sum of salaries of employees being not bosses:
(((Dept as d)
join ((sum(d.employs.Emp.salary)
- (d.boss.Emp.salary)) as s)).(d.name,
s));
#Possibly dynamic type error
if some Dept has no employees or
# if all employees in some
dept have no salary or if some boss have no salary
\
((((Dept as
d) join ((d.employs.Emp) subtract (d.boss.Emp)) as s )) groupas dEmps).(
dEmps.d.name, if exists(dEmps.s.salary) then sum(dEmps.s.salary)
else 0.0);
\
#----------------------------------------------------------------------------------------------------
#8. Is it true that each department employs
an employee earning the same as his/her boss?:
\
forall (Dept as d)
forsome ((d.employs.Emp
subtract d.boss.Emp) as e)
(e.salary = d.boss.Emp.salary);
\
#Possibly dynamic type error
if some employee has no salary
\
forall (Dept as d)
forsome ((d.employs.Emp
subtract d.boss.Emp) as e)
forsome (e.salary
as es)
forsome (d.boss.Emp.salary
as bs)
(es = bs);
\
# If a boss has no salary,
the query ignores him/her
\
#----------------------------------------------------------------------------------------------------
#9. For each employee get the message
containing his/her name and
# the percent of the annual budget of his/her department that
is
# consumed by
his/her monthly salary:
\
Emp.("Employee " + name
+ " consumes " + (salary * 12 * 100 /(worksIn.Dept.budget))
+
"% of
the " + worksIn.Dept.name + "
department budget.");
\
#Possibly dynamic type error
if some employee has no salary
\
(Emp join (if exists(salary) then deref(salary) else 0.0) as s).(
"Employee " + name + " consumes " + (s* 12 *
100/worksIn.Dept.budget) + "% of the " +
worksIn.Dept.name + " department
budget.");
\
#----------------------------------------------------------------------------------------------------
#10. Get cities hosting all departments:
(unique(deref(Dept.location))
as deptcity) where forall(Dept)(deptcity in location);
#----------------------------------------------------------------------------------------------------
#11.For each interval <n,n+999>,
n = 0, 1000, 2000, 3000, ... get the message
# (string)
containing the number of employees having the salary within this interval
# and the interval itself.
# Output
messages should have proper grammatical forms (suffixes -s for nouns (n) and
verbs (v)).
\
(0 as i closeby (i+1000 where i <= max(Emp.salary)) as i)
join (count(Emp where salary >=
i and salary < i+1000) as c)
join ((if c=1 then ("" as n, "s" as v)
else ("s" as n, "" as v)).
(c + " employee" + n + " earn"+ v +"
between "+ i +" and " + (i+999)) as
message);
\
#Possibly dynamic type error
if some employee has no salary
\
(0 as i closeby (i+1000 where i <= max(Emp.salary))
as i)
join (count((Emp where
exists(salary)) where salary >= i and salary <
i+1000) as c)
join ((if c=1 then ("" as n, "s" as v)
else ("s" as n, "" as v)).
(c + " employee" + n + " earn"+ v +"
between "+ i +" and " + (i+999)) as
message);
\
#----------------------------------------------------------------------------------------------------
#12.For each location give the set of department names
that are located at it and
# the average salary of bosses of these departments, providing
that
# the number of clerks that are employed at such a location is
lower than 100.
\
(unique(deref(Dept.location))
as deptcity).
((((Dept
where deptcity in location) groupas
citydepts)
where count(citydepts.employs.(Emp where job = "clerk")) < 100).
(deptcity as cityLocation,
(citydepts.name) groupas cityDeptNames,
avg(citydepts.boss.Emp.salary) as cityDeptBossAvgSal));
\
#----------------------------------------------------------------------------------------------------