Functions and Operators¶

x

boolean(x)
boolean('true')
true
boolean('false')
false
boolean(string('HTSQL'))
true
boolean(string(''))
false
true()
true()
false()
false()
p & q
true()&true()
true
true()&false()
false
false()&false()
false
p | q
true()|true()
true
true()|false()
true
false()|false()
false
!p
!true()
false
!false()
true
null(x)
null()
is_null(x)
is_null(null())
true
if_null(x,y)
if_null(1,0)
1
if_null(null(),0)
0
null_if(x,y)
null_if(1,0)
1
null_if(0,0)
null
x = y
'HTSQL'='QUEL'
false
x != y
'HTSQL'!='QUEL'
true
x == y
'HTSQL'=='QUEL'
false
2==null()
false
x !== y
'HTSQL'!=='QUEL'
true
2!==null()
true
x = {a,b,c,...}
5={2,3,5,7}'
true
x != {a,b,c,...}
5!={2,3,5,7}'
false
x < y
1<10
true
'omega'<'alpha'
false
x <= y
1<=10
true
'omega'<='alpha'
false
x > y
1>10
false
'omega'>'alpha'
true
x >= y
1>=10
false
'omega'>='alpha'
true
if(p1,c1,...,pn,cn)
if(true(),'up','down')
'up'
if(p1,c1,...,pn,cn,o)
if(false(),'up','down')
'down'
switch(x,y1,c1,...,yn,cn)
switch(1,1,'up',0,'down')
'up'
switch(x,y1,c1,...,yn,cn,o)
switch(0,1,'up',0,'down')
'down'
boolean(x)
x
untyped
'false'
'true'
boolean
string
null
◥/{boolean('false'), boolean('true')}

◥/{boolean(null()), boolean(false()), boolean(true())}

◥/{boolean(string(null())), boolean(string('')), boolean(string('HTSQL'))}

◥/{boolean(integer(null())), boolean(0.0), boolean(date('2010-04-15'))}

true()
false()
◥/{true(), false()}

p | q
p & q
! p
boolean()
◥/{true()|true(), true()|false(), false()|true(), false()|false()}

◥/{true()&true(), true()&false(), false()&true(), false()&false()}

◥/{!true(), !false()}

◥/{true()&null(), false()&null(), null()&null(),
  true()|null(), false()|null(), null()|null(),
  !null()}

◥/school?exists(program)&exists(department)|!campus

null()
is_null(x)
x
if_null(x,y)
x
x
y
null_if(x,y)
x
x
y
if_null()
null_if()
◥/{null()}

◥/{is_null(null()), is_null(0)}

◥/{if_null('SQL','HTSQL'), if_null(null(),'HTSQL')}

◥/{null_if('HTSQL','SQL'), null_if('SQL','SQL')}

◥/course{title, credits}?is_null(credits)

◥/course{title, credits}?(credits :if_null 0)=0

◥/course{title, credits}?!(credits :null_if 0)

x = y
x
y
x != y
x
y
x == y
x
y
x !== y
x
y
x = {a,b,c,...}
x
a,b,c,...
x != {a,b,c,...}
x
a,b,c,...
x = {a,b,c,...}
x=a|x=b|x=c|...
x != {a,b,c,...}
x!=a|x!=b|x!=c|...
◥/{1=1.0, 'HTSQL'!='SQUARE'}

◥/{0!=null(), null()=null(), 0!==null(), null()==null()}

◥/'HTSQL'!={'ISBL','SQUARE','QUEL'}

◥/school?campus='old'

◥/school?campus!={'north','south'}

◥/school{code, campus=='old', campus=='north', campus=='south'}

x < y
x
y
x <= y
x
y
x > y
x
y
x >= y
x
y
◥/{23<=17.5, 'HTSQL'<'SQUARE', date('2010-04-15')>=date('1991-08-20')}

◥/school?count(department)>=4

if(p1,c1,p2,c2,...,pn,cn[,o])
p1,p2,...,pN
c1,c2,...,cN
o
ck
pk
o
o
switch(x,y1,c1,y2,c2,...,yn,cn[,o])
x
y1,y2,...,yN
c1,c2,...,cN
o
ck
yk
x
o
o
c1,c2,...,cN
o
x
y1,y2,...,yN
switch()
◥/course{title, if(credits>=5, 'hard',
                  credits>=3, 'medium',
                              'easy') :as level}
       ?department.code='astro'

◥/student{name, switch(gender, 'm', 1,
                              'f', -1) :as sex_code}
        ?program.code='gedu'

integer(x)
integer('60')
60
integer(17.25)
17
integer(string('60'))
60
decimal(x)
decimal('17.25')
17.25
decimal(223607e-5)
2.23607
decimal(string('17.25'))
17.25
float(x)
float('223607e-5')
223607e-5
float(60)
6e1
float(string('223607e-5'))
223607e-5
+ x
+60
- x
-7
x + y
13+7
20
x - y
13-7
6
x * y
13*7
91
x / y
13/7
1.85714285714286
round(x)
round(17.25)
17
round(x,n)
round(17.25,1)
17.3
trunc(x)
trunc(17.25)
17
trunc(x,n)
trunc(17.25,1)
17.2
integer(x)
x
decimal(x)
x
float(x)
x
integer()
decimal()
float()
◥/{integer(2.125), decimal('271828e-5'), float(string(60))}

+ x
x
- x
x
x + y
x
y
x - y
y
x
x * y
x
y
x / y
x
y
◥/{(2+4)*7, -(98-140), 21/5}

round(x)
x
round(x,n)
x
n
trunc(x)
x
trunc(x,n)
x
n
◥/{round(3272.78125), round(3272.78125,2), round(3272.78125,-2)}

◥/{trunc(3272.78125), trunc(3272.78125,2), trunc(3272.78125,-2)}

◥/school{code, avg(department.count(course)) :round 2}

◥/department^avg_credits {avg_credits, count(department)}
 :where(department.avg_credits := avg(course.credits) :trunc(1))

'value'
string(x)
string('Hello')
'Hello'
string(1.0)
'1.0'
string(date('2010-04-15'))
'2010-04-15'
length(s)
length('HTSQL')
5
s + t
'HT' + 'SQL'
'HTSQL'
s ~ t
'HTSQL' ~ 'sql'
true
s !~ t
'HTSQL' !~ 'sql'
false
head(s)
head('HTSQL')
'H'
head(s,n)
head('HTSQL',2)
'HT'
head('HTSQL',-3)
'HT'
tail(s)
tail('HTSQL')
'L'
tail(s,n)
tail('HTSQL',3)
'SQL'
tail('HTSQL',-2)
'SQL'
slice(s,i,j)
slice('HTSQL',1,4)
'TSQ'
slice('HTSQL',-4,-1)
'TSQ'
slice('HTSQL',null(),2)
'HT'
slice('HTSQL',2,null())
'SQL'
at(s,k)
at('HTSQL',2)
'S'
at(s,k,n)
at('HTSQL',1,3)
'TSQ'
at('HTSQL,-4,3)
'TSQ'
at('HTSQL,4,-3)
'TSQ'
upper(s)
upper('htsql')
'HTSQL'
lower(s)
lower('HTSQL')
'htsql'
trim(s)
trim('  HTSQL  ')
'HTSQL'
ltrim(s)
ltrim('  HTSQL  ')
'HTSQL  '
rtrim(s)
rtrim('  HTSQL  ')
'  HTSQL'
replace(s,t,r)
replace('HTSQL','SQL','RAF')
'HTRAF'
string(x)
x
◥/{string('HTSQL'), string(true()), string(2.125), string(datetime('2010-04-15 20:13'))}

◥/department{'Department of '+name+' offers '
            +string(count(course))+' courses' :as text}
           ?exists(course)

length(s)
s
0
◥/{length('HTSQL'), length(''), length(null())}

s + t
s
t
◥/{'HT'+'SQL', null()+'SQL'}

◥/course{department_code+'.'+string(no) :as code, title}

s ~ t
t
s
s !~ t
t
s
◥/{'HTSQL'~'sql', 'sql'!~'HTSQL'}

◥/school?code~'art'

head(s)
s
head(s,n)
n
s
tail(s)
s
tail(s,n)
n
s
slice(s,i,j)
i
j
s
at(s,k)
k
s
at(s,k,n)
n
s
k
0
head()
tail()
n
-n
s
n
1
slice()
i
j
(-i-1)
(-j-1)
s
i
j
at()
n
-n
s
k
n
1
◥/{'HTSQL' :head, 'HTSQL' :head(2), 'HTSQL' :head(-3)}

◥/{'HTSQL' :tail, 'HTSQL': tail(3), 'HTSQL': tail(-2)}

◥/{'HTSQL' :slice(1,-1), 'HTSQL' :slice(1,null()), 'HTSQL' :slice(null(),-1)}

◥/{'HTSQL' :at(2), 'HTSQL' :at(1,3), 'HTSQL': at(-1,-3)}

upper(s)
s
lower(s)
s
◥/{'htsql' :upper, 'HTSQL' :lower}

trim(s)
s
ltrim(s)
s
rtrim(s)
s
◥/{'  HTSQL  ' :trim :replace(' ','!'),
  '  HTSQL  ' :ltrim :replace(' ','!'),
  '  HTSQL  ' :rtrim :replace(' ','!')}

replace(s,t,r)
t
s
r
t
r
◥/{'HTTP' :replace('TP','SQL'),
  'HTTP' :replace(null(), 'SQL'),
  'HTTP' :replace('TP', null())}

date(x)
date('2010-04-15')
time(x)
time('20:13')
datetime(x)
datetime('2010-04-15T20:13')
date(yyyy,mm,dd)
date(2010,4,15)
date('2010-04-15')
datetime(yyyy,mm,dd [,HH,MM,SS])
datetime(2010,4,15,20,13)
datetime('2010-04-15T20:13')
datetime(d,t)
datetime( date('2010-04-15'), time('20:13') )
datetime('2010-04-15T20:13')
today()
today()
now()
now()
date(dt)
date( datetime('2010-04-15T20:13') )
date('2010-04-15')
time(dt)
time( datetime('2010-04-15T20:13') )
time('20:13')
year(d)
year(date('2010-04-15'))
2010
month(d)
month(date('2010-04-15'))
4
day(d)
day(date('2010-04-15'))
15
hour(t)
hour(time('20:13'))
20
minute(t)
minute(time('20:13'))
13
second(t)
second(time('20:13'))
0.0
d + n
date('1991-08-20')+6813
date('2010-04-15')
d - n
date('2028-12-09')-6813
date('2010-04-15')
d1 - d2
date('2028-12-09') - date('1991-08-20')
13626
date(x)
x
time(x)
x
datetime(x)
x
◥/{date('2010-04-15'), time('20:13'), datetime('2010-04-15 20:13')}

◥/student?dob<date('1982-06-01')

date(yyyy,mm,dd)
datetime(yyyy,mm,dd[,HH,MM,SS])
datetime(d,t)
◥/{date(2010,4,15), datetime(2010,4,15,20,13),
  datetime(date('2010-04-15'),time('20:13'))}

◥/{date(2010,4,15), date(2010,3,46), date(2011,-8,15)}

date(dt)
time(dt)
year(d)
month(d)
day(d)
hour(t)
minute(t)
second(t)
second()
◥/{date($dt), time($dt),
  year($d), month($d), day($d),
  hour($t), minute($t), second($t)}
 :where ($d := date('2010-04-15'),
         $t := time('20:13'),
         $dt := datetime($d,$t))

d + n
n
d - n
n
d1 - d2
◥/{date('1991-08-20')+6813, datetime('1991-08-20 02:01')+6813.75833333333}

◥/{date('2028-12-09')-6813, datetime('2028-12-10 14:25')-6813.75833333333}

◥/date('2028-12-09')-date('1991-08-20')

◥/student{name, (start_date-dob)/365 :round(1) :as age}

exists(ps)
exists(course.credits>5)
every(ps)
every(course.credits>5)
count(ps)
count(course.credits>5)
min(xs)
min(course.credits)
max(xs)
max(course.credits)
sum(xs)
sum(course.credits)
avg(xs)
avg(course.credits)
exists(xs)
xs
every(xs)
xs
count(xs)
xs
0
xs
boolean()
◥/course?department.code='astro'

◥/{exists(astro_course.credits>=5),
  every(astro_course.credits>=5),
  count(astro_course.credits>=5)}
 :where astro_course := course?department.code='astro'

◥/course?department.code='pia'

◥/{exists(pia_course.credits>=5),
  every(pia_course.credits>=5),
  count(pia_course.credits>=5)}
 :where pia_course := course?department.code='pia'

min(xs)
xs
max(xs)
xs
◥/{min(astro_course.credits), max(astro_course.credits)}
 :where astro_course := course?department.code='astro'

◥/{min(pia_course.credits), max(pia_course.credits)}
 :where pia_course := course?department.code='pia'

sum(xs)
xs
0
xs
avg(xs)
xs
sum()
avg()
◥/{sum(astro_course.credits), avg(astro_course.credits)}
 :where astro_course := course?department.code='astro'

◥/{sum(pia_course.credits), avg(pia_course.credits)}
 :where pia_course := course?department.code='pia'

flow ? p
school?code='edu'
filter(p)
school.filter(code='edu')
flow ^ x
school^campus
distinct(flow{x})
distinct(school{campus})
flow {x,...}
school{code,name}
select(x,...)
school.select(code,name)
sort(x,...)
course.sort(credits-)
limit(n)
course.limit(10)
limit(n,k)
course.limit(10,20)
x -> xs
school.(campus -> school)
fork([x])
course.fork(credits)
flow ? p
flow
p
filter(p)
p
p
boolean()
◥/school?campus='south'

◥/school.filter(campus='south')

flow ^ x
x
flow
flow ^ {x,...}
x,...
distinct(flow{x,...})
flow{x,...}
flow ^ x
flow
flow
x
^
flow
x
x
x
x
◥/school{code, name, campus, count(department)}

◥/school^campus {campus, count(school)}

◥/school^campus {*, count(^)}

◥/distinct(school{campus}) {campus, count(school)}

◥/school^{num_dept := count(department)}
 {num_dept, count(school)}

◥/school^{campus :if_null '', count(department)}
 {*, count(school)}

{x,...}
flow{x,...}
select(x,...)
◥/{count(school), count(program), count(department)}

◥/select(count(school), count(program), count(department))

◥/school{code, count(program)}

◥/school.select(code, count(program))

◥/school{code, count(program)-}

◥/school{code, num_prog := count(program)}?num_prog<4

◥/department{code, $avg_credits := avg(course.credits),
            count(course?credits>$avg_credits)}

define(x:=...)
define(num_prog:=count(program))
where(expr,x:=...)
count(course?credits>$c) :where $c:=avg(course.credits)
@ x
root()
this()
define(x:=...)
where(expr,x:=...)
@ x
root()
this()
as(x,title)
count(program) :as '# of programs'
x +
credits+
x -
credits-
as(x,title)
◥/school{code :as ID, count(program) :as '# of Programs'}

x +
x -
sort()
◥/school.sort(campus+)

/:html
/:txt
/:csv
/:tsv
/:raw
/:json
/:xml
/:sql
/:txt
/:html
/:html
◥/department{school,*}/:html

◥/department{school,*}/:txt

 | department                                                                                    |
 +-----------------------------------------------+--------+------------------------+-------------+
 | school                                        |        |                        |             |
 +------+-------------------------------+--------+        |                        |             |
 | code | name                          | campus | code   | name                   | school_code |
-+------+-------------------------------+--------+--------+------------------------+-------------+-
 | bus  | School of Business            | south  | acc    | Accounting             | bus         |
 | la   | School of Arts and Humanities | old    | arthis | Art History            | la          |
 | ns   | School of Natural Sciences    | old    | astro  | Astronomy              | ns          |
 | eng  | School of Engineering         | north  | be     | Bioengineering         | eng         |
 :      :                               :        | bursar | Bursar's Office        |             |
 :      :                               :        | career | Career Development     |             |
…

/:xml
/:json
◥/department{school,*}.limit(3)/:json

{
  "department": [
    {
      "school": {
        "code": "bus",
        "name": "School of Business",
        "campus": "south"
      },
      "code": "acc",
      "name": "Accounting",
      "school_code": "bus"
    },
    {
      "school": {
        "code": "la",
        "name": "School of Arts and Humanities",
        "campus": "old"
      },
      "code": "arthis",
      "name": "Art History",
      "school_code": "la"
    },
    {
      "school": {
        "code": "ns",
        "name": "School of Natural Sciences",
        "campus": "old"
      },
      "code": "astro",
      "name": "Astronomy",
      "school_code": "ns"
    }
  ]
}

◥/department{school,*}.limit(3)/:xml

<?xml version="1.0" encoding="UTF-8" ?>
<htsql:result xmlns:htsql="http://htsql.org/2010/xml">
  <department>
    <school>
      <code>bus</code>
      <name>School of Business</name>
      <campus>south</campus>
    </school>
    <code>acc</code>
    <name>Accounting</name>
    <school_code>bus</school_code>
  </department>
  <department>
    <school>
      <code>la</code>
      <name>School of Arts and Humanities</name>
      <campus>old</campus>
    </school>
    <code>arthis</code>
    <name>Art History</name>
    <school_code>la</school_code>
  </department>
  <department>
    <school>
      <code>ns</code>
      <name>School of Natural Sciences</name>
      <campus>old</campus>
    </school>
    <code>astro</code>
    <name>Astronomy</name>
    <school_code>ns</school_code>
  </department>
</htsql:result>

/:csv
/:tsv
◥/department{school,*}/:csv

code,name,campus,code,name,school_code
bus,School of Business,south,acc,Accounting,bus
la,School of Arts and Humanities,old,arthis,Art History,la
…

◥/department{school,*}/:tsv

code	name	campus	code	name	school_code
bus	School of Business	south	acc	Accounting	bus
la	School of Arts and Humanities	old	arthis	Art History	la
…

/:raw
meta
data
◥/department{school,*}.limit(3)/:raw

{
  "meta": {
    "domain": {
      "type": "list",
      "item": {
        "domain": {
          "type": "record",
          "fields": [
            {
              "domain": {
                "type": "record",
                "fields": [
                  {
                    "domain": {
                      "type": "text"
                    },
                    "header": "code",
                    "path": "department.school.code",
                    "syntax": "code",
                    "tag": "code"
                  },
                  {
                    "domain": {
                      "type": "text"
                    },
                    "header": "name",
                    "path": "department.school.name",
                    "syntax": "name",
                    "tag": "name"
                  },
                  {
                    "domain": {
                      "type": "text"
                    },
                    "header": "campus",
                    "path": "department.school.campus",
                    "syntax": "campus",
                    "tag": "campus"
                  }
                ]
              },
              "header": "school",
              "path": "department.school",
              "syntax": "school",
              "tag": "school"
            },
            {
              "domain": {
                "type": "text"
              },
              "header": "code",
              "path": "department.code",
              "syntax": "code",
              "tag": "code"
            },
            {
              "domain": {
                "type": "text"
              },
              "header": "name",
              "path": "department.name",
              "syntax": "name",
              "tag": "name"
            },
            {
              "domain": {
                "type": "text"
              },
              "header": "school_code",
              "path": "department.school_code",
              "syntax": "school_code",
              "tag": "school_code"
            }
          ]
        }
      }
    },
    "header": "department",
    "path": "department",
    "syntax": "\/department{school,*}.limit(3)",
    "tag": "department"
  },
  "data": [
    [
      [
        "bus",
        "School of Business",
        "south"
      ],
      "acc",
      "Accounting",
      "bus"
    ],
    [
      [
        "la",
        "School of Arts and Humanities",
        "old"
      ],
      "arthis",
      "Art History",
      "la"
    ],
    [
      [
        "ns",
        "School of Natural Sciences",
        "old"
      ],
      "astro",
      "Astronomy",
      "ns"
    ]
  ]
}

/:sql
◥/department{school,*}/:sql

SELECT "school"."school",
       "school"."code",
       "school"."name",
       "school"."campus",
       "department"."code",
       "department"."name",
       "department"."school_code"
FROM "ad"."department"
     LEFT OUTER JOIN (SELECT TRUE AS "school",
                             "school"."code",
                             "school"."name",
                             "school"."campus"
                      FROM "ad"."school") AS "school"
                     ON ("department"."school_code" = "school"."code")
ORDER BY 5 ASC
LIMIT 10000

Function	Description	Example Input	Output
`boolean(x)`	cast x to Boolean	`boolean('true')`	`true`
		`boolean('false')`	`false`
		`boolean(string('HTSQL'))`	`true`
		`boolean(string(''))`	`false`
`true()`	logical TRUE value	`true()`
`false()`	logical FALSE value	`false()`
`p & q`	logical AND operator	`true()&true()`	`true`
		`true()&false()`	`false`
		`false()&false()`	`false`
`p \| q`	logical OR operator	`true()\|true()`	`true`
		`true()\|false()`	`true`
		`false()\|false()`	`false`
`!p`	logical NOT operator	`!true()`	`false`
`!p`	logical NOT operator	`!false()`	`true`
`null(x)`	NULL value	`null()`
`is_null(x)`	x is null	`is_null(null())`	`true`
`if_null(x,y)`	x if x is not null; y otherwise	`if_null(1,0)`	`1`
`if_null(x,y)`	x if x is not null; y otherwise	`if_null(null(),0)`	`0`
`null_if(x,y)`	x if x is not equal to y; null otherwise	`null_if(1,0)`	`1`
`null_if(x,y)`	x if x is not equal to y; null otherwise	`null_if(0,0)`	`null`
`x = y`	x is equal to y	`'HTSQL'='QUEL'`	`false`
`x != y`	x is not equal to y	`'HTSQL'!='QUEL'`	`true`
`x == y`	x is equal to y; treats nulls as regular values	`'HTSQL'=='QUEL'`	`false`
`x == y`	x is equal to y; treats nulls as regular values	`2==null()`	`false`
`x !== y`	x is not equal to y; treats nulls as regular values	`'HTSQL'!=='QUEL'`	`true`
`x !== y`	x is not equal to y; treats nulls as regular values	`2!==null()`	`true`
`x = {a,b,c,...}`	x is among a, b, c, ...	`5={2,3,5,7}'`	`true`
`x != {a,b,c,...}`	x is not among a, b, c, ...	`5!={2,3,5,7}'`	`false`
`x < y`	x is less than y	`1<10`	`true`
`x < y`	x is less than y	`'omega'<'alpha'`	`false`
`x <= y`	x is less than or equal to y	`1<=10`	`true`
`x <= y`	x is less than or equal to y	`'omega'<='alpha'`	`false`
`x > y`	x is greater than y	`1>10`	`false`
`x > y`	x is greater than y	`'omega'>'alpha'`	`true`
`x >= y`	x is greater than or equal to y	`1>=10`	`false`
`x >= y`	x is greater than or equal to y	`'omega'>='alpha'`	`true`
`if(p1,c1,...,pn,cn)`	first ck such that pk is TRUE; o or null otherwise	`if(true(),'up','down')`	`'up'`
`if(p1,c1,...,pn,cn,o)`	first ck such that pk is TRUE; o or null otherwise	`if(false(),'up','down')`	`'down'`
`switch(x,y1,c1,...,yn,cn)`	first ck such that x is equal to yk; o or null otherwise	`switch(1,1,'up',0,'down')`	`'up'`
`switch(x,y1,c1,...,yn,cn,o)`	first ck such that x is equal to yk; o or null otherwise	`switch(0,1,'up',0,'down')`	`'down'`

title	credits
Classroom Visit
Spring Basket Weaving Workshop
Peer Portfolio Review	0

title	credits
Classroom Visit
Spring Basket Weaving Workshop
Peer Portfolio Review	0

Function	Description	Example Input	Output
`integer(x)`	cast x to integer	`integer('60')`	`60`
		`integer(17.25)`	`17`
		`integer(string('60'))`	`60`
`decimal(x)`	cast x to decimal	`decimal('17.25')`	`17.25`
		`decimal(223607e-5)`	`2.23607`
		`decimal(string('17.25'))`	`17.25`
`float(x)`	cast x to float	`float('223607e-5')`	`223607e-5`
		`float(60)`	`6e1`
		`float(string('223607e-5'))`	`223607e-5`
`+ x`	x	`+60`
`- x`	negate x	`-7`
`x + y`	add x to y	`13+7`	`20`
`x - y`	subtract y from x	`13-7`	`6`
`x * y`	multiply x by y	`13*7`	`91`
`x / y`	divide x by y	`13/7`	`1.85714285714286`
`round(x)`	round x to the nearest integer	`round(17.25)`	`17`
`round(x,n)`	round x to n decimal places	`round(17.25,1)`	`17.3`
`trunc(x)`	round x to an integer, towards zero	`trunc(17.25)`	`17`
`trunc(x,n)`	round x to n decimal places, towards zero	`trunc(17.25,1)`	`17.2`

avg_credits	count(department)
2.9	1
3.0	4
3.1	1

code	name	campus
art	School of Art & Design	old
bus	School of Business	south
edu	College of Education	old

code	name	campus
art	School of Art & Design	old
edu	College of Education	old
la	School of Arts and Humanities	old

code	name	campus
eng	School of Engineering	north
la	School of Arts and Humanities	old
mus	School of Music & Dance	south

title	level
General Astronomy I	hard
General Astronomy I Lab	easy
Stars and Planets	medium

Function	Description	Example Input	Output
`string(x)`	cast x to string	`string('Hello')`	`'Hello'`
		`string(1.0)`	`'1.0'`
		`string(date('2010-04-15'))`	`'2010-04-15'`
`length(s)`	number of characters in s	`length('HTSQL')`	`5`
`s + t`	concatenate s and t	`'HT' + 'SQL'`	`'HTSQL'`
`s ~ t`	s contains t; case-insensitive	`'HTSQL' ~ 'sql'`	`true`
`s !~ t`	s does not contain t; case-insensitive	`'HTSQL' !~ 'sql'`	`false`
`head(s)`	first character of s	`head('HTSQL')`	`'H'`
`head(s,n)`	first n characters of s	`head('HTSQL',2)`	`'HT'`
`head(s,n)`	first n characters of s	`head('HTSQL',-3)`	`'HT'`
`tail(s)`	last character of s	`tail('HTSQL')`	`'L'`
`tail(s,n)`	last n characters of s	`tail('HTSQL',3)`	`'SQL'`
`tail(s,n)`	last n characters of s	`tail('HTSQL',-2)`	`'SQL'`
`slice(s,i,j)`	i-th to j-th characters of s; null or missing index means the beginning or the end of the string	`slice('HTSQL',1,4)`	`'TSQ'`
		`slice('HTSQL',-4,-1)`	`'TSQ'`
		`slice('HTSQL',null(),2)`	`'HT'`
		`slice('HTSQL',2,null())`	`'SQL'`
`at(s,k)`	k-th character of s	`at('HTSQL',2)`	`'S'`
`at(s,k,n)`	n characters of s starting with k-th character	`at('HTSQL',1,3)`	`'TSQ'`
		`at('HTSQL,-4,3)`	`'TSQ'`
		`at('HTSQL,4,-3)`	`'TSQ'`
`upper(s)`	upper case of s	`upper('htsql')`	`'HTSQL'`
`lower(s)`	lower case of s	`lower('HTSQL')`	`'htsql'`
`trim(s)`	strip leading and trailing spaces from s	`trim(' HTSQL ')`	`'HTSQL'`
`ltrim(s)`	strip leading spaces from s	`ltrim(' HTSQL ')`	`'HTSQL '`
`rtrim(s)`	strips trailing spaces from s	`rtrim(' HTSQL ')`	`' HTSQL'`
`replace(s,t,r)`	replace all occurences of t in s with r	`replace('HTSQL','SQL','RAF')`	`'HTRAF'`

code	title
acc.100	Practical Bookkeeping
acc.200	Introduction to Accounting
acc.234	Accounting Information Systems

Function	Description	Example Input	Output
`date(x)`	cast x to date	`date('2010-04-15')`
`time(x)`	cast x to time	`time('20:13')`
`datetime(x)`	cast x to datetime	`datetime('2010-04-15T20:13')`
`date(yyyy,mm,dd)`	date yyyy-mm-dd	`date(2010,4,15)`	`date('2010-04-15')`
`datetime(yyyy,mm,dd [,HH,MM,SS])`	datetime yyyy-mm-dd HH:MM:SS	`datetime(2010,4,15,20,13)`	`datetime('2010-04-15T20:13')`
`datetime(d,t)`	datetime from date and time	`datetime( date('2010-04-15'), time('20:13') )`	`datetime('2010-04-15T20:13')`
`today()`	current date	`today()`
`now()`	current date and time	`now()`
`date(dt)`	date of dt	`date( datetime('2010-04-15T20:13') )`	`date('2010-04-15')`
`time(dt)`	time of dt	`time( datetime('2010-04-15T20:13') )`	`time('20:13')`
`year(d)`	year of d	`year(date('2010-04-15'))`	`2010`
`month(d)`	month of d	`month(date('2010-04-15'))`	`4`
`day(d)`	day of d	`day(date('2010-04-15'))`	`15`
`hour(t)`	hours of t	`hour(time('20:13'))`	`20`
`minute(t)`	minutes of t	`minute(time('20:13'))`	`13`
`second(t)`	seconds of t	`second(time('20:13'))`	`0.0`
`d + n`	increment d by n days	`date('1991-08-20')+6813`	`date('2010-04-15')`
`d - n`	decrement d by n days	`date('2028-12-09')-6813`	`date('2010-04-15')`
`d1 - d2`	number of days between d1 and d2	`date('2028-12-09') - date('1991-08-20')`	`13626`

id	name	gender	dob	school_code	program_code	start_date	is_active
1036	Jonathan Bouchard	m	1982-02-12	art	ustudio	2007-08-15	false
1041	Lowell Cooper	m	1982-01-05	art	uhist	2007-08-15	false
1113	John Miller	m	1982-05-14	art	uhist	2007-08-15	false

Function	Description	Example Input
`exists(ps)`	TRUE if ps contains at least one TRUE value; FALSE otherwise	`exists(course.credits>5)`
`every(ps)`	TRUE if ps contains only TRUE values; FALSE otherwise	`every(course.credits>5)`
`count(ps)`	number of TRUE values in ps	`count(course.credits>5)`
`min(xs)`	smallest element in xs	`min(course.credits)`
`max(xs)`	largest element in xs	`max(course.credits)`
`sum(xs)`	sum of elements in xs	`sum(course.credits)`
`avg(xs)`	average value of elements in xs	`avg(course.credits)`

department_code	no	title	credits	description
astro	105	General Astronomy I	5	Overview of the current astronomy, its goals and problems, basic instruments, space objects classification.
astro	106	General Astronomy I Lab	2	Laboratory studies that complement the lecture course.
astro	108	Stars and Planets	3	Basics of planet and star formation, their lifecycle from birth to death.

name	sex_code
Sheri Sanchez	-1
Anna Carroll	-1
Alphonse Gilmore	1

text
Department of Accounting offers 12 courses
Department of Art History offers 20 courses
Department of Astronomy offers 22 courses

Function	Description	Example Input
`flow ? p`	records from flow satisfying condition p	`school?code='edu'`
`filter(p)`	records from flow satisfying condition p	`school.filter(code='edu')`
`flow ^ x`	unique values of x as it runs over flow	`school^campus`
`distinct(flow{x})`	unique values of x as it runs over flow	`distinct(school{campus})`
`flow {x,...}`	select output columns x, ... for flow	`school{code,name}`
`select(x,...)`	select output columns x, ... for flow	`school.select(code,name)`
`sort(x,...)`	reorder records in flow by x, ...	`course.sort(credits-)`
`limit(n)`	first n records from flow	`course.limit(10)`
`limit(n,k)`	n records from flow starting from k-th	`course.limit(10,20)`
`x -> xs`	traverse an ad-hoc link	`school.(campus -> school)`
`fork([x])`	traverse a self-referential link	`course.fork(credits)`

Function	Description	Example Input
`define(x:=...)`	add names to the current scope	`define(num_prog:=count(program))`
`where(expr,x:=...)`	evaluate an expression with extra names in the current scope	`count(course?credits>$c) :where $c:=avg(course.credits)`
`@ x`	evaluate an expression in the initial scope
`root()`	root scope
`this()`	current scope

Function	Description	Example Input
`as(x,title)`	set the column title	`count(program) :as '# of programs'`
`x +`	indicate ascending order	`credits+`
`x -`	indicate descending order	`credits-`

code	name	campus
ph	Public Honorariums
sc	School of Continuing Studies
eng	School of Engineering	north

Function	Description
`/:html`	HTML presentation output
`/:txt`	plain text presentation output
`/:csv`	CSV (comma-separated values) output
`/:tsv`	TSV (tab-separated values) output
`/:raw`	JSON-serialized generic output
`/:json`	JSON-serialized object output
`/:xml`	XML-serialized object output
`/:sql`	prints corresponding SQL queries

Functions and Operators¶

Boolean Cast¶

Logical Values¶

Logical Operators¶

NULL Checking¶

Equality Operators¶

Comparison Operators¶

Branching Functions¶

Numeric Cast¶

Arithmetic Expressions¶

Rounding Functions¶

String Cast¶

String Length¶

Concatenation¶

Substring Search¶

Substring Extraction¶

Case Conversion¶

String Trimming¶

Search and Replace¶

Date/Time Cast¶

Date/Time Construction¶

Component Extraction¶

Date/Time Arithmetics¶

Boolean Aggregates¶

Extrema¶

Sum and Average¶

Sieving¶

Projection¶

Selection¶

Calculated Attributes¶

Scopes¶

Title¶

Direction Decorators¶

Presentation Output¶

Object Output¶

Tabular Output¶

Generic Output¶

Query Debug¶