SQL Sample

Code Download

Create a database named "SE4M03" to test, run create, data, show, query in sequence

Tested on a IBM DB2 system

DB2 (IBM SQL)




create.db2


CONNECT TO SE4M03;

CREATE TABLE BRANCH (
  BRANCH_NO CHAR(4) NOT NULL,
  STREET_NO CHAR(4) NOT NULL,
  STREET CHAR(10) NOT NULL,
  CITY CHAR(10) NOT NULL,
  PROVINCE CHAR(2) NOT NULL,
  POSTAL_CODE CHAR(6) NOT NULL,
  MANAGER CHAR(4) NOT NULL,
  PRIMARY KEY (BRANCH_NO),
  CHECK (PROVINCE IN ('AL','BC','MA','NB','NF','NT','NS','NU','ON','PE','QB','SA','YU')),
  CHECK (('A'<=SUBSTR(POSTAL_CODE,1,1) AND SUBSTR(POSTAL_CODE,1,1)<='Z') AND
         ('0'<=SUBSTR(POSTAL_CODE,2,1) AND SUBSTR(POSTAL_CODE,2,1)<='9') AND
         ('A'<=SUBSTR(POSTAL_CODE,3,1) AND SUBSTR(POSTAL_CODE,3,1)<='Z') AND
         ('0'<=SUBSTR(POSTAL_CODE,4,1) AND SUBSTR(POSTAL_CODE,4,1)<='9') AND
         ('A'<=SUBSTR(POSTAL_CODE,5,1) AND SUBSTR(POSTAL_CODE,5,1)<='Z') AND
         ('0'<=SUBSTR(POSTAL_CODE,6,1) AND SUBSTR(POSTAL_CODE,6,1)<='9')),
  UNIQUE(MANAGER)
);

CREATE TABLE STAFF (
  STAFF_NO CHAR(4) NOT NULL,
  LAST_NAME CHAR(20) NOT NULL,
  FIRST_NAME CHAR(10) NOT NULL,
  MIDDLE_NAME CHAR(10),
  STREET_NO CHAR(4) NOT NULL,
  STREET CHAR(10) NOT NULL,
  CITY CHAR(10) NOT NULL,
  PROVINCE CHAR(2) NOT NULL,
  POSTAL_CODE CHAR(6) NOT NULL,
  SEX CHAR(1) NOT NULL,
  SALARY DECIMAL(9,2) NOT NULL,
  ALLOCATED_TO CHAR(4) NOT NULL,
  PRIMARY KEY (STAFF_NO),
  FOREIGN KEY (ALLOCATED_TO) REFERENCES BRANCH,
  CHECK (PROVINCE IN ('AL','BC','MA','NB','NF','NT','NS','NU','ON','PE','QB','SA','YU')),
  CHECK (SEX IN ('F','M','N')),
  CHECK (SALARY > 0),
  CHECK (('A'<=SUBSTR(POSTAL_CODE,1,1) AND SUBSTR(POSTAL_CODE,1,1)<='Z') AND
         ('0'<=SUBSTR(POSTAL_CODE,2,1) AND SUBSTR(POSTAL_CODE,2,1)<='9') AND
         ('A'<=SUBSTR(POSTAL_CODE,3,1) AND SUBSTR(POSTAL_CODE,3,1)<='Z') AND
         ('0'<=SUBSTR(POSTAL_CODE,4,1) AND SUBSTR(POSTAL_CODE,4,1)<='9') AND
         ('A'<=SUBSTR(POSTAL_CODE,5,1) AND SUBSTR(POSTAL_CODE,5,1)<='Z') AND
         ('0'<=SUBSTR(POSTAL_CODE,6,1) AND SUBSTR(POSTAL_CODE,6,1)<='9'))
);

CREATE TABLE OWNER (
  OWNER_NO CHAR(4) NOT NULL,
  NAME CHAR(20) NOT NULL,
  FIRST_NAME CHAR(10),
  MIDDLE_NAME CHAR(10),
  STREET_NO CHAR(4) NOT NULL,
  STREET CHAR(10) NOT NULL,
  CITY CHAR(10) NOT NULL,
  PROVINCE CHAR(2) NOT NULL,
  POSTAL_CODE CHAR(6) NOT NULL,
  TYPE_OF_BUSINESS CHAR(2),
  PRIMARY KEY (OWNER_NO),
  CHECK (PROVINCE IN ('AL','BC','MA','NB','NF','NT','NS','NU','ON','PE','QB','SA','YU')),
  CHECK (('A'<=SUBSTR(POSTAL_CODE,1,1) AND SUBSTR(POSTAL_CODE,1,1)<='Z') AND
         ('0'<=SUBSTR(POSTAL_CODE,2,1) AND SUBSTR(POSTAL_CODE,2,1)<='9') AND
         ('A'<=SUBSTR(POSTAL_CODE,3,1) AND SUBSTR(POSTAL_CODE,3,1)<='Z') AND
         ('0'<=SUBSTR(POSTAL_CODE,4,1) AND SUBSTR(POSTAL_CODE,4,1)<='9') AND
         ('A'<=SUBSTR(POSTAL_CODE,5,1) AND SUBSTR(POSTAL_CODE,5,1)<='Z') AND
         ('0'<=SUBSTR(POSTAL_CODE,6,1) AND SUBSTR(POSTAL_CODE,6,1)<='9')),
  CHECK(TYPE_OF_BUSINESS IS NULL OR (FIRST_NAME IS NULL AND MIDDLE_NAME IS NULL))
);

CREATE TABLE RENTER (
  RENTER_NO CHAR(4) NOT NULL,
  NAME CHAR(20) NOT NULL,
  FIRST_NAME CHAR(10),
  MIDDLE_NAME CHAR(10),
  STREET_NO CHAR(4) NOT NULL,
  STREET CHAR(10) NOT NULL,
  CITY CHAR(10) NOT NULL,
  PROVINCE CHAR(2) NOT NULL,
  POSTAL_CODE CHAR(6) NOT NULL,
  TYPE_OF_BUSINESS CHAR(2),
  PRIMARY KEY (RENTER_NO),
  CHECK (PROVINCE IN ('AL','BC','MA','NB','NF','NT','NS','NU','ON','PE','QB','SA','YU')),
  CHECK (('A'<=SUBSTR(POSTAL_CODE,1,1) AND SUBSTR(POSTAL_CODE,1,1)<='Z') AND
         ('0'<=SUBSTR(POSTAL_CODE,2,1) AND SUBSTR(POSTAL_CODE,2,1)<='9') AND
         ('A'<=SUBSTR(POSTAL_CODE,3,1) AND SUBSTR(POSTAL_CODE,3,1)<='Z') AND
         ('0'<=SUBSTR(POSTAL_CODE,4,1) AND SUBSTR(POSTAL_CODE,4,1)<='9') AND
         ('A'<=SUBSTR(POSTAL_CODE,5,1) AND SUBSTR(POSTAL_CODE,5,1)<='Z') AND
         ('0'<=SUBSTR(POSTAL_CODE,6,1) AND SUBSTR(POSTAL_CODE,6,1)<='9')),
  CHECK(TYPE_OF_BUSINESS IS NULL OR (FIRST_NAME IS NULL AND MIDDLE_NAME IS NULL))
);

CREATE TABLE PROPERTY (
  PROPERTY_NO CHAR(4) NOT NULL,
  STREET_NO CHAR(4) NOT NULL,
  STREET CHAR(10) NOT NULL,
  CITY CHAR(10) NOT NULL,
  PROVINCE CHAR(2) NOT NULL,
  POSTAL_CODE CHAR(6) NOT NULL,
  OVERSEEN_BY CHAR(4) NOT NULL,
  OWNED_BY CHAR(4) NOT NULL,
  TYPE CHAR(2) NOT NULL,
  PRIMARY KEY (PROPERTY_NO),
  FOREIGN KEY (OVERSEEN_BY) REFERENCES STAFF,
  FOREIGN KEY (OWNED_BY) REFERENCES OWNER,
  CHECK (PROVINCE IN ('AL','BC','MA','NB','NF','NT','NS','NU','ON','PE','QB','SA','YU')),
  CHECK (('A'<=SUBSTR(POSTAL_CODE,1,1) AND SUBSTR(POSTAL_CODE,1,1)<='Z') AND
         ('0'<=SUBSTR(POSTAL_CODE,2,1) AND SUBSTR(POSTAL_CODE,2,1)<='9') AND
         ('A'<=SUBSTR(POSTAL_CODE,3,1) AND SUBSTR(POSTAL_CODE,3,1)<='Z') AND
         ('0'<=SUBSTR(POSTAL_CODE,4,1) AND SUBSTR(POSTAL_CODE,4,1)<='9') AND
         ('A'<=SUBSTR(POSTAL_CODE,5,1) AND SUBSTR(POSTAL_CODE,5,1)<='Z') AND
         ('0'<=SUBSTR(POSTAL_CODE,6,1) AND SUBSTR(POSTAL_CODE,6,1)<='9'))
);

CREATE TABLE RENTAL_AGREEMENT (
  PROPERTY_NO CHAR(4) NOT NULL,
  RENTAL_NO CHAR(4) NOT NULL,
  SIGNING_DATE DATE NOT NULL,
  STARTING_DATE DATE NOT NULL,
  ENDING_DATE DATE NOT NULL,
  RENTER_NO CHAR(4) NOT NULL,
  PRIMARY KEY (PROPERTY_NO,RENTAL_NO),
  FOREIGN KEY (PROPERTY_NO) REFERENCES PROPERTY,
  FOREIGN KEY (RENTER_NO) REFERENCES RENTER,
  CHECK (SIGNING_DATE <= STARTING_DATE),
  CHECK (STARTING_DATE <= ENDING_DATE)
);

CREATE TABLE RENTER_EMAIL (
  EMAIL_ADDR CHAR(20) NOT NULL,
  RENTER_NO CHAR(4) NOT NULL,
  PRIMARY KEY (EMAIL_ADDR,RENTER_NO),
  FOREIGN KEY (RENTER_NO) REFERENCES RENTER
);

CREATE TABLE STAFF_EMAIL (
  EMAIL_ADDR CHAR(20) NOT NULL,
  STAFF_NO CHAR(4) NOT NULL,
  PRIMARY KEY (EMAIL_ADDR,STAFF_NO),
  FOREIGN KEY (STAFF_NO) REFERENCES STAFF
);

CREATE TABLE OWNER_EMAIL (
  EMAIL_ADDR CHAR(20) NOT NULL,
  OWNER_NO CHAR(4) NOT NULL,
  PRIMARY KEY (EMAIL_ADDR,OWNER_NO),
  FOREIGN KEY (OWNER_NO) REFERENCES OWNER
);

CREATE TABLE BRANCH_EMAIL (
  EMAIL_ADDR CHAR(20) NOT NULL,
  BRANCH_NO CHAR(4) NOT NULL,
  PRIMARY KEY (EMAIL_ADDR,BRANCH_NO),
  FOREIGN KEY (BRANCH_NO) REFERENCES BRANCH
);

CREATE TABLE RENTER_PHONE (
  AREA_CODE CHAR(3) NOT NULL,
  PHONE_NO CHAR(7) NOT NULL,
  EXTENSION VARCHAR(5),
  RENTER_NO CHAR(4) NOT NULL,
  PRIMARY KEY (AREA_CODE,PHONE_NO,RENTER_NO),
  FOREIGN KEY (RENTER_NO) REFERENCES RENTER,
  CHECK(('0'<=SUBSTR(AREA_CODE,1,1) AND SUBSTR(AREA_CODE,1,1)<='9') AND
        ('0'<=SUBSTR(AREA_CODE,2,1) AND SUBSTR(AREA_CODE,2,1)<='9') AND
        ('0'<=SUBSTR(AREA_CODE,3,1) AND SUBSTR(AREA_CODE,3,1)<='9')),
  CHECK(('0'<=SUBSTR(PHONE_NO,1,1) AND SUBSTR(PHONE_NO,1,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,2,1) AND SUBSTR(PHONE_NO,2,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,3,1) AND SUBSTR(PHONE_NO,3,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,4,1) AND SUBSTR(PHONE_NO,4,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,5,1) AND SUBSTR(PHONE_NO,5,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,6,1) AND SUBSTR(PHONE_NO,6,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,7,1) AND SUBSTR(PHONE_NO,7,1)<='9'))
);

CREATE TABLE STAFF_PHONE (
  AREA_CODE CHAR(3) NOT NULL,
  PHONE_NO CHAR(7) NOT NULL,
  EXTENSION VARCHAR(5),
  STAFF_NO CHAR(4) NOT NULL,
  PRIMARY KEY (AREA_CODE,PHONE_NO,STAFF_NO),
  FOREIGN KEY (STAFF_NO) REFERENCES STAFF,
  CHECK(('0'<=SUBSTR(AREA_CODE,1,1) AND SUBSTR(AREA_CODE,1,1)<='9') AND
        ('0'<=SUBSTR(AREA_CODE,2,1) AND SUBSTR(AREA_CODE,2,1)<='9') AND
        ('0'<=SUBSTR(AREA_CODE,3,1) AND SUBSTR(AREA_CODE,3,1)<='9')),
  CHECK(('0'<=SUBSTR(PHONE_NO,1,1) AND SUBSTR(PHONE_NO,1,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,2,1) AND SUBSTR(PHONE_NO,2,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,3,1) AND SUBSTR(PHONE_NO,3,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,4,1) AND SUBSTR(PHONE_NO,4,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,5,1) AND SUBSTR(PHONE_NO,5,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,6,1) AND SUBSTR(PHONE_NO,6,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,7,1) AND SUBSTR(PHONE_NO,7,1)<='9'))
);

CREATE TABLE OWNER_PHONE (
  AREA_CODE CHAR(3) NOT NULL,
  PHONE_NO CHAR(7) NOT NULL,
  EXTENSION VARCHAR(5),
  OWNER_NO CHAR(4) NOT NULL,
  PRIMARY KEY (AREA_CODE,PHONE_NO,OWNER_NO),
  FOREIGN KEY (OWNER_NO) REFERENCES OWNER,
  CHECK(('0'<=SUBSTR(AREA_CODE,1,1) AND SUBSTR(AREA_CODE,1,1)<='9') AND
        ('0'<=SUBSTR(AREA_CODE,2,1) AND SUBSTR(AREA_CODE,2,1)<='9') AND
        ('0'<=SUBSTR(AREA_CODE,3,1) AND SUBSTR(AREA_CODE,3,1)<='9')),
  CHECK(('0'<=SUBSTR(PHONE_NO,1,1) AND SUBSTR(PHONE_NO,1,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,2,1) AND SUBSTR(PHONE_NO,2,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,3,1) AND SUBSTR(PHONE_NO,3,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,4,1) AND SUBSTR(PHONE_NO,4,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,5,1) AND SUBSTR(PHONE_NO,5,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,6,1) AND SUBSTR(PHONE_NO,6,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,7,1) AND SUBSTR(PHONE_NO,7,1)<='9'))
);

CREATE TABLE BRANCH_PHONE (
   AREA_CODE CHAR(3) NOT NULL,
  PHONE_NO CHAR(7) NOT NULL,
  EXTENSION VARCHAR(5),
  BRANCH_NO CHAR(4) NOT NULL,
  PRIMARY KEY (AREA_CODE,PHONE_NO,BRANCH_NO),
  FOREIGN KEY (BRANCH_NO) REFERENCES BRANCH,
  CHECK(('0'<=SUBSTR(AREA_CODE,1,1) AND SUBSTR(AREA_CODE,1,1)<='9') AND
        ('0'<=SUBSTR(AREA_CODE,2,1) AND SUBSTR(AREA_CODE,2,1)<='9') AND
        ('0'<=SUBSTR(AREA_CODE,3,1) AND SUBSTR(AREA_CODE,3,1)<='9')),
  CHECK(('0'<=SUBSTR(PHONE_NO,1,1) AND SUBSTR(PHONE_NO,1,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,2,1) AND SUBSTR(PHONE_NO,2,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,3,1) AND SUBSTR(PHONE_NO,3,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,4,1) AND SUBSTR(PHONE_NO,4,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,5,1) AND SUBSTR(PHONE_NO,5,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,6,1) AND SUBSTR(PHONE_NO,6,1)<='9') AND
        ('0'<=SUBSTR(PHONE_NO,7,1) AND SUBSTR(PHONE_NO,7,1)<='9'))
);

CREATE TABLE VIEWING (
  PROPERTY_NO CHAR(4) NOT NULL,
  RENTER_NO CHAR(4) NOT NULL,
  VIEWING_DATE DATE NOT NULL,
  PRIMARY KEY (PROPERTY_NO,VIEWING_DATE,RENTER_NO),
  FOREIGN KEY (PROPERTY_NO) REFERENCES PROPERTY,
  FOREIGN KEY (RENTER_NO) REFERENCES RENTER
);

CREATE TABLE NEWSPAPER (
  PAPER_NAME CHAR(20) NOT NULL,
  PRIMARY KEY (PAPER_NAME)
);

CREATE TABLE ADVERTISEMENT (
  PAPER_NAME CHAR(20) NOT NULL,
  AD_NO CHAR(4) NOT NULL,
  AD_DATE DATE NOT NULL,
  PROPERTY_NO CHAR(4) NOT NULL,
  PRIMARY KEY (PAPER_NAME,AD_NO),
  FOREIGN KEY (PAPER_NAME) REFERENCES NEWSPAPER,
  FOREIGN KEY (PROPERTY_NO) REFERENCES PROPERTY
);

ALTER TABLE BRANCH
ADD CONSTRAINT MANAGER_CNST FOREIGN KEY (MANAGER) REFERENCES STAFF(STAFF_NO);

LIST TABLES;

TERMINATE;		
		
		

data.db2

		
CONNECT TO SE4M03;

ALTER TABLE BRANCH
DROP FOREIGN KEY MANAGER_CNST;

INSERT INTO BRANCH (BRANCH_NO,STREET_NO,STREET,CITY,PROVINCE,POSTAL_CODE,MANAGER) VALUES
('1000','1111','11 AVE','HAMILTON','ON','L8A1A1','9004'),
('2000','222','22 ST','DUNDAS','ON','L9B2B2','9003'),
('3000','33','33 AVE','HAMILTON','ON','L8C3C3','9002'),
('4000','4','44 ST','VANCOUVER','BC','V6D4D4','9001');

INSERT INTO STAFF (STAFF_NO,LAST_NAME,FIRST_NAME,MIDDLE_NAME,STREET_NO,STREET,CITY,PROVINCE,POSTAL_CODE,SEX,SALARY,ALLOCATED_TO) VALUES
('9001','WENG','JASPER',NULL,'100','44 ST','VANCOUVER','BC','V6A4A4','M',6300.00,'4000'),
('9002','JIANG','MEI',NULL,'100','33 AVE','HAMILTON','ON','L8A1A1','M',7000.00,'3000'),
('9018','CCC','K','RR','501','66 ST','VANCOUVER','BC','V6A1D4','M',4300.00,'4000'),
('9021','DDDDD','IIIII','SSS','111','11 AVE','HAMILTON','ON','L8B1D1','M',5000.00,'3000'),
('9011','AAAA','III',NULL,'100','44 ST','VANCOUVER','BC','V6A4A4','F',4300.00,'4000'),
('9014','CCC','JJJJ','P','201','55 AVE','VANCOUVER','BC','V6A2B4','F',5300.00,'4000'),
('9019','AAA','KKK','QQ','300','66 AVE','VANCOUVER','BC','V6A3C4','F',4350.00,'4000'),
('9013','BBBB','JJJ',NULL,'401','77 ST','VANCOUVER','BC','V6A2C4','M',4350.00,'4000'),
('9028','F','KKK','SS','205','20 ST','DUNDAS','ON','L9A1A2','F',5200.00,'2000'),
('9015','CCC','III','QQ','306','30 ST','DUNDAS','ON','L9B1K3','M',5300.00,'2000'),
('9029','GGG','K',NULL,'120','22 ST','DUNDAS','ON','L9B2A1','M',4800.00,'1000'),
('9012','CC','MMM',NULL,'222','22 ST','DUNDAS','ON','L9A2B2','F',5350.00,'3000'),
('9017','DDD','K','PP','333','33 AVE','HAMILTON','ON','L8A3F1','M',4950.00,'3000'),
('9022','EEE','JJJ','QQ','444','44 ST','HAMILTON','ON','L8C1G1','F',4800.00,'3000'),
('9016','EEE','JJJ',NULL,'104','33 AVE','HAMILTON','ON','L8C3C3','M',5300.00,'2000'),
('9030','AAAAA','III',NULL,'130','32 ST','HAMILTON','ON','L8K1A1','F',4950.00,'1000'),
('9031','BBB','KKK',NULL,'140','33 ST','HAMILTON','ON','L8Q2N1','F',4800.00,'1000'),
('9032','GGG','JJJ','PP','150','33 ST','HAMILTON','ON','L8Q3M1','M',4800.00,'1000'),
('9003','RAFAEL','ERIK',NULL,'200','20 ST','DUNDAS','ON','L9A1A1','M',7000.00,'2000'),
('9004','WENG','JASPER','CHIACHUN','200','10 ST','HAMILTON','ON','L8B1A1','M',6800.50,'1000');

INSERT INTO OWNER (OWNER_NO,NAME,FIRST_NAME,MIDDLE_NAME,STREET_NO,STREET,CITY,PROVINCE,POSTAL_CODE,TYPE_OF_BUSINESS) VALUES
('5003','AAA','JJJJJ','QQ','1280','32 AVE','VANCOUVER','BC','V6Z6Z6',NULL),
('5011','PPP',NULL,NULL,'1399','21 ST','VANCOUVER','BC','V6Z5Z5','A'),
('5007','AA','N',NULL,'3344','61 AVE','HAMILTON','ON','L8Z0Q1',NULL),
('5012','DDD','NNN',NULL,'2888','31 AVE','DUNDAS','ON','L9Q1Q3',NULL),
('5006','BBBBB','K',NULL,'1021','41 ST','DUNDAS','ON','L9Q9U3',NULL),
('5016','PPPP',NULL,NULL,'3595','21 AVE','DUNDAS','ON','L9X0Q2','B'),
('5008','BB','III',NULL,'3344','32 ST','HAMILTON','ON','L8Z7N1',NULL),
('5015','QQQ',NULL,NULL,'4433','31 ST','HAMILTON','ON','L8Z8Q7','A'),
('5002','BB','IIII',NULL,'2380','21 ST','VANCOUVER','BC','V6Z5V6',NULL),
('5001','QQQ',NULL,NULL,'2399','42 ST','VANCOUVER','BC','V6Z5V5','A'),
('5010','BB','MMM','SS','3399','36 ST','VANCOUVER','BC','V6Z5K5',NULL),
('5009','EEE','MMM',NULL,'9988','32 AVE','HAMILTON','ON','L8Z4Q3',NULL),
('5014','CCCCC','KKKKK',NULL,'5566','61 ST','HAMILTON','ON','L8Z5Q3',NULL),
('5005','PPP',NULL,NULL,'7788','72 AVE','HAMILTON','ON','L8Z6N2','B'),
('5004','QQQ',NULL,NULL,'2211','42 ST','HAMILTON','ON','L8Z3Q7','A'),
('5013','FFF','JJJ','RR','1122','52 ST','HAMILTON','ON','L8Z9M2',NULL);

INSERT INTO RENTER (RENTER_NO,NAME,FIRST_NAME,MIDDLE_NAME,STREET_NO,STREET,CITY,PROVINCE,POSTAL_CODE,TYPE_OF_BUSINESS) VALUES
('6009','BYBBB','KYK',NULL,'1921','29 ST','DUNDAS','ON','L9Q4U2',NULL),
('6001','PYP',NULL,NULL,'2941','29 AVE','DUNDAS','ON','L9Y0Q2','B'),
('6006','BYB','IYI',NULL,'8293','28 ST','VANCOUVER','BC','V6Z2V6',NULL),
('6013','QYQ',NULL,NULL,'9487','49 ST','VANCOUVER','BC','V6Z1V5','A'),
('6005','PYPP',NULL,NULL,'1029','28 ST','VANCOUVER','BC','V6Z5Z3','A'),
('6012','BYBBB','MY','RR','5938','38 ST','VANCOUVER','BC','V6Z5K8',NULL),
('6014','CYC','KYK',NULL,'5281','69 ST','HAMILTON','ON','L8Z0Q3',NULL),
('6003','BYB','IYIII',NULL,'1294','39 ST','HAMILTON','ON','L8Z9N1',NULL),
('6010','QYQQQ',NULL,NULL,'6932','39 ST','HAMILTON','ON','L8Z0U7','A'),
('6002','QYQQQ',NULL,NULL,'7482','49 ST','HAMILTON','ON','L8Z2Q7','A'),
('6008','DYD','NYN',NULL,'2932','39 AVE','DUNDAS','ON','L9Q1Q0',NULL),
('6004','PYP',NULL,NULL,'7359','78 AVE','HAMILTON','ON','L8Z0N2','B'),
('6007','FY','JYJ','S','3292','59 ST','HAMILTON','ON','L8Z0M2',NULL),
('6011','AYAA','NYNN',NULL,'2104','68 AVE','HAMILTON','ON','L8Z0Q9',NULL);

INSERT INTO PROPERTY (PROPERTY_NO,STREET_NO,STREET,CITY,PROVINCE,POSTAL_CODE,OVERSEEN_BY,OWNED_BY,TYPE) VALUES
('3016','1299','9 ST','VANCOUVER','BC','V6T2H2','9001','5003','AP'),
('3017','4092','2 ST','VANCOUVER','BC','V6S6U4','9019','5001','TH'),
('3012','6541','4 ST','DUNDAS','ON','L9R8I2','9003','5012','AP'),
('3013','3252','2 AVE','DUNDAS','ON','L9G7G2','9015','5016','TH'),
('3014','3939','5 ST','DUNDAS','ON','L9W7R9','9028','5006','TH'),
('3001','1280','1 ST','HAMILTON','ON','L8U2I9','9030','5007','TH'),
('3011','2534','3 ST','DUNDAS','ON','L9D8D1','9028','5010','TH'),
('3002','1120','7 AVE','HAMILTON','ON','L8Y5S9','9029','5013','AP'),
('3003','2230','6 AVE','HAMILTON','ON','L8H7I2','9031','5004','HO'),
('3015','1099','9 AVE','DUNDAS','ON','L9T9I3','9029','5012','AP'),
('3009','1259','2 ST','HAMILTON','ON','L8H5P7','9021','5014','HO'),
('3010','3942','6 AVE','DUNDAS','ON','L9J3I9','9016','5009','AP'),
('3004','1245','3 ST','HAMILTON','ON','L8E2G9','9032','5015','AP'),
('3005','2223','1 AVE','HAMILTON','ON','L8W5I2','9002','5015','TH'),
('3006','5430','5 ST','HAMILTON','ON','L8F7I9','9022','5008','HO'),
('3007','1230','8 AVE','HAMILTON','ON','L8S8F4','9032','5005','HO'),
('3008','1029','1 ST','HAMILTON','ON','L8G6I5','9017','5016','AP'),
('3018','4038','5 AVE','VANCOUVER','BC','V6H5T4','9011','5010','AP'),
('3019','1492','1 AVE','VANCOUVER','BC','V6R3R6','9019','5002','HO'),
('3020','2038','8 ST','VANCOUVER','BC','V6O2U8','9014','5011','HO');

INSERT INTO RENTAL_AGREEMENT (PROPERTY_NO,RENTAL_NO,SIGNING_DATE,STARTING_DATE,ENDING_DATE,RENTER_NO) VALUES
('3008','0002','2005-12-15','2006-01-15','2006-05-01','6004'),
('3010','0001','2006-01-15','2006-03-15','2007-03-15','6001'),
('3006','0001','2006-03-01','2006-05-01','2007-03-01','6003'),
('3006','0002','2007-01-15','2007-04-15','2008-05-01','6002'),
('3007','0001','2007-01-15','2007-04-15','2008-05-01','6014'),
('3004','0001','2006-02-01','2006-03-15','2007-01-01','6010'),
('3004','0002','2007-02-01','2007-04-15','2008-02-01','6008'),
('3001','0001','2006-01-01','2006-03-01','2007-03-01','6007'),
('3001','0002','2007-01-01','2007-04-01','2008-05-01','6011'),
('3005','0001','2008-01-01','2008-04-01','2008-11-01','6009'),
('3018','0003','2007-03-15','2007-05-15','2008-05-15','6012'),
('3019','0001','2005-02-15','2005-05-15','2006-05-15','6012'),
('3017','0001','2011-02-15','2011-05-15','2012-05-15','6006'),
('3018','0001','2005-02-15','2005-05-15','2006-03-15','6013'),
('3008','0001','2005-12-15','2006-01-15','2006-05-01','6004'),
('3018','0002','2006-01-15','2006-03-15','2007-05-15','6005'),
('3010','0002','2007-01-15','2007-03-15','2008-05-15','6001'),
('3010','0003','2008-10-15','2008-11-01','2009-11-15','6009'),
('3012','0001','2005-01-15','2005-03-15','2006-03-15','6011'),
('3013','0001','2010-02-15','2010-03-15','2011-04-15','6011'),
('3014','0001','2011-02-15','2011-05-15','2012-05-15','6010'),
('3001','0003','2008-08-01','2008-09-15','2009-01-01','6007'),
('3001','0004','2009-01-30','2009-03-01','2011-05-01','6002'),
('3019','0002','2011-01-15','2011-03-15','2012-05-15','6013');

INSERT INTO STAFF_EMAIL (EMAIL_ADDR,STAFF_NO) VALUES
('AA21@II.HHH','9001'),
('KK22@JJ.FFF','9002'),
('FFF23@IIII.CC','9003'),
('F24@GGG.HHH','9004'),
('FFF25@GGG.CC','9011'),
('AAA26@JJJ.HHH','9019'),
('FFF27@GGG.HHH','9013'),
('F28@GGG.HHH','9014'),
('AA29@GGG.HHH','9019'),
('FFF31@III.CC','9013'),
('F33@GGG.HHH','9019'),
('II34@GGG.CCC','9013'),
('FFF36@GGG.HHH','9018'),
('FFF38@JJJ.HHH','9021'),
('F39@GGG.HHH','9012'),
('KK41@GGG.CC','9017'),
('F43@GGG.HHH','9012'),
('AAA45@GG.CCC','9017'),
('I46@GG.HHH','9022'),
('KK47@GG.HHH','9016'),
('FFF48@JJJ.HHH','9028'),
('KK49@GGG.CCC','9015'),
('FFF50@II.HHH','9029'),
('BBB1@GGG.HHH','9030'),
('II52@JJJ.CCC','9031'),
('FFF53@III.CCC','9032');

INSERT INTO OWNER_EMAIL (EMAIL_ADDR,OWNER_NO) VALUES
('AAA31@GGGG.CCC','5003'),
('BB31@JJ.DDD','5003'),
('AAA31@III.CC','5003'),
('AAA32@JJJJ.HHH','5011'),
('C33@JJJJJJ.CCC','5002'),
('AAA34@III.CCC','5001'),
('EEEE41@GGGGG.CCC','5010'),
('BB43@JJJJ.CCC','5009'),
('AAA45@III.CC','5014'),
('KK46@III.HH','5005'),
('AAA51@GGG.CCC','5008'),
('KK45@III.DDDD','5014'),
('E46@GGGGGG.CCC','5005'),
('I51@JJJ.CCC','5008'),
('JJ52@III.HHH','5015'),
('AAA56@III.DDD','5004'),
('AAA57@JJJ.DD','5013'),
('AAA58@III.CCC','5007'),
('DD59@JJJJJ.DD','5012'),
('AAA61@III.CCC','5006'),
('EEE64@GGGGGG.DD','5016');

INSERT INTO BRANCH_EMAIL (EMAIL_ADDR,BRANCH_NO) VALUES
('AAA99@JJ.HHH','1000'),
('AAA97@II.HHH','3000'),
('C98@JJJJ.DD','2000'),
('EEEE96@GGGG.CC','4000');

INSERT INTO RENTER_PHONE (AREA_CODE,PHONE_NO,EXTENSION,RENTER_NO) VALUES
('604','4151234',NULL,'6006'),
('604','4129873',NULL,'6013'),
('604','3029485','123','6013'),
('604','6934831',NULL,'6005'),
('604','2346843','13323','6012'),
('905','9012345',NULL,'6014'),
('905','9123122','27231','6004'),
('905','9123133','27224','6004'),
('905','9283413',NULL,'6004'),
('905','9052131',NULL,'6007'),
('289','9345314','24','6011'),
('289','9736525',NULL,'6003'),
('905','9345123','2924','6010'),
('905','9015314',NULL,'6002'),
('905','9124344',NULL,'6008'),
('289','9134514',NULL,'6009'),
('905','9035632',NULL,'6001');

INSERT INTO STAFF_PHONE (AREA_CODE,PHONE_NO,EXTENSION,STAFF_NO) VALUES
('604','4381094',NULL,'9001'),
('905','3234132','2349','9002'),
('289','9234514',NULL,'9003'),
('905','6453542',NULL,'9004'),
('604','8947281','23','9011'),
('604','6784242',NULL,'9014'),
('604','4476754',NULL,'9019'),
('604','4141434','12344','9013'),
('604','4123144','124','9013'),
('604','4113435',NULL,'9018'),
('289','2451451',NULL,'9021'),
('905','2431854','1345','9012'),
('905','6217145','21','9017'),
('905','6217145','06','9022'),
('905','6217145','05','9016'),
('905','6211231','12','9016'),
('289','9945314',NULL,'9028'),
('905','2562610',NULL,'9015'),
('905','2569251',NULL,'9029'),
('905','4328956',NULL,'9030'),
('289','2591483','12847','9031'),
('905','8342194',NULL,'9032');

INSERT INTO OWNER_PHONE (AREA_CODE,PHONE_NO,EXTENSION,OWNER_NO) VALUES
('604','4151564',NULL,'5003'),
('604','4359314',NULL,'5011'),
('604','6551544','255','5002'),
('604','6551231',NULL,'5002'),
('604','6551544','258','5001'),
('604','7934083',NULL,'5001'),
('604','5840314',NULL,'5010'),
('905','4328451',NULL,'5009'),
('289','9232133',NULL,'5014'),
('905','7247616',NULL,'5005'),
('289','5734514',NULL,'5005'),
('905','4313956',NULL,'5008'),
('289','9249103',NULL,'5015'),
('905','3231956',NULL,'5015'),
('905','4145154','05579','5015'),
('905','2449283','05','5004'),
('905','3920390',NULL,'5013'),
('289','3042999',NULL,'5007'),
('905','5043914','15','5012'),
('905','5145994',NULL,'5012'),
('289','9234534',NULL,'5006'),
('905','4329509',NULL,'5016');

INSERT INTO BRANCH_PHONE (AREA_CODE,PHONE_NO,EXTENSION,BRANCH_NO) VALUES
('905','4334928',NULL,'2000'),
('905','9418543',NULL,'1000'),
('289','2485945',NULL,'3000'),
('604','5493103',NULL,'4000');

INSERT INTO VIEWING (PROPERTY_NO,RENTER_NO,VIEWING_DATE) VALUES
('3018','6013','2005-02-01'),
('3007','6014','2007-01-03'),
('3001','6007','2005-12-18'),
('3012','6011','2005-01-02'),
('3012','6011','2010-02-08'),
('3014','6010','2011-01-28'),
('3017','6006','2005-01-30'),
('3001','6007','2005-12-22'),
('3001','6002','2008-12-09'),
('3004','6010','2006-01-31'),
('3004','6008','2007-01-20'),
('3005','6009','2007-12-30'),
('3006','6002','2006-12-15'),
('3019','6012','2005-02-01'),
('3019','6012','2005-02-03'),
('3019','6012','2005-02-10'),
('3019','6012','2005-02-14'),
('3006','6002','2006-12-18'),
('3008','6004','2005-11-30'),
('3010','6001','2006-01-10'),
('3010','6009','2008-09-28'),
('3018','6012','2007-03-01'),
('3019','6013','2011-01-11');

INSERT INTO NEWSPAPER (PAPER_NAME) VALUES
('THE GLOBE AND MAIL'),
('NATIONAL POST');

INSERT INTO ADVERTISEMENT (PAPER_NAME,AD_NO,AD_DATE,PROPERTY_NO) VALUES
('THE GLOBE AND MAIL','001','2005-01-01','3019'),
('THE GLOBE AND MAIL','002','2005-01-04','3018'),
('THE GLOBE AND MAIL','003','2005-01-04','3016'),
('THE GLOBE AND MAIL','004','2005-01-05','3019'),
('THE GLOBE AND MAIL','005','2005-10-20','3008'),
('THE GLOBE AND MAIL','006','2005-10-20','3003'),
('THE GLOBE AND MAIL','007','2005-10-20','3001'),
('THE GLOBE AND MAIL','008','2005-10-22','3003'),
('THE GLOBE AND MAIL','009','2005-12-31','3004'),
('THE GLOBE AND MAIL','010','2006-01-02','3010'),
('THE GLOBE AND MAIL','011','2006-01-02','3016'),
('THE GLOBE AND MAIL','012','2006-01-03','3010'),
('THE GLOBE AND MAIL','013','2006-12-02','3006'),
('THE GLOBE AND MAIL','014','2007-01-15','3018'),
('THE GLOBE AND MAIL','015','2007-01-16','3020'),
('THE GLOBE AND MAIL','016','2011-01-01','3019'),
('NATIONAL POST','001','2005-01-01','3012'),
('NATIONAL POST','002','2005-01-04','3017'),
('NATIONAL POST','003','2005-01-05','3018'),
('NATIONAL POST','004','2005-01-05','3016'),
('NATIONAL POST','005','2005-01-05','3003'),
('NATIONAL POST','006','2005-01-10','3019'),
('NATIONAL POST','007','2005-10-20','3008'),
('NATIONAL POST','008','2005-10-20','3001'),
('NATIONAL POST','009','2005-10-22','3008'),
('NATIONAL POST','010','2005-12-31','3004'),
('NATIONAL POST','011','2005-12-31','3010'),
('NATIONAL POST','012','2006-01-02','3004'),
('NATIONAL POST','013','2006-12-01','3006'),
('NATIONAL POST','014','2006-12-30','3004'),
('NATIONAL POST','015','2007-01-30','3018'),
('NATIONAL POST','016','2007-01-30','3020'),
('NATIONAL POST','017','2010-12-30','3014'),
('NATIONAL POST','018','2010-12-31','3014');

INSERT INTO RENTER_EMAIL (EMAIL_ADDR,RENTER_NO) VALUES
('AAA11@BBB.CC','6006'),
('AAA12@DDD.FFF','6013'),
('EE13@B.CCC','6005'),
('AA14@DDD.CC','6005'),
('KKK15@BB.CCC','6012'),
('A16@BBB.CCC','6014'),
('EEE17@B.HH','6004'),
('AAA18@DDD.CC','6004'),
('EEE19@BBB.CCC','6004'),
('KKK20@B.HHH','6007'),
('A21@BBB.CCC','6011'),
('AAA22@BBB.FFF','6003'),
('EEE23@DDD.CC','6003'),
('A24@BBB.CCC','6010'),
('III25@BBB.C','6002'),
('EEE26@BBB.CCC','6008'),
('AAA27@DDD.FFF','6008'),
('EE28@BBB.CCC','6009'),
('AA29@BBB.C','6001');

ALTER TABLE BRANCH
ADD CONSTRAINT MANAGER_CNST FOREIGN KEY (MANAGER) REFERENCES STAFF(STAFF_NO);

TERMINATE;
	
		

query.db2

		
-- Course: SE 4M03
-- Student: Daryl Dippel
-- Student No: 0750884

CONNECT TO SE4M03;

-- QUERY 1
-- Give staff members numbers whose salary is > 5000

SELECT DISTINCT STAFF.STAFF_NO
  FROM STAFF
  WHERE STAFF.SALARY > 5000;

-- QUERY 2
-- Give the renter number of each of the renters who has a viewing record. Please, avoid duplications.

SELECT DISTINCT VIEWING.RENTER_NO
  FROM VIEWING;
    
-- QUERY 3
-- Give the dates of all the advertisements posted in THE GLOBE AND MAIL in 2005. Please, avoid duplications.

SELECT DISTINCT ADVERTISEMENT.AD_DATE
  FROM ADVERTISEMENT
 WHERE PAPER_NAME = 'THE GLOBE AND MAIL'
   AND year(AD_DATE) = 2005
 ;
  
-- QUERY 4
-- Give the email addresses and the renter number for all the private renters. Please, sort them by the renter number. 

SELECT  S.EMAIL_ADDR, T.RENTER_NO
FROM (	SELECT RENTER.RENTER_NO, RENTER.TYPE_OF_BUSINESS
          FROM RENTER
		 WHERE TYPE_OF_BUSINESS IS NULL
	  ) AS T,
     (  SELECT RENTER_EMAIL.RENTER_NO, RENTER_EMAIL.EMAIL_ADDR
          FROM RENTER_EMAIL
      ) AS S
WHERE T.RENTER_NO=S.RENTER_NO
ORDER BY RENTER_NO ASC
;

-- QUERY 5
-- Find the properties that are already advertised but not yet rented. Please, avoid duplications. 

SELECT  T.PROPERTY_NO
FROM (  SELECT PROPERTY.PROPERTY_NO
          FROM PROPERTY
      ) AS T,
	  (  SELECT DISTINCT ADVERTISEMENT.PROPERTY_NO
          FROM ADVERTISEMENT
      ) AS S
WHERE T.PROPERTY_NO=S.PROPERTY_NO
MINUS
SELECT  RENTAL_AGREEMENT.PROPERTY_NO
  FROM  RENTAL_AGREEMENT
 WHERE  ENDING_DATE <= '03/17/2011'
   OR   STARTING_DATE >= '03/17/2011'
;

-- QUERY 6
-- Give the names and the branch numbers of all the staff members working in the branch which is located in HAMILTON. The names should be listed in an alphabetic order (by last, then by first, then by middle names). 

SELECT  T.FIRST_NAME, T.MIDDLE_NAME, T.LAST_NAME, S.BRANCH_NO
FROM  ( SELECT STAFF.FIRST_NAME, STAFF.MIDDLE_NAME, STAFF.LAST_NAME, STAFF.ALLOCATED_TO
	  FROM STAFF
      )     AS T,
      (  
      SELECT BRANCH.BRANCH_NO
	FROM BRANCH
       WHERE CITY = 'HAMILTON'
      )   AS S
WHERE T.ALLOCATED_TO=S.BRANCH_NO
ORDER BY LAST_NAME, FIRST_NAME, MIDDLE_NAME DESC;

-- QUERY 7
-- Give the staff numbers and the names of all the workers who live on the same street, city, and province as their manager. The names should be listed in an alphabetic order (by last, then by first, then by middle names).

SELECT  T.STAFF_NO, T.FIRST_NAME, T.MIDDLE_NAME, T.LAST_NAME
FROM  ( SELECT STAFF_NO, FIRST_NAME, MIDDLE_NAME, LAST_NAME, ALLOCATED_TO, STREET, CITY, PROVINCE
	FROM STAFF
      )     AS T,
      (  
     SELECT MANAGER, BRANCH_NO, STREET, CITY, PROVINCE
	FROM BRANCH
      )   AS S
WHERE 	T.ALLOCATED_TO = S.BRANCH_NO AND
	T.CITY = S.CITY AND
	T.STREET = S.STREET AND
	T.PROVINCE = S.PROVINCE
ORDER BY LAST_NAME, FIRST_NAME, MIDDLE_NAME DESC;

-- Does not return expected results but upon checking
-- ('9028','F','KKK','SS','205','20 ST','DUNDAS','ON','L9A1A2','F',5200.00,'2000'),
-- does not match any manager

-- QUERY 8
-- Find the branch number and the average salary of the branch that has the highest average salary. Please, call the branch number as BRANCH_NO and the average salary as AVG_SALARY


SELECT T.ALLOCATED_TO AS BRANCH_NO, T.AVG_SALARY
  FROM	(
	SELECT ALLOCATED_TO, AVG(SALARY) AS AVG_SALARY
	  FROM STAFF
	 GROUP BY ALLOCATED_TO
	 ORDER BY AVG_SALARY DESC
	) AS T,
	(
	SELECT MAX(AVG_SALARY) AS MAX_SALARY
	 FROM(
		SELECT ALLOCATED_TO, AVG(SALARY) AS AVG_SALARY
		  FROM STAFF
		 GROUP BY ALLOCATED_TO
		 ORDER BY AVG_SALARY DESC
		)
	) AS S
 
 WHERE T.AVG_SALARY = S.MAX_SALARY
 ;

 
-- QUERY 9
-- Find the owners and renters who have 2 or more phone numbers. Call the owner/renter number as CUSTOMER_NO, set the value of TYPE_OF_CUSTOMER to 'OWNER' if the customer is an owner, and to 'RENTER' if he/she is a renter. Please, only list the CUSTOMER_NO and TYPE_OF_CUSTOMER


SELECT OWNER_NO, 'OWNER' AS TYPE_OF_CUSTOMER
FROM (SELECT OWNER_PHONE.OWNER_NO,C
      FROM OWNER_PHONE, (SELECT OWNER_NO,COUNT(*) AS C
                     FROM OWNER_PHONE
                     GROUP BY OWNER_NO) AS T
      WHERE OWNER_PHONE.OWNER_NO=T.OWNER_NO)
WHERE C > 1
GROUP BY OWNER_NO
UNION
SELECT RENTER_NO, 'RENTER' AS TYPE_OF_CUSTOMER
FROM (SELECT RENTER_PHONE.RENTER_NO,C
      FROM RENTER_PHONE, (SELECT RENTER_NO,COUNT(*) AS C
                     FROM RENTER_PHONE
                     GROUP BY RENTER_NO) AS T
      WHERE RENTER_PHONE.RENTER_NO=T.RENTER_NO)
WHERE C > 1
GROUP BY RENTER_NO
;

-- QUERY 10
-- Assuming that each advertisement costs 100 dollars, give the branch number and the amount spent on the advertisements for each branch. Name the branch number as BRANCH_NO, and the amount as AD_COST.

SELECT ALLOCATED_TO AS BRANCH_NO,COUNT(*)*100 AS AD_COST
                     FROM (
			SELECT Y.PROPERTY_NO, Z.ALLOCATED_TO
			FROM
			(SELECT PROPERTY_NO
			FROM ADVERTISEMENT) AS Y,
			(SELECT PROPERTY_NO, OVERSEEN_BY
			FROM PROPERTY) AS X,
			(SELECT STAFF_NO, ALLOCATED_TO
			FROM STAFF) AS Z
			WHERE Y.PROPERTY_NO = X.PROPERTY_NO
			AND X.OVERSEEN_BY = Z.STAFF_NO)
		GROUP BY ALLOCATED_TO;
		
SELECT DISTINCT STAFF.STAFF_NO AS DIPPELDJ_0750884
  FROM STAFF
  WHERE STAFF.SALARY > 500000;
  
TERMINATE;

	
		

show.db2

		
-- SE 4M03
-- Daryl Dippel
-- 0750884


CONNECT TO SE4M03;

ECHO =============== TABLE BRANCH =============== ;
SELECT * FROM BRANCH;
ECHO;

ECHO =============== TABLE STAFF =============== ;
SELECT * FROM STAFF;
ECHO;

ECHO =============== TABLE OWNER =============== ;
SELECT * FROM OWNER;
ECHO;

ECHO =============== TABLE RENTER =============== ;
SELECT * FROM RENTER;
ECHO;

ECHO =============== TABLE PROPERTY =============== ;
SELECT * FROM RENTAL_AGREEMENT;
ECHO;

ECHO =============== TABLE STAFF EMAIL =============== ;
SELECT * FROM STAFF_EMAIL;
ECHO;

ECHO =============== TABLE OWNER EMAIL =============== ;
SELECT * FROM OWNER_EMAIL;
ECHO;

ECHO =============== TABLE BRANCH EMAIL =============== ;
SELECT * FROM BRANCH_EMAIL;
ECHO;

ECHO =============== TABLE RENTER EMAIL =============== ;
SELECT * FROM RENTER_EMAIL;
ECHO;

ECHO =============== TABLE RENTER PHONE =============== ;
SELECT * FROM RENTER_PHONE;
ECHO;

ECHO =============== TABLE STAFF PHONE =============== ;
SELECT * FROM STAFF_PHONE;
ECHO;

ECHO =============== TABLE OWNER PHONE =============== ;
SELECT * FROM OWNER_PHONE;
ECHO;

ECHO =============== TABLE BRANCH PHONE =============== ;
SELECT * FROM BRANCH_PHONE;
ECHO;

ECHO =============== TABLE VIEWING =============== ;
SELECT * FROM VIEWING;
ECHO;

ECHO =============== TABLE NEWSPAPER =============== ;
SELECT * FROM NEWSPAPER;
ECHO;

ECHO =============== TABLE ADVERTISEMENT =============== ;
SELECT * FROM ADVERTISEMENT;
ECHO;



TERMINATE;