Andre Long
						cisa 321 SQL 
						November 23, 2004
						Chapter 8 homework


1)a)
     [Oracle]
    drop table valid_lunch_dates cascade constraints;
    create table valid_lunch_dates as
    select distinct lunch_date
    from l_lunches;

    insert into valid_lunch_dates
    values ('15-dec-2005');

    commit;

    alter table valid_lunch_dates
    add constraint pk_valid_dates
    primary key (lunch_date);

    alter table l_lunches
    add constraint fk_lunch_date
    foreign key (lunch_date)
          references valid_lunch_dates (lunch_date);

   [Access]
   select distinct lunch_date
   into valid_lunch_dates
   from l_lunches;

   insert into valid_lunch_dates
   values(#15-dec-2005#);

  alter table valid_lunch_dates
  add constraint pk_valid_dates
  primary key (lunch_date);

  alter table l_lunches
  add constraint fk_lunch_date
  foreign key (lunch_date)
         references valid_lunch_dates (lunch_date);

b)drop table valid_phone_numbers;
    create table valid_phone_numbers as
    select distinct phone_number
    from l_employees;


//This section does not work for Oracle (1B) so I was instructed to complete assignment in Access
[Access]
drop table valid_phone_numbers;

create table valid_phone_numbers
(employee_phone_number  varchar2(10));

select phone_number
from l_employees;

insert into valid_phone_numbers
values('4907');
  

2)
	a) [oracle]
                    alter table l_lunch_items
                    add constraint ck_quantity
                    check (quantity in (1, 2) );

                    [Access]
                    Do the same thing inside GUI and add validation rule " =1 OR =2"

	b)[Oracle]

                  update l_foods                    //Before you can add a constraint in l_foods, we must remove null values in the
                  set price_increase=0         //price_increase coloumn and put zerp in its place
                  where price_increase is null ;
                   
                   alter table l_foods
                   add constraint min_price_increase
                   check (price_increase <(.25*price) );

3)
	a)alter table l_departments
                   add constraint nn_dept_name
                   check (department_name is not null);
                    
                  [Access]
                   See sample data base LongA_SQLFUN2002
                   L_Lunches:Table

	b)alter table l_foods
                   add constraint description
                   check(description is not null);