#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

              "Honolulu" as location, 500000 as budget);

 

         employee := create permanent Emp("Poe" as name, "boss" as job,

              ("Honolulu" as city, "5'th avenue" as street, 4 as house) as address,

              (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, "Park Avenue" as street, 443 as house) as address,

              (ref department) as worksIn, 2200 as salary);

         

          department := create permanent Dept("HR" as name,

              "Honolulu" as location, "Norwalk" as location, 200000 as budget);

             

         employee := create permanent Emp("Bert" as name, "boss" as job,

              ("Honolulu" as city, "5'th avenue" as street, 44 as house) as address,

              (ref department) as worksIn, 2200 as salary);

 

         department :<< boss(ref employee);

             

          create permanent Emp("Daniels" as name, "clerk" as job,

              ("Norwalk" as city, "4'th avenue" as street, 24 as house) as address,

              (ref department) as worksIn, 1200 as salary);

         

          create permanent Emp("Ripper" as name, "clerk" as job,

              ("Norwalk" as city, "Park avenue" as street, 41 as house) as address,

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

\

#----------------------------------------------------------------------------------------------------