Andre Long
								cisa 321 SQL
								November 10, 2004



				        Chapter 7 Homework


1)
	a) [Oracle]
                    drop table ex0701a;
                    create table ex0701a
                    (my_date      date);

                     insert into ex0701a
                     values('12-oct-2015');

                     commit;

                     select to_char(my_date, 'month dd, yyyy')
                                                                       as usual_date_format,
                                to_char(my_date,   'day') as weekday,
                                to_char(my_date,   'ddd') as day_of_year,
                                to_char(my_date,   'q') as quarter,
                                to_char(my_date,   'ww') as week_of_year
                    from ex0701a;

                    [Access]
                    drop table ex0701a;
                    create table ex0701a
                    (my_date      datetime);

                    insert into ex0701a
                    values(#12-oct-2015#);

                    select format(my_date, 'mmmm dd, yyyy')
                                                                          as usual_date_format,
                               format(my_date,      'dddd') as weekday,
                               format(my_date,      'y') as day_of_year,
                               format(my_date,      'q') as quarter,
                               format(my_date,      'ww') as week_of_year
                    from ex0701a;


	b) drop table my_days;
                    create table my_days
                    (my_seq_id        number,
                     my_event          varchar2(25),
                     my_date            date);

                   alter table my_days
                   add constraint pk_my_days
                   primary key (my_seq_id);

                   drop sequence seq_my_days;
                   create sequence seq_my_days
                   start with 1
                   increment by 1;

                   insert into my_days
                   values (seq_my_days.nextval,
                   'birth date', '31-dec-1969');

                   insert into my_days
                   values (seq_my_days.nextval,
                   'college graduation', '24-jun-2006');

                   insert into my_days
                   values (seq_my_days.nextval,
                   'wedding', '14-feb-1994');

                   commit;

                   select my_seq_id,
                              my_event,
                              to_char(my_date, 'day month dd, yyyy') as my_date from my_days
                   order by my_seq_id;

                   [Access]
                   drop table my_days;

                   create table my_days
                   (my_seq_id          counter,
                    my_event            text(25),
                    my_date              datetime);

                  alter table my_days
                  add constraint pk_my_days
                  primary key (my_seq_id);

                  insert into my_days (my_event, my_date)
                  values('birth date', #16-feb-1969#);

                  insert into my_days (my_event, my_date)
                  values('college graduation', #24-jun-2006#);
                
                  insert into my_days (my_event, my_date)
                  values('wedding' , #14-feb-2005#);

                  select my_seq_id,
                             my_event,
                             format (my_date, 'dddd mmmm dd, yyyy') as my_date2
                  from my_days
                  order by my_seq_id;


2)	
	a)[Oracle]
                   insert into ex0701a
                   values(to_date('14-oct-2015  14:05', 'dd-mon-yyyy hh24:mi') );

                   [Access]
                   insert into ex0701a
                   values(#14-oct-2015 14:05#);

	b) insert into my_days
                    values(seq_my_days.nextval, 'New Job', '17-dec-04 14:05');


3)
	a) See section 7-12

	b) select   column_id,
                                 column_name,              //Note: The instructor (Mr Wei) and I could not get this code to run
                                 data_type,
                                 data_length,
                                 data_precision,
                                 data_scale,
                                 nullable
                    from user_tab_columns
                    where ( table_name = 'my_days' or table_name ='ex0701a')
                    order by column_id;


4)
	a) See section 7-15

	b) select object_name,
                               object_type,
                               created,
                               last_ddl_time,
                               status
                    from user_objects;