TPC-W (MCT, Shallow, Deep, respectively)
{blue}customer-order-orderline
{purple}billing address-order-orderline
{red}shipping address-order-orderline
{green}date-order-orderline
{yellow}author-item-orderline

TQ1 Return the customer id of the order that has matching id attribute value (1).
For $a in //{blue}customer//{blue}order[@id="1"]
Return 
   createColor(black, <result>{$a/@id}</result>)
   
For $a in //customer//order[@id="1"]
Return 
   <result>{$a/@id}</result>
   
For $a in //customer//order[@id="1"]
Return 
   <result>{$a/@id}</result>
   
TQ2 Group orders with total amount bigger than a certain number (11000.0), by customer id, display user_name and calculate the total number of each group.
For $a in //{blue}customer
Let $b := $a//{blue}order[{blue}total > 11000.00]
Where count($b) > 0
Return createColor(black,
              <result>
 	<customerid>{$a/@id}</customerid>
 	<customername>{$a/{blue}user_name/text()}</customername>
 	<count>{count($b)}</count>
              </result>)

For $a in //customer
Let $b := $a//order[total > 11000.00]
Where count($b) > 0
Return <result>
 	<customerid>{$a/@id}</customerid>
 	<customername>{$a/{blue}user_name/text()}</customername>
 	<count>{count($b)}</count>
              </result>
              
For $a in //customer
Let $b := $a//order[total > 11000.00]
Where count($b) > 0
Return <result>
 	<customerid>{$a/@id}</customerid>
 	<customername>{$a/{blue}user_name/text()}</customername>
 	<count>{count($b)}</count>
              </result>

TQ3 Return the order line item ids of an order with an attribute value (3).
For $a in //{blue}order[@id="3"]//{blue}order_line,
       $b in //{yellow}item[//{yellow}order_line = $a]
Return createColor(black,
	<result>{$b/@id}</result>)

For $a in //order[@id="3"]
       $b in //item
Where $a//order_line/@item_idref = $b/@id
Return <result>{$b/@id}</result>

For $a in //order[@id="3"]
Return <result>{$a//order_line/item/@id}</result>

TQ4  List the orders (order id, ship type), with total amount larger than a certain number (11000.0), ordered alphabetically by ship type.
For $a in //{blue}order [{blue}total > 11000.00]
Order by $a/{blue}ship_type
Return createColor(black,
	<result>
	     {$a/@id}
	     {$a/{blue}ship_type}
	</result>)

For $a in //order [total > 11000.00]
Order by $a/ship_type
Return <result>
	     {$a/@id}
	     {$a/ship_type}
              </result>

For $a in //order [total > 11000.00]
Order by $a/ship_type
Return <result>
	     {$a/@id}
	     {$a/ship_type}
              </result>
	
TQ5 List all order lines of a certain order with id attribute value (5).
For $a in //{blue}order[@id="5"]
Return  createColor(black,
               <result>
                {$a//{blue}order_line}
               </result>)
 
For $a in //order[@id="5"]
Return <result>
                {$a//order_line}
               </result>
               
For $a in //order[@id="5"]
Return <result>
                {$a//order_line}
               </result>
               
TQ6 List the ids of orders that only have one order line.
For $a in //{blue}order
Where empty($a/{blue}order_lines/{blue}order_line[2])
Return   createColor(black,
	<result>{$a/@id}</result>)

For $a in //order
Where empty($a/order_lines/order_line[2])
Return  <result>{$a/@id}</result>

For $a in //order
Where empty($a/order_lines/order_line[2])
Return  <result>{$a/@id}</result>

TQ7 Return the ids of authors whose biographies contain a certain word (``hockey").
For $a in //{yellow}author
Where contains ($a/{yellow}biography,"hockey")
Return createColor(black,
	<result>{$a/@id}</result>)

For $a in //author
Where contains ($a/biography,"hockey")
Return <result>{$a/@id}</result>

For $a in //author
Where contains ($a/biography,"hockey")
Return <result>{distinct-values($a/@id)}</result>

TQ8 For a particular order with id attribute value (7), get its customer name and phone, and its order status.
For $a in //{blue}order[@id="7"],
       $b in //{blue}customer
Where some($b//{blue}order = $a)
Return createColor(black,
               <result>	
	{$b/{blue}first_name}
	{$b/{blue}last_name}
	{$b/{blue}phone_number}
	{$a/{blue}order_status}
               </result>)

For $a in //order[@id="7"],
       $b in //customer
Where some($b//order = $a)
Return <result>	
	{$b/first_name}
	{$b/last_name}
	{$b/phone_number}
	{$a/order_status}
               </result>

For $a in //order[@id="7"],
       $b in //customer
Where some($b//order = $a)
Return <result>	
	{$b/first_name}
	{$b/last_name}
	{$b/phone_number}
	{$a/order_status}
               </result>

TQ9 List all item ids with quantity ordered each time less than 20.
For $a in //{blue}item[{blue}order_line/{blue}quantity_of_item < 20]
Return createColor(black,
	<result>{distinct-values($a/@id)}</result>)

For $a in //item,
      $b in //order_line[quantity_of_item < 20]
Where $a/@id = $b/@item_idref
Return <result>{distinct-values($a/@id)}</result>

For $a in //order_line[quantity_of_item < 20]/item
Return <result>{distinct-values($a/@id)}</result>

TQ10 Return customers where shipping address is in Canada and that some of the order's order_status is "PENDING".
For $a in //{blue}customer,
       $b in $a//{blue}order,
       $b in //{red}country[{red}name"Canada"]//{red}shipping_addresses/{red}address//{red}order
Where $b/{red}order_status = "PENDING"
Return createColor(black
	<result>{distinct-values($a)}</result>)

For $a in //customer,
       $o in $a//order,
       $b in //country[name="Canada"]//address
Where $o/@shipaddr_idref = $b/@id and
	$o/order_status = "PENDING"
Return <result>{distinct-values($a)}</result>

For $a in //customer,
       $b in $a//order
Where	$b/order_status = "PENDING" and
	$b/ship_address/country/name = "Canada"
Return <result>{distinct-values($a)}</result>

TQ11 Return order ids that have shipping address in the city of "Honolulu".
For $a in //{red}shipping_addresses/{red}address[{red}name_of_city = "Honolulu]//{red}order
Return createColor(black,
	<result>{distinct-values($a/@id)}</result>)

For $a in //order
       $b in //address[name_of_city = "Honolulu"]
Where $a/@shipaddr_idref = $b/@id
Return <result>{distinct-values($a/@id)}</result>

For $a in //order[ship_address/name_of_city = "Honolulu"]
Return <result>{distinct-values($a/@id)}</result>


TQ12 Return cost of item that has ISBN = 1ZKUVJGIDBQJLE (item1).
For $c in //{yellow}item[{yellow}ISBN = "1ZKUVJGIDBQJLE"]
Return createColor(black,
	<result>{$c/cost}</result>)

For $c in //item[ISBN = "1ZKUVJGIDBQJLE"]
Return <result>{$c/cost}</result>

For $c in //item[ISBN = "1ZKUVJGIDBQJLE"]
Return <result>{$c/cost}</result>

TQ13 Return orders in year 2002 that is SHIPPED (order_status).
For $a in //{green}year[ = 2002]//{green}order[{green}order_status = "SHIPPED"]
Return createColor(black,
	<result>{$a}</result>)

For $a in //order [order_status = "SHIPPED"],
       $b in //year [ = 2002]
Where $a/@time_idref = $b//day/@id
Return <result>{$a}</result>

For $a in distinct-values(//order[order_status = "SHIPPED" and //year = 2002])
Return <result>{$a}</result>


TQ14 Return orders in year December 2002  that is SHIPPED (order_status).
For $a in //{green}year [ = 2002]//{green}month [ = 12] //{green}order[{green}order_status = "SHIPPED"]
Return createColor(black,
	<result>{$a}</result>)

For $a in //order [order_status = "SHIPPED"],
       $b in //year [ = 2002]//month [ = 12]
Where $a/@time_idref = $b//day/@id
Return <result>{$a}</result>

For $a in distinct-values(//order[order_status = "SHIPPED" and //year = 2002 and //month = 12])
Return <result>{$a}</result>

TQ15 For each author, count number of order_line that has quantity_of_item ordered each time not more than 2.
For $a in //{yellow}author
Let $b := $a//{yellow}order_line[{yellow}quantity_of_item <= 2]
Return createColor(black,<result>
	{$a}
	<count> {count($b)}</count>
              </result>)

For $a in //author
Let $b := //order_line[quantity_of_item <= 2]
Where $a/item/@id = $b/@item_idref
Return <result>
	{$a}
	<count> {count($b)}</count>
              </result>

For $a in //author
Let $b := //order_line[quantity_of_item <= 2]
Where $b/author = $a
Return <result>
	{$a}
	<count> {count($b)}</count>
              </result>


TQ16 Return all country name that is the destination of orders, ordered by number of orders from that country.
For $a in //{red}country
Let $b := $a/{red}shipping_addresses//{red}order
Order by count($b)
Return createColor(black,
	<result>{$a/{red}name}</result>)

For $a in //country
Let $b := //order
Where $b/@shipaddr_idref = $a//address/@id
Order by count($b)
Return <result>{$a/name}</result>

For $a in //country
Let $b := //order
Where $b/ship_address/country = $a
Order by count($b)
Return <result>{$a/name}</result>

TU1 Modify the last name of author id (1) to Doe.
For $a in //{yellow}author [@id = "1"]
Modify($a/{yellow}last_name/text() = "Doe")

For $a in //author [@id = "1"]
Modify($a/last_name/text() = "Doe")

For $a in //author [@id = "1"]
Modify($a/last_name/text() = "Doe")

TU2 Modify zip code of an address (id = 444)  to 01111.
For $a in //{red}address [@id = "444"]
Modify($a/{red}zip_code/text() = "01111")

For $a in //address [@id = "444"]
Modify($a/zip_code/text() = "01111")

For $a in //address [@id = "444"]
Modify($a/zip_code/text() = "01111")

TU3 Modify order_status of today order (11/16/2003) to "SHIPPED".
For $a in //{green}year[ = 2003]/{green}month [ = 11]/{green}day [= 16] //{green}order
Modify($a/{green}order_status = "SHIPPED")

For $a in //order,
       $b in //year[=2003] /month [ =11] / day [= 16]
Where $a/@time_idref = $b/@id
Modify($a/order_status = "SHIPPED")

For $a in order[year = 2003 and month = 11 and day = 16]
Modify($a/order_status = "SHIPPED")

TU4 Modify zip_code of billing_address of order id = "1" to "88888".
For $a in //{purple}billing_addresses/{purple}address[{purple}order/@id = "1"]
Modify($a/{purple}zip_code = "88888")

For $a in //order[@id = "1"],
       $b in //address
Where $a/@billaddr_idref = $b/@id
Modify($a/zip_code = "88888")

For $a in order[@id = "1"]/bill_address
Modify($a/zip_code = "88888")

SIGMOD Record (MCT, Shallow, Deep, respectively)
{blue}date-issue-articles
{red}editor-topic-articles

SQ1 Display an article with title "Database Compression".
For $a in //{blue}article[{blue}title = "Database Compression"]
Return createColor(black,
	<result>{$a}</result>)

For $a in //article[title = "Database Compression"]
Return <result>{$a}</result>

For $a in //article[title = "Database Compression"]
Return <result>{$a}</result>

SQ2 Return all articles in the topic "Industry Perspectives".
For $a in //{red}topic[ = "Industry Perspectives"]
Return createColor(black,
	<result>{$a/{red}article}</result>)

For $a in //topic[ = "Industry Perspectives"]
       $b in //article
Where $a/@id = $b/@topic_idref
Return <result>{$b}</result>

For $a in //topic[ = "Industry Perspectives"]
Return <result>{$a/article}</result>

SQ3 Return number of articles by topic, for the first editor.
For $a in //{red}editor [@id = "e1"]//{red}topic
Let  $b := $a/{red}article
Return createColor(black,
              <result>
	<topic>{$a/@id}</topic>
	<count>{count($b)}</count>
              </result>)

For $a in //editor [@id = "e1"]//topic
Let  $b := //article
Where $b/@topic_idref = $a/@id
Return <result>
	<topic>{$a/@id}</topic>
	<count>{count($b)}</count>
              </result>
              
For $id1 in distinct-values(//issue[editor/@id = "e1"]//topic/@id)
Let  $b := {
	For $t in //issue[editor/@id = "e1"]//topic
	        $id2 = $t/@id
	Let $cc : = $t/article
	Where $id1 = $id2
	Return $cc
	}
Return <result>
	<topic>{$id1}</topic>
	<count>{count($b)}</count>
              </result>

SQ4 List all topic names defined by each editor.
For $a in //{red}editor
Let $b := $a/{red}topic
Return createColor(black,
             <result>
	{$a}
	<topic>{$b/text()}</topic>
             </result>)

For $a in editor
Let $b := $a/topic
Return <result>
	{$a}
	<topic>{$b/text()}</topic>
             </result>

For $a in //issue/editor,
       $id1 in distinct-values($a/@id)
Let  $b := {
	For $c in //issue
	        $id2 = $c/editor/@id
	Let $cc : = $c/topic
	Where $id1 = $id2
	Return $cc
	}
Return <result>
	{$a}
	<topic>{distinct-values($b/text())}</topic>
              </result>

SQ5 Display article title and authors published in year 2002 issue.
For $a in //{blue}year[= 2002]//{blue}article
Return  createColor(black,
               <result>
	{$a/{blue}title}
	{$a/{blue}authors}
              </result>)

For $a in //year [= 2002] //issue,
       $b in //article
Where $a/@id = $b/@issue_idref
Return <result>
	{$b/title}
	{$b/authors}
              </result>

For $a in //year[ = 2002]/issue//article
Return <result>
	{$a/title}
	{$a/authors}
              </result>

SU1 Modify the name of topic from "Reports" to "Database Reports".
For $a in //{blue}topic[ = "Reports"]
Modify($a/text() = "Database Reports")

For $a in //topic[ = "Reports"]
Modify($a/text() = "Database Reports")

For $a in //topic[ = "Reports"]
Modify ($a/text() = "Database Reports") 

SU2 Modify the name of topic of an article ("Report on the EDBT'02 Panel on Scientific Data Integration") to "Reports" .
For $a in //{blue}topic[//{blue}article/{blue}title = "Report on the EDBT'02 Panel on Scientific Data Integration"]
Modify($a/text() = "Reports")

For $a in //article[title  = "Report on the EDBT'02 Panel on Scientific Data Integration"]
       $b in //topic
Where $b/@id = $a/@topic_idref
Modify($b/text() = "Reports")

For $a in //topic[//article/title = "Report on the EDBT'02 Panel on Scientific Data Integration"]
Let $b := //topic
Where $b/@id = $a/@id
Modify($b/text() = "Reports")