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:

Group SELECT Command Questions

1.    Display the maximum, minimum and average salary and commission earned.

SELECT max(sal), min(sal), avg(sal), max(comm), min(comm), avg(comm) FROM emp;

2.    Display the department number, total salary payout and total commission payout for each
department.

SELECT deptno, sum(sal), sum(comm) FROM emp GROUP BY deptno;

3.    Display the department number, total salary payout and total commission payout for each
Department that pays at least one employee commission.

SELECT deptno,sum(sal),sum(comm) FROM emp GROUP BY deptno HAVING sum(comm)> 0;

4.    Display the department number and number of clerks in each department.

SELECT deptno, count(job) FROM emp WHERE job = 'CLERK' GROUP BY deptno;

5.    Display the department number and total salary of employees in each department that employs four or more people.

SELECT deptno, sum(sal) FROM emp GROUP BY deptno HAVING count(empno) >= 4;

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

SELECT mgr, count(mgr) FROM emp WHERE mgr IS NOT NULL GROUP BY mgr;


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: