1. SQL Dialogs in Digital Assistant
  2. OMRQL Reference

3 OMRQL Reference

Here are the keywords that you can use when you define OMRQL queries for the utterances that you add to the query entities dataset. Note that you use the canonical names and not primary names and synonyms

Component OMRQL Keywords OMRQL Example Constraints
Basic Components
  • SELECT
  • *
  • FROM
 SELECT * FROM Emp The OMRQL can't name attributes that aren't referenced in the utterance.
Filtering WHERE

Currently, you can't add custom training data that filters by date or datetime.

 SELECT * FROM Emp WHERE comm > 0 None.
Linking Entities (see the information about link attributes below) . (period) SELECT * FROM Emp WHERE Dept.loc = 'NYC' None.
Ordering
  • ORDER BY
  • LIMIT
  • ASC
  • DESC
 SELECT name FROM Emp ORDER BY hiredate DESC LIMIT 10 The OMRQL can order data using ORDER BY <ATTR> [LIMIT N] only if the utterance includes the word order or its natural language synonyms such as sorted, ordered, highest, and smallest.
Aggregate Functions
  • COUNT
  • DISTINCT
  • AVG
  • SUM
  • MIN
  • MAX
 SELECT AVG(sal) from Emp The OMRQL can contain DISTINCT only if the utterance contains that word or a natural language synonym such as different or unique.
Grouping
  • GROUP BY
  • HAVING
 SELECT COUNT(*) FROM Emp GROUP BY Dept.loc HAVING Dept.loc = 'NYC' None.
Comparison Operators
  • =
  • !=
  • <>
  • >
  • >=
  • <
  • <=
  • LIKE
  • NOT LIKE
  • BETWEEN
  • IN
  • NOT IN
 SELECT * from Dept WHERE name IN ('Sales', 'HR') For the >, >=, <, and <= operators, the utterance must contain an equivalent natural language synonym such as greater than, at least, less than, and at most.

If the utterance doesn't contain an operator synonym, then the OMRQL must contain =.

The OMRQL can contain LIKE only if the utterance contains that word or a natural language synonym such as includes, contains, or substring.

The OMRQL can contain BETWEEN only if the utterance contains that word or a natural language synonym such as in the range of.
Logical Operators
  • AND
  • OR
  • NOT
 SELECT name FROM Emp WHERE sal > 100000 AND role = 'VP' None.

All the values in the OMRQL statement must exactly match the database value and format. Take, for example, the utterance "who is the employee whose name is Jones", If the database values for the name attribute are all capital letters, then the name value must also be all capital letters. That is "SELECT * FROM Emp WHERE name = 'JONES'".

When the utterance that you are mapping uses a synonym for the actual database value, then that synonym must be defined for the value in the value list, and the OMRQL must use the actual database value. For example, if the utterance is "show the department whose location is the big apple", then "big apple" must be defined in the dept_loc value list as a synonym for the value "NEW YORK", and the OMRQL must be "SELECT * FROM Dept WHERE loc = 'NEW YORK'".

Here are some examples of how to write OMRQL for your utterances:

Utterance SQL OMRQL Comments
Show me all employees who work as a clerk SELECT * FROM Emp WHERE job = 'CLERK' SELECT * FROM Emp WHERE job = 'CLERK' OMRQL is identical to SQL.
Show me all employees who work in sales department SELECT * FROM Emp AS T1 JOIN Dept AS T2 ON T1.deptno = T2.deptno WHERE T2.dname = 'SALES' SELECT * FROM Emp WHERE dept.dname = 'SALES' Instead of a JOIN, use "link_attribute.attribute_name" to refer to an attribute from another entity.
Adams is a member of what department? SELECT * FROM Dept AS T1 JOIN Emp AS T2 ON T1.deptno = T2.deptno WHERE T2.ename = 'Adams' SELECT * FROM Dept WHERE emp.ename = 'ADAMS' Instead of a JOIN, use "link_attribute.attribute_name" to refer to an attribute from another entity.
What is the department location and job role of employee Adams SELECT T1.LOC, T2.JOB FROM DEPT T1 JOIN EMP T2 ON T1.DEPTNO = T2.DEPTNO WHERE T2.ENAME = 'ADAMS' SELECT loc, emp.job FROM Dept WHERE emp.ename = 'ADAMS' Notice how the OMRQL is simpler to write than the SQL.
How many employees are there for every job role? SELECT COUNT(*), job FROM Emp GROUP BY job SELECT COUNT(*), job FROM Emp GROUP BY job OMRQL is identical to SQL.
Which employee has the highest salary? SELECT * FROM Emp ORDER BY salary DESC LIMIT 1 SELECT * FROM Emp ORDER BY salary DESC LIMIT 1 OMRQL is identical to SQL.
Show employee name and department name ordered by the salary in ascending order SELECT T1.ename, T2.dname FROM Emp AS T1 JOIN Dept AS T2 ON T1.deptno = T2.deptno ORDER BY T1.sal ASC SELECT ename, dept.dname FROM Emp ORDER BY salary ASC Notice how the OMRQL is simpler to write than the SQL.

With the exception of linking entities, the OMRQL components are similar to SQL. Instead of an SQL JOIN, you use a pair of link attributes to link one entity to another. Attribute links have primary names and synonyms that define the relationship between the entities. For example an employee/department attribute link with a 1-1 relationship can have a primary name "department" and synonyms "works in", "belongs to", and "team". A department/employees attribute link with a 1-many relationship can have a primary name "employees" and synonyms "members", and "workers".

Besides the typical primary key/foreign key link attributes you also can have these types of link attributes:

  • Multiple link attributes from one entity to another that define multiple semantic relationships.
  • A link attribute from an entity to itself that implies a self join.
  • A link attribute for an intersection table due to a many-to-many join