Adding Multiple Columns on Existing Table

To add multiple columns to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name

ADD ( column_1 column-definition,

  column_2 column-definition,

  ... 

  column_n column_definition );

For example:

ALTER TABLE DEPARTMENTS_D

ADD ( supplier_name varchar2(50),

  city varchar2(45) );

ALTER TABLE ASSETS

ADD ( START_DATE DATE ,END_DATE DATE );

ALTER TABLE COST

ADD(X_CUSTOM VARCHAR2(50),

     DATASOURCE_NUM_ID NUMBER

     TENANT_ID NUMBER

     ETL_PROC_WID NUMBER

     INTEGRATION_ID VARCHAR2(50));

 

Understanding Set Operators in Oracle SQL/PLSQL-Part02

Now lets see what is Intersect and Minus operator. For that we will insert new value in TableA

SQL> insert into Table_A values(19,5,'mulund6','SADF');

1 row created.

SQL> select * from Table_A;

  ORDERNO    CUSTNO CUSADD     CUSNAME

--------- --------- ---------- ---------------

       12         1 mulund     mich

       13         2 mulund1    michl

       14         3 mulund3    michle

       15         4 mulund4    michlen

       19         5 mulund6    SADF 

SQL> select * from Table_B;

    ORDNO    ITEMNO ITEMNAME         QTY

--------- --------- ---------- ---------

       12        67 nut              100

       13        77 nut1             200

       14        87 nut2             300

       15        97 nut3             400

       16       107 nut4             500  

Note :The intersect operator gives only the common values.  The minus operator returns all distinct rows of the first query but not of the second query.

Understanding Set Operators in Oracle SQL/PLSQL-Part01

Below we have tried to explain the use of Set operators in SQL by using simple examples.

First let’s check the Union all and Union operators. For that lets create tables as below:

SQL> desc Table_A

 Name                            Null?    Type

 ------------------------------- -------- ----

 ORDERNO                                  NUMBER(8)

 CUSTNO                                   NUMBER(5)

 CUSADD                                   VARCHAR2(10)

 CUSNAME                                  VARCHAR2(15)

SQL> desc Table_B

 Name                            Null?    Type

 ------------------------------- -------- ----

 ORDNO                                    NUMBER(8)

 ITEMNO                                   NUMBER(4)

 ITEMNAME                                 VARCHAR2(10)

 QTY                                      NUMBER(10)

Insert values as below:

SQL> select * from Table_A;

  ORDERNO    CUSTNO CUSADD     CUSNAME

--------- --------- ---------- ---------------

       12         1 mulund     mich

       13         2 mulund1    michl

       14         3 mulund3    michle

       15         4 mulund4    michlen

SQL> select * from Table_B;

    ORDNO    ITEMNO ITEMNAME         QTY

--------- --------- ---------- ---------

       12        67 nut              100

       13        77 nut1             200

       14        87 nut2             300

       15        97 nut3             400

       16       107 nut4             500

Note:The union operator DOES NOT return duplicate values.  The union all operator returns including the duplicate values.

SQL> select orderno from Table_A

union

select ordno from Table_B;

  ORDERNO

---------

       12

       13

       14

       15

       16

SQL> select orderno from Table_A

union all

select ordno from Table_B;

  ORDERNO

---------

       12

       13

       14

       15

       12

       13

       14

       15

       16

9 rows selected.