drop table offering;

create table student
(
  stdssn 	varchar2(11),
  stdfirstname	varchar2(50),
  stdlastname	varchar2(50),
  stdcity	varchar2(50),
  stdstate	varchar2(2),
  stdzip	varchar2(10),
  stdmajor	varchar2(6),
  stdclass	varchar2(2),
  stdgpa	varchar2(5)
);

create table faculty
(
  facssn	varchar2(11),
  facfirstname	varchar2(50),
  faclastname	varchar2(50),
  faccity	varchar2(50),
  facstate	varchar2(2),
  facdept	varchar2(6),
  facrank	varchar2(4),
  facsalary	varchar2(12),
  facsupervisor	varchar2(11), 
  fachiredate	date,
  faczipcode	varchar2(10)
);

create table course
(
  courseno	varchar2(6),
  crsdesc	varchar2(250),
  crsunits	number
);

create table enrollment
(
  offerno	varchar2(4),
  stdssn	varchar2(11),
  enrgrade	varchar2(5)
);

create table offering
(
  offerno	varchar2(4),
  courseno	varchar2(6),
  offterm	varchar2(10),
  offyear	varchar2(4),
  offlocation	varchar2(6),
  offtime	varchar2(10),
  facssn	varchar2(11),
  offdays	varchar2(5)
);

insert into student values('123-45-6789', 'Homer', 'Wells', 'Seattle', 'WA', '98121-1111', 'IS', 'FR', '3.00');
insert into student values('124-56-7890', 'Norbert', 'Bothell', 'WA', '98011-2121', 'FIN', 'JR', '2.70');
insert into student values('234-56-7890', 'Candy', 'Kendall', 'Tacoma', 'WA', '99042-3321', 'ACCT', 'JR', '3.50');

insert into enrollment values('1234', '123-45-6789', '3.3');
insert into enrollment values('1234', '234-56-7890', '3.5');
insert into enrollment values('4321', '123-45-6789', '3.5');
insert into enrollment values('4321', '124-56-7890', '3.2');

insert into offering values('1111', 'IS320', 'SUMMER', '2000', 'BLM302', '10:30 AM', null, 'MW');
insert into offering values('1234', 'IS320', 'FALL', '1999', 'BLM302', '10:30 AM', '098-76-5432', 'MW');
insert into offering values('2222', 'IS460', 'SUMMER', '1999', 'BLM412', '1:30 PM', null, 'TTH');
insert into offering values('3333', 'IS320', 'SPRING', '2000', 'BLM214', '8:30 AM', '098-76-5432', 'MW');
insert into offering values('4321', 'IS320', 'FALL', '1999', 'BLM214', '3:30 PM', '098-76-5432', 'TTH');
insert into offering values('4444', 'IS320', 'SPRING', '2000', 'BLM302', '3:30 PM', '543-21-0987', TTH');
insert into offering values('5678', 'IS480', 'SPRING', '2000', 'BLM302', '10:30 AM', '987-65-4321', 'MW');
insert into offering values('5679', 'IS480', 'SPRING', '2000', 'BLM412', '3:30 PM', '876-54-3210', 'TTH');
insert into offering values('9876', 'IS460', 'SPRING', '2000', 'BLM307', '1:30 PM', '654-32-1098', 'TTH');

insert into course values('IS320', 'Fundamentals of Business', 4);
insert into course values('IS460', 'Systems Analysis', 4);
insert into course values('IS470', 'Business Data Communications', 4);
insert into course values('IS480', 'Fundamentals of Database', 4);

insert into faculty values('098-76-5432', 'Leonard', 'Vince', 'Seatle', 'WA', 'MS', 'ASST', '$35,000', '654-32-1098', '01-APR-90', '98111-9921');
insert into faculty values('543-21-0987', 'Victoria', 'Emmanuel', 'Bothell', 'WA', 'MS', 'PROF', '$120,000', null, '01-APR-91', '98011-2242');
insert into faculty values('654-32-1098', 'Leonard', 'Fibon', 'Seatle', 'WA', 'MS', 'ASSC', '$70,000', '543-21-0987', '01-APR-89', '98121-0094');
insert into faculty values('765-43-2109', 'Nicki', 'Macon', 'Bellevue', 'WA', 'FIN', 'PROF', '$65,000', null, '01-APR-92', '98015-9945');
insert into faculty values('876-54-3210', 'Christopher', 'Colan', 'Seatle', 'WA', 'MS', 'ASST', '$40,000', '654-32-1098', '01-APR-94', '98114-1332');
insert into faculty values('987-65-4321', 'Julia', 'Mills', 'Seatle', 'WA', 'FIN', 'ASSC', '$75,000', '765-43-2109', '01-APR-95', '98114-9954');