SQL Queries — Joins

If you are a SQL Beginner and keen to learn of SQL Joins, this article is the best guide for you.

Image Courtesy www.educba.com

What are joins used for?

he purpose of JOINs in SQL is to access data from multiple tables based on logical relationships between them. JOINS are used to fetch data from database tables and represent the result dataset as a separate table.

Hands on Queries

Above image is the representation of dept table ued in this article.

Given image is the representation of emp table ued in this article.

1. Display the employee names and department names of all the employees working in the job type .

Select ename , dname from emp e join dept d
on e.deptno = d.deptno 
where job ='SALESMAN' or job='ANALYST' 
order by ename; 

2. Display the following output:

ENAME DEPARTMENT_NAME
— — — — — — — — — — — — — — — — -
SMITH SMITH works in RESEARCH
ALLEN works in SALES
WARD works in SALES
JONES works in RESEARCH

select ename as "ENAME", ename|| ' works in '|| dname as "DEPARTMENT_NAME" 
from emp e join dept d
on e.deptno = d.deptno
order by ename;

3. Display the highest salary for each job within each department as per the following report:

Department Job Highest Sal
Accounting Clerk 1300
Accounting Manager 2450
Accounting President 5000
Research Analyst 3000

select dname as "Department", job as "Job", max(sal) as "Highest Sal"
from emp e join dept d
on e.deptno = d.deptno
group by dname, job
order by dname;

4. Display employee name , department name, location and job from all the employees of Manager job having salary above 2900.

select ename, dname, loc, job 
from emp e join dept d
on e.deptno = d.deptno
where job='MANAGER' and
sal > 2900;

5. Display the name of the employees reporting to KING.

select ename from emp 
where mgr= (select empno from emp where ename='KING');

6. Display the count of employees reporting to KING.

select count(ename) from emp 
where mgr= (select empno from emp where ename='KING');

7. Produce the following output:
Manager Names:
— — — — — — — — — — — — -
The Manager of SMITH is FORD
The Manager of ALLEN is BLAKE
The Manager of WARD is BLAKE

select 'The Manager of '|| e1.ename  ||' is ' || e2.ename as "Manager Names"
from emp e1 left join emp e2
on e1.mgr = e2.empno
where e1.mgr is not null;

8. Display location-wise number of employees.

Select loc, count(ename) 
from emp e join dept d
on e.deptno = d.deptno
group by loc;