No image

SQL Tutorial part 3

Let's study the join function of SQL

Database table :

empno=employee number / ename = employee name / sal=salary / com=commision number / deptno=department number

1. Print out the employee's name, salary, occupation, and departmental location whose monthly salary is between 500 and 2000 and the first letter of the name begins with S.

Oracle

SELECT e.ename, e.sal, e.job, d.loc
from emp e
JOIN dept d
ON e.deptno=d.deptno 
WHERE sal BETWEEN 500 AND 2000 AND e.ename LIKE 'S%';
SELECT e.ename, e.sal, e.job, d.loc
from emp e, dept d
where e.deptno=d.deptno AND sal BETWEEN 500 AND 2000 
AND e.ename LIKE 'S%';

MySQL

SELECT e.ename, e.sal, e.job, d.loc
from emp e
JOIN dept d
ON e.deptno=d.deptno 
WHERE sal BETWEEN 500 AND 2000 AND e.ename LIKE 'S%';
SELECT e.ename, e.sal, e.job, d.loc 
from emp e, dept d 
where e.deptno=d.deptno AND sal BETWEEN 500 AND 2000 
AND e.ename LIKE 'S%';

PostgreSQL

SELECT e.ename, e.sal, e.job, d.loc 
from test.emp e, test.dept d 
where e.deptno=d.deptno AND sal BETWEEN 500 AND 2000 
AND e.ename LIKE 'S%';

 

2. Join emp table and dept table. Print out names and department names from each table. Show 'no enames' if 'OPERATIONS' department doesn't have an employee.

Oracle

select nvl(e.ename,'no ename'), d.dname
from emp e, dept d
where e.deptno(+) = d.deptno;
select nvl(e.ename,'no ename'),d.dname
from emp e
right outer join dept d on e.deptno=d.deptno;

MySQL

select coalesce(e.ename,'no ename'),d.dname
from emp e
right outer join dept d on e.deptno=d.deptno;

PostgreSQL

select coalesce(e.ename,'no ename'),d.dname
from test.emp e
right outer join test.dept d on e.deptno=d.deptno;

 

3. Print out name, salary, location even if there is no location data of Jack.

Oracle

SELECT e.ename, e.sal, d.loc
FROM EMP e, DEPT d
WHERE e.deptno = d.deptno(+);
select e.ename, e.sal, d.loc
from emp e
left outer join dept d on e.deptno=d.deptno;

MySQL

select e.ename, e.sal, d.loc
from emp e
left outer join dept d on e.deptno=d.deptno;

PostgreSQL

select e.ename, e.sal, d.loc
from test.emp e
left outer join test.dept d on e.deptno=d.deptno;

 

4. Print out name, job and department name to see the missing data in both line.

Oracle

select e.ename, e.job, d.dname
from emp e full outer join dept d
on e.deptno=d.deptno;

MySQL

select e.ename, e.job, d.dname
from emp e left join dept d
on e.deptno=d.deptno
union
select e.ename, e.job, d.dname
from emp e right join dept d
on e.deptno=d.deptno;

PostgreSQL

select e.ename, e.job, d.dname
from test.emp e full outer join test.dept d
on e.deptno=d.deptno;

 

5. List the name of manager and employees belong to each manager horizontally.

Oracle

select mgr.ename mgr, 
       listagg(empno.ename,',')within group(order by empno.ename asc) empno
from emp mgr, emp empno
where empno.mgr = mgr.empno 
group by mgr.ename, mgr.sal
order by mgr.sal desc;

MySQL

select mgr.ename mgr,
group_concat(empno.ename order by empno.ename asc separator ',')
from emp mgr, emp empno
where empno.mgr=mgr.empno
group by mgr.ename, mgr.sal
order by mgr.sal desc;

PostgreSQL

select mgr.ename mgr,
array_to_string(array_agg(empno.ename),',')
from test.emp mgr, test.emp empno
where empno.mgr=mgr.empno
group by mgr.ename, mgr.sal
order by mgr.sal desc;
Do you need database performance monitoring? Contact us and we will send you a free quote
[email protected]