-- drop all tables ------------------------------------------------- DROP VIEW IF EXISTS v_student_roster; DROP VIEW IF EXISTS v_key_column; DROP TABLE IF EXISTS class; DROP TABLE IF EXISTS course; DROP TABLE IF EXISTS address; DROP TABLE IF EXISTS instructor_info; DROP TABLE IF EXISTS instructor; DROP TABLE IF EXISTS email_msg; DROP TABLE IF EXISTS student_email; DROP TABLE IF EXISTS student; DROP TABLE IF EXISTS payment; DROP TABLE IF EXISTS hierarchy; DROP TABLE IF EXISTS relationship; DROP TABLE IF EXISTS addrbook; DROP TABLE IF EXISTS output; -- create course table -------------------------------------------- CREATE TABLE course ( course_id CHAR(8) NOT NULL PRIMARY KEY, description VARCHAR(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 and Design',995); INSERT INTO course VALUES('X52-9562','Java Web Services',1095); -- create instructor table ----------------------------------------- CREATE TABLE instructor ( instructor_id SERIAL NOT NULL PRIMARY KEY, lname VARCHAR(20) NOT NULL, fname VARCHAR(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(DEFAULT,'Sultan','Sam','000-02-0001','M'); INSERT INTO instructor VALUES(DEFAULT,'Pefanis','George','000-02-0002','M'); INSERT INTO instructor VALUES(DEFAULT,'martin','susan','000-02-0003','F'); INSERT INTO instructor VALUES(DEFAULT,'Paller','Marc','000-02-0004','M'); INSERT INTO instructor VALUES(DEFAULT,'O''Brien','Mary','000-02-0005','F'); INSERT INTO instructor VALUES(DEFAULT,'Katz','Eric','000-02-0006','M'); -- create student table ------------------------------------------- CREATE TABLE student ( student_id SERIAL NOT NULL, lname VARCHAR(20) NOT NULL, fname VARCHAR(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(DEFAULT,'Burns','Barbara','000-01-0001','F'); INSERT INTO student VALUES(DEFAULT,'Cambria','Vincent','000-01-0002','M'); INSERT INTO student VALUES(DEFAULT,'Davidson','Duncan','000-01-0003','M'); INSERT INTO student VALUES(DEFAULT,'Smith','David','000-01-0004','M'); INSERT INTO student VALUES(DEFAULT,'Thomas','Eugene','000-01-0005','M'); INSERT INTO student VALUES(DEFAULT,'Owens','Cynthia','000-01-0006','F'); INSERT INTO student VALUES(DEFAULT,'Willis','Eileen','000-01-0007','F'); INSERT INTO student VALUES(DEFAULT,'Myers','Rick','000-01-0008','M'); INSERT INTO student VALUES(DEFAULT,'Ryan','Natasha','000-01-0009','F'); INSERT INTO student VALUES(DEFAULT,'Stack','Patrick','000-01-0010','M'); INSERT INTO student VALUES(DEFAULT,'Tobias','Wayne','000-01-0011','M'); INSERT INTO student VALUES(DEFAULT,'Race','Joseph','000-01-0012','M'); INSERT INTO student VALUES(DEFAULT,'Nelson','Colette','000-01-0013','F'); INSERT INTO student VALUES(DEFAULT,'Brinson','Angel','000-01-0014','F'); INSERT INTO student VALUES(DEFAULT,'Soley','John','000-01-0015','M'); INSERT INTO student VALUES(DEFAULT,'Grace','Robert','000-01-0016','M'); INSERT INTO student VALUES(DEFAULT,'Tok','Kathy','000-01-0017','F'); INSERT INTO student VALUES(DEFAULT,'miller','janet','000-01-0018','F'); INSERT INTO student VALUES(DEFAULT,'Austin','Maria','000-01-0019','F'); INSERT INTO student VALUES(DEFAULT,'Teagan','Edward','000-01-0020','M'); INSERT INTO student VALUES(DEFAULT,'Milgrom','Anya','000-01-0021','F'); INSERT INTO student VALUES(DEFAULT,'Vasquez','Lillian','000-01-0022','F'); INSERT INTO student VALUES(DEFAULT,'Chan','David','000-01-0023','M'); INSERT INTO student VALUES(DEFAULT,'James','Phyllis','000-01-0024','F'); INSERT INTO student VALUES(DEFAULT,'Sultan','Sam','000-02-0001','M'); -- create instructor_info table -------------------------------------- CREATE TABLE instructor_info ( instructor_id INT NOT NULL, active_status CHAR(1) DEFAULT 'A', start_date DATE, specialty VARCHAR(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','1999-09-01','Web technology, Java, XML, SQL'); INSERT INTO instructor_info VALUES(2,'A','2000-02-15','ASP, .NET, HTML'); INSERT INTO instructor_info VALUES(3,'A','1998-07-01',NULL); INSERT INTO instructor_info VALUES(4,'A','1995-01-01','Oracle, DB2'); INSERT INTO instructor_info VALUES(5,'I','2003-03-25',NULL); INSERT INTO instructor_info VALUES(6,'A','2001-05-15','JavaScript, C, Flash'); -- create student_email table -------------------------------------- CREATE TABLE student_email ( email_id SERIAL NOT NULL, student_id INT NOT NULL, email VARCHAR(100), CONSTRAINT stu_email_pk PRIMARY KEY (email_id) ); ALTER TABLE student_email ADD CONSTRAINT stu_email_fk FOREIGN KEY (student_id) REFERENCES student (student_id); INSERT INTO student_email VALUES(DEFAULT, 1,'barbara.burns@nyu.edu'); INSERT INTO student_email VALUES(DEFAULT, 2,'vcambria@yahoo.com'); INSERT INTO student_email VALUES(DEFAULT, 3,'duncan.davidson@nyu.edu'); INSERT INTO student_email VALUES(DEFAULT, 3,'davidson123@aol.com'); INSERT INTO student_email VALUES(DEFAULT, 5,'ethomas@xyz.com'); INSERT INTO student_email VALUES(DEFAULT, 8,'rick.myers@nyu.edu'); INSERT INTO student_email VALUES(DEFAULT, 8,'rick.myers@abc.com'); INSERT INTO student_email VALUES(DEFAULT, 8,'myers101@aol.com'); INSERT INTO student_email VALUES(DEFAULT, 12,'joerace321@xyz.tv'); INSERT INTO student_email VALUES(DEFAULT, 15,'soleyj@nyu.edu'); -- create email_msg table -------------------------------------------- CREATE TABLE email_msg ( msg_id SERIAL NOT NULL PRIMARY KEY, email_id INT NOT NULL, receipt_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, sender VARCHAR(99) NOT NULL, message VARCHAR(500) NOT NULL, CONSTRAINT email_msg_fk FOREIGN KEY (email_id) REFERENCES student_email (email_id) ); INSERT INTO email_msg VALUES(DEFAULT,1, '2018-07-21','sam.sultan@nyu.edu','This is my first email message'); INSERT INTO email_msg VALUES(DEFAULT,1, '2018-07-22','sam.sultan@nyu.edu','This is my second email message'); INSERT INTO email_msg VALUES(DEFAULT,3, '2018-07-23','sam.sultan@nyu.edu','Hello World'); INSERT INTO email_msg VALUES(DEFAULT,3, '2018-07-24','sam.sultan@nyu.edu','Goodbye World'); INSERT INTO email_msg VALUES(DEFAULT,4, '2018-07-25','sam.sultan@nyu.edu','Using a different email address'); INSERT INTO email_msg VALUES(DEFAULT,7, '2018-07-26','sam.sultan@nyu.edu','How are you?'); INSERT INTO email_msg VALUES(DEFAULT,7, '2018-07-27','sam.sultan@nyu.edu','Have not heard from you in a while'); INSERT INTO email_msg VALUES(DEFAULT,7, '2018-07-28','sam.sultan@nyu.edu','Are you there?'); INSERT INTO email_msg VALUES(DEFAULT,10,'2018-07-29','sam.sultan@nyu.edu','What a beautiful day to learn SQL'); -- create address table --------------------------------------------- CREATE TABLE address ( address_id SERIAL NOT NULL, stu_id INT, inst_id INT, addr_type CHAR(1) NOT NULL, street1 VARCHAR(50) NOT NULL, street2 VARCHAR(50), city VARCHAR(25) NOT NULL, state CHAR(2), country VARCHAR(20), CONSTRAINT address_pk PRIMARY KEY (address_id), CONSTRAINT addr_stu_id_fk FOREIGN KEY (stu_id) REFERENCES student (student_id), CONSTRAINT addr_inst_id_fk FOREIGN KEY (inst_id) REFERENCES instructor (instructor_id) ); CREATE INDEX addr_stu_idx ON address (stu_id); CREATE INDEX addr_inst_idx ON address (inst_id); INSERT INTO address VALUES(DEFAULT, null, 1, 'H','123 Main Street','','Bronx','NY',''); INSERT INTO address VALUES(DEFAULT, null, 1, 'W','7 East 12 Street','','New York','NY',''); INSERT INTO address VALUES(DEFAULT, 15, null, 'H','828 Eight Avenue',null,'New York','NY',''); INSERT INTO address VALUES(DEFAULT, 3, null, 'H','222 Second Avenue',null,'Brooklyn','NY',''); INSERT INTO address VALUES(DEFAULT, 3, null, 'W','126 Madison Avenue',null,'New York','NY',''); INSERT INTO address VALUES(DEFAULT, null, 3, 'H','5 West 4th Street','Apt 44','New York','NY',''); INSERT INTO address VALUES(DEFAULT, 5, null, 'W','323 Third Avenue',null,'Bronx','NY',''); INSERT INTO address VALUES(DEFAULT, 7, null, 'H','424 Forth Avenue',null,'New York','NY',''); INSERT INTO address VALUES(DEFAULT, 9, null, 'W','525 Fifth Avenue',null,'Yonkers','NY',''); INSERT INTO address VALUES(DEFAULT, 1, null, 'H','121 First Avenue',null,'New York','NY',''); INSERT INTO address VALUES(DEFAULT, 11, null, 'W','626 Sixth Avenue',null,'Hoboken','NJ',''); INSERT INTO address VALUES(DEFAULT, 13, null, 'H','727 Seventh Avenue',null,'New York','NY',''); INSERT INTO address VALUES(DEFAULT, 17, null, 'W','929 Ninth Avenue',null,'Brooklyn','NY',''); INSERT INTO address VALUES(DEFAULT, 19, null, 'H','101 Tenth Avenue',null,'Newark','NJ',''); INSERT INTO address VALUES(DEFAULT, null, 5, 'H','121 Eleventh Avenue',null,'New York','NY',''); INSERT INTO address VALUES(DEFAULT, null, 5, 'W','48 East 42 Street',null,'New York','NY',''); INSERT INTO address VALUES(DEFAULT, 21, null, 'H','1 Ontario Drive',null,'Toronto',null,'Canada'); INSERT INTO address VALUES(DEFAULT, 21, null, 'W','1155 Broadway',null,'New York','NY',''); -- create class table ---------------------------------------------- CREATE TABLE class ( class_id SERIAL 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); CREATE INDEX class_inst_idx ON class (inst_ssn); CREATE INDEX class_stu_idx ON class (stu_ssn); INSERT INTO class VALUES(DEFAULT,'X52-9272',1,'000-02-0001','000-01-0002'); INSERT INTO class VALUES(DEFAULT,'X52-9272',1,'000-02-0001','000-01-0004'); INSERT INTO class VALUES(DEFAULT,'X52-9272',1,'000-02-0001','000-01-0006'); INSERT INTO class VALUES(DEFAULT,'X52-9272',1,'000-02-0001','000-01-0008'); INSERT INTO class VALUES(DEFAULT,'X52-9272',1,'000-02-0001','000-01-0010'); INSERT INTO class VALUES(DEFAULT,'X52-9272',1,'000-02-0001','000-01-0012'); INSERT INTO class VALUES(DEFAULT,'X52-9272',1,'000-02-0001','000-01-0014'); INSERT INTO class VALUES(DEFAULT,'X52-9272',1,'000-02-0001','000-01-0016'); INSERT INTO class VALUES(DEFAULT,'X52-9272',1,'000-02-0001','000-01-0018'); INSERT INTO class VALUES(DEFAULT,'X52-9272',1,'000-02-0001','000-01-0020'); INSERT INTO class VALUES(DEFAULT,'X52-9272',1,'000-02-0001','000-01-0022'); INSERT INTO class VALUES(DEFAULT,'X52-9759',1,'000-02-0001','000-01-0001'); INSERT INTO class VALUES(DEFAULT,'X52-9759',1,'000-02-0001','000-01-0003'); INSERT INTO class VALUES(DEFAULT,'X52-9759',1,'000-02-0001','000-01-0005'); INSERT INTO class VALUES(DEFAULT,'X52-9759',1,'000-02-0001','000-01-0007'); INSERT INTO class VALUES(DEFAULT,'X52-9759',1,'000-02-0001','000-01-0008'); INSERT INTO class VALUES(DEFAULT,'X52-9759',1,'000-02-0001','000-01-0010'); INSERT INTO class VALUES(DEFAULT,'X52-9759',1,'000-02-0001','000-01-0013'); INSERT INTO class VALUES(DEFAULT,'X52-9759',1,'000-02-0001','000-01-0015'); INSERT INTO class VALUES(DEFAULT,'X52-9759',1,'000-02-0001','000-01-0017'); INSERT INTO class VALUES(DEFAULT,'X52-9759',1,'000-02-0001','000-01-0019'); INSERT INTO class VALUES(DEFAULT,'X52-9740',1,'000-02-0002','000-01-0001'); INSERT INTO class VALUES(DEFAULT,'X52-9740',1,'000-02-0002','000-01-0002'); INSERT INTO class VALUES(DEFAULT,'X52-9740',1,'000-02-0002','000-01-0003'); INSERT INTO class VALUES(DEFAULT,'X52-9740',1,'000-02-0002','000-01-0004'); INSERT INTO class VALUES(DEFAULT,'X52-9740',1,'000-02-0002','000-01-0005'); INSERT INTO class VALUES(DEFAULT,'X52-9740',1,'000-02-0002','000-01-0006'); INSERT INTO class VALUES(DEFAULT,'X52-9740',1,'000-02-0002','000-01-0007'); INSERT INTO class VALUES(DEFAULT,'X52-9740',1,'000-02-0002','000-01-0008'); INSERT INTO class VALUES(DEFAULT,'X52-9740',1,'000-02-0002','000-01-0009'); INSERT INTO class VALUES(DEFAULT,'X52-9740',1,'000-02-0002','000-01-0010'); INSERT INTO class VALUES(DEFAULT,'X52-9740',2,'000-02-0003','000-01-0011'); INSERT INTO class VALUES(DEFAULT,'X52-9740',2,'000-02-0003','000-01-0012'); INSERT INTO class VALUES(DEFAULT,'X52-9740',2,'000-02-0003','000-01-0013'); INSERT INTO class VALUES(DEFAULT,'X52-9740',2,'000-02-0003','000-01-0014'); INSERT INTO class VALUES(DEFAULT,'X52-9740',2,'000-02-0003','000-01-0015'); INSERT INTO class VALUES(DEFAULT,'X52-9740',2,'000-02-0003','000-01-0016'); INSERT INTO class VALUES(DEFAULT,'X52-9740',2,'000-02-0003','000-01-0017'); INSERT INTO class VALUES(DEFAULT,'X52-9740',2,'000-02-0003','000-01-0018'); INSERT INTO class VALUES(DEFAULT,'X52-9740',2,'000-02-0003','000-01-0019'); INSERT INTO class VALUES(DEFAULT,'X52-9740',2,'000-02-0003','000-01-0020'); INSERT INTO class VALUES(DEFAULT,'X52-9740',2,'000-02-0003','000-01-0021'); INSERT INTO class VALUES(DEFAULT,'X52-9740',2,'000-02-0003','000-01-0022'); INSERT INTO class VALUES(DEFAULT,'X52-9755',1,'000-02-0006','000-01-0003'); INSERT INTO class VALUES(DEFAULT,'X52-9755',1,'000-02-0006','000-01-0006'); INSERT INTO class VALUES(DEFAULT,'X52-9755',1,'000-02-0006','000-01-0009'); INSERT INTO class VALUES(DEFAULT,'X52-9755',1,'000-02-0006','000-01-0012'); INSERT INTO class VALUES(DEFAULT,'X52-9755',1,'000-02-0006','000-01-0015'); INSERT INTO class VALUES(DEFAULT,'X52-9755',1,'000-02-0006','000-01-0018'); INSERT INTO class VALUES(DEFAULT,'X52-9755',1,'000-02-0006','000-01-0021'); -- create payment table ---------------------------------------------------- CREATE TABLE payment ( payment_num SERIAL NOT NULL PRIMARY KEY, vendor VARCHAR(40) NOT NULL, amount DECIMAL(6,2) DEFAULT 0, description VARCHAR(40) NOT NULL, pay_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); INSERT INTO payment VALUES(DEFAULT,'Con Edison',125.15,'Electric','2017-02-01'); INSERT INTO payment VALUES(DEFAULT,'Verizon',54.79,'Home Phone','2017-03-01'); INSERT INTO payment VALUES(DEFAULT,'Allstate',1240.50,'Home Insurance','2017-03-02'); INSERT INTO payment VALUES(DEFAULT,'Verizon',49.95,'Cell Phone','2017-03-03'); INSERT INTO payment VALUES(DEFAULT,'Verizon',39.95,'Internet Service','2017-03-04'); INSERT INTO payment VALUES(DEFAULT,'Con Edison',59.63,'Electric','2017-03-05'); INSERT INTO payment VALUES(DEFAULT,'Department of Water',155.43,'Water and Sewers','2017-03-06'); INSERT INTO payment VALUES(DEFAULT,'Allstate',1550.25,'Car Insurance','2017-03-07'); -- create hierachy table -------------------------------------------------- CREATE TABLE hierarchy ( employee_id SERIAL NOT NULL, lname VARCHAR(20) NOT NULL, fname VARCHAR(20) NOT NULL, manager_id INT, CONSTRAINT empl_id PRIMARY KEY (employee_id), 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(DEFAULT,'Burns','Barbara',NULL); INSERT INTO hierarchy VALUES(DEFAULT,'Tobias','Wayne',1); INSERT INTO hierarchy VALUES(DEFAULT,'Stack','Patrick',1); INSERT INTO hierarchy VALUES(DEFAULT,'Milgrom','Anya',2); INSERT INTO hierarchy VALUES(DEFAULT,'Vasquez','Lilian',4); INSERT INTO hierarchy VALUES(DEFAULT,'Davidson','Duncan',4); -- create relationship table -------------------------------------------------- CREATE TABLE relationship ( row_pk SERIAL NOT NULL PRIMARY KEY, from_id INT NOT NULL, to_id INT NOT NULL ); INSERT INTO relationship VALUES(DEFAULT,1,3); INSERT INTO relationship VALUES(DEFAULT,2,3); INSERT INTO relationship VALUES(DEFAULT,3,4); INSERT INTO relationship VALUES(DEFAULT,6,4); INSERT INTO relationship VALUES(DEFAULT,3,5); INSERT INTO relationship VALUES(DEFAULT,6,5); INSERT INTO relationship VALUES(DEFAULT,4,7); INSERT INTO relationship VALUES(DEFAULT,4,8); INSERT INTO relationship VALUES(DEFAULT,4,9); -- create addrbook table -------------------------------------------------- CREATE TABLE addrbook ( lname VARCHAR(20) NOT NULL, fname VARCHAR(20) NOT NULL, street VARCHAR(50) NOT NULL, city VARCHAR(20) NOT NULL, state CHAR(2) NOT NULL, zip CHAR(5) NOT NULL, phone VARCHAR(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'); -- create output table ----------------------------------------------------- CREATE TABLE output ( line VARCHAR(4000) ); -- create v_student_roster view --------------------------------------------- CREATE VIEW v_student_roster AS SELECT s.student_id, s.fname, s.lname, s.ssn, s.sex, course_id, description, price, i.fname AS inst_fname, i.lname AS inst_lname, NOW() AS as_of FROM student s LEFT JOIN class c ON s.ssn=stu_ssn LEFT JOIN instructor i ON i.ssn=inst_ssn LEFT JOIN course co USING(course_id) ORDER BY 1; -- create v_key_column view ------------------------------------------------ CREATE VIEW v_key_column AS SELECT DISTINCT kcu.table_name, kcu.column_name, CASE WHEN tc.constraint_type='PRIMARY KEY' THEN '0PRIMARY KEY' ELSE tc.constraint_type END AS constraint_type, kcu.constraint_name, kcu.ordinal_position, CASE WHEN tc.constraint_type='FOREIGN KEY' THEN ccu.table_name END AS ref_table_name, CASE WHEN tc.constraint_type='FOREIGN KEY' THEN ccu.column_name END AS ref_column_name FROM information_schema.key_column_usage kcu JOIN information_schema.table_constraints tc USING(constraint_name) JOIN information_schema.constraint_column_usage ccu USING(constraint_name) UNION SELECT tablename, SUBSTRING(indexdef, '\(.+\)'), 'INDEX', indexname, null, null, null FROM pg_indexes WHERE schemaname='public' AND indexdef not like '%UNIQUE%' ORDER BY 1,3,4,2,5;