SQL to MarkLogic mapping

CREATE TABLE USERS (a 
            Number
            , b 
            Number
            )

Not necessary

ALTER TABLE users ADD ...

Not necessary

INSERT INTO USERS VALUES(3,5)

document-insert("/foo.xml", 
  <user>
    <a>3</a>
    <b>5</b>
  </user>
)

SELECT a,b FROM users

/user/(a|b)

SELECT * FROM users

/user

SELECT * FROM users WHERE age=33

/user[age = 33]

SELECT a,b FROM users WHERE age=33

/user[age = 33]/(a|b)

SELECT * FROM users WHERE age=33 
 ORDER BY name

for $user in /user[age = 33]
order by $user/name
return $user

SELECT * FROM users WHERE age>33

/user[age > 33]

SELECT * FROM users WHERE age!=33

/user[age != 33]

SELECT * FROM users 
 WHERE name LIKE 
            "%Joe%"

(: text substring, matches "Joey" :)/user[fn:contains(name, "Joe")]

(: full word match :)
/user[cts:contains(name, "Joe")]  

SELECT * FROM users 
 WHERE name LIKE 
            "Joe%"

/user[cts:contains(name, "Joe*")]

SELECT * FROM users 
 WHERE age>33 AND 
       age<=40

/user[age > 33 and age <= 40]

SELECT * FROM users 
 ORDER BY name DESC

for $user in /user
order by $user/name descending
return $user

SELECT * FROM users 
 WHERE a=1 AND 
       b='q'

/user[a = 1][b = 'q']

SELECT * FROM users 
 LIMIT 10 SKIP 20

/user[21 to 30]

SELECT * FROM users WHERE a=1 or b=2

/user[a = 1 or b = 2]

SELECT * FROM users LIMIT 1

(/user)[1]

SELECT order_id FROM 
 orders o, order_line_items li 
 WHERE li.order_id=o.order_id 
 AND   li.sku=12345

/items[sku = 12345]/order_id

SELECT customer.name FROM customers,orders 
 WHERE orders.id=
            "q179"
            AND   orders.custid=customer.id 

/customer
 [id = fn:doc("/order/q179")/custid]/name

SELECT DISTINCT last_name FROM users

(: standard :)
fn:distinct-values(/user/last_name)          

(: optimized :)
cts:element-values(xs:QName("last_name")) 

SELECT COUNT(*y) FROM users

estimate(/user)

SELECT COUNT(*y) FROM users 
 WHERE AGE > 30

estimate(/user[age > 30])

SELECT COUNT(AGE) from users

estimate(/user/age)

CREATE INDEX myindexname ON users(name) 

Not necessary

CREATE INDEX myindexname 
 ON users(name, ts DESC) 

Not necessary

EXPLAIN SELECT * FROM users WHERE z=3

plan(/user[z = 3])

UPDATE users SET a=1 WHERE b='q'

node-replace(/user[b = 'q']/a, <a>1</a>)

UPDATE users SET a=a+2 WHERE b='q'

for $a in /user[b = 'q']/a
return node-replace($a, <a>{$a + 2}</a>)

DELETE FROM users WHERE z=
            "abc"

(: auto iterates :)
document-delete(/user[z = "abc"])