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, {\$a/@id}) For \$a in //customer//order[@id="1"] Return {\$a/@id} For \$a in //customer//order[@id="1"] Return {\$a/@id} 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, {\$a/@id} {\$a/{blue}user_name/text()} {count(\$b)} ) For \$a in //customer Let \$b := \$a//order[total > 11000.00] Where count(\$b) > 0 Return {\$a/@id} {\$a/{blue}user_name/text()} {count(\$b)} For \$a in //customer Let \$b := \$a//order[total > 11000.00] Where count(\$b) > 0 Return {\$a/@id} {\$a/{blue}user_name/text()} {count(\$b)} 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, {\$b/@id}) For \$a in //order[@id="3"] \$b in //item Where \$a//order_line/@item_idref = \$b/@id Return {\$b/@id} For \$a in //order[@id="3"] Return {\$a//order_line/item/@id} 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, {\$a/@id} {\$a/{blue}ship_type} ) For \$a in //order [total > 11000.00] Order by \$a/ship_type Return {\$a/@id} {\$a/ship_type} For \$a in //order [total > 11000.00] Order by \$a/ship_type Return {\$a/@id} {\$a/ship_type} TQ5 List all order lines of a certain order with id attribute value (5). For \$a in //{blue}order[@id="5"] Return createColor(black, {\$a//{blue}order_line} ) For \$a in //order[@id="5"] Return {\$a//order_line} For \$a in //order[@id="5"] Return {\$a//order_line} 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, {\$a/@id}) For \$a in //order Where empty(\$a/order_lines/order_line[2]) Return {\$a/@id} For \$a in //order Where empty(\$a/order_lines/order_line[2]) Return {\$a/@id} 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, {\$a/@id}) For \$a in //author Where contains (\$a/biography,"hockey") Return {\$a/@id} For \$a in //author Where contains (\$a/biography,"hockey") Return {distinct-values(\$a/@id)} 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, {\$b/{blue}first_name} {\$b/{blue}last_name} {\$b/{blue}phone_number} {\$a/{blue}order_status} ) For \$a in //order[@id="7"], \$b in //customer Where some(\$b//order = \$a) Return {\$b/first_name} {\$b/last_name} {\$b/phone_number} {\$a/order_status} For \$a in //order[@id="7"], \$b in //customer Where some(\$b//order = \$a) Return {\$b/first_name} {\$b/last_name} {\$b/phone_number} {\$a/order_status} 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, {distinct-values(\$a/@id)}) For \$a in //item, \$b in //order_line[quantity_of_item < 20] Where \$a/@id = \$b/@item_idref Return {distinct-values(\$a/@id)} For \$a in //order_line[quantity_of_item < 20]/item Return {distinct-values(\$a/@id)} 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 {distinct-values(\$a)}) 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 {distinct-values(\$a)} For \$a in //customer, \$b in \$a//order Where \$b/order_status = "PENDING" and \$b/ship_address/country/name = "Canada" Return {distinct-values(\$a)} 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, {distinct-values(\$a/@id)}) For \$a in //order \$b in //address[name_of_city = "Honolulu"] Where \$a/@shipaddr_idref = \$b/@id Return {distinct-values(\$a/@id)} For \$a in //order[ship_address/name_of_city = "Honolulu"] Return {distinct-values(\$a/@id)} TQ12 Return cost of item that has ISBN = 1ZKUVJGIDBQJLE (item1). For \$c in //{yellow}item[{yellow}ISBN = "1ZKUVJGIDBQJLE"] Return createColor(black, {\$c/cost}) For \$c in //item[ISBN = "1ZKUVJGIDBQJLE"] Return {\$c/cost} For \$c in //item[ISBN = "1ZKUVJGIDBQJLE"] Return {\$c/cost} 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, {\$a}) For \$a in //order [order_status = "SHIPPED"], \$b in //year [ = 2002] Where \$a/@time_idref = \$b//day/@id Return {\$a} For \$a in distinct-values(//order[order_status = "SHIPPED" and //year = 2002]) Return {\$a} 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, {\$a}) For \$a in //order [order_status = "SHIPPED"], \$b in //year [ = 2002]//month [ = 12] Where \$a/@time_idref = \$b//day/@id Return {\$a} For \$a in distinct-values(//order[order_status = "SHIPPED" and //year = 2002 and //month = 12]) Return {\$a} 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, {\$a} {count(\$b)} ) For \$a in //author Let \$b := //order_line[quantity_of_item <= 2] Where \$a/item/@id = \$b/@item_idref Return {\$a} {count(\$b)} For \$a in //author Let \$b := //order_line[quantity_of_item <= 2] Where \$b/author = \$a Return {\$a} {count(\$b)} 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, {\$a/{red}name}) For \$a in //country Let \$b := //order Where \$b/@shipaddr_idref = \$a//address/@id Order by count(\$b) Return {\$a/name} For \$a in //country Let \$b := //order Where \$b/ship_address/country = \$a Order by count(\$b) Return {\$a/name} 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, {\$a}) For \$a in //article[title = "Database Compression"] Return {\$a} For \$a in //article[title = "Database Compression"] Return {\$a} SQ2 Return all articles in the topic "Industry Perspectives". For \$a in //{red}topic[ = "Industry Perspectives"] Return createColor(black, {\$a/{red}article}) For \$a in //topic[ = "Industry Perspectives"] \$b in //article Where \$a/@id = \$b/@topic_idref Return {\$b} For \$a in //topic[ = "Industry Perspectives"] Return {\$a/article} 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, {\$a/@id} {count(\$b)} ) For \$a in //editor [@id = "e1"]//topic Let \$b := //article Where \$b/@topic_idref = \$a/@id Return {\$a/@id} {count(\$b)} 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 {\$id1} {count(\$b)} SQ4 List all topic names defined by each editor. For \$a in //{red}editor Let \$b := \$a/{red}topic Return createColor(black, {\$a} {\$b/text()} ) For \$a in editor Let \$b := \$a/topic Return {\$a} {\$b/text()} 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 {\$a} {distinct-values(\$b/text())} SQ5 Display article title and authors published in year 2002 issue. For \$a in //{blue}year[= 2002]//{blue}article Return createColor(black, {\$a/{blue}title} {\$a/{blue}authors} ) For \$a in //year [= 2002] //issue, \$b in //article Where \$a/@id = \$b/@issue_idref Return {\$b/title} {\$b/authors} For \$a in //year[ = 2002]/issue//article Return {\$a/title} {\$a/authors} 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")