# XQuery/XQuery from SQL
## The Scott/Tiger example

A classic database widely used in teaching Relational databases concerns Employees, Departments and SalaryGrades. In Oracle training material it is known by the demo username and password, Scott/Tiger.
These three tables converted to XML (via the XML add-in to Excel XQuery/Excel and XML ) are:
Employees XML Table MySQL
Departments XML Table MySQL
Salary Grades XML Table MySQL
A port of the Oracle SQL file to MySQL can be found here.

## Execution environments

The eXist demo server is used for the XQuery examples. These are returned either as plain XML or converted to table format.
The equivalent SQL queries are executed on an w:MySQL server,also based at the University of the West of England in Bristol

## Basic Queries
### Counting Records
##### Task: How many Employees?

SQL: select count(*) from Emp; MySQL
XQuery: count(//Emp) XML

##### Task: How many Departments?

SQL: select count(*) from dept MySQL
XQuery: count(//Dept) XML

### Selecting records
#### Task: Show all Employees with a salary greater than 1000

SQL: select * from emp where sal > 1000; MySQL
XQuery: //Emp[Sal>1000] XML Table

#### Task: Show all Employees with a salary greater than 1000 and less than 2000

SQL: select * from emp where sal between 1000 and 2000; MySQL
XQuery: //Emp[Sal>1000][Sal<2000] XML Table
Here, successive filter conditions replace the anded conditions implied by 'between'.
Although there is no 'between' function in XQuery, it is a simple matter to write one:
which simplifies the query to //Emp[local:between(Sal,1000,2000)] XML Table
and has the advantage that the conversion of Sal to a number is now implicit in the function signature.

#### Task: Show all employees with no Commission

SQL: select * from emp where comm is null; MySQL
XQuery: //Emp[empty(Comm/text)] XML Table
Note that empty(Comm) is not enough, since this is true only if the element itself is absent, which in this sample XML it is not.
XQuery: //Emp[empty(Comm)] XML

#### Task: Select the first 5 employees

SQL: select * from emp limit 5; MySQL
XQuery: //Emp[position <=5]
XML Table

### Selecting Columns
#### List Employee names and salaries

SQL: Select ename,sal from emp MySQL
Surprisingly, selecting only a subset of children in a node (pruning) is not supported in XPath.
//Emp/(Ename,Sal) XML retrieves the required elements, but the parent Emp nodes have been lost.
//Emp/(Ename|Sal) XML is better since it keeps the elements in sequence, but it does not return Emp nodes with only the Ename and Sal children as required.
//Emp/*[name(.) = ("Ename","Sal")] XML uses reflection on the element names.
XQuery:
XML Table
Here an XQuery FLWOR expression is used to create a new EMP element from the original elements.

### Computing values
#### Computing the Annual Salary

Task: Compute the Annual Salaries of all employees. The Annual Salary is computed from 12 times the Monthly salary plus Commission. Since commission may be null, it must be replaced by a suitable numeric value:
SQL: select 12 * sal + ifnull(comm,0) from emp; MySQL
XQuery: //Emp/(12*number(Sal)+(if(exists(Comm/text)) then number(Comm) else 0)) XML
The SQL function COALESCE is the same as IFNULL but will accept multiple arguments:
SQL: select 12 * sal + coalesce(comm,0) from emp; MySQL
XQuery: //Emp/(12*number(Sal)+ number((Comm/text,0)[1])) XML
The lack of a schema in this simple example to carry information on the type of the items, leads to the need for explicit conversion of strings to numbers.
Note the XQuery idiom:
computes the first non-null item in the sequence, the counterpart of COALESCE.

#### Selecting and Creating Columns

Task: List the employee names with their Annual Salary.
SQL: select ename, 12 * sal + ifnull(comm,0) as "Annual Salary" from emp; MySQL
XQuery:
XML Table
Again we have the problem of tree-pruning, but now with added grafting, which again requires the explicit construction of an XML node.

## SQL Operators
### IN

Task: Show all employees whose Job is either ANALYST or MANAGER
SQL: select * from emp where job in ("ANALYST","MANAGER") MySQL
XQuery: //Emp[Job = ("ANALYST","MANAGER")]
XML Table

### NOT IN

Task :Select all employees whose Job is not 'ANALYST' or 'MANAGER'
SQL: select * from emp where job not in ("ANALYST","MANAGER") MySQL
This doesn't work:
XQuery: //Emp[Job !=("ANALYST","MANAGER")] XML Table
The generalised equals here is always true since everyone is either not an ANALYST or not a MANAGER. This works:
XQuery: //Emp[not(Job =("ANALYST","MANAGER"))] XML Table

### Distinct values

Task: Show the different Jobs which Employees have
MySQL: select distinct job from emp; MySQL
XQuery: distinct-values(//Emp/Job) XML

### Pattern Matching

Task: List all Employees with names starting with "S"
MySQL: select * from emp where ename like "S%"; MySQL
XQuery: //Emp[starts-with(Ename,"S")] XML Table
See starts-with
Task: List all Employees whose name contains "AR"
MySQL: select * from emp where ename like "%AR%"; MySQL
XQuery: //Emp[contains(Ename,"AR")] XML Table
See contains
Task: List all Employees whose name contains "ar" ignoring the case
MySQL: select * from emp where ename like "%ar%"; MySQL
LIKE in SQL is case insensitive, but fn:contains is not, so the case needs to be converted:
XQuery: //Emp[contains(upper-case(Ename),upper-case("ar"))] XML Table
See upper-case
More complex patterns need regular expressions.
MySQL: select * from emp where ename regexp "M.*R"; MySQL
XQuery: //Emp[matches(Ename,"M.*R")] XML Table
See matches
Similarly, SQL's REGEXP is case-insensitive, whereas additional flags control matching in the XQuery matches
MySQL: select * from emp where ename regexp "m.*r"; MySQL
XQuery: //Emp[matches(Ename,"m.*r",'i')] XML Table
('i' makes the regex match case insensitive.)

## Table Joins
#### Simple Inner joins

Task: Find the name of the department that employee 'SMITH' works in:
SQL :
MySQL
XPath : //Dept[DeptNo = //Emp[Ename='SMITH']/DeptNo]/Dname
XML
Perhaps a FLWOR expression in XQuery would be more readable:
XML
Task: To find the names of all employees in Accounting
SQL:
MySQL
XPath: //Emp[DeptNo = //Dept[Dname='Accounting']/DeptNo]/Ename
XML
XQuery:
XML
Note that in this release of eXist, the order of the operands in the equality is significant - to be fixed in a later release.
XQuery: //Emp[Dname='Accounting'/DeptNo = //Emp/DeptNo]/Ename
XML

#### More complex Inner Join

Task: List the name of each Employee, together with the name and location of their department.
SQL:
MySQL
Where elements must be selected from several nodes, XPath is insufficient and XQuery is needed:
XQuery:
This join could be written as:
XML Table
But it would be more commonly written in the form of a sub-selection:
XML Table

#### Inner Join with Selection

Task: List the names and department of all Analysts
SQL:
MySQL
XQuery:
XML Table

#### 1 to Many query

Task: List the departments and the number of employees in each department
SQL:
MySQL
XQuery:
XML Table

#### Theta (Inequality) Join

Task: List the names and salary grade of staff in ascending grade order
Grades are defined by a minimum and maximum salary.
SQL:
MySQL
XQuery:
XML Table

#### Recursive Relations

The relationship between an employee and their manager is a recursive relationship.
Task: List the name of each employee together with the name of their manager.
SQL:
MySQL
XQuery:
XML Table
The XQuery result is not quite the same as the SQL result. King, who has no manager, is missing from the SQL inner join. To produce the same result in XQuery, we would filter for employees with Managers:
XML Table
Alternatively, an outer join returns all employees, including King:
SQL:
MySQL

### Conversion to an organisational tree

The manager relationship defines a tree structure, with King at the root, her direct reports as her children and so on. A recursive function in XQuery solves this task.
XQuery:
XML

### Conversion to a Department/Employee Hierarchy

For export, a single XML file could be created with Employees nested within Departments. This is possible without introducing redundancy or loss of data because the Dept/Emp relationship is exactly one to many.
XQuery:
XML
With this simple approach, the foreign key DeptNo in Emp has been included but it is now redundant. The except operator is useful here:
XML
Note that this assumes there are no attributes to be copied. If there are, these would be copied with
$emp/@*

## Working with the hierarchical data

This hierarchical data can be queried directly in XQuery.

### Path to Employee

Almost all the queries remain the same (except for the change of element name to Employee). This is because the path used to select Emps in the Emp.xml document is //Emp and is now //Employee in the merged document. If a full path had been used (/EmpList/Emp), this would need to be replaced by /Company/Department/Employee

### Simple Navigation

Task: To find the department name of employee 'Smith'
XQuery: //Employee[Ename='SMITH']/../Dname XML
Task: To find the names of employees in the Accounting department
XQuery: //Department[Dname='Accounting']/Employee/Ename XML

### Department /Employee Join

The main changes are in queries which require a join between Employee and Departments because they are already nested and thus become navigation up (from Employee to Department ) or down (from Department to Employee) the tree.

#### many - one

The query to list the Employees and the location of their Department with separate documents was:
XML Table
With one nested document, this becomes:
XML Table using the parent access to move up the tree.

#### 1 - many

To list departments and the number of employees in the separate tables is :
XML Table
which becomes:
XML Table

## Summarising and Grouping
### Summary data

Task: Show the number, average (rounded), min and max salaries for Managers.
SQL: SELECT count(*), round(avg(sal)), min(sal), max(sal) FROM emp WHERE job='MANAGER'; MySQL
XQuery:
XML
Better to factor out the XPath expression for the subset of employess:
XML
It would be better to tag the individual values computed:
XML

### Grouping

Task: Show the number, average (rounded), min and max salaries for each Job.
SQL: SELECT job, count(*), round(avg(sal)), min(sal), max(sal) FROM emp GROUP BY job; MySQL
In XQuery, grouping must be done by iterating over the groups. Each Group is identified by the Job and we can get the set (sequence) of all Jobs using the distinct-values function:
XML Table

### Hierarchical report

Task: List the departments , their employee names and salaries and the total salary in each department
This must generate a nested table.
SQL: ?
XQuery:
XML
Note that the functional nature of the XQuery language means that each total must be calculated explicitly, not rolled up incrementally as might be done in an imperative language. This has the advantage that the formulae are explicit and independent and can thus be placed anywhere in the report, such as at the beginning instead of at the end:
XML

### Restricted Groups

Task: Show the number, average (rounded), min and max salaries for each Job where there are at least 2 employees in the group.
SQL:
MySQL
XQuery:
XML Table

## Date Handling
### Selecting by Date

Task: list all employees hired in the current millenium
SQL: SELECT * from job where hiredate >= '2000-01-01' MySQL
XQuery: //Emp[HireDate >= '2000-01-01']
Actually this comparison is a string comparison because of the lack of a schema to define HireDate as an xs:date.
XML Table