List of articles Terminology Choose language

Examples

Example 1. Select employee, salary of which above average salary in their department, in order of decreasing of their salary.

create table department {
  id         num      primary key,
  name       string
};
create table employee {
  id         num      primary key,
  name       string,
  salary     num,
  department num      references department (id)
};

Request is on the left below, output is on the right below (field "department" of table "employee" of is not in output, because it is already used during creating output).

select department.employee
  where @salary > (
    select avg(@salary)
    from   employee e
    where  e/@department=department/@id
  ) order by @salary desc;

<department id="1"  name="Technical">
  <employee id="54" name="Fraud"   salary="3200">
  <employee id="72" name="Kitter"  salary="3100">
  <employee id="31" name="Tomson"  salary="3000">
</department>
<department id="2"  name="Marketting">
  <employee id="25" name="Johnson" salary="4100">
  <employee id="64" name="Smith"   salary="4000">
</department>

Example 2. Let there are a set of cities and set of flights between them.

create table city (
  id     num primary key,
  name   string
);
create table flight (
  c1     num references city (id),
  c2     num references city (id),
  t1     time,
  t2     time,
  day    weekdays
);

in what days of week there are direct flights from city "a" into city "b"

select flight
  from city[@name="a"].flight#c1:c2.city[@name="b"];

<flight t1="5.30"  t2="8.30"  day="sunday">
<flight t1="7.30"  t2="10.30" day="monday">
<flight t1="9.30"  t2="12.30" day="tuesday">
<flight t1="15.30" t2="18.30" day="wednesday">
<flight t1="17.30" t2="20.30" day="thursday">
<flight t1="19.30" t2="22.30" day="friday">
<flight t1="7.30"  t2="10.30" day="saturday">

how to reach city "b" from city "a" on Monday (time for change flight is not less 30 minutes)

select (flight[@t1 @t2].city)*
  from  city[@name="a"].(flight#c1:c2[@day="monday"].city)*
  where previous(flight)/@t1 - next(flight)/@t2 > 00.30
    and last(city)/@name="b";

<flight   t1="5.30"  t2="5.50">
 <city    name="C">
  <flight t1="7.00"  t2="11.00">
   <city  name="B">
  </flight>
 </city>
</flight>
<flight   t1="5.10"  t2="12.10">
 ...
  <city   name="B">
 ...
</flight>

it's necessary to flight from city "a" for Monday and to return back into it on Friday through cities "b", "c", "d". In what sequence it's necessary to visit cities to not make more than one flight per day

select  city[@name="a"].(flight#c1:c2.city[@name as @n])*
  where first(flight)/@day="monday"
    and last(flight)/@day="friday"
    and @n in ("b", "c", "d", "a")
    and previous(flight)/@t1 - next(flight)/@t2 > 00.30
    and last(city)/@name="a"

<city          id="18"    name="A">
 <flight       t1="5.30"  t2="13.00"  day="monday">
  <city        id="23"    name="D">
   ...
  </city>
 </flight>

 <flight       t1="5.30"  t2="5.50"  day="monday">
  <city        id="18"    name="B">
   <flight     t1="7.00"  t2="11.00" day="tuesday">
    <city      id="14"    name="C">
     <flight   t1="12.00" t2="13.00" day="wednesday">
      <city    id="23"    name="D">
       <flight t1="14.00" t2="17.00" day="thursday">
        <city  id="15"    name="A">
       </flight>
      </city>
     </flight>
    </city>
   </flight>
  </city>
 </flight>

</city>

Example 3. Let we search path in graph, going through minimum quantity of nodes. Each node is record in table "point". Bonds between nodes is in table "bond". Path is searched from node, primary key of which is in field "tab.from" (initial node), to node, primary key of which is in field "tab.to" (final node), table "tab" has one record.

create table point (
  id     num primary key,
  moment num
);
create table bond (
  p1     num references point (id),
  p2     num references point (id)
);
create table tab (
  from   num references point (id),
  to     num references point (id)
);

Let's look at all nodes from initial node to final node - wave goes. Each node in field "moment" contains moment of receipts of wave (moment is equal to double quantity of gone nodes). We find all the most short (with identical length) pathes in graph (if pathes exist).

update point set @moment=null;
update tab#from.point.(bond#p1:p2.point[@moment=null])*.tab#to
  set  @moment=!
;
if           tab#to.point/@moment is not null
then select  point.(point)*
       from  tab#from.point.(bond#p1:p2.point[@moment=!])*.tab#to
else select "<p>Pass does not exist</p>"
;

<point    id="3">
 <point   id="4"
  ...
   <point id="11"
  ...
 </point>
</point>

Example 4. Each node, referring to other node, specifies weight of bond to new node (in field "weight"). It's required to find such path in graph, for which sum of weights is minimal. It's supposed, that contour with negative total weight don't exist in graph (such contour allow to make weight of path as small as you want, "having scrolled" in contour necessary quantity of time. For example, weight_a_b=weight_b_a in symmetric graph, any bond with negative weight is such contour).

create table point (
  id     num primary key,
  moment num,
  total  float4
);
create table bond (
  p1     num references point (id),
  p2     num references point (id),
  weight float4
);
create table tab (
  from   num references point (id),
  to     num references point (id)
);

We search pass

update point set @moment=null, @total=null;
update tab#from.point.(bond#p1:p2.point[@moment=null or @total>../../@total+./@weight])*.tab#to
  set  @moment=!, @total=../../@total+./@weight
;
if           tab#to.point/@moment is not null
then select  point.(point)*
       from  tab#from.point.(bond#p1:p2.point[@moment=! and @total=../../@total+./@weight])*.tab#to
else select "<p>Pass does not exist</p>"
;

Dmitry Turin

List of articles Terminology Choose language