Since all exemples in this blog for the NOSQL databases will be based on Oracle EMP/DEPT exemple, if you don’t have Oracle background, here I provide some usefull information.
To create the 2 tables, you can use Oracle livesql:
https://livesql.oracle.com/apex/livesql/file/content_O5AEB2HE08PYEPTGCFLZU9YCV.html
Below are some typical queries. I provide the statements that I used some years ago while teaching in the EFREI school in Paris. I see that one of my students put it into his website here, so you can find those exemples as well in the Power Point format:
adrien.poupa.fr › efrei
The inner join (5 ways to do it..)
Select * from emp, dept where emp.deptno=dept.deptno;
Select * from emp inner join dept on emp.deptno=dept.deptno;
Select * from emp natural join dept;
Select * from emp join dept on emp.deptno=dept.deptno;
Select * from emp e,dept d where e.deptno=d.deptno;
The outer join (6 ways to do it …)
The ANSI standard to perform an outer join (compatible with Oracle since 9i)
Select * from emp left outer join dept on emp.deptno=dept.deptno; (to show employees without department)
Select * from emp right outer join dept on emp.deptno=dept.deptno; (to show empty departments)
Select * from emp full outer join dept on emp.deptno=dept.deptno; (to show both)
The old Oracle way to do the same thing:
Select * from emp,dept where emp.deptno=dept.deptno(+);
(The sign + is on the side where there is the missing information. In this case, if the information is missing on the dept side, there is an employee on the emp side that does not have a department)
Select * from emp,dept where emp.deptno(+)=dept.deptno;
(In this case, if the information is missing on the emp side, there is the department on the dept side that does not have employees)
Select * from emp,dept where emp.deptno=dept.deptno(+)
Union all
Select * from emp,dept where emp.deptno(+)=dept.deptno;
Grouping functions:
select max(sal),min(sal),avg(sal),count(*),count(ename),count(comm),stddev(sal),
sum(sal)
from emp;
Select sum(sal),deptno from
emp
group by deptno;
Select sum(sal),job from emp
group by job;
Select sum(sal),job,deptno from emp
group by job,deptno;
select d.deptno, d.dname, max(sal)
from emp e , dept d
where e.deptno = d.deptno
group by d.deptno,d.dname;
Select sum(sal),deptno
from emp
group by rollup(deptno);
Select sum(sal),deptno,job
from emp group by cube(deptno,job);
select d.deptno, d.dname, max(sal)
from emp e , dept d
where e.deptno = d.deptno
and deptno> 10
group by d.deptno,d.dname; nselect d.deptno, d.dname, max(sal)
from emp e , dept d
where e.deptno = d.deptno
group by d.deptno,d.dname
having d. deptno=10;
A question: Which one of this 2 statements is better?
a) select d.deptno, d.dname, max(sal)
from emp e , dept d
where e.deptno = d.deptno
and deptno> 10
group by d.deptno,d.dname;
b) select d.deptno, d.dname, max(sal)
from emp e , dept d
where e.deptno = d.deptno
group by d.deptno,d.dname
having d. deptno=10;
Order by functions
Select * from emp order by sal; …. order by [field] asc/desc nulls first/last
Select * from emp order by empno;
Select * from emp order by empno,ename;
Select * from emp order by 1,2;
Select * from emp order by sal desc;
Select * from emp order by comm;
Select * from emp order by comm nulls first;
Select * from emp order by comm nuls last;
Select * from emp order by comm asc nulls first;
Select * from emp order by comm desc nulls first;
Select * from emp order by comm asc nulls last;
Select * from emp order by comm desc nulls last;
Other functions:
Select upper(ename) from emp;
Select lower(ename) from emp;
Select initcap(ename) from emp;
Select dump(ename) from emp;
Select length(ename) from emp;
Select substr(ename,1,5) from emp;
Select rpad(ename,10,’*’) from emp;
Select lpad(ename,10,’*’) from emp;
Subqueries:
a) The subquery in the where clause:
The so called existential subqueries:
Select * from emp where exists (select 1 from dept where dept.Deptno=emp.deptno);
Select * from emp where not exists (select 1 from dept where dept.Deptno=emp.deptno);
Select * from emp where deptno in (select deptno from dept);
Select * from emp where deptno not in (select deptno from dept);
Other typical types of subqueries in the where clause:
Select * from emp where sal > (select min(sal) from emp);
Select * from emp where sal > any (select sal from emp);
Select * from emp where sal < (select max(sal) from emp);
Select * from emp where sal < all (select sal from emp where deptno=10) and deptno=20;
b) the subquery in the from clause:
select * from (select * from emp where ….)
c) the subquery in the select clause: (So called “scalar subquery”)
select ename,sal,(select avg(sal) from emp where deptno=e.deptno group by deptno) avg_sal from emp e;
Practical Oracle,SQL Server, other databases and Big Data & AI 