List the item number and description of all bicycles that currently have an auction in progress, ordered by item number.

Solution in XQuery:

<result>
  {
    for $i in doc("items.xml")//item_tuple
    where $i/start_date <= current-date()
      and $i/end_date >= current-date() 
      and contains($i/description, "Bicycle")
    order by $i/itemno
    return
        <item_tuple>
            { $i/itemno }
            { $i/description }
        </item_tuple>
  }
</result>

Note:

This solution assumes that the current date is 1999-01-31.

Expected Result:

<result>
    <item_tuple>
        <itemno>1003</itemno>
        <description>Old Bicycle</description>
    </item_tuple>
    <item_tuple>
        <itemno>1007</itemno>
        <description>Racing Bicycle</description>
    </item_tuple>
</result>

The above query returns an element named item_tuple, but its definition does not match the definition of item_tuple in the DTD.





For all bicycles, list the item number, description, and highest bid (if any), ordered by item number.

Solution in XQuery:

<result>
  {
    for $i in doc("items.xml")//item_tuple
    let $b := doc("bids.xml")//bid_tuple[itemno = $i/itemno]
    where contains($i/description, "Bicycle")
    order by $i/itemno
    return
        <item_tuple>
            { $i/itemno }
            { $i/description }
            <high_bid>{ max($b/bid) }</high_bid>
        </item_tuple>
  }
</result> 

Expected Result:

<result>
  <item_tuple>
    <itemno>1001</itemno>
    <description>Red Bicycle</description>
    <high_bid>55.0</high_bid>
  </item_tuple>
  <item_tuple>
    <itemno>1003</itemno>
    <description>Old Bicycle</description>
    <high_bid>20.0</high_bid>
  </item_tuple>
  <item_tuple>
    <itemno>1007</itemno>
    <description>Racing Bicycle</description>
    <high_bid>225</high_bid>
  </item_tuple>
  <item_tuple>
    <itemno>1008</itemno>
    <description>Broken Bicycle</description>
    <high_bid></high_bid>
  </item_tuple>
</result> 




Find cases where a user with a rating worse (alphabetically, greater) than "C" is offering an item with a reserve price of more than 1000.

Solution in XQuery:

<result>
  {
    for $u in doc("users.xml")//user_tuple
    for $i in doc("items.xml")//item_tuple
    where $u/rating > "C" 
       and $i/reserve_price > 1000 
       and $i/offered_by = $u/userid
    return
        <warning>
            { $u/name }
            { $u/rating }
            { $i/description }
            { $i/reserve_price }
        </warning>
  }
</result>

Expected Result:

<result>
    <warning>
        <name>Dee Linquent</name>
        <rating>D</rating>
        <description>Helicopter</description>
        <reserve_price>50000</reserve_price>
    </warning>
</result>




List item numbers and descriptions of items that have no bids.

Solution in XQuery:

<result>
  {
    for $i in doc("items.xml")//item_tuple
    where empty(doc("bids.xml")//bid_tuple[itemno = $i/itemno])
    return
        <no_bid_item>
            { $i/itemno }
            { $i/description }
        </no_bid_item>
  }
</result> 

Expected Result:

<result>
    <no_bid_item>
        <itemno>1005</itemno>
        <description>Tennis Racket</description>
    </no_bid_item>
    <no_bid_item>
        <itemno>1006</itemno>
        <description>Helicopter</description>
    </no_bid_item>
    <no_bid_item>
        <itemno>1008</itemno>
        <description>Broken Bicycle</description>
    </no_bid_item>
</result>




For bicycle(s) offered by Tom Jones that have received a bid, list the item number, description, highest bid, and name of the highest bidder, ordered by item number.

Solution in XQuery:

<result>
  {
    for $seller in doc("users.xml")//user_tuple,
        $buyer in  doc("users.xml")//user_tuple,
        $item in  doc("items.xml")//item_tuple,
        $highbid in  doc("bids.xml")//bid_tuple
    where $seller/name = "Tom Jones"
      and $seller/userid  = $item/offered_by
      and contains($item/description , "Bicycle")
      and $item/itemno  = $highbid/itemno
      and $highbid/userid  = $buyer/userid
      and $highbid/bid = max(
                              doc("bids.xml")//bid_tuple
                                [itemno = $item/itemno]/bid
                         )
    order by ($item/itemno)
    return
        <jones_bike>
            { $item/itemno }
            { $item/description }
            <high_bid>{ $highbid/bid }</high_bid>
            <high_bidder>{ $buyer/name }</high_bidder>
        </jones_bike>
  }
</result> 

The above query does several joins, and requires the results in a particular order. If there were no order by clause, results would be reported in document order. If you do not care about the order, you can use the unordered function to inform the query processor that the order of the lists in the for clause is not significant, which means that the tuples can be generated in any order. This can enable better optimization.

Unordered Solution in XQuery:

<result>
  {
   unordered (
    for $seller in doc("users.xml")//user_tuple,
        $buyer in doc("users.xml")//user_tuple,
        $item in doc("items.xml")//item_tuple,
        $highbid in  doc("bids.xml")//bid_tuple
    where $seller/name = "Tom Jones"
      and $seller/userid  = $item/offered_by
      and contains($item/description , "Bicycle")
      and $item/itemno  = $highbid/itemno
      and $highbid/userid  = $buyer/userid
      and $highbid/bid = max(
                              doc("bids.xml")//bid_tuple
                                [itemno = $item/itemno]/bid
                         )
    return
        <jones_bike>
            { $item/itemno }
            { $item/description }
            <high_bid>{ $highbid/bid }</high_bid>
            <high_bidder>{ $buyer/name }</high_bidder>
        </jones_bike>
    )
  }
</result> 

Expected Result:

<result>
    <jones_bike>
        <itemno>1001</itemno>
        <description>Red Bicycle</description>
        <high_bid>
            <bid>55</bid>
        </high_bid>
        <high_bidder>
            <name>Mary Doe</name>
        </high_bidder>
    </jones_bike>
</result>




For each item whose highest bid is more than twice its reserve price, list the item number, description, reserve price, and highest bid.

Solution in XQuery:

<result>
  {
    for $item in doc("items.xml")//item_tuple
    let $b := doc("bids.xml")//bid_tuple[itemno = $item/itemno]
    let $z := max($b/bid)
    where $item/reserve_price * 2 < $z
    return
        <successful_item>
            { $item/itemno }
            { $item/description }
            { $item/reserve_price }
            <high_bid>{$z }</high_bid>
         </successful_item>
  }
</result> 

Expected Result:

<result>
    <successful_item>
        <itemno>1002</itemno>
        <description>Motorcycle</description>
        <reserve_price>500</reserve_price>
        <high_bid>1200.0</high_bid>
    </successful_item>
    <successful_item>
        <itemno>1004</itemno>
        <description>Tricycle</description>
        <reserve_price>15</reserve_price>
        <high_bid>40.0</high_bid>
    </successful_item>
</result> 


Find the highest bid ever made for a bicycle or tricycle.

Solution in XQuery:

let $allbikes := doc("items.xml")//item_tuple
                    [contains(description, "Bicycle") 
                     or contains(description, "Tricycle")]
let $bikebids := doc("bids.xml")//bid_tuple[itemno = $allbikes/itemno]
return
    <high_bid>
      { 
        max($bikebids/bid) 
      }
    </high_bid> 

Expected Result:

<high_bid>225</high_bid>


How many items were actioned (auction ended) in March 1999?

Solution in XQuery:

let $item := doc("items.xml")//item_tuple
  [end_date >= xs:date("1999-03-01") and end_date <= xs:date("1999-03-31")]
return
    <item_count>
      { 
        count($item) 
      }
    </item_count>

Expected Result:

<item_count>3</item_count>



List the number of items auctioned each month in 1999 for which data is available, ordered by month.

Solution in XQuery:

<result>
  {
    let $end_dates := doc("items.xml")//item_tuple/end_date
    for $m in distinct-values(for $e in $end_dates 
                              return get-month-from-date($e))
    let $item := doc("items.xml")
        //item_tuple[get-year-from-date(end_date) = 1999 
                     and get-month-from-date(end_date) = $m]
    order by $m
    return
        <monthly_result>
            <month>{ $m }</month>
            <item_count>{ count($item) }</item_count>
        </monthly_result>
  }
</result>

Expected Result:

<result>
    <monthly_result>
        <month>1</month>
        <item_count>1</item_count>
    </monthly_result>
    <monthly_result>
        <month>2</month>
        <item_count>2</item_count>
    </monthly_result>
    <monthly_result>
        <month>3</month>
        <item_count>3</item_count>
    </monthly_result>
    <monthly_result>
        <month>4</month>
        <item_count>1</item_count>
    </monthly_result>
    <monthly_result>
        <month>5</month>
        <item_count>1</item_count>
    </monthly_result>
</result> 



For each item that has received a bid, list the item number, the highest bid, and the name of the highest bidder, ordered by item number.

Solution in XQuery:

<result>
 {
    for $highbid in doc("bids.xml")//bid_tuple,
        $user in doc("users.xml")//user_tuple
    where $user/userid = $highbid/userid 
      and $highbid/bid = max(doc("bids.xml")//bid_tuple[itemno=$highbid/itemno]/bid)
    order by $highbid/itemno
    return
        <high_bid>
            { $highbid/itemno }
            { $highbid/bid }
            <bidder>{ $user/name/text() }</bidder>
        </high_bid>
  }
</result> 

Expected Result:

<result>
    <high_bid>
        <itemno>1001</itemno>
        <bid>55</bid>
        <bidder>Mary Doe</bidder>
    </high_bid>
    <high_bid>
        <itemno>1002</itemno>
        <bid>1200</bid>
        <bidder>Mary Doe</bidder>
    </high_bid>
    <high_bid>
        <itemno>1003</itemno>
        <bid>20</bid>
        <bidder>Jack Sprat</bidder>
    </high_bid>
    <high_bid>
        <itemno>1004</itemno>
        <bid>40</bid>
        <bidder>Tom Jones</bidder>
    </high_bid>
    <high_bid>
        <itemno>1007</itemno>
        <bid>225</bid>
        <bidder>Roger Smith</bidder>
    </high_bid>
</result> 




List the item number and description of the item(s) that received the highest bid ever recorded, and the amount of that bid.

Solution in XQuery:

let $highbid := max(doc("bids.xml")//bid_tuple/bid)
return
    <result>
     {
        for $item in doc("items.xml")//item_tuple,
            $b in doc("bids.xml")//bid_tuple[itemno = $item/itemno]
        where $b/bid = $highbid
        return
            <expensive_item>
                { $item/itemno }
                { $item/description }
                <high_bid>{ $highbid }</high_bid>
            </expensive_item>
     }
    </result> 

Expected Result:

<result>
    <expensive_item>
        <itemno>1002</itemno>
        <description>Motorcycle</description>
        <high_bid>1200.0</high_bid>
    </expensive_item>
</result> 


List the item number and description of the item(s) that received the largest number of bids, and the number of bids it (or they) received.

Solution in XQuery:

declare function local:bid_summary()
  as element()*
{
    for $i in distinct-values(doc("bids.xml")//itemno)
    let $b := doc("bids.xml")//bid_tuple[itemno = $i]
    return
        <bid_count>
            <itemno>{ $i }</itemno>
            <nbids>{ count($b) }</nbids>
        </bid_count>
};

<result>
 {
    let $bid_counts := local:bid_summary(),
        $maxbids := max($bid_counts/nbids),
        $maxitemnos := $bid_counts[nbids = $maxbids]
    for $item in doc("items.xml")//item_tuple,
        $bc in $bid_counts
    where $bc/nbids =  $maxbids and $item/itemno = $bc/itemno
    return
        <popular_item>
            { $item/itemno }
            { $item/description }
            <bid_count>{ $bc/nbids/text() }</bid_count>
        </popular_item>
 }
</result> 

Expected Result:

<result>
    <popular_item>
        <itemno>1001</itemno>
        <description>Red Bicycle</description>
        <bid_count>5</bid_count>
    </popular_item>
    <popular_item>
        <itemno>1002</itemno>
        <description>Motorcycle</description>
        <bid_count>5</bid_count>
    </popular_item>
</result> 



For each user who has placed a bid, give the userid, name, number of bids, and average bid, in order by userid.

Solution in XQuery:

<result>
 {
    for $uid in distinct-values(doc("bids.xml")//userid),
        $u in doc("users.xml")//user_tuple[userid = $uid]
    let $b := doc("bids.xml")//bid_tuple[userid = $uid]
    order by $u/userid
    return
        <bidder>
            { $u/userid }
            { $u/name }
            <bidcount>{ count($b) }</bidcount>
            <avgbid>{ avg($b/bid) }</avgbid>
        </bidder>
  }
</result> 

Expected Result:

<result>
    <bidder>
        <userid>U01</userid>
        <name>Tom Jones</name>
        <bidcount>2</bidcount>
        <avgbid>220.0</avgbid>
    </bidder>
    <bidder>
        <userid>U02</userid>
        <name>Mary Doe</name>
        <bidcount>5</bidcount>
        <avgbid>387.0</avgbid>
    </bidder>
    <bidder>
        <userid>U03</userid>
        <name>Dee Linquent</name>
        <bidcount>2</bidcount>
        <avgbid>487.5</avgbid>
    </bidder>
    <bidder>
        <userid>U04</userid>
        <name>Roger Smith</name>
        <bidcount>5</bidcount>
        <avgbid>266.0</avgbid>
    </bidder>
    <bidder>
        <userid>U05</userid>
        <name>Jack Sprat</name>
        <bidcount>2</bidcount>
        <avgbid>110.0</avgbid>
    </bidder>
</result> 


List item numbers and average bids for items that have received three or more bids, in descending order by average bid.

Solution in XQuery:

<result>
 {
    for $i in distinct-values(doc("bids.xml")//itemno)
    let $b := doc("bids.xml")//bid_tuple[itemno = $i]
    let $avgbid := avg($b/bid)
    where count($b) >= 3
    order by $avgbid descending
    return
        <popular_item>
            <itemno>{ $i }</itemno>
            <avgbid>{ $avgbid }</avgbid>
        </popular_item>
  }
</result> 

Expected Result:

<result>
    <popular_item>
        <itemno>1002</itemno>
        <avgbid>800.0</avgbid>
    </popular_item>
    <popular_item>
        <itemno>1007</itemno>
        <avgbid>200.0</avgbid>
    </popular_item>
    <popular_item>
        <itemno>1001</itemno>
        <avgbid>45.0</avgbid>
    </popular_item>
</result>





List names of users who have placed multiple bids of at least $100 each.

Solution in XQuery:

<result>
  {
    for $u in doc("users.xml")//user_tuple
    let $b := doc("bids.xml")//bid_tuple[userid=$u/userid and bid>=100]
    where count($b) > 1
    return
        <big_spender>{ $u/name/text() }</big_spender>
  }
</result>

Expected Result:

<result>
    <big_spender>Mary Doe</big_spender>
    <big_spender>Dee Linquent</big_spender>
    <big_spender>Roger Smith</big_spender>
</result>





List all registered users in order by userid; for each user, include the userid, name, and an indication of whether the user is active (has at least one bid on record) or inactive (has no bid on record).

Solution in XQuery:

<result>
  {
    for $u in doc("users.xml")//user_tuple
    let $b := doc("bids.xml")//bid_tuple[userid = $u/userid]
    order by $u/userid
    return
        <user>
            { $u/userid }
            { $u/name }
            {
                if (empty($b))
                  then <status>inactive</status>
                  else <status>active</status>
            }
        </user>
  }
</result>

Expected Result:

<result>
    <user>
        <userid>U01</userid>
        <name>Tom Jones</name>
        <status>active</status>
    </user>
    <user>
        <userid>U02</userid>
        <name>Mary Doe</name>
        <status>active</status>
    </user>
    <user>
        <userid>U03</userid>
        <name>Dee Linquent</name>
        <status>active</status>
    </user>
    <user>
        <userid>U04</userid>
        <name>Roger Smith</name>
        <status>active</status>
    </user>
    <user>
        <userid>U05</userid>
        <name>Jack Sprat</name>
        <status>active</status>
    </user>
    <user>
        <userid>U06</userid>
        <name>Rip Van Winkle</name>
        <status>inactive</status>
    </user>
</result>


List the names of users, if any, who have bid on every item.

Solution in XQuery:

<frequent_bidder>
  {
    for $u in doc("users.xml")//user_tuple
    where 
      every $item in doc("items.xml")//item_tuple satisfies 
        some $b in doc("bids.xml")//bid_tuple satisfies 
          ($item/itemno = $b/itemno and $u/userid = $b/userid)
    return
        $u/name
  }
</frequent_bidder>

Expected Result:

<frequent_bidder /> 

(No users satisfy Q17.)




List all users in alphabetic order by name. For each user, include descriptions of all the items (if any) that were bid on by that user, in alphabetic order.

Solution in XQuery:

<result>
  {
    for $u in doc("users.xml")//user_tuple
    order by $u/name
    return
        <user>
            { $u/name }
            {
                for $b in distinct-values(doc("bids.xml")//bid_tuple
                                             [userid = $u/userid]/itemno)
                for $i in doc("items.xml")//item_tuple[itemno = $b]
                let $descr := $i/description/text()
                order by $descr
                return
                    <bid_on_item>{ $descr }</bid_on_item>
            }
        </user>
  }
</result>

Expected Result:

<result>
    <user>
        <name>Dee Linquent</name>
        <bid_on_item>Motorcycle</bid_on_item>
        <bid_on_item>Racing Bicycle</bid_on_item>
    </user>
    <user>
        <name>Jack Sprat</name>
        <bid_on_item>Old Bicycle</bid_on_item>
        <bid_on_item>Racing Bicycle</bid_on_item>
    </user>
    <user>
        <name>Mary Doe</name>
        <bid_on_item>Motorcycle</bid_on_item>
        <bid_on_item>Red Bicycle</bid_on_item>
    </user>
    <user>
        <name>Rip Van Winkle</name>
    </user>
    <user>
        <name>Roger Smith</name>
        <bid_on_item>Motorcycle</bid_on_item>
        <bid_on_item>Old Bicycle</bid_on_item>
        <bid_on_item>Racing Bicycle</bid_on_item>
        <bid_on_item>Red Bicycle</bid_on_item>
    </user>
    <user>
        <name>Tom Jones</name>
        <bid_on_item>Motorcycle</bid_on_item>
        <bid_on_item>Tricycle</bid_on_item>
    </user>
</result>