/*** Query 1:find the organization unit that has the most people ***/
define function  max_count() returns integer
{
  let $b := ( distinct-values(document("SampleData.xml")//OrgUnit) )
  return
  max(
     for $c in $b
     return
        count(
	   for $p in document("SampleData.xml")//Person
	   where $p/OrgUnit = $c
	   return $p
             )     
   )
}

let $maxnum := max_count()
let $ou := ( distinct-values(document("SampleData.xml")//OrgUnit) )
for $o in $ou
let $num := count(
               	for $p in document("SampleData.xml")//Person
		where $p/OrgUnit = $o
		return $p
                 )
where $num = $maxnum
return $o

/* Result of Query 1 for sample data */
<?xml version="1.0"?>
<quip:result xmlns:quip="http://namespaces.softwareag.com/tamino/quip/">  
  <OrgUnit>NY Company</OrgUnit>
</quip:result>



/*** Query 2:list all information of people who are not members of any project or authors of any product ***/
let $a := document("SampleData.xml")//Author
let $m := document("SampleData.xml")//Member
for $p in document("SampleData.xml")//Person
where not($p/@SSN  = $a/@SSN or $p/@SSN = $m/@SSN)
return $p

/* Result of Query 2 for sample data */
<?xml version="1.0"?>
<quip:result xmlns:quip="http://namespaces.softwareag.com/tamino/quip/">
  <Person SSN="0000003"> 
    <Name>Judy</Name> 
    <OrgUnit>NY Company</OrgUnit>
    <Emailaddr>judy@nyc.com</Emailaddr>    
    <homepage>505 East Loop Road</homepage>  
  </Person>  
  <Person SSN="0000004">    
    <Name>Jet</Name>    
    <OrgUnit>Boston Company</OrgUnit>    
    <Emailaddr>jet@bc.com</Emailaddr>    
    <homepage>502 East Loop Road</homepage>  
  </Person>
</quip:result>



/*** Query 3:find the total amount of fundings of a project (assuming that the amount is divided equally among multiple projects with the same funding)  ***/
define function  get_fundamount() returns element*
{

for $f in document("SampleData.xml")//Funding
let $num := count(
               	for $proj in document("SampleData.xml")//Project
               	where some $mf in $proj/projFunding satisfies $mf/@id = $f/@id
		return $proj
		 )
return
<fund id = "{$f/@id}" 
 amount="{
if ($num = 0)
then 
$f/Amount
else
$f/Amount div $num 
}" />

}

let $fs := get_fundamount()
for $proj in document("SampleData.xml")//Project
let $g := sum(
             for $f in $fs
             where some $my in $proj/projFunding satisfies $my/@id = $f/@id
             return $f/@amount
             )
return 
<projreport id="{$proj/@id}"
    money="{$g}"   />

/* Result of Query 3 for sample data */
<?xml version="1.0"?>
<quip:result xmlns:quip="http://namespaces.softwareag.com/tamino/quip/"> 
  <projreport id="P1" money="150000" />  
  <projreport id="P2" money="250000" />  
  <projreport id="P3" money="200000" />
</quip:result>



/*** Query 4:list names of all products of any project that has a funding with the most recent start date  ***/
define function  get_maxdatefund() returns element*
{
for $f in document("SampleData.xml")//Funding
where $f/Startdate = max(for $s in document("SampleData.xml")//Startdate
                         return $s)
return $f
}

let $maxf := get_maxdatefund()
for $proc in document("SampleData.xml")//Product
let $g := ( 
             for $p in document("SampleData.xml")//Project
             where some $mp in $p/projProduct/@id satisfies $mp = $proc/@id
             return $p
           )
where some $my in $g satisfies $my/projFunding/@id = $maxf/@id
return 
<product name="{$proc/Name}" />

/* Result of Query 4 for sample data */
<?xml version="1.0"?>
<quip:result xmlns:quip="http://namespaces.softwareag.com/tamino/quip/">  
  <product name="Dvd player" />  
  <product name="Mp3 player" />
</quip:result>



/*** Query 5:find the source that funded every project whose all products' names are contained in the general description  ***/
define function get_descprocs() returns element*
{
for $proc in document("SampleData.xml")//Product
for $g in document("SampleData.xml")//gendescript
where contains($g/text(), $proc/@id)
return $proc
}

define function  get_projs() returns element*
{
let $procs := get_descprocs()
for $proj in document("SampleData.xml")//Project
where every $my in $proj/projProduct satisfies $my/@id = $procs/@id
return $proj
}

let $projs := get_projs()
for $f in document("SampleData.xml")//Funding
where every $p in $projs satisfies $f/@id = $p/projFunding/@id
return $f

/* Result of Query 5 for sample data */
<?xml version="1.0"?>
<quip:result xmlns:quip="http://namespaces.softwareag.com/tamino/quip/">  
  <Funding id="fund3">    
    <Source>State Fund</Source>    
    <Amount>300000</Amount>    
    <Startdate>1995-11-02</Startdate>    
    <Enddate>1996-07-02</Enddate>  
  </Funding>
</quip:result>


/*** Constraint 4:each product of a project has at least one author that is a member of the project ***/
for $pj in document("SampleData.xml")//Project
for $pr in  (
            for $prod in document("SampleData.xml")//Product
            where $pj/projProduct/@id = $prod/@id
            return $prod
             )
where not(some $a in $pr/Author satisfies $a/@SSN = $pj/Member/@SSN)
return 
<constraint project="{$pj/@id}"
   product="{$pr/@id}" />

/* Result of Constraint 4 for sample data */
<?xml version="1.0"?>
<quip:result xmlns:quip="http://namespaces.softwareag.com/tamino/quip/">
  <constraint project="P3" product="Prod3" />
</quip:result>
/* The output tells us that the sample data violate the constraint 4. The product "prod3" in the project
   "P3" doesn't satisfy the constraint   */