/* I have parts and orders tables from last time */

describe parts
describe orders

select * 
from parts;

select *
from   orders;

insert into parts
values
('10605', 'hammer', 30, 9.99, '003', sysdate);

/*  NOTE:
   
    *   DBMSs support domain integrity by ONLY
        permitting inserts and updates of
	 rows whose values meet the domain
	 constraints of its attributes
*/

prompt THIS WILL FAIL:

insert into parts
values
('10606', 'widget', 30, 9.99, '0003', sysdate);

/*
    the DBMS supports entity integrity
    by making sure all rows in all relations
    have a non-null, unique primary key
*/

prompt THIS WILL FAIL, TOO:

insert into parts
values
('10605', 'hammer', 30, 9.99, '003', sysdate);

/*
    A good RDBMS will also support
    referential integrity -- make sure
    that a foreign key attribute's value
    is either NULL (if it can be)
    OR is indeed the same as a corresponding
    row in the table that foreign key
    references
*/

prompt this insert into orders WORKS:

insert into orders
values
('111111', '11111111', '10603', sysdate, 6, 'B', 'U');

prompt THIS WILL FAIL, order for nonexistent part:

insert into orders
values
('111112', '11111112', '10602', sysdate, 8, 'I', 'F');

/*
   basic/simplest SQL DELETE statement

   DELETE FROM tblname
   WHERE bool_condition;

   this deletes each row of tblname
   for which bool_condition is true

   (if NO where clause?
    deletes ALL the rows of that table;)
*/

prompt THIS WILL FAIL, there is an order for this part

delete from parts
where part_num = '10603';

/*
    simplest/basic SQL UPDATE command:

    UPDATE tblname
    SET attrib = expression
    WHERE bool_condition;

    for each row for which bool_condition is true,
    set the give attribute to the value
    of the given expression

    no WHERE clause? make this change to ALL rows
*/

prompt THIS WILL FAIL, can't change ordered part num, either

update parts
set part_num = '13'
where part_num = '10603';

prompt THIS WILL FAIL, can't CHANGE order to nonexistent part

update orders
set part_num = '15'
where part_num = '10603';

/*
    another INSERT variant:
    *   good when you don't want to specify values
        for all attributes in a new row,
    *   also good when you don't want to give the
        attributes' values in an order of your choice

    INSERT INTO tblname(attrib1, attrib2, ... attrib_n)
    VALUES
    (attrib1_val, attrib2_val, ... attrib_n_val);
  
    *   NOTE: the attributes not specified will
        be NULL (or the value specified by DEFAULT
	 if that was given in the attribute declaration)

    *   This is the PREFERRED way to set attributes
        to NULL in a new row
	 (because it doesn't override any specified
	 defaults)
*/

insert into orders(part_num, order_num, order_date,
                   cust_num, delivery_code)
values
('10604', '222222', sysdate, '22222222', 'U');