Task: Count all the employees!

Solution:

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

Task: Show all Employees with a salary greater than 1000.

Solution:

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

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

Solution:

SQL: select * from emp where sal between 1000 and 2000;
XQuery: //Emp[Sal>1000][Sal<2000]

(: there is no between function in XQuery, so write one :)
declare function local:between($value as xs:decimal, $min as xs:decimal, $max as xs:decimal) as xs:boolean 
{ 
	$value >= $min and $value <= $max 
};
(: the modified solution :)
//Emp[local:between(Sal,1000,2000)] 

Task: Show all employees with no Commission.

Solution:

SQL: select * from emp where comm is null;
XQuery: //Emp[empty(Comm/text())]

Task: Select the first 5 employees.

Solution:

SQL: select * from emp limit 5;
XQuery: //Emp[position() <=5]

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.

Solution:

SQL: select 12 * sal + ifnull(comm,0) from emp;
XQuery: //Emp/(12*number(Sal)+(if(exists(Comm/text())) then number($Comm) else 0))

Task: List the employee names with their Annual Salary.

Solution:

SQL: select ename, 12 * sal + ifnull(comm,0) as "Annual Salary" from emp; 
XQuery: 
for $emp in //Emp 
return <Emp> {$emp/Ename} <AnnualSalary> 
	{12*number($emp/Sal)+ 
	(if (exists($emp/Comm/text())) then number($emp/Comm) 
		else 0)
	 }
 </AnnualSalary>
</Emp>

Task: List all Employees whose name contains "AR".

Solution:

SQL: select * from emp where ename like "%AR%"
XQuery://Emp[contains(Ename,"AR")] 

Task: Find the name of the department that employee 'SMITH' works in!

Solution:

SQL: select dept.dname from emp, dept where dept.deptno = emp.deptno and ename='SMITH';
XQuery: let $dept := //Emp[Ename='SMITH']/DeptNo return //Dept[DeptNo = $dept ]/Dname 
XPath:  //Dept[DeptNo = //Emp[Ename='SMITH']/DeptNo]/Dname

Task: List the departments and the number of employees in each department

Solution:

SQL: select dname, count(*) from dept natural join emp group by emp.deptno;
XQuery: 
for $dept in //Dept let $headCount := count(//Emp[DeptNo=$dept/DeptNo]) 
return <Dept>
 {$dept/Dname}
 <HeadCount>{$headCount}</HeadCount>
 </Dept>

Task: List the name of each employee together with the name of their manager.

Solution:

SQL: select e.ename, m.ename from emp e, emp m where e.mgr = m.empno;
XQuery: 
for $emp in //Emp 
	let $manager := //Emp[EmpNo = $emp/MgrNo] return <Emp> 
	{$emp/Ename} <Manager>{string($manager/Ename)}</Manager>
 </Emp>

Task: Show the number, average (rounded), min and max salaries for Managers.

Solution:

SQL: select count(*), round(avg(sal)), min(sal), max(sal) FROM emp WHERE job='MANAGER';
XQuery:
let $managers := //Emp[Job='MANAGER'] return <Statistics> <Count>{(count($managers}</Count> <Average>{round(avg($managers/Sal))}</Average> <Min>{min($managers/Sal)}</Min> <Max>{max($managers/Sal))}</Max>
 </Statistics> 

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

Solution:

SQL: select job, count(*), round(avg(sal)), min(sal), max(sal) FROM emp GROUP BY job HAVING count(*) > 1;
XQuery: 
for $job in distinct-values(//Emp/Job) 
let $employees := //Emp[Job=$job]
 where count($employees) > 1
 return <Statistics>
 	<Job>{$job}</Job>
 	<Count>{count($employees )}</Count> <Average>{round(avg($employees /Sal))}</Average> <Min>{min($employees /Sal)}</Min>
	 <Max>{max($employees /Sal)}</Max>
 </Statistics> 

Task: List the departments , their employee names and salaries and the total salary in each department.

Solution:

SQL: This must generate a nested table.
XQuery: 
<Report> {
 for $dept in //Dept
 let $subtotal := sum(//Emp[DeptNo = $dept/DeptNo]/Sal) 
 return
  <Department> 
   {$dept/Dname}
   {for $emp in //Emp[DeptNo = $dept/DeptNo] 
    return <Emp> {$emp/Ename} {$emp/Sal} </Emp> 
    }
    <SubTotal>{$subtotal}</SubTotal>
  </Department> }
  <Total>{sum(//Emp/Sal)}</Total>
 </Report>