Database Updates (insert, delete, update)

General Syntax:

insert into tablename [(column {, column})]
[values (expression {, expression})] | [Sub-select]

delete from tablename [corr_name]
[where search_condition]

update tablename [corr_name]
set column = expression {, column = expression}
[where search_condition]

(1) Insert into the orders table.

SQL> insert into orders
     values (1111,'sep','c003','a05','p02',500,1000);

1 row created

SQL> insert into orders (ordno, month, cid, aid, pid)
     values (1107, 'aug', 'c006', 'a04', 'p01');

1 row created.

SQL> select * 
     from orders;

     ORDNO MON CID  AID PID        QTY    DOLLARS
---------- --- ---- --- --- ---------- ----------
      1011 jan c001 a01 p01       1000        450
      1012 jan c001 a01 p01       1000        450
                        ...
                        ...
      1111 sep c003 a05 p02        500       1000
      1107 aug c006 a04 p01
17 rows selected.

Note the two rows that were inserted in previous slide.

(2) Create a new table "swcusts" with same fields as 
    customers and insert rows from "customers" table 
    with city field equals "Dallas" or "Austin".

SQL> select * 
     from customers;

CID  CNAME         CITY                     DISCNT
---- ------------- -------------------- ----------
c001 Tiptop        Duluth                       10
c002 Basics        Dallas                       12 <<
c003 Allied        Dallas                        8 <<
c004 ACME          Duluth                        8
c006 ACME          Kyoto                         0
c007 Windix        Dallas                          <<

6 rows selected.

SQL> create table swcusts (
         cid     char(4) not null, 
         cname   varchar(13),
         city    varchar(20), 
         discnt  float);

Table created.

SQL> insert into swcusts 
         select * 
         from customers 
         where city in ('Dallas','Austin');

3 rows created.

SQL> select * 
     from swcust;

CID  CNAME         CITY                     DISCNT
---- ------------- -------------------- ----------
c002 Basics        Dallas                       12
c003 Allied        Dallas                        8
c007 Windix        Dallas

(3) Increase the percent commission by 50% for all 
    agents in "New York".

SQL> update agents 
     set percent = 1.5 * percent 
     where city = 'New York';

2 rows updated.
SQL> select * from agents;

AID ANAME         CITY            PERCENT
--- ------------- ------------ ----------
a01 Smith         New York              9  << old value = 6
a02 Jones         Newark                6
a03 Brown         Tokyo                 7
a04 Gray          New York              9  << old value = 6
a05 Otasi         Duluth                5
a06 Smith         Dallas                5

(4) Delete all agents from "New York".

SQL> delete from agents 
     where city = 'New York';

2 rows deleted.

SQL> select * 
     from agents;

AID ANAME         CITY                    PERCENT
--- ------------- -------------------- ----------
a02 Jones         Newark                        6
a03 Brown         Tokyo                         7
a05 Otasi         Duluth                        5
a06 Smith         Dallas                        5

(5) Delete all agents who have total orders of less than $600.

SQL> delete from agents 
     where aid in (select aid 
                   from orders 
                   group by aid 
                   having sum(dollars) < 600);

1 row deleted.

SQL> select * 
     from agents;

AID ANAME         CITY                    PERCENT
--- ------------- -------------------- ----------
a03 Brown         Tokyo                         7
a05 Otasi         Duluth                        5
a06 Smith         Dallas                        5