-- CS 325 - Week 3 Labs - 3:00 lab

-- added spooling after lab, so can post script's results, also

spool 325lab03-2-out.txt

-- let's drop and create a parts table

drop table parts cascade constraints;

create table parts
(part_num          integer,
 part_name         varchar2(25),
 quantity_on_hand  smallint,
 price             decimal(6, 2),
 level_code        char(3),   -- must be 3 digits
 last_inspected    date,
 primary key       (part_num)
);

/* 
    basic SQL insert command ONLY inserts ONE NEW ROW
    into a table at a time...
    *   (don't worry, DBMSs often provide tools to 
        slurp rows from
        say a CSV file INTO a table...
	 (for example, Oracle has SQL*Loader)
*/

/* SQL string literals are written in
   SINGLE QUOTES!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*/

/* for insert use, for inserting a date value.
   use a string literal written as
   'DD-MON-YYYY' or 'DD-MON-YY'
*/

insert into parts
values
(10603, 'hexagonal wrench', 13, 9.99, '003', '05-SEP-2016');
      
-- sysdate is a built-in function that returns the
--    current date (and time)

insert into parts
values
(10604, 'tire', 287, 39.99, '333', sysdate);

/* 
    the SIMPLEST SQL SELECT statement:

    SELECT *
    FROM tblname;
*/

select *
from   parts;

/*
    SQL also allows additional domain constraints --
    constraints on the values for a given attribute;

    you place these in the create table statement (usually)
    AFTER the attribute's type and before its ending comma

    *   I can use NOT NULL to say an attribute
        MUST be given a value

	 maxpoints   integer not null,

    *   I can use DEFAULT to specify a value for an attribute
        if NO value is EXPLICITLY given for that attribute

        quantity integer default 1,

    *   I can use the IN operator to specify that an attribute
        must be one of a small set of values

        car_color varchar2(9) check(car_color in ('red', 'blue',
                                                  'turquoise')),

    *   oh yes -- and CHECK can be used to require that
        an attribute must pass a boolean test to be
	 permitted

	 quiz_grade integer check(quiz_grade >= 0 AND
                                 quiz_grade <= 100),

				  there's also a BETWEEN operator (this has the same
				     effect as the previous example -- BETWEEN is inclusive)

				     quiz_grade integer check(quiz_grade between 0 and 100),

*/

drop table orders cascade constraints;

create table orders
(order_num   char(6),
 cust_num    char(8),
 part_num    integer,
 order_date  date default sysdate,
 quantity    integer default 1 not null,
 order_code  char(1) check(order_code in ('B', 'I', 'G')),
 delivery_code char(1) check(delivery_code in ('U', 'F',
                                'P')) not null,
 primary key (order_num),
 foreign key (part_num) references parts
);

spool off

-- end of 325lab03-2.sql