# drop all tables #################################################

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;

DROP VIEW IF EXISTS v_student_roster;
DROP VIEW IF EXISTS v_key_column;

# create course table ############################################

CREATE TABLE course
(
    course_id    CHAR(8)       NOT NULL PRIMARY KEY,
    description  VARCHAR(40)   NOT NULL,
    price        INTEGER       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 INTEGER       NOT NULL    PRIMARY KEY AUTOINCREMENT,
    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(1,'Sultan','Sam','000-02-0001','M');
INSERT INTO instructor VALUES(2,'Pefanis','George','000-02-0002','M');
INSERT INTO instructor VALUES(3,'martin','susan','000-02-0003','F');
INSERT INTO instructor VALUES(4,'Paller','Marc','000-02-0004','M');
INSERT INTO instructor VALUES(5,'O''Brien','Mary','000-02-0005','F');
INSERT INTO instructor VALUES(6,'Katz','Eric','000-02-0006','M');


# create student table ###########################################

CREATE TABLE student
(
    student_id INTEGER       NOT NULL PRIMARY KEY AUTOINCREMENT,
    lname      VARCHAR(20)   NOT NULL,
    fname      VARCHAR(20)   NOT NULL,
    ssn        CHAR(11)      NOT NULL,
    sex        CHAR(1)       NOT NULL,

    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(1,'Burns','Barbara','000-01-0001','F');
INSERT INTO student VALUES(2,'Cambria','Vincent','000-01-0002','M');
INSERT INTO student VALUES(3,'Davidson','Duncan','000-01-0003','M');
INSERT INTO student VALUES(4,'Smith','David','000-01-0004','M');
INSERT INTO student VALUES(5,'Thomas','Eugene','000-01-0005','M');
INSERT INTO student VALUES(6,'Owens','Cynthia','000-01-0006','F');
INSERT INTO student VALUES(7,'Willis','Eileen','000-01-0007','F');
INSERT INTO student VALUES(8,'Myers','Rick','000-01-0008','M');
INSERT INTO student VALUES(9,'Ryan','Natasha','000-01-0009','F');
INSERT INTO student VALUES(10,'Stack','Patrick','000-01-0010','M');
INSERT INTO student VALUES(11,'Tobias','Wayne','000-01-0011','M');
INSERT INTO student VALUES(12,'Race','Joseph','000-01-0012','M');
INSERT INTO student VALUES(13,'Nelson','Colette','000-01-0013','F');
INSERT INTO student VALUES(14,'Brinson','Angel','000-01-0014','F');
INSERT INTO student VALUES(15,'Soley','John','000-01-0015','M');
INSERT INTO student VALUES(16,'Grace','Robert','000-01-0016','M');
INSERT INTO student VALUES(17,'Tok','Kathy','000-01-0017','F');
INSERT INTO student VALUES(18,'miller','janet','000-01-0018','F');
INSERT INTO student VALUES(19,'Austin','Maria','000-01-0019','F');
INSERT INTO student VALUES(20,'Teagan','Edward','000-01-0020','M');
INSERT INTO student VALUES(21,'Milgrom','Anya','000-01-0021','F');
INSERT INTO student VALUES(22,'Vasquez','Lillian','000-01-0022','F');
INSERT INTO student VALUES(23,'Chan','David','000-01-0023','M');
INSERT INTO student VALUES(24,'James','Phyllis','000-01-0024','F');
INSERT INTO student VALUES(25,'Sultan','Sam','000-02-0001','M');


# create instructor_info table ######################################

CREATE TABLE instructor_info
(
    instructor_id  INTEGER      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    INTEGER       NOT NULL PRIMARY KEY AUTOINCREMENT,
    student_id  INTEGER       NOT NULL,
    email       VARCHAR(100), 

    CONSTRAINT stu_email_fk FOREIGN KEY (student_id) REFERENCES student (student_id)
);

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


# create email_msg table ############################################

CREATE TABLE email_msg
(
    msg_id       INTEGER      NOT NULL         PRIMARY KEY  AUTOINCREMENT,
    email_id     INTEGER      NOT NULL         REFERENCES student_email (email_id), 
    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(1,1, '2018-07-21','sam.sultan@nyu.edu','This is my first email message');
INSERT INTO email_msg VALUES(2,1, '2018-07-22','sam.sultan@nyu.edu','This is my second email message');
INSERT INTO email_msg VALUES(3,3, '2018-07-23','sam.sultan@nyu.edu','Hello World');
INSERT INTO email_msg VALUES(4,3, '2018-07-24','sam.sultan@nyu.edu','Goodbye World');
INSERT INTO email_msg VALUES(5,4, '2018-07-25','sam.sultan@nyu.edu','Using a different email address');
INSERT INTO email_msg VALUES(6,7, '2018-07-26','sam.sultan@nyu.edu','How are you?');
INSERT INTO email_msg VALUES(7,7, '2018-07-27','sam.sultan@nyu.edu','Have not heard from you in a while');
INSERT INTO email_msg VALUES(8,7, '2018-07-28','sam.sultan@nyu.edu','Are you there?');
INSERT INTO email_msg VALUES(9,10,'2018-07-29','sam.sultan@nyu.edu','What a beautiful day to learn SQL');


# create address table #############################################

CREATE TABLE address
(
    address_id INTEGER       NOT NULL PRIMARY KEY AUTOINCREMENT,
    stu_id     INTEGER,
    inst_id    INTEGER,
    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 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);         # index on FK is advisible
#CREATE INDEX addr_inst_idx ON address (inst_id);        # automatically done by mysql

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


# create class table ##############################################

CREATE TABLE class
(
    class_id    INTEGER       NOT NULL PRIMARY KEY AUTOINCREMENT,
    course_id   CHAR(8)       NOT NULL,
    session_id  INTEGER       NOT NULL,
    inst_ssn    CHAR(11)      NOT NULL,
    stu_ssn     CHAR(11)      NOT NULL,

    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);     # automatically done by mysql
#CREATE INDEX class_stu_idx     ON class (stu_ssn);      # automatically done by mysql

INSERT INTO class VALUES(1,'X52-9272',1,'000-02-0001','000-01-0002');
INSERT INTO class VALUES(2,'X52-9272',1,'000-02-0001','000-01-0004');
INSERT INTO class VALUES(3,'X52-9272',1,'000-02-0001','000-01-0006');
INSERT INTO class VALUES(4,'X52-9272',1,'000-02-0001','000-01-0008');
INSERT INTO class VALUES(5,'X52-9272',1,'000-02-0001','000-01-0010');
INSERT INTO class VALUES(6,'X52-9272',1,'000-02-0001','000-01-0012');
INSERT INTO class VALUES(7,'X52-9272',1,'000-02-0001','000-01-0014');
INSERT INTO class VALUES(8,'X52-9272',1,'000-02-0001','000-01-0016');
INSERT INTO class VALUES(9,'X52-9272',1,'000-02-0001','000-01-0018');
INSERT INTO class VALUES(10,'X52-9272',1,'000-02-0001','000-01-0020');
INSERT INTO class VALUES(11,'X52-9272',1,'000-02-0001','000-01-0022');

INSERT INTO class VALUES(12,'X52-9759',1,'000-02-0001','000-01-0001');
INSERT INTO class VALUES(13,'X52-9759',1,'000-02-0001','000-01-0003');
INSERT INTO class VALUES(14,'X52-9759',1,'000-02-0001','000-01-0005');
INSERT INTO class VALUES(15,'X52-9759',1,'000-02-0001','000-01-0007');
INSERT INTO class VALUES(16,'X52-9759',1,'000-02-0001','000-01-0008');
INSERT INTO class VALUES(17,'X52-9759',1,'000-02-0001','000-01-0010');
INSERT INTO class VALUES(18,'X52-9759',1,'000-02-0001','000-01-0013');
INSERT INTO class VALUES(19,'X52-9759',1,'000-02-0001','000-01-0015');
INSERT INTO class VALUES(20,'X52-9759',1,'000-02-0001','000-01-0017');
INSERT INTO class VALUES(21,'X52-9759',1,'000-02-0001','000-01-0019');

INSERT INTO class VALUES(22,'X52-9740',1,'000-02-0002','000-01-0001');
INSERT INTO class VALUES(23,'X52-9740',1,'000-02-0002','000-01-0002');
INSERT INTO class VALUES(24,'X52-9740',1,'000-02-0002','000-01-0003');
INSERT INTO class VALUES(25,'X52-9740',1,'000-02-0002','000-01-0004');
INSERT INTO class VALUES(26,'X52-9740',1,'000-02-0002','000-01-0005');
INSERT INTO class VALUES(27,'X52-9740',1,'000-02-0002','000-01-0006');
INSERT INTO class VALUES(28,'X52-9740',1,'000-02-0002','000-01-0007');
INSERT INTO class VALUES(29,'X52-9740',1,'000-02-0002','000-01-0008');
INSERT INTO class VALUES(30,'X52-9740',1,'000-02-0002','000-01-0009');
INSERT INTO class VALUES(31,'X52-9740',1,'000-02-0002','000-01-0010');

INSERT INTO class VALUES(32,'X52-9740',2,'000-02-0003','000-01-0011');
INSERT INTO class VALUES(33,'X52-9740',2,'000-02-0003','000-01-0012');
INSERT INTO class VALUES(34,'X52-9740',2,'000-02-0003','000-01-0013');
INSERT INTO class VALUES(35,'X52-9740',2,'000-02-0003','000-01-0014');
INSERT INTO class VALUES(36,'X52-9740',2,'000-02-0003','000-01-0015');
INSERT INTO class VALUES(37,'X52-9740',2,'000-02-0003','000-01-0016');
INSERT INTO class VALUES(38,'X52-9740',2,'000-02-0003','000-01-0017');
INSERT INTO class VALUES(39,'X52-9740',2,'000-02-0003','000-01-0018');
INSERT INTO class VALUES(40,'X52-9740',2,'000-02-0003','000-01-0019');
INSERT INTO class VALUES(41,'X52-9740',2,'000-02-0003','000-01-0020');
INSERT INTO class VALUES(42,'X52-9740',2,'000-02-0003','000-01-0021');
INSERT INTO class VALUES(43,'X52-9740',2,'000-02-0003','000-01-0022');

INSERT INTO class VALUES(44,'X52-9755',1,'000-02-0006','000-01-0003');
INSERT INTO class VALUES(45,'X52-9755',1,'000-02-0006','000-01-0006');
INSERT INTO class VALUES(46,'X52-9755',1,'000-02-0006','000-01-0009');
INSERT INTO class VALUES(47,'X52-9755',1,'000-02-0006','000-01-0012');
INSERT INTO class VALUES(48,'X52-9755',1,'000-02-0006','000-01-0015');
INSERT INTO class VALUES(49,'X52-9755',1,'000-02-0006','000-01-0018');
INSERT INTO class VALUES(50,'X52-9755',1,'000-02-0006','000-01-0021');


# create payment table ####################################################

CREATE TABLE payment
(   
    payment_num  INTEGER                NOT NULL PRIMARY KEY AUTOINCREMENT,    
    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(1,'Con Edison',125.15,'Electric','2017-01-31');
INSERT INTO payment  VALUES(2,'Verizon',54.79,'Home Phone','2017-02-28');
INSERT INTO payment  VALUES(3,'Allstate',1240.50,'Home Insurance','2017-03-01');
INSERT INTO payment  VALUES(4,'Verizon',49.95,'Cell Phone','2017-03-02');
INSERT INTO payment  VALUES(5,'Verizon',39.95,'Internet Service','2017-03-03');
INSERT INTO payment  VALUES(6,'Con Edison',59.63,'Electric','2017-03-04');
INSERT INTO payment  VALUES(7,'Department of Water',155.43,'Water and Sewers','2017-03-05');
INSERT INTO payment  VALUES(8,'Allstate',1550.25,'Car Insurance','2017-03-06');


# create hierachy table ##################################################

CREATE TABLE hierarchy 
(   
    employee_id  INTEGER            NOT NULL PRIMARY KEY AUTOINCREMENT,    
    lname        VARCHAR(20)        NOT NULL,   
    fname        VARCHAR(20)        NOT NULL,   
    manager_id   INTEGER,

    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(1,'Burns','Barbara',NULL);
INSERT INTO hierarchy  VALUES(2,'Tobias','Wayne',1);
INSERT INTO hierarchy  VALUES(3,'Stack','Patrick',1);
INSERT INTO hierarchy  VALUES(4,'Milgrom','Anya',2);
INSERT INTO hierarchy  VALUES(5,'Vasquez','Lilian',4);
INSERT INTO hierarchy  VALUES(6,'Davidson','Duncan',4);


# create relationship table ##################################################

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

INSERT INTO relationship  VALUES(1,1,3);
INSERT INTO relationship  VALUES(2,2,3);
INSERT INTO relationship  VALUES(3,3,4);
INSERT INTO relationship  VALUES(4,6,4);
INSERT INTO relationship  VALUES(5,3,5);
INSERT INTO relationship  VALUES(6,6,5);
INSERT INTO relationship  VALUES(7,4,7);
INSERT INTO relationship  VALUES(8,4,8);
INSERT INTO relationship  VALUES(9,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,
            DATETIME('now','localtime') 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  sm.name AS table_name, fk."from" AS column_name, 
            'FOREIGN KEY' AS constraint_type, null AS constraint_name, null AS ordinal_position,  #Get all foreign keys 
            fk."table" AS ref_table_name, fk."to" AS ref_column_name
      FROM  sqlite_master sm
      JOIN  pragma_foreign_key_list(sm.name) fk  
UNION
    SELECT  sm.name AS table_name, tbl.name AS column_name, 
            ' PRIMARY KEY', 'PRIMARY', null, null, null                                          #Get all primary keys
      FROM  sqlite_master sm
      JOIN  pragma_table_info(sm.name) tbl 
     WHERE  pk=1
UNION 
    SELECT  sm.name AS table_name, xInfo.name AS column_name,  
            CASE xList."unique" WHEN 1 THEN 'UNIQUE INDEX' ELSE 'INDEX' END,                     #Get all indexes        
            xList.name AS constraint_name, seqno AS ordinal_position,
            null, null
      FROM  sqlite_master sm
      JOIN  pragma_index_list(sm.name)    xList 
      JOIN  pragma_index_info(xList.name) xInfo 
     ORDER BY 1,3,4,5,2;