Andre Long
									cisa 321 (SQL)
									November 1, 2004
									Chapter 6 homework


1)
	a)   [ ORACLE SQL ]
                      drop table boston_celtics_games;
                      create table boston_celtics_games
                      (
                          game_id              number,
                          game_date          date,
                          opponent              varchar2(30),
                          celtics_score       number,
                          opponent_score   number,
                          game_duration     number
                       );


                       [ ACCESS ]
                       drop table boston_celtics_games;
                       create table boston_celtics_games
                       (
                        game_id                  smallint,
                        game_date              datetime,
                        opponent                  varchar(30),
                        celtics_score           byte,
                        opponent_score       byte,
                        game_duration         smallint
                        );

	b) [ORACLE ]
                    drop table sunny_songs;
                    create table sunny_songs
                    (
                        Song_ID            number,
                        Song                 varchar2(50),
                        Artist                 varchar2(50),
                        Duration            interval day to second,                    //formatt example for interval and date  '+1 0:1:30'      and    '02-Nov-2004'
                        Year                   date
                     );


2) For problem#2 the loader is not functioning so I will load data into boston_celtics_games one row at a time

	a) insert into boston_celtics_games
                    values (1,'03-NOV-2007', 'NEW YORK KNICKS', 99, 98, 132,null);
                  
                    insert into boston_celtics_games
                    values (2,"07-NOV-2007", "LOS ANGELES", 108, 101, 140, null);

                    insert into boston_celtics_games
                    values (3,'12-NOV-2007', 'GOLDEN STATE WARRIORS', 97, 94, 118, null);

	    insert into boston_celtics_games
                    values (4,'17-NOV-2007', 'MIAMI HEAT', 103, 99, 116, null);

                    insert into boston_celtics_games
                    values (5,'23-NOV-2007', 'CHICAGO BULLS', 107, 103, 145, null);

                    insert into boston_celtics_games
                    values (6,'28-NOV-2007', 'HOUSTON ROCKETS', 102, 99, 121, null);


	Acces version { this data must be imported into access, because you can not loaded it via SQL }
	    1,11/03/2007, 'NEW YORK KNICKS', 99, 98, 132
	    2,11/07/2007, 'LOS ANGELES LAKERS', 108, 101, 140
	    3,11/12/2007, 'GOLDEN STATE WARRIORS', 97, 94, 118
	    4,11/17/2007, 'MIAMI HEAT', 103, 99, 116
	    5,11/23/2007, 'CHICAGO BULLS', 107, 103, 145
	    6,11/28/2007, 'HOUSTON ROCKETS', 102, 99, 121

	b) insert into sunny_songs
                    values (2, 'Red Light District', 'Ludicris', '+1 0:6:15', '15-Oct-2004');

                    insert into sunny_songs
                    values(3, 'Breath', 'Fabulist', '+1 0:10:20', '21-Oct-2004');

                    insert into sunny_songs
                    values(4, 'My DJ', 'Wayne', '+1 0:7:25', '28-Oct-2004');

                    insert into sunny_songs
                    values(5, 'Never Give Up', 'Too Short', '+1 0:7:15, '15-Sep-2004');

                    insert into sunny_songs
                    values(6, 'I Luv', 'Too Short', '+1 0:8:09', '30-Sep-2000');



3)
	   [Oracle & Access]
	a) 
	   alter table boston_celtics_games
	   add constraint pk_bc_games
	   primary key (game_id);


	b)
	   alter table sunny_songs
	   add constraint pk_sunny_songs
	   primary key (Song_ID);




4)
	a) [Oracle]
                     alter table boston_celtics_games
                     add game_comments varchar2(2000);

                     [Access]
                     alter table boston_celtics_games
                     add column game_comments longtext;



	b) alter table sunny_songs
                     add album_name varchar2(100);




5)
	a) update boston_celtics_games
                    set game_comments = 'This game was down to the wire.'
                    where game_id = 3;

	b) update sunny_songs
                    set album_name = 'Chicken and Beer'
                    where Song_ID = 2;

	    update sunny_songs
                    set album_name = 'Chase the Cat'
                    where Song_ID = 6;

                    
6)
	a)  [Oracle & Access]
                     insert into boston_celtics_games
                     values(7, '05-dec-2007', 'alaska huskies', 98, 105, 131, 'first game in alaska');

                     [Access]
                     insert into boston_celtics_games
                     values(7, #05-dec-2007#, 'alaska huskies', 98, 105, 131, 'first game in alaska');


	b) insert into sunny_songs
                     values(7, 'Today  was a good day', 'Ice Cube', '+1 0:7:13', '15-Nov-1992', 'Good Year Pimp');