|
SQL>
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
1 row created.
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7369.00 SMITH CLERK 7902.00 17-DEC-80 800.00 20
7499.00 ALLEN SALESMAN 7698.00 20-FEB-81 1600.00 300.00 30
7521.00 WARD SALESMAN 7698.00 22-FEB-81 1250.00 500.00
7566.00 JONES MANAGER 7839.00 02-APR-81 2975.00 20
7654.00 MARTIN SALESMAN 7698.00 28-SEP-81 1250.00 1400.00 30
7698.00 BLAKE MANAGER 7839.00 01-MAY-81 2850.00
7782.00 CLARK MANAGER 7839.00 09-JUN-81 2450.00 10
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7788.00 SCOTT ANALYST 7566.00 09-DEC-82 3000.00 20
7839.00 KING PRESIDENT 17-NOV-81 5000.00 10
7844.00 TURNER SALESMAN 7698.00 08-SEP-81 1500.00 .00 30
7876.00 ADAMS CLERK 7788.00 12-JAN-83 1100.00 20
7900.00 JAMES CLERK 7698.00 03-DEC-81 950.00 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-------- ---------- --------- -------- --------- -------- -------- ------
7902.00 FORD ANALYST 7566.00 03-DEC-81 3000.00 20
7934.00 MILLER CLERK 7782.00 23-JAN-82 1300.00 10
14 rows selected.
SQL>
SQL> set echo on
SQL>
SQL> select max(count(*)) from emp group by deptno, job;
MAX(COUNT(*))
-------------
4.00
SQL>
SQL> column deptno format 999
SQL> column sal_1 format 9999
SQL> column sal_2 format 9999
SQL> column sal_3 format 9999
SQL> column sal_4 format 9999
SQL> column ename_1 format a6
SQL> column ename_4 format a6
SQL>
SQL> select deptno, job,
2 max( decode( rn, 1, ename, null )) ename_1,
3 max( decode( rn, 1, sal, null )) sal_1,
4 max( decode( rn, 2, ename, null )) ename_2,
5 max( decode( rn, 2, sal, null )) sal_2,
6 max( decode( rn, 3, ename, null )) ename_3,
7 max( decode( rn, 3, sal, null )) sal_3,
8 max( decode( rn, 4, ename, null )) ename_4,
9 max( decode( rn, 4, sal, null )) sal_4
10 from ( select deptno, job, ename, sal,
11 row_number() over ( partition by deptno, job
12 order by sal, ename ) rn
13 from emp
14 )
15 group by deptno, job
16 /
DEPTNO JOB ENAME_ SAL_1 ENAME_2 SAL_2 ENAME_3 SAL_3 ENAME_ SAL_4
------ --------- ------ ----- ---------- ----- ---------- ----- ------ -----
10 CLERK MILLER 1300
MANAGER CLARK 2450
PRESIDENT KING 5000
20 ANALYST FORD 3000 SCOTT 3000
CLERK SMITH 800 ADAMS 1100
MANAGER JONES 2975
30 CLERK JAMES 950
MANAGER BLAKE 2850
SALESMAN MARTIN 1250 WARD 1250 TURNER 1500 ALLEN 1600
9 rows selected.
SQL> drop table emp;
Table dropped.
|