Mega Code Archive
Copying selected columns from multiple tables
<![CDATA[
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));
SQL>
SQL> INSERT INTO EMP VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-DEC-1980', 'DD-MON-YYYY'), 800, NULL, 20);
SQL> INSERT INTO EMP VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600, 300, 30);
SQL> INSERT INTO EMP VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
SQL> INSERT INTO EMP VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
SQL> INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN', 7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
SQL> INSERT INTO EMP VALUES (7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
SQL> INSERT INTO EMP VALUES (7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
SQL> INSERT INTO EMP VALUES (7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
SQL> INSERT INTO EMP VALUES (7839, 'KING', 'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
SQL> INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN', 7698,TO_DATE('8-SEP-1981', 'DD-MON-YYYY'), 1500, 0, 30);
SQL> INSERT INTO EMP VALUES (7876, 'ADAMS', 'CLERK', 7788,TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
SQL> INSERT INTO EMP VALUES (7900, 'JAMES', 'CLERK', 7698,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 950, NULL, 30);
SQL> INSERT INTO EMP VALUES (7902, 'FORD', 'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'), 3000, NULL, 20);
SQL> INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);
SQL>
SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) );
SQL>
SQL> INSERT INTO DEPT VALUES (10, 'ACCOUNTING', 'NEW YORK');
SQL> INSERT INTO DEPT VALUES (20, 'RESEARCH', 'DALLAS');
SQL> INSERT INTO DEPT VALUES (30, 'SALES', 'CHICAGO');
SQL> INSERT INTO DEPT VALUES (40, 'OPERATIONS', 'BOSTON');
SQL>
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
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
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL>
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL>
SQL>
SQL>
SQL> -- Copying selected columns from multiple tables
SQL>
SQL> CREATE TABLE newTable
2 AS (SELECT emp.empno, dept.deptno, emp.ename FROM emp, dept);
SQL>
SQL> select * from newTable;
EMPNO DEPTNO ENAME
---------- ---------- ----------
7369 10 SMITH
7499 10 ALLEN
7521 10 WARD
7566 10 JONES
7654 10 MARTIN
7698 10 BLAKE
7782 10 CLARK
7788 10 SCOTT
7839 10 KING
7844 10 TURNER
7876 10 ADAMS
EMPNO DEPTNO ENAME
---------- ---------- ----------
7900 10 JAMES
7902 10 FORD
7934 10 MILLER
7369 20 SMITH
7499 20 ALLEN
7521 20 WARD
7566 20 JONES
7654 20 MARTIN
7698 20 BLAKE
7782 20 CLARK
7788 20 SCOTT
EMPNO DEPTNO ENAME
---------- ---------- ----------
7839 20 KING
7844 20 TURNER
7876 20 ADAMS
7900 20 JAMES
7902 20 FORD
7934 20 MILLER
7369 30 SMITH
7499 30 ALLEN
7521 30 WARD
7566 30 JONES
7654 30 MARTIN
EMPNO DEPTNO ENAME
---------- ---------- ----------
7698 30 BLAKE
7782 30 CLARK
7788 30 SCOTT
7839 30 KING
7844 30 TURNER
7876 30 ADAMS
7900 30 JAMES
7902 30 FORD
7934 30 MILLER
7369 40 SMITH
7499 40 ALLEN
EMPNO DEPTNO ENAME
---------- ---------- ----------
7521 40 WARD
7566 40 JONES
7654 40 MARTIN
7698 40 BLAKE
7782 40 CLARK
7788 40 SCOTT
7839 40 KING
7844 40 TURNER
7876 40 ADAMS
7900 40 JAMES
7902 40 FORD
EMPNO DEPTNO ENAME
---------- ---------- ----------
7934 40 MILLER
SQL>
SQL> drop table newTable;
SQL>
SQL>
SQL> drop table dept;
SQL>
SQL> drop table emp;
SQL>
SQL>
]]>