DML (Inserts, Deletes, Updates) examples.

--using Pet table:
CREATE TABLE pet (name VARCHAR2(12) PRIMARY KEY, owner VARCHAR2(12),species VARCHAR2(12), sex CHAR(1), birth DATE, death DATE, weight NUMBER(3) NOT NULL, cuteness NUMBER(2) DEFAULT 0);

Inserts, Deletes and Updates are not permanent until COMMIT executed
(or logoff).  Implicit Commit with any DDL statement (e.g. Create,
Alter, Drop).  ROLLBACK undoes changes to last Commit.  
SAVEPOINT savepointname;       checkpoint
ROLLBACK TO savepointname;
??@ create tables do not do commit?

--insert a row into table
INSERT INTO tablename VALUES(column1val,column2value,...,columnNvalue);

--all column values, in column order
INSERT INTO pet VALUES('Cleo','Bob','dog','f','3-Jan-2004',null,12,80);

--specified columns only, others get their default value.
INSERT INTO pet(name,owner,weight) VALUES('Spot','Al',8);
--default default value is Null.  
--DEFAULT constraint makes a different default (e.g. cuteness column)
--notice that cuteness is 0, not null.  others are null

--can not let null be assigned to Not Null column:
INSERT INTO pet(name,owner) VALUES('asdfasdf','Al')
*
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."PET"."WEIGHT")

--can not insert a row with same value as primary key existing value
INSERT INTO pet(name,owner,weight) VALUES('Spot','Al',8)
*
ERROR at line 1:
ORA-00001: unique constraint (HR.SYS_C004534) violated
--primary key maintains "entity integrity": each row (entity) is unique

--insert data from other tables using subquery
INSERT INTO tablename (SELECT columns FROM other,tables [Where ...]);

--insert into multiple tables.  unconditionally
INSERT ALL 
  INTO table1 VALUES(.....)
  INTO table2 VALUES(.....)     more tables too
 SELECT columns FROM other,tables [Where ...];    no parens!
--insert into multiple tables.  conditionally
INSERT ALL 
 WHEN condition1 THEN  INTO table1 VALUES(.....)
 WHEN condition2 THEN  INTO table2 VALUES(.....)     more tables too
 SELECT columns FROM other,tables [Where ...];    no parens!

--"upsert" insert if new row, else update existing row
MERGE INTO t1  USING t2 ON (t1.col=t2.col)
  WHEN MATCHED THEN UPDATE SET t1.colx=t2.coly, t1.colw=t2.colz...
  WHEN NOT MATCHED THEN INSERT (t1.colx,t1.colw...) VALUES (t2.coly,t2.colz...);


--delete row(s) that match Where condition
DELETE FROM tablename [WHERE condition];

DELETE FROM pet WHERE name='Spot';
DELETE FROM pet WHERE sex='f';
--delete all rows (no Where clause)
DELETE FROM pet;

--delete all rows. 
TRUNCATE TABLE event;
--faster than Delete.   implicit Commit

--delete rows using subquery:
DELETE FROM pet WHERE cuteness<(SELECT Avg(cuteness) FROM pet);


--update row(s) column(s) values
UPDATE tablename SET column=value[,column=value...] [WHERE condition];

--one column of one row
UPDATE pet SET weight=100 WHERE name='Fluffy';
-- >1 column of one row.   new value based on current value
UPDATE pet SET weight=10,cuteness=cuteness/2 WHERE name='Chirpy';
--one column of several rows
UPDATE pet SET cuteness=cuteness/2 WHERE sex='m';
--several columns of several rows...
--all rows (no Where clause)
UPDATE pet SET weight=weight*1.2;

--can not update to violate a constraint like Not Null, or unique primary key
UPDATE pet SET name='Fluffy' WHERE name='Chirpy';
ERROR at line 1:
ORA-00001: unique constraint (HR.SYS_C004534) violated

UPDATE pet SET weight=null WHERE name='Chirpy';
ERROR at line 1:
ORA-01407: cannot update ("HR"."PET"."WEIGHT") to NULL

--update column to value returned by single-row subquery:
UPDATE pet SET cuteness=(SELECT Avg(cuteness) FROM pet) WHERE name='Fluffy';

--update column to its Default value
UPDATE pet SET cuteness = DEFAULT WHERE name='Fluffy';