Join SELECT Command Questions

1.    Display the name of each employee with his department name.

SELECT ename, dname FROM emp , dept where  emp.deptno = dept.deptno;
“OR”
SELECT ename, dname FROM emp INNER JOIN dept ON emp.deptno = dept.deptno;
“0R”
SELECT ename, dname FROM emp INNER JOIN dept USING(deptno);

2.    Display a list of all departments with the employees in each department.

SELECT dname, ename FROM dept LEFT OUTER JOIN emp ON dept.deptno = emp.deptno;
“OR”
SELECT dname, ename FROM dept LEFT OUTER JOIN emp USING(dept);
“OR”
SELECT dname, ename FROM dept LEFT JOIN emp ON dept.deptno = emp.deptno;
“OR”
SELECT dname, ename FROM dept , emp WHERE  dept.deptno = emp.deptno(+)
            NOTE: OUTER keyword is optional in LEFT OUTER JOIN.

3.    Display all the departments with the manager for that department.

SELECT dname, ename FROM emp INNER JOIN dept ON emp.deptno = dept.deptno
WHERE job = 'MANAGER';

4.    Display the names of each employee with the name of his/her boss.

SELECT s.ename, b.ename FROM emp s INNER JOIN emp b ON s.mgr = b.empno;

5.    Display the names of each employee with the name of his/her boss with a blank for the boss of the president.

SELECT s.ename, b.ename FROM emp s LEFT OUTER JOIN emp b ON s.mgr = b.empno;

6.    Display the employee number and name of each employee who manages other employees with the number of people he or she manages.

SELECT a.mgr, b.ename, count(a.mgr) FROM empa INNER JOIN emp b
ON a.mgr = b.empno WHERE a.mgr IS NOT NULL GROUP BY a.mgr, b.ename;

7.    Repeat the display for the last question, but this time display the rows in descending order of the number of employees managed.

SELECT a.mgr, b.ename, count(a.mgr) FROM empa INNER JOIN emp b
ON a.mgr = b.empno WHERE a.mgr IS NOT NULL GROUP BY a.mgr, b.ename
ORDER BY count(a.mgr) DESC;

I am interested in hearing your feedback about this question & answer, so that I can improve my lab series on sql for you.connect with us on facebook, twitter

Share

Did you enjoy reading this and found it useful? If so, please share it with your friends: