-----
-- CIS 180 - Intro to SQL
-- Class 4 - 3-7-03
-----

-- last modified: 3-7-03

-----
-- REMINDER: hw3_setup.sql and set_up_ex_tbls.sql have MORE examples
-- of create table and insert statements!
-----

-- write script results to file 180class4_results.txt

spool 180class4_results.txt

-----
-- DROP table 
-----

-- drop table tbl;
--
-- OR, in a script where you KNOW you're deleting ALL in a "world",
-- drop table tbl cascade constraints;

drop table time_card;

-----
-- CREATE table
-----

create table time_card
(empl_num    char(4),
-- work_date   date     default(sysdate) not null,
-- (above inserts TIME and date, so problematic
 work_date   date       not null,
 hour_in     integer    default(800) check(hour_in between 0 and 2400),
 hour_out    integer    check(hour_out between 0 and 2400),  
 primary key (empl_num, work_date),
 foreign key (empl_num) references empl(empl_num)
);

-- let's describe our new table:
describe time_card

-- current contents: none
prompt New table time_card starts out empty:
prompt *************************************
select *
from   time_card;

-- (try to ignore --- this removes the employee I'm going to
-- insert below... I'd re-run set_up_ex_tbls.sql, but I don't know
-- how many of you have copied that over...)
delete from empl
where  empl_num = '9777';

-----
-- INSERT statement, VERSION #1
-----

-- to insert one row and fill ALL the columns in that row 

insert into time_card
values
('7369', '01-mar-2003', 900, 1400);

-- See, I now have a row in time_card!

select *
from time_card;

-- BAD - violating the check clause
prompt THIS is what happens if you try to insert a value 
prompt    violating a check clause:
prompt **************************************************
insert into time_card
values
('7369', '02-Mar-2003', 800, 2500);

-- BAD - putting in a time_card for a non-employee (violating
-- the foreign key constraint!)
prompt THIS is what happens if you try to put in a value for
prompt    a foreign key that ISN'T in the parent table:
prompt ******************************************************
insert into time_card
values
('9999', '02-Mar-2003', 800, 1700);

-----
-- INSERT STATEMENT - version 2
-----

-- PREFERRED version if you do not want to fill a particular column!

-- ONLY fills the columns stated after the table name

insert into empl(empl_last_name, empl_num, hiredate, dept_num, salary)
values
('Johnson', '9777', sysdate, '300', 1000);

-- see the new row?
select *
from   empl;

-- see how this one triggers the default for hour_in?

insert into time_card(hour_out, empl_num, work_date)
values
(1500, '9777', '06-Mar-2003');

-- see the new row, with default value 800 for hour_in?

select *
from   time_card;

-----
-- SOME Oracle data dictionary tables
-----

-- user_catalog --- what tables do I have?

select *
from   user_catalog;

-- MORE about your tables... user_tables (synonym: tabs)
-- (AVOID using with *...)

describe tabs

select table_name
from   tabs;

-- MORE about the columns in your tables ... user_tab_columns
-- (synonym: cols)
-- (AVOID using with *)

describe cols

select table_name, column_name
from cols;

-- stop writing results to file 180class4_results.txt

spool off