-- drop all tables in proper order ----------------------------------

DROP TABLE class;
DROP TABLE course;
DROP TABLE address;
DROP TABLE instructor_info;
DROP TABLE instructor;
DROP TABLE student_email;
DROP TABLE student;
 
DROP TABLE payment;
DROP TABLE hierarchy;
DROP TABLE relationship;
DROP TABLE addrbook;
DROP TABLE output;

DROP VIEW  v_key_column;
DROP MATERIALIZED VIEW v_payment_total 

-- Create course table ----------------------------------------------

CREATE TABLE course
(
	course_id    CHAR(8)       NOT NULL PRIMARY KEY,
	description  VARCHAR2(40)  NOT NULL,
	price        INT           NOT NULL
);

INSERT INTO course VALUES('X52-9272','SQL Programming Language',540);
INSERT INTO course VALUES('X52-9759','XML for Web Development',1095);
INSERT INTO course VALUES('X52-9740','Web Page Development with HTML',1095);
INSERT INTO course VALUES('X52-9238','Introduction to Java',1095);
INSERT INTO course VALUES('X52-9742','Intensive Web Development',3995);
INSERT INTO course VALUES('X52-9755','JavaScript',1095);
INSERT INTO course VALUES('X52-9267','Object Oriented Analysis/Design',995);
INSERT INTO course VALUES('X52-9562','Java web Services',1095);

commit;

-- Create instructor table ------------------------------------------

DROP   SEQUENCE seq_instructor;
CREATE SEQUENCE seq_instructor;

CREATE TABLE instructor
(
	instructor_id INT           NOT NULL    PRIMARY KEY,
	lname         VARCHAR2(20)  NOT NULL,
	fname         VARCHAR2(20)  NOT NULL,
	ssn           CHAR(11)      NOT NULL 	UNIQUE,
	sex           CHAR(1)       NOT NULL 	CHECK (sex = 'M' OR sex = 'F')
);

CREATE INDEX inst_name_idx ON instructor (lname, fname);

INSERT INTO instructor VALUES(seq_instructor.NEXTVAL,'Sultan','Sam','000-02-0001','M');
INSERT INTO instructor VALUES(seq_instructor.NEXTVAL,'Pefanis','George','000-02-0002','M');
INSERT INTO instructor VALUES(seq_instructor.NEXTVAL,'martin','susan','000-02-0003','F');
INSERT INTO instructor VALUES(seq_instructor.NEXTVAL,'Paller','Marc','000-02-0004','M');
INSERT INTO instructor VALUES(seq_instructor.NEXTVAL,'O''Brien','Mary','000-02-0005','F');
INSERT INTO instructor VALUES(seq_instructor.NEXTVAL,'Katz','Eric','000-02-0006','M');

commit;

-- Create student table ---------------------------------------------

DROP   SEQUENCE seq_student;
CREATE SEQUENCE seq_student;

CREATE TABLE student 
(
	student_id INT           NOT NULL,
	lname      VARCHAR2(20)  NOT NULL,
	fname      VARCHAR2(20)  NOT NULL,
	ssn        CHAR(11)      NOT NULL,
	sex        CHAR(1)       NOT NULL,
--
	CONSTRAINT student_pk  PRIMARY KEY (student_id), 
	CONSTRAINT stu_ssn_uq  UNIQUE      (ssn), 
	CONSTRAINT stu_sex_chk CHECK       (sex IN ('M', 'F'))
);

CREATE INDEX stu_name_idx ON student (lname, fname);

INSERT INTO student VALUES(seq_student.NEXTVAL,'Burns','Barbara','000-01-0001','F');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Cambria','Vincent','000-01-0002','M');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Davidson','Duncan','000-01-0003','M');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Runyan','David','000-01-0004','M');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Thomas','Eugene','000-01-0005','M');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Owens','Cynthia','000-01-0006','F');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Willis','Eileen','000-01-0007','F');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Myers','Rick','000-01-0008','M');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Ryan','Natasha','000-01-0009','F');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Stack','Patrick','000-01-0010','M');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Tobias','Wayne','000-01-0011','M');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Race','Joseph','000-01-0012','M');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Nelson','Colette','000-01-0013','F');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Brinson','Angel','000-01-0014','F');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Soley','John','000-01-0015','M');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Grace','Robert','000-01-0016','M');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Tok','Kathy','000-01-0017','F');
INSERT INTO student VALUES(seq_student.NEXTVAL,'miller','janet','000-01-0018','F');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Austin','Maria','000-01-0019','F');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Teagan','Edward','000-01-0020','M');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Milgrom','Anya','000-01-0021','F');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Vasquez','Lillian','000-01-0022','F');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Chan','David','000-01-0023','M');
INSERT INTO student VALUES(seq_student.NEXTVAL,'James','Phyllis','000-01-0024','F');
INSERT INTO student VALUES(seq_student.NEXTVAL,'Ramos','Michael','000-01-0025','M');

commit;

-- Create instructor info table -------------------------------------

CREATE TABLE instructor_info
(
        instructor_id  INT          NOT NULL,
        active_status  CHAR(1)		DEFAULT 'A',
        start_date     DATE         DEFAULT SYSDATE,
        specialty      VARCHAR2(100),
--
	CONSTRAINT inst_info_pk  PRIMARY KEY (instructor_id),  
	CONSTRAINT inst_info_fk  FOREIGN KEY (instructor_id) REFERENCES instructor (instructor_id),
	CONSTRAINT inst_stat_chk CHECK       (active_status = 'A' OR active_status = 'I')
);
 
INSERT INTO instructor_info VALUES(1,'A','01-SEP-1999','Web technology, Java, XML, SQL');
INSERT INTO instructor_info VALUES(2,'A','15-FEB-2000','ASP, .NET, HTML');
INSERT INTO instructor_info VALUES(3,'A','01-JUL-1998',NULL);
INSERT INTO instructor_info VALUES(4,'A','01-JAN-1995','Oracle, DB2');
INSERT INTO instructor_info VALUES(5,'I','25-MAR-2003',NULL);
INSERT INTO instructor_info VALUES(6,'A','15-MAY-2001','JavaScript, C, Flash');

commit;

-- Create student email table  --------------------------------------

  DROP SEQUENCE seq_student_email;
CREATE SEQUENCE seq_student_email;

CREATE TABLE student_email 
(
	email_id    INT  NOT NULL,
	student_id  INT  NOT NULL,
	email       VARCHAR2(100)  
);

ALTER TABLE student_email
  ADD CONSTRAINT stu_email_pk PRIMARY KEY (email_id)
  ADD CONSTRAINT stu_email_fk FOREIGN KEY (student_id) REFERENCES student (student_id);

CREATE INDEX stu_email_idx ON student_email (student_id);	-- index on FK is advisible

INSERT INTO student_email VALUES(seq_student_email.NEXTVAL,1,'barbara.burns@nyu.edu');
INSERT INTO student_email VALUES(seq_student_email.NEXTVAL,2,'vcambria@yahoo.com');
INSERT INTO student_email VALUES(seq_student_email.NEXTVAL,3,'duncan.davidson@nyu.edu');
INSERT INTO student_email VALUES(seq_student_email.NEXTVAL,3,'davidson123@aol.com');
INSERT INTO student_email VALUES(seq_student_email.NEXTVAL,5,'ethomas@xyz.com');
INSERT INTO student_email VALUES(seq_student_email.NEXTVAL,8,'rick.myers@nyu.edu');
INSERT INTO student_email VALUES(seq_student_email.NEXTVAL,8,'rick.myers@abc.com');
INSERT INTO student_email VALUES(seq_student_email.NEXTVAL,8,'myers101@aol.com');
INSERT INTO student_email VALUES(seq_student_email.NEXTVAL,12,'joerace321@xyz.tv');
INSERT INTO student_email VALUES(seq_student_email.NEXTVAL,15,'soleyj@nyu.edu');

commit;

-- Create address table ---------------------------------------------

DROP   SEQUENCE seq_address;
CREATE SEQUENCE seq_address;

CREATE TABLE address
(
        address_id INT           NOT NULL,
        stu_id	   INT		     REFERENCES student    (student_id),
        inst_id	   INT		     REFERENCES instructor (instructor_id),
        addr_type  CHAR(1)       NOT NULL,
        street1    VARCHAR2(50)  NOT NULL,
        street2    VARCHAR2(50),
        city       VARCHAR2(25)  NOT NULL,
        state      CHAR(2),
        country    VARCHAR2(20),
--
	CONSTRAINT address_pk PRIMARY KEY (address_id)
);

CREATE INDEX addr_stu_idx  ON address (stu_id);			-- index on FK is advisible
CREATE INDEX addr_inst_idx ON address (inst_id);		-- index on FK is advisible

INSERT INTO address VALUES(seq_address.NEXTVAL,'',1, 'H','123 Main Street','','Bronx','NY','');
INSERT INTO address VALUES(seq_address.NEXTVAL,'',1, 'W','7 East 12 Street','','New York','NY','');
INSERT INTO address VALUES(seq_address.NEXTVAL,15,'','H','828 Eight Avenue',null,'New York','NY','');
INSERT INTO address VALUES(seq_address.NEXTVAL,3,'', 'H','222 Second Avenue',null,'Brooklyn','NY','');
INSERT INTO address VALUES(seq_address.NEXTVAL,3,'', 'W','126 Madison Avenue',null,'Brooklyn','NY','');
INSERT INTO address VALUES(seq_address.NEXTVAL,'',3, 'H','5 West 4th Street','Apt 44','New York','NY','');
INSERT INTO address VALUES(seq_address.NEXTVAL,5,'', 'W','323 Third Avenue',null,'Bronx','NY','');
INSERT INTO address VALUES(seq_address.NEXTVAL,7,'', 'H','424 Forth Avenue',null,'New York','NY','');
INSERT INTO address VALUES(seq_address.NEXTVAL,9,'', 'W','525 Fifth Avenue',null,'Yonkers','NY','');
INSERT INTO address VALUES(seq_address.NEXTVAL,1,'', 'H','121 First Avenue',null,'New York','NY','');
INSERT INTO address VALUES(seq_address.NEXTVAL,11,'','W','626 Sixth Avenue',null,'Hoboken','NJ','');
INSERT INTO address VALUES(seq_address.NEXTVAL,13,'','H','727 Seventh Avenue',null,'New York','NY','');
INSERT INTO address VALUES(seq_address.NEXTVAL,17,'','W','929 Ninth Avenue',null,'Brooklyn','NY','');
INSERT INTO address VALUES(seq_address.NEXTVAL,19,'','H','101 Tenth Avenue',null,'Newark','NJ','');
INSERT INTO address VALUES(seq_address.NEXTVAL,'',5, 'H','121 Eleventh Avenue',null,'New York','NY','');
INSERT INTO address VALUES(seq_address.NEXTVAL,'',5, 'W','48 East 42 Street',null,'New York','NY','');
INSERT INTO address VALUES(seq_address.NEXTVAL,21,'','H','1 Ontario Drive',null,'Toronto',null,'Canada');
INSERT INTO address VALUES(seq_address.NEXTVAL,21,'','W','1155 Browdway',null,'New York','NY','');

commit;

-- Create class table -----------------------------------------------

DROP   SEQUENCE seq_class;
CREATE SEQUENCE seq_class;

CREATE TABLE class
(
	class_id    INT           NOT NULL,
	course_id   CHAR(8)       NOT NULL,
	session_id  INT           NOT NULL,
	inst_ssn    CHAR(11)      NOT NULL,
	stu_ssn     CHAR(11)      NOT NULL,
--
	CONSTRAINT class_pk    PRIMARY KEY (class_id),
	CONSTRAINT course_fk   FOREIGN KEY (course_id) REFERENCES course (course_id),
	CONSTRAINT inst_ssn_fk FOREIGN KEY (inst_ssn)  REFERENCES instructor (ssn),
	CONSTRAINT stu_ssn_fk  FOREIGN KEY (stu_ssn)   REFERENCES student (ssn),
	CONSTRAINT course_uniq UNIQUE      (course_id, inst_ssn, stu_ssn)
);

CREATE INDEX class_course_idx  ON class (course_id);		-- index on FK is advisible
CREATE INDEX class_inst_idx    ON class (inst_ssn);		    -- index on FK is advisible
CREATE INDEX class_stu_idx     ON class (stu_ssn);		    -- index on FK is advisible

INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9272',1,'000-02-0001','000-01-0002');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9272',1,'000-02-0001','000-01-0004');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9272',1,'000-02-0001','000-01-0006');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9272',1,'000-02-0001','000-01-0008');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9272',1,'000-02-0001','000-01-0010');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9272',1,'000-02-0001','000-01-0012');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9272',1,'000-02-0001','000-01-0014');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9272',1,'000-02-0001','000-01-0016');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9272',1,'000-02-0001','000-01-0018');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9272',1,'000-02-0001','000-01-0020');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9272',1,'000-02-0001','000-01-0022');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9759',1,'000-02-0001','000-01-0001');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9759',1,'000-02-0001','000-01-0003');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9759',1,'000-02-0001','000-01-0005');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9759',1,'000-02-0001','000-01-0007');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9759',1,'000-02-0001','000-01-0008');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9759',1,'000-02-0001','000-01-0010');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9759',1,'000-02-0001','000-01-0013');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9759',1,'000-02-0001','000-01-0015');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9759',1,'000-02-0001','000-01-0017');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9759',1,'000-02-0001','000-01-0019');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',1,'000-02-0002','000-01-0001');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',1,'000-02-0002','000-01-0002');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',1,'000-02-0002','000-01-0003');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',1,'000-02-0002','000-01-0004');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',1,'000-02-0002','000-01-0005');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',1,'000-02-0002','000-01-0006');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',1,'000-02-0002','000-01-0007');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',1,'000-02-0002','000-01-0008');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',1,'000-02-0002','000-01-0009');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',1,'000-02-0002','000-01-0010');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',2,'000-02-0003','000-01-0011');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',2,'000-02-0003','000-01-0012');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',2,'000-02-0003','000-01-0013');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',2,'000-02-0003','000-01-0014');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',2,'000-02-0003','000-01-0015');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',2,'000-02-0003','000-01-0016');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',2,'000-02-0003','000-01-0017');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',2,'000-02-0003','000-01-0018');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',2,'000-02-0003','000-01-0019');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',2,'000-02-0003','000-01-0020');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',2,'000-02-0003','000-01-0021');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9740',2,'000-02-0003','000-01-0022');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9755',1,'000-02-0006','000-01-0003');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9755',1,'000-02-0006','000-01-0006');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9755',1,'000-02-0006','000-01-0009');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9755',1,'000-02-0006','000-01-0012');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9755',1,'000-02-0006','000-01-0015');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9755',1,'000-02-0006','000-01-0018');
INSERT INTO class VALUES(seq_class.NEXTVAL,'X52-9755',1,'000-02-0006','000-01-0021');

commit;

-- Create payment table ---------------------------------------------

DROP   SEQUENCE seq_payment;
CREATE SEQUENCE seq_payment;
 
CREATE TABLE payment 
(	
	payment_num  INT 		        NOT NULL PRIMARY KEY,	
	vendor	     VARCHAR2(40)      	NOT NULL,	
	amount       DECIMAL(6,2)       DEFAULT 0,
	description  VARCHAR2(40)      	NOT NULL,
	pay_date     DATE		DEFAULT SYSDATE 
);

INSERT INTO payment  VALUES(seq_payment.NEXTVAL,'Con Edison',125.15,'Electric','01-FEB-2012');
INSERT INTO payment  VALUES(seq_payment.NEXTVAL,'Verizon',54.79,'Home Phone','01-MAR-2012');
INSERT INTO payment  VALUES(seq_payment.NEXTVAL,'Allstate',1240.50,'Home Insurance','02-MAR-2012');
INSERT INTO payment  VALUES(seq_payment.NEXTVAL,'Verizon',49.95,'Cell Phone','03-MAR-2012');
INSERT INTO payment  VALUES(seq_payment.NEXTVAL,'Verizon',39.95,'DSL Line','04-MAR-2012');
INSERT INTO payment  VALUES(seq_payment.NEXTVAL,'Con Edison',59.63,'Electric','05-MAR-2012');
INSERT INTO payment  VALUES(seq_payment.NEXTVAL,'Department of Water',155.43,'Water and Sewers','06-MAR-2012');
INSERT INTO payment  VALUES(seq_payment.NEXTVAL,'Allstate',1550.25,'Car Insurance','07-MAR-2012');

commit;

-- Create hierarchy table -------------------------------------------

DROP   SEQUENCE seq_hierarchy;
CREATE SEQUENCE seq_hierarchy;

CREATE TABLE hierarchy 
(	
	employee_id  INT 		        NOT NULL,	
	lname        VARCHAR2(20)      	NOT NULL,	
	fname        VARCHAR2(20)      	NOT NULL,	
	manager_id   INT,
--
	CONSTRAINT empl_id  PRIMARY KEY (employee_id)
);

ALTER TABLE hierarchy 
	ADD CONSTRAINT hier_mgr_fk   FOREIGN KEY (manager_id) REFERENCES hierarchy (employee_id); 

CREATE INDEX hier_name_idx ON hierarchy (lname, fname);

INSERT INTO hierarchy  VALUES(seq_hierarchy.NEXTVAL,'Burns','Barbara',NULL);
INSERT INTO hierarchy  VALUES(seq_hierarchy.NEXTVAL,'Tobias','Wayne',1);
INSERT INTO hierarchy  VALUES(seq_hierarchy.NEXTVAL,'Stack','Patrick',1);
INSERT INTO hierarchy  VALUES(seq_hierarchy.NEXTVAL,'Milgrom','Anya',2);
INSERT INTO hierarchy  VALUES(seq_hierarchy.NEXTVAL,'Vasquez','Lilian',4);
INSERT INTO hierarchy  VALUES(seq_hierarchy.NEXTVAL,'Davidson','Duncan',4);

commit;

-- Create family_tree table -------------------------------------------

DROP   SEQUENCE seq_relationship;
CREATE SEQUENCE seq_relationship;

CREATE TABLE relationship 
(	
    row_pk       INT 		    NOT NULL PRIMARY KEY,	
	from_id      INT      		NOT NULL,	
	to_id        INT      		NOT NULL	
);

INSERT INTO relationship  VALUES(seq_relationship.NEXTVAL,1,3);
INSERT INTO relationship  VALUES(seq_relationship.NEXTVAL,2,3);
INSERT INTO relationship  VALUES(seq_relationship.NEXTVAL,3,4);
INSERT INTO relationship  VALUES(seq_relationship.NEXTVAL,6,4);
INSERT INTO relationship  VALUES(seq_relationship.NEXTVAL,3,5);
INSERT INTO relationship  VALUES(seq_relationship.NEXTVAL,6,5);
INSERT INTO relationship  VALUES(seq_relationship.NEXTVAL,4,7);
INSERT INTO relationship  VALUES(seq_relationship.NEXTVAL,4,8);
INSERT INTO relationship  VALUES(seq_relationship.NEXTVAL,4,9);

commit;

-- Create addressBook table -------------------------------------------

CREATE TABLE addrbook 
(	
	lname        VARCHAR2(20) 	    NOT NULL,	
	fname        VARCHAR2(20)      	NOT NULL,	
	street       VARCHAR2(50)      	NOT NULL,	
	city         VARCHAR2(20)       NOT NULL,
	state        CHAR(2)            NOT NULL,
	zip          CHAR(5)            NOT NULL,
	phone        VARCHAR2(20)       NOT NULL
);

INSERT INTO addrbook VALUES('Anile', 'Joseph', '35 Forest Avenue', 'Glen Cove', 'NY', '12345', '(516)937-4021');
INSERT INTO addrbook VALUES('Brown', 'Robert', '96 Landing Road', 'Brooklyn', 'NY', '12345', '(718)921-7505');
INSERT INTO addrbook VALUES('Colin', 'Albert', '64 Lisa Drive', 'Plainview', 'NY', '12345', '(516)671-2895');
INSERT INTO addrbook VALUES('Davies', 'Todd', '129 Georgia Street', 'Forest Hills', 'NY', '12345', '(718)496-3717');
INSERT INTO addrbook VALUES('Eisner', 'Jack', '6 Sunset Drive', 'East Norwich', 'CT', '12345', '(215)759-1976');
INSERT INTO addrbook VALUES('Field', 'Diane', '254 West 79 Street', 'New York', 'NY', '12345', '(212)647-9372');
INSERT INTO addrbook VALUES('Gambi', 'Teresa', '12 Cypress Avenue', 'Syosset', 'NY', '12345', '(516)299-5478');
INSERT INTO addrbook VALUES('Hart', 'Richard', '31 Cherry Lane', 'Hicksville', 'NY', '12345', '(516)679-7920');
INSERT INTO addrbook VALUES('Izzo', 'Anthony', '342 Coleridge Drive', 'Locus Grove', 'NY', '12345', '(516)367-3066');
INSERT INTO addrbook VALUES('Johnson', 'Carl', '8 Bayview Drive', 'Bayville', 'NY', '12345', '(516)299-7643');
INSERT INTO addrbook VALUES('Sullivan', 'John', '22 Oakdale Avenue', 'Huntington', 'NY', '12345', '(516)453-3239');
INSERT INTO addrbook VALUES('Sultan', 'Sam', '123 Main Street', 'New York', 'NY', '12345', '(212)123-1234');
INSERT INTO addrbook VALUES('Sultan', 'Carol', '123 Main Street', 'New York', 'NY', '12345', '(212)123-1234');
INSERT INTO addrbook VALUES('Tillman', 'Christopher', '70 Oyster Bay Road', 'Oyster Bay', 'NY', '12345', '(516)922-4941');
INSERT INTO addrbook VALUES('Urich', 'Robert', '123 Hollywood Lane', 'Century City', 'CA', '12345', '(415)498-5301');
INSERT INTO addrbook VALUES('Valante', 'Vincent', '67 Redbank Road', 'Holmdel', 'NJ', '12345', '(212)123-1234');
INSERT INTO addrbook VALUES('Williams', 'Leonard', '1267 2nd Ave', 'New York', 'NY', '12345', '(212)765-3476');
INSERT INTO addrbook VALUES('Xu', 'Wei', '16 Jericho Turnpike', 'Jericho', 'NY', '12345', '(516)628-8309');
INSERT INTO addrbook VALUES('Yen', 'Jerry', '123 Johnson Circle', 'Bethpage', 'NY', '12345', '(631)327-8567');

commit;

-- Create table output ----------------------------------------------
 
CREATE TABLE output
(        
        line	VARCHAR2(4000) 
);
 
-- Create v_key_column view -----------------------------------------

CREATE VIEW v_key_column AS
SELECT ucc1.table_name, ucc1.column_name,
       uc.constraint_type, uc.constraint_name, ucc1.position, 
       ucc2.table_name AS ref_table_name, ucc2.column_name AS ref_column_name
FROM      user_constraints uc 
JOIN      user_cons_columns ucc1 ON uc.constraint_name   = ucc1.constraint_name
LEFT JOIN user_cons_columns ucc2 ON uc.r_constraint_name = ucc2.constraint_name 
WHERE     uc.constraint_type  IN ('P','R','U')
ORDER BY  1,3,2,4,5;

commit;

-- Create v_payment_total materialized view (refresh every 1/2 hour) ---------
/*
CREATE MATERIALIZED VIEW v_payment_total
   BUILD IMMEDIATE
   REFRESH COMPLETE START WITH sysdate NEXT sysdate+1/48         
   AS SELECT vendor, SUM(amount) AS tot_sales
      FROM payment
      GROUP BY vendor;

commit;
*/