Andre Long
					CISA 321 SQL 
					December 1, 2004
					Chapter 9 Homework


1)
	a)[Oracle & Access]
                     select 12 + 34
                     from dual;

	b) [Oracle]
                       select 12 + null                                    select substr('abcdefghi',3,4)
                       from dual;                                             from dual;

                       select  ' first ' || ' second '                    select length('abcdefg')
                       from dual;                                             from dual;

	     select  instr('abcdefg', 'cd')
	     from dual;

	     select instr('abcdefg', 'zz')
                       from dual;

	     select to_date('07-mar-2011') + 2
	     from dual;

	     select add_months(to_date('07-mar-2011'),2)
	     from dual;

	     select add_months(to_date('07-mar-2011'),24)
	     from dual;

	     select to_date('27-mar-2011') -  to_date('07-mar-2011')
	     from dual;

	     [Access]
	     select 12 + null
	     from dual;

	     select 'first' & 'second'
	     from dual;

	     select Mid('abcdefghij',3,4)
	     from dual;

	    select len('abcdefg')
	    from dual;

	    select InStr('abcdefg', 'cd')
	    from dual;

	    select #07-mar-2011# +2
	    from dual;                    

	    select  DateAdd('m',2,#07-mar-2011#)
	    from dual;

	    select DateAdd('y',2,#07-mar-2011#)
	    from dual;

	    select #27-mar-2011# - #07-mar-2011#
	    from dual;

2)
	a)[Oracle & Access]
	   select n,
                               n/3
	   from sec0908_test_numbers
	   order by n;

                   b) [Oracle]
	     select n,
	               n + 100
	     from sec0908_test_numbers;

	     select n,
	               5-n
	     from sec0908_test_numbers;

	      select n,
	               5 * n
	     from sec0908_test_numbers;

	      select n,
	               n / 10
	     from sec0908_test_numbers;

	      select n,
	               10 / n
	     from sec0908_test_numbers;

	      select n,
	               10 / n
	     from sec0908_test_numbers
                       where NOT (n=0);

	    select n,
                                 power(2, n)
	    from sec0908_test_numbers;

	    select n,
                                 sqrt(n)
	    from sec0908_test_numbers;

	     select n,
                                 sqrt(n)
	    from sec0908_test_numbers
	    where (n>=0);

	      select n,
                                 floor(n/3)
	    from sec0908_test_numbers;
	 
	     select n,
                                 mod(n,3)
	    from sec0908_test_numbers;

	      select n,
                                 sign(n)
	    from sec0908_test_numbers;

	     select n,
                                 abs(n)
	    from sec0908_test_numbers;

                     [Access]
                     select n,
                               n+100
                     from sec0908_test_numbers;

	      select n,
                               5-n
                     from sec0908_test_numbers;

	     select n,
                               5*n
                     from sec0908_test_numbers;

	    select n,
                               n / 10
                     from sec0908_test_numbers;

	     select n,
                                10 / n
                     from sec0908_test_numbers;

	      select n,
                                10 / n
                     from sec0908_test_numbers
                     where NOT (n=0);

	       select n,
                                2^n
                     from sec0908_test_numbers;

	      select n,
                                sqr(n)
                     from sec0908_test_numbers;

	     select n,
                                sqr(n)
                     from sec0908_test_numbers
	     where n>=0;

	      select n,
                                 n / 3
                     from sec0908_test_numbers;

	       select n,
                                  n mod 3
                     from sec0908_test_numbers;

	     select n,
                                sgn(n)
                     from sec0908_test_numbers;
	     
	     select n,
                                abs(n)
                     from sec0908_test_numbers;


3)
	a) Read sections 9-10 to 9-12

	b) select employee_id,
	    substr(first_name, 1,1) || ' . ' || ' ' || last_name  as employee_name
	    from l_employees
	    order by employee_id;


4)
	a)[Oracle]
	   drop table ex0904a;

	   create table ex0904a
                   (beg_date   date,
	    end_date   date);

	  insert into ex0904a
	  values ('21-dec-2020', '21-mar-2021');

	  commit;

	  select end_date  -  beg_date  as number_of_days
	  from ex0904a;


	  [Access]
	  drop table ex0904a;

	  create table ex0904a
	  (beg_date    datetime,
	   end_date    datetime);

	  insert into ex0904a
	  values (#21-dec-2020#, #21-mar-2021#);

	  select end_date  -  beg_date  as number_of_days
	  from ex0904a;

	b) [Oracle]
	    insert into ex0904a
	    values ('29-jul-1969', '1-jan-2000');
	    select end_date  -  beg_date  as number_of_days
                    from ex0904a;
	

	   [Access]
	    insert into ex0904a
	    values (#29-jul-1969#, #1-jan-2000#);
	    select end_date  -  beg_date  as number_of_days
                    from ex0904a;