Andre Long
						CISA 321 SQL
						December 2, 2004
						Chapter 10 Homework


1)
	a)[Oracle]
	select employee_id,
	       first_name,
	       last_name,
	       nvl(hire_date, '01-jan-1900') as hire_date
	from l_employees;

	  [Access]
	select employee_id,
	       first_name,
	       last_name,
	       nz(hire_date, '1/1/1900') as hire_dates
	from l_employees;

	select employee_id,
	       first_name,
	       last_name,
	       nz(hire_date, #1/1/1900#) as hire_dates
	from l_employees;

	b) [Oracle]
           select employee_id,
		  first_name,
		  last_name,
		  nvl(phone_number, '0000') as phone_number
	   from l_employees;	


2)
	a) Read section 10-9
	b) Find a solution for x^3-67x^2+5x-718=0
	   between 0 and 99 within two decimal places
	   [Oracle]
    step1  select n,
		 (n*n*n*3-n*n*67+n*5-718) as value_of_function
	   from numbers_0_to_99
	   order by n;	
           Conclusion=there is a solution between 22 & 23

    step2  create or replace view sec1009 as
	   select n,
		  22 + (n/100) as m
	   from numbers_0_to_99;
    display view sec1009
	   select * from sec1009;	

    step3  select m,
		 (m*m*m*3-m*m*67+m*5-718) as value_of_function
	   from sec1009
	   order by (m*100);	
	   Conclusion= there is a solution (two digit accuacy)
		       between 22.72 and 22.73

	   [Access]
    step1  select n,
		 (n*n*n*3-n*n*67+n*5-718) as value_of_function
	   from numbers_0_to_99
	   order by n;	
	   Conclusion=there is a solution between 22 & 23

    step2  select n,
		  22 + (n/100) as m
	   from numbers_0_to_99;

    step3  select m,
		 (m*m*m*3-m*m*67+m*5-718) as value_of_function
	   from sec1009
	   order by (m*100);	
	   Conclusion= there is a solution (two digit accuacy)
		       between 22.72 and 22.73


3)
	a)Read section 10-11
	b)[Oracle]
    step1 drop table sec1011_boundries;
	  create table sec1011_boundries
	  (start_date    date,
	   end_date      date);

	  insert into sec1011_boundries
	  values ('24-may-2005', '25-jul-2005');
	  commit;
	
    step2 drop table sec1011_calendar;
	  create table sec1011_calendar as
	  select n,
		 start_date + n as date_1
	  from numbers_0_to_99, sec1011_boundries
	  where start_date + n < end_date;

    step3 delete from sec1011_calendar
	  where( (to_char(date_1, 'dy')='mon' ) OR
		 (to_char(date_1, 'dy')='tue' ) OR
		 (to_char(date_1, 'dy')='wed' ) OR
		 (to_char(date_1, 'dy')='thu' )  );
	  update sec1011_calendar
	     set date_1=null
	  where to_char(date_1, 'dy')='fri';
	  commit;

    step4     set null ' ';
	  column day_of_the_week format a15;
	  select to_char(date_1, 'day') as day_of_the_week,
	         to_char(date_1, 'mm/dd/yyyy') as week_end
	  from sec1011_calendar
	  order by n;


	[Access]
   step1    drop table sec1011_boundries;

	create table  sec1011_boundries
	(start_date      datetime,
	 end_date       datetime);

	insert into sec1011_boundries
	values (#24-may-2005#, #30-jul-2005#);          

   step2   select n,
	         cdate(start_date + n) as date_1
	into sec1011_calendar
	from numbers_0_to_99, sec1011_boundries 
	where start_date + n < end_date;

   step3   delete from sec1011_calendar
	where format (date_1, 'ddd') = 'mon'  OR
	           format (date_1, 'ddd') = 'tue'    OR
                           format (date_1, 'ddd') = 'wed'  OR
                           format (date_1, 'ddd') = 'thu'   ;

	update sec1011_calendar
	set date_1 = null
	where format(date_1, 'ddd') = 'fri';	

   step4    select  format(date_1, 'dddd') as day_of_the_week,
	            format(date_1, 'mm/dd/yyyy') as week_end
	from sec1011_calendar
	order by n;


	c) [Access]
	drop table calendar_Andre;

	create table calendar_Andre
	( start_date        datetime,
	  end_time         datetime);

	insert into calendar_Andre
	values (#24-may-1966#, #24-may-2066#);

	select start_date,
	           end_date,
	          #24-may-2066# -  #24-may-1966#   as days_of_life
	from calendar_Andre;	


4)
	a)[Access]
	drop table US;

	create table US
	( start_date      datetime );
	 

	insert into US
	values (#04-jul-1776#);	

	select start_date,
	          
	           #04-jul-1776# + 100000 as Age_of_US
	from US;