oracle sql query
If you can't read please download the document
DESCRIPTION
ORACLE SQL QUERYTRANSCRIPT
SQL> cl scr SQL> SELECT Ename, Dept.Deptno, Dname, Loc 2 FROM Emp CROSS JOIN Dept 3 / ENAME DEPTNO DNAME ---------- ---------- -------------KING 10 ACCOUNTING BLAKE 10 ACCOUNTING CLARK 10 ACCOUNTING JONES 10 ACCOUNTING MARTIN 10 ACCOUNTING ALLEN 10 ACCOUNTING TURNER 10 ACCOUNTING JAMES 10 ACCOUNTING WARD 10 ACCOUNTING FORD 10 ACCOUNTING SMITH 10 ACCOUNTING ENAME DEPTNO DNAME ---------- ---------- -------------SCOTT 10 ACCOUNTING ADAMS 10 ACCOUNTING MILLER 10 ACCOUNTING KING 20 RESEARCH BLAKE 20 RESEARCH CLARK 20 RESEARCH JONES 20 RESEARCH MARTIN 20 RESEARCH ALLEN 20 RESEARCH TURNER 20 RESEARCH JAMES 20 RESEARCH ENAME DEPTNO DNAME ---------- ---------- -------------WARD 20 RESEARCH FORD 20 RESEARCH SMITH 20 RESEARCH SCOTT 20 RESEARCH ADAMS 20 RESEARCH MILLER 20 RESEARCH KING 30 SALES BLAKE 30 SALES CLARK 30 SALES JONES 30 SALES MARTIN 30 SALES ENAME DEPTNO DNAME ---------- ---------- -------------ALLEN 30 SALES TURNER 30 SALES JAMES 30 SALES WARD 30 SALES FORD 30 SALES SMITH 30 SALES SCOTT 30 SALES ADAMS 30 SALES MILLER 30 SALES KING 40 OPERATIONS LOC ------------NEW YORK NEW YORK NEW YORK NEW YORK NEW YORK NEW YORK NEW YORK NEW YORK NEW YORK NEW YORK NEW YORK LOC ------------NEW YORK NEW YORK NEW YORK DALLAS DALLAS DALLAS DALLAS DALLAS DALLAS DALLAS DALLAS LOC ------------DALLAS DALLAS DALLAS DALLAS DALLAS DALLAS CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO LOC ------------CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO BOSTONBLAKE40 OPERATIONSBOSTON LOC ------------BOSTON BOSTON BOSTON BOSTON BOSTON BOSTON BOSTON BOSTON BOSTON BOSTON BOSTONENAME DEPTNO DNAME ---------- ---------- -------------CLARK 40 OPERATIONS JONES 40 OPERATIONS MARTIN 40 OPERATIONS ALLEN 40 OPERATIONS TURNER 40 OPERATIONS JAMES 40 OPERATIONS WARD 40 OPERATIONS FORD 40 OPERATIONS SMITH 40 OPERATIONS SCOTT 40 OPERATIONS ADAMS 40 OPERATIONSENAME DEPTNO DNAME LOC ---------- ---------- -------------- ------------MILLER 40 OPERATIONS BOSTON 56 rows selected. SQL> ed Wrote file afiedt.buf 1 SELECT Ename, Dept.Deptno, Dname, Loc 2* FROM Emp, Dept SQL> / ENAME DEPTNO DNAME ---------- ---------- -------------KING 10 ACCOUNTING BLAKE 10 ACCOUNTING CLARK 10 ACCOUNTING JONES 10 ACCOUNTING MARTIN 10 ACCOUNTING ALLEN 10 ACCOUNTING TURNER 10 ACCOUNTING JAMES 10 ACCOUNTING WARD 10 ACCOUNTING FORD 10 ACCOUNTING SMITH 10 ACCOUNTING ENAME DEPTNO DNAME ---------- ---------- -------------SCOTT 10 ACCOUNTING ADAMS 10 ACCOUNTING MILLER 10 ACCOUNTING KING 20 RESEARCH BLAKE 20 RESEARCH CLARK 20 RESEARCH JONES 20 RESEARCH MARTIN 20 RESEARCH ALLEN 20 RESEARCH TURNER 20 RESEARCH JAMES 20 RESEARCH LOC ------------NEW YORK NEW YORK NEW YORK NEW YORK NEW YORK NEW YORK NEW YORK NEW YORK NEW YORK NEW YORK NEW YORK LOC ------------NEW YORK NEW YORK NEW YORK DALLAS DALLAS DALLAS DALLAS DALLAS DALLAS DALLAS DALLASENAME DEPTNO DNAME LOC ---------- ---------- -------------- ------------WARD 20 RESEARCH DALLASFORD SMITH SCOTT ADAMS MILLER KING BLAKE CLARK JONES MARTIN20 20 20 20 20 30 30 30 30 30RESEARCH RESEARCH RESEARCH RESEARCH RESEARCH SALES SALES SALES SALES SALESDALLAS DALLAS DALLAS DALLAS DALLAS CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO LOC ------------CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO BOSTON BOSTON LOC ------------BOSTON BOSTON BOSTON BOSTON BOSTON BOSTON BOSTON BOSTON BOSTON BOSTON BOSTONENAME DEPTNO DNAME ---------- ---------- -------------ALLEN 30 SALES TURNER 30 SALES JAMES 30 SALES WARD 30 SALES FORD 30 SALES SMITH 30 SALES SCOTT 30 SALES ADAMS 30 SALES MILLER 30 SALES KING 40 OPERATIONS BLAKE 40 OPERATIONS ENAME DEPTNO DNAME ---------- ---------- -------------CLARK 40 OPERATIONS JONES 40 OPERATIONS MARTIN 40 OPERATIONS ALLEN 40 OPERATIONS TURNER 40 OPERATIONS JAMES 40 OPERATIONS WARD 40 OPERATIONS FORD 40 OPERATIONS SMITH 40 OPERATIONS SCOTT 40 OPERATIONS ADAMS 40 OPERATIONSENAME DEPTNO DNAME LOC ---------- ---------- -------------- ------------MILLER 40 OPERATIONS BOSTON 56 rows selected. SQL> cl scr SQL> SELECT Ename, Dept.Deptno, Dname, Loc 2 FROM Emp CROSS JOIN Dept 3 WHERE Emp.Deptno = Dept.Deptno; ENAME DEPTNO DNAME ---------- ---------- -------------KING 10 ACCOUNTING BLAKE 30 SALES CLARK 10 ACCOUNTING JONES 20 RESEARCH MARTIN 30 SALES ALLEN 30 SALES TURNER 30 SALES LOC ------------NEW YORK CHICAGO NEW YORK DALLAS CHICAGO CHICAGO CHICAGOJAMES WARD FORD SMITH30 30 20 20SALES SALES RESEARCH RESEARCHCHICAGO CHICAGO DALLAS DALLAS LOC ------------DALLAS DALLAS NEW YORKENAME DEPTNO DNAME ---------- ---------- -------------SCOTT 20 RESEARCH ADAMS 20 RESEARCH MILLER 10 ACCOUNTING 14 rows selected. SQL> cl scr SQL> SELECT Ename, Deptno, Dname, Loc 2 FROM Emp NATURAL JOIN Dept; ENAME DEPTNO DNAME ---------- ---------- -------------KING 10 ACCOUNTING BLAKE 30 SALES CLARK 10 ACCOUNTING JONES 20 RESEARCH MARTIN 30 SALES ALLEN 30 SALES TURNER 30 SALES JAMES 30 SALES WARD 30 SALES FORD 20 RESEARCH SMITH 20 RESEARCH ENAME DEPTNO DNAME ---------- ---------- -------------SCOTT 20 RESEARCH ADAMS 20 RESEARCH MILLER 10 ACCOUNTING 14 rows selected. SQL> ed Wrote file afiedt.bufLOC ------------NEW YORK CHICAGO NEW YORK DALLAS CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO DALLAS DALLAS LOC ------------DALLAS DALLAS NEW YORK1 SELECT Ename, Emp.Deptno, Dname, Loc 2* FROM Emp NATURAL JOIN Dept SQL> / SELECT Ename, Emp.Deptno, Dname, Loc * ERROR at line 1: ORA-25155: column used in NATURAL join cannot have qualifier SQL> SELECT Ename, Deptno, Dname, Loc 2 FROM Emp JOIN Dept 3 USING(Deptno); ENAME DEPTNO DNAME ---------- ---------- -------------KING 10 ACCOUNTING BLAKE 30 SALES LOC ------------NEW YORK CHICAGOCLARK JONES MARTIN ALLEN TURNER JAMES WARD FORD SMITH10 20 30 30 30 30 30 20 20ACCOUNTING RESEARCH SALES SALES SALES SALES SALES RESEARCH RESEARCHNEW YORK DALLAS CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO DALLAS DALLAS LOC ------------DALLAS DALLAS NEW YORKENAME DEPTNO DNAME ---------- ---------- -------------SCOTT 20 RESEARCH ADAMS 20 RESEARCH MILLER 10 ACCOUNTING 14 rows selected.SQL> SELECT Ename, Dept.Deptno, Dname, Loc 2 FROM Emp JOIN Dept 3 ON Emp.Deptno = Dept.Deptno; ENAME DEPTNO DNAME ---------- ---------- -------------KING 10 ACCOUNTING BLAKE 30 SALES CLARK 10 ACCOUNTING JONES 20 RESEARCH MARTIN 30 SALES ALLEN 30 SALES TURNER 30 SALES JAMES 30 SALES WARD 30 SALES FORD 20 RESEARCH SMITH 20 RESEARCH ENAME DEPTNO DNAME ---------- ---------- -------------SCOTT 20 RESEARCH ADAMS 20 RESEARCH MILLER 10 ACCOUNTING 14 rows selected. SQL> ed Wrote file afiedt.buf 1 2 3* SQL> SELECT Ename, Dept.Deptno, Dname, Loc FROM Emp INNER JOIN Dept ON Emp.Deptno = Dept.Deptno / LOC ------------NEW YORK CHICAGO NEW YORK DALLAS CHICAGO CHICAGO LOC ------------NEW YORK CHICAGO NEW YORK DALLAS CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO DALLAS DALLAS LOC ------------DALLAS DALLAS NEW YORKENAME DEPTNO DNAME ---------- ---------- -------------KING 10 ACCOUNTING BLAKE 30 SALES CLARK 10 ACCOUNTING JONES 20 RESEARCH MARTIN 30 SALES ALLEN 30 SALESTURNER JAMES WARD FORD SMITH30 30 30 20 20SALES SALES SALES RESEARCH RESEARCHCHICAGO CHICAGO CHICAGO DALLAS DALLAS LOC ------------DALLAS DALLAS NEW YORKENAME DEPTNO DNAME ---------- ---------- -------------SCOTT 20 RESEARCH ADAMS 20 RESEARCH MILLER 10 ACCOUNTING 14 rows selected. SQL> cl scr SQL> 2 3 4 5 6 7 SELECT E.Ename Employee, M.Ename Manager FROM Emp E INNER JOIN Emp M ON(E.MGR = M.Empno) / MANAGER ---------KING KING KING BLAKE BLAKE BLAKE BLAKE BLAKE CLARK JONES JONES MANAGER ---------FORD SCOTTEMPLOYEE ---------JONES CLARK BLAKE WARD JAMES TURNER ALLEN MARTIN MILLER SCOTT FORD EMPLOYEE ---------SMITH ADAMS13 rows selected. SQL> 2 3 4 5 SELECT Ename, Sal, Grade, Dept.Deptno, Dname FROM Emp JOIN Dept ON Emp.Deptno = Dept.Deptno JOIN SalGrade ON Emp.Sal BETWEEN LoSal AND HiSal;ENAME SAL GRADE DEPTNO DNAME ---------- ---------- ---------- ---------- -------------SMITH 800 1 20 RESEARCH JAMES 950 1 30 SALES ADAMS 1100 1 20 RESEARCH MARTIN 1250 2 30 SALES WARD 1250 2 30 SALES MILLER 1300 2 10 ACCOUNTING TURNER 1500 3 30 SALESALLEN CLARK BLAKE JONES1600 2450 2850 29753 4 4 430 10 30 20SALES ACCOUNTING SALES RESEARCHENAME SAL GRADE DEPTNO DNAME ---------- ---------- ---------- ---------- -------------FORD 3000 4 20 RESEARCH SCOTT 3000 4 20 RESEARCH KING 5000 5 10 ACCOUNTING 14 rows selected. SQL> ed Wrote file afiedt.buf 1 2 3 4* SQL> SELECT Ename, Sal, Grade, Deptno, Dname FROM Emp NATURAL JOIN Dept JOIN SalGrade ON Emp.Sal BETWEEN LoSal AND HiSal /ENAME SAL GRADE DEPTNO DNAME ---------- ---------- ---------- ---------- -------------SMITH 800 1 20 RESEARCH JAMES 950 1 30 SALES ADAMS 1100 1 20 RESEARCH MARTIN 1250 2 30 SALES WARD 1250 2 30 SALES MILLER 1300 2 10 ACCOUNTING TURNER 1500 3 30 SALES ALLEN 1600 3 30 SALES CLARK 2450 4 10 ACCOUNTING BLAKE 2850 4 30 SALES JONES 2975 4 20 RESEARCH ENAME SAL GRADE DEPTNO DNAME ---------- ---------- ---------- ---------- -------------FORD 3000 4 20 RESEARCH SCOTT 3000 4 20 RESEARCH KING 5000 5 10 ACCOUNTING 14 rows selected. SQL> ed Wrote file afiedt.buf 1 2 3 4 5* SQL> SELECT Ename, Sal, Grade, Deptno, Dname FROM Emp JOIN Dept USING(Deptno) JOIN SalGrade ON Emp.Sal BETWEEN LoSal AND HiSal /ENAME SAL GRADE DEPTNO DNAME ---------- ---------- ---------- ---------- -------------SMITH 800 1 20 RESEARCH JAMES 950 1 30 SALES ADAMS 1100 1 20 RESEARCH MARTIN 1250 2 30 SALESWARD MILLER TURNER ALLEN CLARK BLAKE JONES1250 1300 1500 1600 2450 2850 29752 2 3 3 4 4 430 10 30 30 10 30 20SALES ACCOUNTING SALES SALES ACCOUNTING SALES RESEARCHENAME SAL GRADE DEPTNO DNAME ---------- ---------- ---------- ---------- -------------FORD 3000 4 20 RESEARCH SCOTT 3000 4 20 RESEARCH KING 5000 5 10 ACCOUNTING 14 rows selected. SQL> ed Wrote file afiedt.buf 1 SELECT Ename, Sal, Grade, Dept.Deptno, Dname 2 FROM Emp CROSS JOIN Dept 3 WHERE Emp.Deptno = Dept.Deptno 4 JOIN SalGrade 5* ON Emp.Sal BETWEEN LoSal AND HiSal SQL> / JOIN SalGrade * ERROR at line 4: ORA-00933: SQL command not properly ended SQL> ed Wrote file afiedt.buf 1 2 3 4 5* SQL> JOIN SELECT Ename, Sal, Grade, Dept.Deptno, Dname FROM Emp CROSS JOIN Dept WHERE Emp.Deptno = Dept.Deptno AND JOIN SalGrade ON Emp.Sal BETWEEN LoSal AND HiSal / SalGrade * ERROR at line 4: ORA-00920: invalid relational operator SQL> ed Wrote file afiedt.buf 1 SELECT Ename, Sal, Grade, Dept.Deptno, Dname 2 FROM Emp CROSS JOIN Dept 3 ON Emp.Deptno = Dept.Deptno 4 JOIN SalGrade 5* ON Emp.Sal BETWEEN LoSal AND HiSal SQL> / ON Emp.Deptno = Dept.Deptno * ERROR at line 3: ORA-00933: SQL command not properly endedSQL> cl scr SQL> 2 3 4 5 6 7 8 SELECT E.Ename, M.Ename, Sal, Grade, D.Deptno, Dname FROM Emp E INNER JOIN Dept D ON E.Deptno = D.Deptno INNER JOIN Emp M ON E.Empno = M.MGR INNER JOIN SalGrade S ON E.Sal BETWEEN LoSal AND HiSal; ENAME SAL GRADE DEPTNO DNAME ---------- ---------- ---------- ---------- -------------BLAKE 2850 5 10 ACCOUNTING CLARK 2450 5 10 ACCOUNTING JONES 2975 5 10 ACCOUNTING MARTIN 1250 4 30 SALES ALLEN 1600 4 30 SALES TURNER 1500 4 30 SALES JAMES 950 4 30 SALES WARD 1250 4 30 SALES FORD 3000 4 20 RESEARCH SMITH 800 4 20 RESEARCH SCOTT 3000 4 20 RESEARCH ENAME SAL GRADE DEPTNO DNAME ---------- ---------- ---------- ---------- -------------ADAMS 1100 4 20 RESEARCH MILLER 1300 4 10 ACCOUNTINGENAME ---------KING KING KING BLAKE BLAKE BLAKE BLAKE BLAKE JONES FORD JONES ENAME ---------SCOTT CLARK13 rows selected. SQL> ed Wrote file afiedt.buf 1 2 3 4 5 6 7 8* SQL> SELECT E.Ename Manager, M.Ename Employee, Sal, Grade, D.Deptno, Dname FROM Emp E INNER JOIN Dept D ON E.Deptno = D.Deptno INNER JOIN Emp M ON E.Empno = M.MGR INNER JOIN SalGrade S ON E.Sal BETWEEN LoSal AND HiSal / EMPLOYEE SAL GRADE DEPTNO DNAME ---------- ---------- ---------- ---------- -------------BLAKE 2850 5 10 ACCOUNTING CLARK 2450 5 10 ACCOUNTING JONES 2975 5 10 ACCOUNTING MARTIN 1250 4 30 SALES ALLEN 1600 4 30 SALES TURNER 1500 4 30 SALES JAMES 950 4 30 SALES WARD 1250 4 30 SALES FORD 3000 4 20 RESEARCH SMITH 800 4 20 RESEARCH SCOTT 3000 4 20 RESEARCHMANAGER ---------KING KING KING BLAKE BLAKE BLAKE BLAKE BLAKE JONES FORD JONESMANAGER ---------SCOTT CLARKEMPLOYEE SAL GRADE DEPTNO DNAME ---------- ---------- ---------- ---------- -------------ADAMS 1100 4 20 RESEARCH MILLER 1300 4 10 ACCOUNTING13 rows selected. SQL> cl scr SQL> SELECT Ename, Dept.Deptno, Dname, Loc 2 FROM Emp RIGHT JOIN Dept 3 ON Emp.Deptno = Dept.Deptno; ENAME DEPTNO DNAME ---------- ---------- -------------KING 10 ACCOUNTING BLAKE 30 SALES CLARK 10 ACCOUNTING JONES 20 RESEARCH MARTIN 30 SALES ALLEN 30 SALES TURNER 30 SALES JAMES 30 SALES WARD 30 SALES FORD 20 RESEARCH SMITH 20 RESEARCH ENAME DEPTNO DNAME ---------- ---------- -------------SCOTT 20 RESEARCH ADAMS 20 RESEARCH MILLER 10 ACCOUNTING 40 OPERATIONS 15 rows selected. SQL> ed Wrote file afiedt.buf 1 2 3* SQL> SELECT Ename, Dept.Deptno, Dname, Loc FROM Dept RIGHT JOIN Emp ON Emp.Deptno = Dept.Deptno / LOC ------------NEW YORK NEW YORK NEW YORK DALLAS DALLAS DALLAS DALLAS DALLAS CHICAGO CHICAGO CHICAGO LOC ------------NEW YORK CHICAGO NEW YORK DALLAS CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO DALLAS DALLAS LOC ------------DALLAS DALLAS NEW YORK BOSTONENAME DEPTNO DNAME ---------- ---------- -------------MILLER 10 ACCOUNTING CLARK 10 ACCOUNTING KING 10 ACCOUNTING ADAMS 20 RESEARCH SCOTT 20 RESEARCH SMITH 20 RESEARCH FORD 20 RESEARCH JONES 20 RESEARCH WARD 30 SALES JAMES 30 SALES TURNER 30 SALESENAME DEPTNO DNAME LOC ---------- ---------- -------------- -------------ALLEN MARTIN BLAKE 14 rows selected.30 SALES 30 SALES 30 SALESCHICAGO CHICAGO CHICAGOSQL> ed Wrote file afiedt.buf 1 2 3* SQL> SELECT Ename, Dept.Deptno, Dname, Loc FROM Dept LEFT JOIN Emp ON Emp.Deptno = Dept.Deptno / LOC ------------NEW YORK CHICAGO NEW YORK DALLAS CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO DALLAS DALLAS LOC ------------DALLAS DALLAS NEW YORK BOSTONENAME DEPTNO DNAME ---------- ---------- -------------KING 10 ACCOUNTING BLAKE 30 SALES CLARK 10 ACCOUNTING JONES 20 RESEARCH MARTIN 30 SALES ALLEN 30 SALES TURNER 30 SALES JAMES 30 SALES WARD 30 SALES FORD 20 RESEARCH SMITH 20 RESEARCH ENAME DEPTNO DNAME ---------- ---------- -------------SCOTT 20 RESEARCH ADAMS 20 RESEARCH MILLER 10 ACCOUNTING 40 OPERATIONS 15 rows selected. SQL> ed Wrote file afiedt.buf 1 2 3* SQL>SELECT Ename, Dept.Deptno, Dname, Loc FROM Dept LEFT OUTER JOIN Emp ON Emp.Deptno = Dept.Deptno / LOC ------------NEW YORK CHICAGO NEW YORK DALLAS CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO DALLAS DALLAS LOCENAME DEPTNO DNAME ---------- ---------- -------------KING 10 ACCOUNTING BLAKE 30 SALES CLARK 10 ACCOUNTING JONES 20 RESEARCH MARTIN 30 SALES ALLEN 30 SALES TURNER 30 SALES JAMES 30 SALES WARD 30 SALES FORD 20 RESEARCH SMITH 20 RESEARCH ENAME DEPTNO DNAME---------- ---------- -------------SCOTT 20 RESEARCH ADAMS 20 RESEARCH MILLER 10 ACCOUNTING 40 OPERATIONS 15 rows selected. SQL> ed Wrote file afiedt.buf 1 2 3* SQL> FROM------------DALLAS DALLAS NEW YORK BOSTONSELECT Ename, Dept.Deptno, Dname, Loc FROM Emp RIGET OUTER JOIN Dept ON Emp.Deptno = Dept.Deptno / Emp RIGET OUTER JOIN Dept * ERROR at line 2: ORA-00933: SQL command not properly ended SQL> ed Wrote file afiedt.buf 1 2 3* SQL> SELECT Ename, Dept.Deptno, Dname, Loc FROM Emp RIGHT OUTER JOIN Dept ON Emp.Deptno = Dept.Deptno / LOC ------------NEW YORK CHICAGO NEW YORK DALLAS CHICAGO CHICAGO CHICAGO CHICAGO CHICAGO DALLAS DALLAS LOC ------------DALLAS DALLAS NEW YORK BOSTONENAME DEPTNO DNAME ---------- ---------- -------------KING 10 ACCOUNTING BLAKE 30 SALES CLARK 10 ACCOUNTING JONES 20 RESEARCH MARTIN 30 SALES ALLEN 30 SALES TURNER 30 SALES JAMES 30 SALES WARD 30 SALES FORD 20 RESEARCH SMITH 20 RESEARCH ENAME DEPTNO DNAME ---------- ---------- -------------SCOTT 20 RESEARCH ADAMS 20 RESEARCH MILLER 10 ACCOUNTING 40 OPERATIONS 15 rows selected. SQL> cl scr SQL> SELECT Deptno 2 FROM emp 3 WHERE Ename = 'MILLER'; DEPTNO ----------10 SQL> SELECT Ename, Sal, Job, Deptno 2 FROM Emp 3 WHERE Deptno = 10; ENAME SAL JOB DEPTNO ---------- ---------- --------- ---------KING 5000 PRESIDENT 10 CLARK 2450 MANAGER 10 MILLER 1300 CLERK 10 SQL> ed Wrote file afiedt.buf 1 2 3 4 5* SQL> SELECT Ename, Sal, Job, Deptno FROM Emp WHERE Deptno = (SELECT Deptno FROM emp WHERE Ename = 'MILLER') /ENAME SAL JOB DEPTNO ---------- ---------- --------- ---------KING 5000 PRESIDENT 10 CLARK 2450 MANAGER 10 MILLER 1300 CLERK 10 SQL> SELECT * 2 FROM (SELECT * 3 FROM Dept); DEPTNO ---------10 20 30 40 DNAME -------------ACCOUNTING RESEARCH SALES OPERATIONS LOC ------------NEW YORK DALLAS CHICAGO BOSTONSQL> SELECT Ename, Sal, Job, (SELECT SUM(Sal) FROM Emp) SalSum 2 FROM Emp; ENAME SAL JOB SALSUM ---------- ---------- --------- ---------KING 5000 PRESIDENT 29025 BLAKE 2850 MANAGER 29025 CLARK 2450 MANAGER 29025 JONES 2975 MANAGER 29025 MARTIN 1250 SALESMAN 29025 ALLEN 1600 SALESMAN 29025 TURNER 1500 SALESMAN 29025 JAMES 950 CLERK 29025 WARD 1250 SALESMAN 29025 FORD 3000 ANALYST 29025 SMITH 800 CLERK 29025 ENAME SAL JOB SALSUM ---------- ---------- --------- ---------SCOTT 3000 ANALYST 29025 ADAMS 1100 CLERK 29025MILLER 14 rows selected. SQL> cl scr SQL> 2 3 4 (1300 CLERK29025SELECT Ename, Sal, Job FROM Emp );ENAME SAL JOB ---------- ---------- --------KING 5000 PRESIDENT BLAKE 2850 MANAGER CLARK 2450 MANAGER JONES 2975 MANAGER MARTIN 1250 SALESMAN ALLEN 1600 SALESMAN TURNER 1500 SALESMAN JAMES 950 CLERK WARD 1250 SALESMAN FORD 3000 ANALYST SMITH 800 CLERK ENAME SAL JOB ---------- ---------- --------SCOTT 3000 ANALYST ADAMS 1100 CLERK MILLER 1300 CLERK 14 rows selected. SQL> ed Wrote file afiedt.buf 1 ( 2 SELECT Ename, Sal, Job 3 FROM Emp 4 ORDER BY Sal 5* ) SQL> / ORDER BY Sal * ERROR at line 4: ORA-00907: missing right parenthesis SQL> ed Wrote file afiedt.buf 1 2 3 4 5* SQL> ( SELECT Ename, Sal, Job FROM Emp ) ORDER BY Sal /ENAME SAL JOB ---------- ---------- ---------SMITH JAMES ADAMS MARTIN WARD MILLER TURNER ALLEN CLARK BLAKE JONES800 950 1100 1250 1250 1300 1500 1600 2450 2850 2975CLERK CLERK CLERK SALESMAN SALESMAN CLERK SALESMAN SALESMAN MANAGER MANAGER MANAGERENAME SAL JOB ---------- ---------- --------FORD 3000 ANALYST SCOTT 3000 ANALYST KING 5000 PRESIDENT 14 rows selected. SQL> cl scr SQL> SELECT Ename, Sal, Job 2 FROM Emp 3 WHERE Sal > (SELECT Sal 4 FROM Emp 5 WHERE Empno = 7566); ENAME SAL JOB ---------- ---------- --------KING 5000 PRESIDENT FORD 3000 ANALYST SCOTT 3000 ANALYST SQL> SELECT Sal 2 FROM Emp 3 WHERE Empno = 7566; SAL ---------2975 SQL> SELECT Ename, Sal, Job 2 FROM Emp 3 WHERE Sal > 2975; ENAME SAL JOB ---------- ---------- --------KING 5000 PRESIDENT FORD 3000 ANALYST SCOTT 3000 ANALYST SQL> cl scr SQL> 2 3 4 5 6 SELECT Ename, Sal, Job FROM Emp WHERE Job = (SELECT Job FROM Emp WHERE Ename = UPPER('smith')) ORDER BY Sal;ENAME SAL JOB ---------- ---------- --------SMITH 800 CLERK JAMES 950 CLERK ADAMS 1100 CLERK MILLER 1300 CLERK SQL> SQL> SELECT Job 2 FROM Emp 3 WHERE Ename = UPPER('smith'); JOB --------CLERK SQL> cl scr SQL> ed Wrote file afiedt.buf 1 2 3 4 5 6* SQL> SELECT Ename, Sal, Job FROM Emp WHERE Job = (SELECT Job FROM Emp WHERE Ename = 'SMITH' AND Ename 'SMITH') /no rows selected SQL> ed Wrote file afiedt.buf 1 2 3 4 5 6* SQL> SELECT Ename, Sal, Job FROM Emp WHERE Job = (SELECT Job FROM Emp WHERE Ename = 'SMITH' ) AND Ename 'SMITH' /ENAME SAL JOB ---------- ---------- --------JAMES 950 CLERK ADAMS 1100 CLERK MILLER 1300 CLERK SQL> ed Wrote file afiedt.buf 1 2 3 4 5 6* SQL> SELECT Empno, Ename, Hiredate, Sal FROM Emp WHERE Hiredate > (SELECT Hiredate FROM Emp WHERE Ename = 'TURNER') ORDER BY Sal /EMPNO ---------7900 7876 7654 7934 7902 7788 7839ENAME ---------JAMES ADAMS MARTIN MILLER FORD SCOTT KINGHIREDATE SAL --------- ---------03-DEC-81 950 12-JAN-83 1100 28-SEP-81 1250 23-JAN-82 1300 03-DEC-81 3000 09-DEC-82 3000 17-NOV-81 50007 rows selected. SQL> ed Wrote file afiedt.buf 1 2 3 4 5 6* SQL> SELECT Empno, Ename, Hiredate, Sal FROM Emp WHERE Hiredate < (SELECT Hiredate FROM Emp WHERE Ename = 'TURNER') ORDER BY Sal / ENAME ---------SMITH WARD ALLEN CLARK BLAKE JONES HIREDATE SAL --------- ---------17-DEC-80 800 22-FEB-81 1250 20-FEB-81 1600 09-JUN-81 2450 01-MAY-81 2850 02-APR-81 2975EMPNO ---------7369 7521 7499 7782 7698 75666 rows selected. SQL> ed Wrote file afiedt.buf 1 2 3 4 5 6 7* SQL> SELECT Empno, Ename, Hiredate, Sal FROM Emp WHERE Hiredate < (SELECT Hiredate FROM Emp WHERE Ename = 'TURNER') AND Ename LIKE '%A%' ORDER BY Sal / ENAME ---------WARD ALLEN CLARK BLAKE HIREDATE SAL --------- ---------22-FEB-81 1250 20-FEB-81 1600 09-JUN-81 2450 01-MAY-81 2850EMPNO ---------7521 7499 7782 7698SQL> ed Wrote file afiedt.buf 1 SELECT Empno, Ename, Hiredate, Sal 2 FROM Emp 3 WHERE Hiredate < (SELECT Hiredate 4 FROM Emp5 6 7 8* SQL>WHERE Ename = 'TURNER') AND Ename LIKE '%A%' AND Sal > 1700 ORDER BY Sal / ENAME ---------CLARK BLAKE HIREDATE SAL --------- ---------09-JUN-81 2450 01-MAY-81 2850EMPNO ---------7782 7698 SQL> cl scrSQL> SELECT Empno, Ename, Sal, Job 2 FROM Emp 3 WHERE Deptno = (SELECT Deptno 4 FROM Dept 5 WHERE Dname = 'SALES'); EMPNO ---------7698 7654 7499 7844 7900 7521 ENAME SAL JOB ---------- ---------- --------BLAKE 2850 MANAGER MARTIN 1250 SALESMAN ALLEN 1600 SALESMAN TURNER 1500 SALESMAN JAMES 950 CLERK WARD 1250 SALESMAN6 rows selected. SQL> ed Wrote file afiedt.buf 1 2 3 4* SQL> SELECT Empno, Ename, Sal, Job FROM Emp, Dept WHERE Emp.Deptno = Dept.Deptno AND Dname = 'SALES' / ENAME SAL JOB ---------- ---------- --------BLAKE 2850 MANAGER MARTIN 1250 SALESMAN ALLEN 1600 SALESMAN TURNER 1500 SALESMAN JAMES 950 CLERK WARD 1250 SALESMANEMPNO ---------7698 7654 7499 7844 7900 75216 rows selected. SQL> SQL> 2 3 4 SET AUTOTRACE ON EXPLAIN SELECT Empno, Ename, Sal, Job FROM Emp, Dept WHERE Emp.Deptno = Dept.Deptno AND Dname = 'SALES'; ENAME SAL JOB ---------- ---------- --------BLAKE 2850 MANAGER MARTIN 1250 SALESMANEMPNO ---------7698 76547499 7844 7900 7521ALLEN TURNER JAMES WARD1600 1500 950 1250SALESMAN SALESMAN CLERK SALESMAN6 rows selected. Execution Plan ---------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=5 Bytes=370 ) 1 2 3 0 1 1 HASH JOIN (Cost=7 Card=5 Bytes=370) TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Byt es=22) TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt es=728)SQL> SELECT Empno, Ename, Sal, Job 2 FROM Emp 3 WHERE Deptno = (SELECT Deptno 4 FROM Dept 5 WHERE Dname = 'SALES'); EMPNO ---------7698 7654 7499 7844 7900 7521 ENAME SAL JOB ---------- ---------- --------BLAKE 2850 MANAGER MARTIN 1250 SALESMAN ALLEN 1600 SALESMAN TURNER 1500 SALESMAN JAMES 950 CLERK WARD 1250 SALESMAN6 rows selected. Execution Plan ---------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=52) 1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes= 52) 2 1 TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Byt es=22)SQL> cl scr SQL> SELECT Empno, Ename, Sal, Comm, Sal + NVL( Comm, 0 ) 2 FROM Emp 3 WHERE Deptno = (SELECT Deptno 4 FROM Dept 5 WHERE Loc = 'DALLAS');EMPNO ---------7566 7902 7369 7788 7876ENAME SAL COMM SAL+NVL(COMM,0) ---------- ---------- ---------- --------------JONES 2975 2975 FORD 3000 3000 SMITH 800 800 SCOTT 3000 3000 ADAMS 1100 1100Execution Plan ---------------------------------------------------------0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=59) 1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes= 59) 2 1 TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Byt es=21)SQL> cl scr SQL> SET AUTOTRACE ON EXPLAIN SQL> SET AUTOTRACE OFF EXPLAIN SQL> SELECT MAX(Sal) FROM Emp; MAX(SAL) ---------5000 SQL> SELECT Ename, MAX(Sal) 2 FROM Emp; SELECT Ename, MAX(Sal) * ERROR at line 1: ORA-00937: not a single-group group function SQL> ed Wrote file afiedt.buf 1 2 3* SQL> SELECT Ename, MAX(Sal) FROM Emp GROUP BY Ename /ENAME MAX(SAL) ---------- ---------ADAMS 1100 ALLEN 1600 BLAKE 2850 CLARK 2450 FORD 3000 JAMES 950 JONES 2975 KING 5000 MARTIN 1250 MILLER 1300SCOTT3000ENAME MAX(SAL) ---------- ---------SMITH 800 TURNER 1500 WARD 1250 14 rows selected. SQL> SELECT Ename, Job , Sal 2 FROM Emp 3 WHERE Sal = (SELECT MAX(Sal) 4 FROM Emp); ENAME JOB SAL ---------- --------- ---------KING PRESIDENT 5000 SQL> SELECT Ename, Job, Sal 2 FROM Emp 3 WHERE Sal = (SELECT MIN(Sal) 4 FROM Emp); ENAME JOB SAL ---------- --------- ---------SMITH CLERK 800 SQL> SELECT Ename, Job, Sal 2 FROM Emp 3 WHERE Sal > (SELECT AVG(Sal) 4 FROM Emp); ENAME ---------KING BLAKE CLARK JONES FORD SCOTT JOB SAL --------- ---------PRESIDENT 5000 MANAGER 2850 MANAGER 2450 MANAGER 2975 ANALYST 3000 ANALYST 30006 rows selected. SQL> SELECT AVG(Sal) 2 FROM Emp; AVG(SAL) ---------2073.21429 SQL> ed Wrote file afiedt.buf 1 2* SQL> 2 3 4 SELECT AVG(Sal) FROM Emp SELECT Ename, Sal FROM Emp WHERE Sal = (SELECT MAX(Sal) FROM Emp5WHERE Deptno = 20);ENAME SAL ---------- ---------FORD 3000 SCOTT 3000 SQL> ed Wrote file afiedt.buf 1 2 3 4 5* SQL> SELECT Ename, Sal FROM Emp WHERE Sal = (SELECT MAX(Sal) FROM Emp WHERE Deptno = 20) SPOOL OFF