Oracle SQL Plus
1 select e.ename,m.ename mgr,m.empno mgrno,e.empno
2 from emp e,emp m
3* where e.mgr=m.empno
SQL> /
ENAME MGR MGRNO EMPNO
----------
---------- ---------- ----------
SMITH FORD 7902 7369
ALLEN BLAKE 7698 7499
WARD BLAKE 7698 7521
JONES KING 7839 7566
MARTIN BLAKE 7698 7654
BLAKE KING 7839 7698
CLARK KING 7839 7782
SCOTT JONES 7566 7788
TURNER BLAKE 7698 7844
ADAMS SCOTT 7788 7876
JAMES BLAKE 7698 7900
ENAME MGR MGRNO EMPNO
----------
---------- ---------- ----------
FORD JONES 7566 7902
MILLER CLARK 7782 7934
13 rows
selected.
SQL> select distinct e1.job
2 from emp e1, emp x
3 where x.job=e1.job
4 and x.deptno !=e1.deptno;
JOB
---------
CLERK
MANAGER
SQL> select ename,dname
from emp cross join dept;
ENAME DNAME
----------
--------------
SMITH ACCOUNTING
ALLEN ACCOUNTING
WARD ACCOUNTING
JONES ACCOUNTING
MARTIN ACCOUNTING
BLAKE ACCOUNTING
CLARK ACCOUNTING
SCOTT ACCOUNTING
KING ACCOUNTING
TURNER ACCOUNTING
ADAMS ACCOUNTING
ENAME DNAME
----------
--------------
JAMES ACCOUNTING
FORD ACCOUNTING
MILLER ACCOUNTING
SMITH RESEARCH
ALLEN RESEARCH
WARD RESEARCH
JONES RESEARCH
MARTIN RESEARCH
BLAKE RESEARCH
CLARK RESEARCH
SCOTT RESEARCH
ENAME DNAME
----------
--------------
KING RESEARCH
TURNER RESEARCH
ADAMS RESEARCH
JAMES RESEARCH
FORD RESEARCH
MILLER RESEARCH
SMITH SALES
ALLEN SALES
WARD SALES
JONES SALES
MARTIN SALES
ENAME DNAME
----------
--------------
BLAKE SALES
CLARK SALES
SCOTT SALES
KING SALES
TURNER SALES
ADAMS SALES
JAMES SALES
FORD SALES
MILLER SALES
SMITH OPERATIONS
ALLEN OPERATIONS
ENAME DNAME
----------
--------------
WARD OPERATIONS
JONES OPERATIONS
MARTIN OPERATIONS
BLAKE OPERATIONS
CLARK OPERATIONS
SCOTT OPERATIONS
KING OPERATIONS
TURNER OPERATIONS
ADAMS OPERATIONS
JAMES OPERATIONS
FORD OPERATIONS
ENAME DNAME
----------
--------------
MILLER OPERATIONS
56 rows
selected.
SQL> select ename,dname from emp
natural join dept;
ENAME DNAME
----------
--------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
ENAME DNAME
----------
--------------
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
14 rows
selected.
SQL> select ename,dname from emp
join dept using (deptno);
ENAME DNAME
----------
--------------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
ENAME DNAME
----------
--------------
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
14 rows
selected.
SQL> select e.ename,e.sal,m.ename from
emp e join emp m on(e.mgr=m.empno);
ENAME SAL ENAME
----------
---------- ----------
SMITH 800 FORD
ALLEN 1600 BLAKE
WARD 1250 BLAKE
JONES 2975 KING
MARTIN 1250 BLAKE
BLAKE 2850 KING
CLARK 2450 KING
SCOTT 3000 JONES
TURNER 1500 BLAKE
ADAMS 1100 SCOTT
JAMES 950 BLAKE
ENAME SAL ENAME
----------
---------- ----------
FORD 3000 JONES
MILLER 1300 CLARK
13 rows
selected.
SQL> select e.ename,d.dname from emp
e,dept d where d.deptno=e.deptno(+);
ENAME DNAME
----------
--------------
CLARK ACCOUNTING
KING ACCOUNTING
MILLER ACCOUNTING
SMITH RESEARCH
ADAMS RESEARCH
FORD RESEARCH
SCOTT RESEARCH
JONES RESEARCH
ALLEN SALES
BLAKE SALES
MARTIN SALES
ENAME DNAME
---------- --------------
JAMES SALES
TURNER SALES
WARD SALES
OPERATIONS
15 rows
selected.
SQL> select e.ename,e.sal,x.grade from
emp e,salgrade x where e.sal between x.losal and x.hisal;
ENAME SAL GRADE
----------
---------- ----------
SMITH 800 1
ADAMS 1100 1
JAMES 950 1
WARD 1250 2
MARTIN 1250 2
MILLER 1300 2
ALLEN 1600 3
TURNER 1500 3
JONES 2975 4
BLAKE 2850 4
CLARK 2450 4
ENAME SAL GRADE
----------
---------- ----------
SCOTT 3000 4
FORD 3000 4
KING 5000 5
14 rows
selected.
SQL> select deptno,empno,ename,sum(sal)
from emp
2 where deptno>20
3 group by cube(deptno,empno,ename)
4 order by deptno;
DEPTNO
EMPNO ENAME SUM(SAL)
----------
---------- ---------- ----------
30
7499 ALLEN 1600
30
7499 1600
30
7521 WARD 1250
30
7521 1250
30
7654 MARTIN 1250
30
7654 1250
30
7698 BLAKE 2850
30
7698 2850
30
7844 TURNER 1500
30
7844 1500
30
7900 JAMES 950
DEPTNO
EMPNO ENAME SUM(SAL)
----------
---------- ---------- ----------
30
7900 950
30 ALLEN 1600
30 BLAKE 2850
30 JAMES 950
30 MARTIN 1250
30 TURNER 1500
30
WARD 1250
30 9400
7499 ALLEN 1600
7499 1600
7521 WARD 1250
DEPTNO
EMPNO ENAME SUM(SAL)
----------
---------- ---------- ----------
7521 1250
7654 MARTIN 1250
7654 1250
7698 BLAKE 2850
7698 2850
7844 TURNER 1500
7844 1500
7900 JAMES 950
7900 950
ALLEN 1600
BLAKE 2850
DEPTNO
EMPNO ENAME SUM(SAL)
----------
---------- ---------- ----------
JAMES 950
MARTIN 1250
TURNER 1500
WARD 1250
9400
38 rows
selected.
SQL> select deptno,ename,sum(sal),grouping(deptno)
2 from emp where deptno
> 5 group by rollup(deptno,ename);
DEPTNO ENAME SUM(SAL)
GROUPING(DEPTNO)
----------
---------- ---------- ----------------
10 KING 5000 0
10 CLARK 2450 0
10 MILLER 1300 0
10 8750 0
20 FORD 3000 0
20 ADAMS 1100 0
20 JONES 2975 0
20 SCOTT 3000 0
20 SMITH 800 0
20 10875 0
30 WARD 1250 0
DEPTNO ENAME SUM(SAL)
GROUPING(DEPTNO)
----------
---------- ---------- ----------------
30 ALLEN 1600 0
30 BLAKE 2850 0
30 JAMES 950 0
30 MARTIN 1250 0
30 TURNER 1500 0
30 9400 0
29025 1
18 rows
selected.
SQL> select ename,sal,deptno from
emp x
2 where sal>(select avg(sal)
from emp where deptno=x.deptno);
ENAME SAL DEPTNO
----------
---------- ----------
ALLEN 1600 30
JONES 2975 20
BLAKE 2850 30
SCOTT 3000 20
KING 5000 10
FORD 3000 20
6 rows
selected.
SQL> select * from emp where empno in (select mgr from emp);
EMPNO ENAME JOB MGR HIREDATE SAL COMM
----------
---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20
7698 BLAKE MANAGER
7839 01-MAY-81 2850
30
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
----------
---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
7902 FORD ANALYST 7566 03-DEC-81 3000
20
6 rows
selected.
SQL> select max(sal),job,ename from
emp
2 where sal in(select max(sal) from emp
group by job)
3 group by job,ename;
MAX(SAL)
JOB ENAME
----------
--------- ----------
1300 CLERK MILLER
3000 ANALYST FORD
3000 ANALYST SCOTT
2975 MANAGER JONES
1600 SALESMAN ALLEN
5000 PRESIDENT KING
6 rows
selected.
SQL> select lastname ,sum(sal)
2 from emp
3 where lastname like '%a%'
4 group by lastname
5 having firstname like 'm%'
6 order by firstname desc;
order by firstname desc
*
ERROR at line 6:
ORA-00904: "FIRSTNAME":
invalid identifier
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM
----------
---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7369 SMITH CLERK 7902 17-DEC-80 800
20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300
30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
----------
---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7566 JONES MANAGER 7839 02-APR-81 2975
20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400
30
7698 BLAKE MANAGER 7839 01-MAY-81 2850
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
----------
---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7782 CLARK MANAGER 7839 09-JUN-81 2450
10
7788 SCOTT ANALYST 7566 19-APR-87 3000
20
7839 KING PRESIDENT 17-NOV-81 5000
10
EMPNO ENAME JOB MGR HIREDATE SAL COMM
---------- ----------
--------- ---------- --------- ---------- ----------
DEPTNO
----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0
30
7876 ADAMS CLERK 7788 23-MAY-87 1100
20
7900 JAMES CLERK 7698 03-DEC-81 950
30
EMPNO ENAME JOB MGR HIREDATE SAL COMM
----------
---------- --------- ---------- --------- ---------- ----------
DEPTNO
----------
7902 FORD ANALYST 7566 03-DEC-81 3000
20
7934 MILLER CLERK 7782 23-JAN-82 1300
10
14 rows
selected.
sql>select ename
,job,sum(sal)
from emp
where ename
like '%a%'
group by ename,job
having job
like 'm%' ;
order by job
desc
SQL> spool off