drop table emp
ORA-00942: table or view does not existMore Details: https://docs.oracle.com/error-help/db/ora-00942
create table emp (
EMPNO NUMBER(4) ,
ENAME VARCHAR2(10) ,
JOB VARCHAR2(9) ,
MGR NUMBER(4) ,
HIREDATE DATE ,
SAL NUMBER(7,2) ,
COMM NUMBER(7,2) ,
DEPTNO NUMBER(2)
)
Table created.
Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_date('17/DEC/80','DD/MON/RR'),800,null,20)
1 row(s) inserted.
Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_date('20/FEB/81','DD/MON/RR'),1600,300,30)
1 row(s) inserted.
Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_date('22/FEB/81','DD/MON/RR'),1250,500,30)
1 row(s) inserted.
Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_date('02/APR/81','DD/MON/RR'),2975,null,20)
1 row(s) inserted.
Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_date('28/SEP/81','DD/MON/RR'),1250,1400,30)
1 row(s) inserted.
Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_date('01/MAY/81','DD/MON/RR'),2850,null,30)
1 row(s) inserted.
Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_date('09/JUN/81','DD/MON/RR'),2450,null,10)
1 row(s) inserted.
Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_date('09/DEC/82','DD/MON/RR'),3000,null,20)
1 row(s) inserted.
Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7839,'KING','PRESIDENT',null,to_date('17/NOV/81','DD/MON/RR'),5000,null,10)
1 row(s) inserted.
Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_date('08/SEP/81','DD/MON/RR'),1500,null,30)
1 row(s) inserted.
Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_date('12/JAN/83','DD/MON/RR'),1100,null,20)
1 row(s) inserted.
Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_date('03/DEC/81','DD/MON/RR'),950,null,30)
1 row(s) inserted.
Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_date('03/DEC/81','DD/MON/RR'),3000,null,20)
1 row(s) inserted.
Insert into emp (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_date('23/JAN/82','DD/MON/RR'),1300,null,10)
1 row(s) inserted.
commit
Statement processed.
select empno, ename, job, hiredate, sal
from emp
order by sal
EMPNO | ENAME | JOB | HIREDATE | SAL | 7369 | SMITH | CLERK | 17-DEC-80 | 800 | 7900 | JAMES | CLERK | 03-DEC-81 | 950 | 7876 | ADAMS | CLERK | 12-JAN-83 | 1100 | 7521 | WARD | SALESMAN | 22-FEB-81 | 1250 | 7654 | MARTIN | SALESMAN | 28-SEP-81 | 1250 | 7934 | MILLER | CLERK | 23-JAN-82 | 1300 | 7844 | TURNER | SALESMAN | 08-SEP-81 | 1500 | 7499 | ALLEN | SALESMAN | 20-FEB-81 | 1600 | 7782 | CLARK | MANAGER | 09-JUN-81 | 2450 | 7698 | BLAKE | MANAGER | 01-MAY-81 | 2850 | 7566 | JONES | MANAGER | 02-APR-81 | 2975 | 7788 | SCOTT | ANALYST | 09-DEC-82 | 3000 | 7902 | FORD | ANALYST | 03-DEC-81 | 3000 | 7839 | KING | PRESIDENT | 17-NOV-81 | 5000 |
---|
select empno, ename, job, hiredate, sal,
rank() OVER (order by hiredate) as hire_seq
from emp
order by sal
EMPNO | ENAME | JOB | HIREDATE | SAL | HIRE_SEQ | 7369 | SMITH | CLERK | 17-DEC-80 | 800 | 1 | 7900 | JAMES | CLERK | 03-DEC-81 | 950 | 10 | 7876 | ADAMS | CLERK | 12-JAN-83 | 1100 | 14 | 7521 | WARD | SALESMAN | 22-FEB-81 | 1250 | 3 | 7654 | MARTIN | SALESMAN | 28-SEP-81 | 1250 | 8 | 7934 | MILLER | CLERK | 23-JAN-82 | 1300 | 12 | 7844 | TURNER | SALESMAN | 08-SEP-81 | 1500 | 7 | 7499 | ALLEN | SALESMAN | 20-FEB-81 | 1600 | 2 | 7782 | CLARK | MANAGER | 09-JUN-81 | 2450 | 6 | 7698 | BLAKE | MANAGER | 01-MAY-81 | 2850 | 5 | 7566 | JONES | MANAGER | 02-APR-81 | 2975 | 4 | 7902 | FORD | ANALYST | 03-DEC-81 | 3000 | 10 | 7788 | SCOTT | ANALYST | 09-DEC-82 | 3000 | 13 | 7839 | KING | PRESIDENT | 17-NOV-81 | 5000 | 9 |
---|
select empno, ename, job, sal
from emp
order by sal
EMPNO | ENAME | JOB | SAL | 7369 | SMITH | CLERK | 800 | 7900 | JAMES | CLERK | 950 | 7876 | ADAMS | CLERK | 1100 | 7521 | WARD | SALESMAN | 1250 | 7654 | MARTIN | SALESMAN | 1250 | 7934 | MILLER | CLERK | 1300 | 7844 | TURNER | SALESMAN | 1500 | 7499 | ALLEN | SALESMAN | 1600 | 7782 | CLARK | MANAGER | 2450 | 7698 | BLAKE | MANAGER | 2850 | 7566 | JONES | MANAGER | 2975 | 7788 | SCOTT | ANALYST | 3000 | 7902 | FORD | ANALYST | 3000 | 7839 | KING | PRESIDENT | 5000 |
---|
select empno, ename, job, sal,
rank() OVER (order by sal) as sal_rank,
dense_rank() OVER (order by sal) as sal_dense_rank
from emp
order by sal
EMPNO | ENAME | JOB | SAL | SAL_RANK | SAL_DENSE_RANK | 7369 | SMITH | CLERK | 800 | 1 | 1 | 7900 | JAMES | CLERK | 950 | 2 | 2 | 7876 | ADAMS | CLERK | 1100 | 3 | 3 | 7521 | WARD | SALESMAN | 1250 | 4 | 4 | 7654 | MARTIN | SALESMAN | 1250 | 4 | 4 | 7934 | MILLER | CLERK | 1300 | 6 | 5 | 7844 | TURNER | SALESMAN | 1500 | 7 | 6 | 7499 | ALLEN | SALESMAN | 1600 | 8 | 7 | 7782 | CLARK | MANAGER | 2450 | 9 | 8 | 7698 | BLAKE | MANAGER | 2850 | 10 | 9 | 7566 | JONES | MANAGER | 2975 | 11 | 10 | 7788 | SCOTT | ANALYST | 3000 | 12 | 11 | 7902 | FORD | ANALYST | 3000 | 12 | 11 | 7839 | KING | PRESIDENT | 5000 | 14 | 12 |
---|
select empno, ename, job, sal,
row_number() OVER (order by sal) as sal_row_number
from emp
order by sal
EMPNO | ENAME | JOB | SAL | SAL_ROW_NUMBER | 7369 | SMITH | CLERK | 800 | 1 | 7900 | JAMES | CLERK | 950 | 2 | 7876 | ADAMS | CLERK | 1100 | 3 | 7521 | WARD | SALESMAN | 1250 | 4 | 7654 | MARTIN | SALESMAN | 1250 | 5 | 7934 | MILLER | CLERK | 1300 | 6 | 7844 | TURNER | SALESMAN | 1500 | 7 | 7499 | ALLEN | SALESMAN | 1600 | 8 | 7782 | CLARK | MANAGER | 2450 | 9 | 7698 | BLAKE | MANAGER | 2850 | 10 | 7566 | JONES | MANAGER | 2975 | 11 | 7788 | SCOTT | ANALYST | 3000 | 12 | 7902 | FORD | ANALYST | 3000 | 13 | 7839 | KING | PRESIDENT | 5000 | 14 |
---|
select empno, ename, job, sal,
row_number() OVER (order by sal, empno) as sal_row_number
from emp
order by sal
EMPNO | ENAME | JOB | SAL | SAL_ROW_NUMBER | 7369 | SMITH | CLERK | 800 | 1 | 7900 | JAMES | CLERK | 950 | 2 | 7876 | ADAMS | CLERK | 1100 | 3 | 7521 | WARD | SALESMAN | 1250 | 4 | 7654 | MARTIN | SALESMAN | 1250 | 5 | 7934 | MILLER | CLERK | 1300 | 6 | 7844 | TURNER | SALESMAN | 1500 | 7 | 7499 | ALLEN | SALESMAN | 1600 | 8 | 7782 | CLARK | MANAGER | 2450 | 9 | 7698 | BLAKE | MANAGER | 2850 | 10 | 7566 | JONES | MANAGER | 2975 | 11 | 7788 | SCOTT | ANALYST | 3000 | 12 | 7902 | FORD | ANALYST | 3000 | 13 | 7839 | KING | PRESIDENT | 5000 | 14 |
---|