-- 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; */