Create a table from one or more other tables : Create Table « Table « Oracle PL/SQL Tutorial





SQL>
SQL> CREATE TABLE person (
  2       person_code VARCHAR2(3) PRIMARY KEY,
  3       first_name  VARCHAR2(15),
  4       last_name   VARCHAR2(20),
  5       hire_date   DATE
  6       );

Table created.

SQL>
SQL>
SQL> INSERT INTO person VALUES ('CA', 'Chase', 'At', '01-FEB-02');

1 row created.

SQL> INSERT INTO person VALUES ('GA', 'Gary', 'Talor', '15-FEB-02');

1 row created.

SQL> INSERT INTO person VALUES ('BB', 'Bob', 'Bark', '28-FEB-02');

1 row created.

SQL> INSERT INTO person VALUES ('LB', 'Laren', 'Baby', '01-MAR-02');

1 row created.

SQL> INSERT INTO person VALUES ('LN', 'Linda', 'Norman', '01-JUN-03');

1 row created.

SQL>
SQL> CREATE TABLE product_order (
  2       product_name  VARCHAR2(25),
  3       salesperson   VARCHAR2(3),
  4       order_date DATE,
  5       quantity      NUMBER(4,2)
  6       );

Table created.

SQL>
SQL>
SQL> INSERT INTO product_order VALUES ('Product 1', 'CA', '14-JUL-03', 1);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 2', 'BB', '14-JUL-03', 75);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 3', 'GA', '14-JUL-03', 2);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 4', 'GA', '15-JUL-03', 8);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 5', 'LB', '15-JUL-03', 20);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 6', 'CA', '16-JUL-03', 5);

1 row created.

SQL> INSERT INTO product_order VALUES ('Product 7', 'CA', '17-JUL-03', 1);

1 row created.

SQL>
SQL> CREATE TABLE product (
  2       product_name     VARCHAR2(25) PRIMARY KEY,
  3       product_price    NUMBER(4,2),
  4       quantity_on_hand NUMBER(5,0),
  5       last_stock_date  DATE
  6       );

Table created.

SQL>
SQL> INSERT INTO product VALUES ('Product 1', 99,  1,    '15-JAN-03');

1 row created.

SQL> INSERT INTO product VALUES ('Product 2', 75,  1000, '15-JAN-02');

1 row created.

SQL> INSERT INTO product VALUES ('Product 3', 50,  100,  '15-JAN-03');

1 row created.

SQL> INSERT INTO product VALUES ('Product 4', 25,  10000, null);

1 row created.

SQL> INSERT INTO product VALUES ('Product 5', 9.95,1234, '15-JAN-04');

1 row created.

SQL> INSERT INTO product VALUES ('Product 6', 45,  1,    TO_DATE('December 31, 2008, 11:30 P.M.','Month dd, YYYY, HH:MI P.M.'));

1 row created.

SQL>
SQL> CREATE TABLE product_order_log2 AS
  2       SELECT purc.order_date,
  3              prod.product_name,
  4              prod.product_price,
  5              purc.quantity,
  6              pers.first_name,
  7              pers.last_name
  8       FROM   product  prod,
  9              person   pers,
 10              product_order purc
 11       WHERE  prod.product_name = purc.product_name
 12              AND
 13              pers.person_code = purc.salesperson;

Table created.

SQL>
SQL> SELECT * FROM product_order_log2;

ORDER_DAT PRODUCT_NAME              PRODUCT_PRICE   QUANTITY FIRST_NAME      LAST_NAME
--------- ------------------------- ------------- ---------- --------------- --------------------
14-JUL-03 Product 1                            99          1 Chase           At
14-JUL-03 Product 2                            75         75 Bob             Bark
14-JUL-03 Product 3                            50          2 Gary            Talor
15-JUL-03 Product 4                            25          8 Gary            Talor
15-JUL-03 Product 5                          9.95         20 Laren           Baby
16-JUL-03 Product 6                            45          5 Chase           At

6 rows selected.

SQL>
SQL> drop table product_order;

Table dropped.

SQL>
SQL> drop table product;

Table dropped.

SQL>
SQL> drop table person;

Table dropped.

SQL>
SQL> drop table product_order_log2;

Table dropped.










6.1.Create Table
6.1.1.Creating a Table
6.1.2.Create table with storage setting
6.1.3.Create table with storage setting 2
6.1.4.Create an external table
6.1.5.Create copy table
6.1.6.Create a table from one or more other tables
6.1.7.Rename a table
6.1.8.One column with three constraints
6.1.9.Use referencing columns
6.1.10.Create a table based on a hash cluster
6.1.11.Create table with 'organization index OVERFLOW'
6.1.12.Create table with 'organization index'
6.1.13.Cascade delete setting
6.1.14.Demonstrate a simple External table
6.1.15.Create intermediate table for calculation
6.1.16.Make myCode a CACHE table.