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

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

spool 325lab03-1-out.txt

-- drop if it exists,
-- and then create a table using the SQL create table
-- command:

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)
);

-- the basic insert for inserting a NEW row
--    can only insert ONE row at a time
-- (don't worry, the DBMS often has tools
--    for slurping in CSV file data into
--    database tables...! e.g., SQL*Loader)

-- NOTE!!!!!!!!!!!!!!!!!!!!!!!!!
-- string literals in SQL are written in
--    SINGLE QUOTES!!!!!!!!!!!!!

-- default format for using insert to insert
--    a date:
--    '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, although we can't see
--     that yet

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

-- the simplest SQL SELECT statement:
-- *   see ALL the contents (all the rows,
--     all the columns) of a table:
--
--     SELECT *
--     FROM tblname;

select *
from   parts;   

/*
 a DBMS often supports DOMAIN INTEGRITY --
    only allowing rows to have column values
    that make sense for each columns' meanings --
    with additional DOMAIN CONSTRAINTS

    ...put these in the create table statement (usually)
       AFTER an attribute's type but before the
       comma

 you can say that a column MUST have a value
    (it cannot be empty) with NOT NULL

    maxpoints integer not null,

 you can say that, if a value is not specified for
    a column, give it a default value, with DEFAULT

    quantity integer default 1,

  you can say that a column must contain one
     of a small number of values using IN
     (below, a car_color must be IN the set
     of 'red', 'green', or 'turquoise')

     car_color varchar2(9) IN ('red', 'green', 'turquoise'),

 you can specify that a condition MUST be true
     for a domain's value with CHECK

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

     this is also supported: (BETWEEN is inclusive...)

     quiz_grade integer check(quiz_grade between 0 and 100),

*/

/* here is a new table */

drop table orders cascade constraints;

create table orders
(order_num   char(6),
 cust_num    char(8) not null,
 part_num    integer,
 order_date  date,
 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-1.sql