SQL Queries Homework Week 4
1.
SELECT e.ename, e.deptno, d.dname, d.deptno FROM dept d LEFT OUTER JOIN emp e ON d.deptno = e.deptno ORDER BY d.deptno;
SQL> SELECT e.ename, e.deptno, d.dname, d.deptno 2 FROM dept d 3 LEFT OUTER JOIN emp e 4 ON d.deptno = e.deptno 5 ORDER BY d.deptno;
ENAME DEPTNO DNAME DEPTNO
---------- ---------- -------------- ----------
MILLER 10 ACCOUNTING 10
KING 10 ACCOUNTING 10
CLARK 10 ACCOUNTING 10
FORD 20 RESEARCH 20
ADAMS 20 RESEARCH 20
JONES 20 RESEARCH 20
SMITH 20 RESEARCH 20
SCOTT 20 RESEARCH 20
JAMES 30 SALES 30
TURNER 30 SALES 30
BLAKE 30 SALES 30
ENAME DEPTNO DNAME DEPTNO
---------- ---------- -------------- ----------
MARTIN 30 SALES 30
WARD 30 SALES 30
ALLEN 30 SALES 30 OPERATIONS 40 STARFLEET 60
16 rows selected.
2.
SELECT e.ename, e.deptno, d.dname, d.deptno FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno ORDER BY e.deptno, e.ename;
SQL> SELECT e.ename, e.deptno, d.dname, d.deptno 2 FROM emp e 3 LEFT OUTER JOIN dept d 4 ON e.deptno = d.deptno 5 ORDER BY e.deptno, e.ename;
ENAME DEPTNO DNAME DEPTNO
---------- ---------- -------------- ----------
CLARK 10 ACCOUNTING 10
KING 10 ACCOUNTING 10
MILLER 10 ACCOUNTING 10
ADAMS 20 RESEARCH 20
FORD 20 RESEARCH 20
JONES
SELECT e.ename, e.deptno, d.dname, d.deptno FROM dept d LEFT OUTER JOIN emp e ON d.deptno = e.deptno ORDER BY d.deptno;
SQL> SELECT e.ename, e.deptno, d.dname, d.deptno 2 FROM dept d 3 LEFT OUTER JOIN emp e 4 ON d.deptno = e.deptno 5 ORDER BY d.deptno;
ENAME DEPTNO DNAME DEPTNO
---------- ---------- -------------- ----------
MILLER 10 ACCOUNTING 10
KING 10 ACCOUNTING 10
CLARK 10 ACCOUNTING 10
FORD 20 RESEARCH 20
ADAMS 20 RESEARCH 20
JONES 20 RESEARCH 20
SMITH 20 RESEARCH 20
SCOTT 20 RESEARCH 20
JAMES 30 SALES 30
TURNER 30 SALES 30
BLAKE 30 SALES 30
ENAME DEPTNO DNAME DEPTNO
---------- ---------- -------------- ----------
MARTIN 30 SALES 30
WARD 30 SALES 30
ALLEN 30 SALES 30 OPERATIONS 40 STARFLEET 60
16 rows selected.
2.
SELECT e.ename, e.deptno, d.dname, d.deptno FROM emp e LEFT OUTER JOIN dept d ON e.deptno = d.deptno ORDER BY e.deptno, e.ename;
SQL> SELECT e.ename, e.deptno, d.dname, d.deptno 2 FROM emp e 3 LEFT OUTER JOIN dept d 4 ON e.deptno = d.deptno 5 ORDER BY e.deptno, e.ename;
ENAME DEPTNO DNAME DEPTNO
---------- ---------- -------------- ----------
CLARK 10 ACCOUNTING 10
KING 10 ACCOUNTING 10
MILLER 10 ACCOUNTING 10
ADAMS 20 RESEARCH 20
FORD 20 RESEARCH 20
JONES