DROP TABLE workorder;
DROP TABLE contractor;
DROP TABLE complaints;
DROP TABLE rental_invoice;
DROP TABLE tenant_family;
DROP TABLE tenant_auto;
DROP TABLE tenant;
DROP TABLE rental;
DROP TABLE apartment;
DROP TABLE staff;

DROP sequence apartment_sequence1;
DROP sequence apartment_sequence2;
DROP sequence rental_sequence;
DROP sequence rental_invoice_sequence;
DROP sequence staff_sequence;
DROP sequence complaints_sequence;
DROP sequence contractor_sequence;
DROP sequence workorder_sequence;

CREATE TABLE apartment
(apt_no NUMBER(3) constraint apartment_pk primary key,
apt_type NUMBER(1) constraint apartment_type_ck CHECK ((apt_type = 0) OR (apt_type = 1) OR (apt_type = 2) OR (apt_type = 3)),
apt_status CHAR(1) constraint apartment_status_ck CHECK ((apt_status = 'R') OR (apt_status = 'V')),
apt_utility CHAR(1) constraint apartment_utility_ck CHECK ((apt_utility = 'Y') OR (apt_utility = 'N')),
flooring VARCHAR2(10),
balcony CHAR(1),
apt_deposit_amt NUMBER(3),
apt_rent_amt NUMBER(3));

CREATE TABLE staff
(staff_no VARCHAR2(5) constraint staff_pk primary key,
first_name VARCHAR2(15),
last_name VARCHAR2(15),
position VARCHAR2(12),
status CHAR(1) constraint staff_status_ck CHECK ((status='T') OR (status='R')),
gender CHAR(1),
dob DATE,
salary NUMBER(5));

CREATE TABLE rental
(rental_no NUMBER(6) constraint rental_pk primary key,
rental_date DATE constraint rental_date_nn NOT NULL,
rental_status CHAR(1)constraint rental_status_ck CHECK ((rental_status = 'S') OR (rental_status = 'O')),
lease_type VARCHAR2(3) DEFAULT 'One' constraint lease_type_ck CHECK ((lease_type = 'One') OR (lease_type = 'Six')),
lease_start DATE,
lease_end DATE,
staff_no VARCHAR2(5) constraint rental_apt_fk1 references staff,
apt_no NUMBER(3) constraint rental_apt_fk2 references apartment);

CREATE TABLE tenant
(tenant_ss NUMBER(9) constraint tenant_pk primary key,
tenant_name VARCHAR2(25),
tenant_dob DATE,
marital CHAR(1)constraint tenant_marital_ck CHECK ((marital = 'M') OR (marital = 'S')),
work_phone VARCHAR2(10),
home_phone VARCHAR2(10),
employer_name VARCHAR2(25),
gender CHAR(1) constraint tenant_gender_ck CHECK ((gender = 'M') OR (gender = 'F')),
email VARCHAR2(50),
credit_score NUMBER(3),
credit_agency VARCHAR2(15),
rental_no NUMBER(6) constraint tenant_rental_fk references rental);

CREATE TABLE tenant_auto
(tenant_ss NUMBER(9) constraint tenant_auto_fk references tenant,
license_no VARCHAR2(6),
auto_make VARCHAR2(15),
auto_model VARCHAR2(15),
auto_year NUMBER(4),
auto_color VARCHAR2(10),
parking_fee NUMBER(4,2),
constraint tenant_auto_pk primary key (tenant_ss,license_no));

CREATE TABLE tenant_family
(tenant_ss NUMBER(9) constraint tenant_family_fk references tenant,
family_ss NUMBER(9),
name VARCHAR2(25),
spouse CHAR(1) constraint family_spouse_ck CHECK ((spouse = 'Y') OR (spouse = 'N')),
child CHAR(1) constraint family_child_ck CHECK ((child = 'Y') OR (child = 'N')),
gender CHAR(1) constraint family_gender_ck CHECK ((gender = 'M') OR (gender = 'F')),
dob DATE,
constraint tenant_family_pk primary key(tenant_ss,family_ss));

CREATE TABLE rental_invoice
(invoice_no NUMBER(6) constraint rental_invoice_pk primary key,
invoice_date DATE,
invoice_due NUMBER(4),
cc_no NUMBER(16),
cc_type VARCHAR2(10),
cc_exp_date DATE,
rental_no NUMBER(6) constraint rental_invoice_fk references rental);

CREATE TABLE complaints
(complaint_no NUMBER(6) constraint complaints_pk primary key,
complaint_date DATE,
rental_complaint VARCHAR2(100),
apt_complaint VARCHAR2(100),
rental_no NUMBER(6) constraint complaints_fk1 references rental,
apt_no NUMBER(3) constraint complaints_fk2 references apartment,
status CHAR(1) constraint complaint_status_ck CHECK ((status = 'F') OR (status = 'P') OR (status = NULL)));

CREATE TABLE contractor
(contractor_id VARCHAR2(5) constraint contractor_pk primary key,
name VARCHAR2(50),
work_type VARCHAR2(50),
street VARCHAR2(50),
city VARCHAR2(15),
state CHAR(2),
zip NUMBER(5),
email VARCHAR2(50),
phone VARCHAR2(10));

CREATE TABLE workorder
(workorder_no VARCHAR2(4) constraint workorder_pk primary key,
workorder_date DATE,
work_desc VARCHAR2(100),
work_type VARCHAR2(15),
tenant_in CHAR(1),
complaint_no NUMBER(6) constraint workorder_fk1 references complaints,
contractor_id VARCHAR2(5) constraint workorder_fk2 references contractor);

CREATE sequence apartment_sequence1
START WITH 100
nocache;

CREATE sequence apartment_sequence2
START WITH 200
nocache;

CREATE sequence staff_sequence
START WITH 200
increment BY 10
nocache;

CREATE sequence rental_sequence
START WITH 100101
nocache;

CREATE sequence rental_invoice_sequence
START WITH 1000
nocache;

CREATE sequence complaints_sequence
START WITH 10010
nocache;

CREATE sequence contractor_sequence
START WITH 1011
nocache;

CREATE sequence workorder_sequence
START WITH 101
nocache;

INSERT INTO apartment
VALUES (apartment_sequence1.NEXTVAL,0,'R','Y','Carpet','N',200,300);
INSERT INTO apartment
VALUES (apartment_sequence1.NEXTVAL,0,'R','N','Carpet','N',200,300);
INSERT INTO apartment
VALUES (apartment_sequence1.NEXTVAL,0,'R','Y','Carpet','N',200,300);
INSERT INTO apartment
VALUES (apartment_sequence1.NEXTVAL,1,'V','N','Carpet','N',300,400);
INSERT INTO apartment
VALUES (apartment_sequence1.NEXTVAL,1,'R','Y','Carpet','N',300,400);

INSERT INTO apartment
VALUES (apartment_sequence2.NEXTVAL,2,'V','Y','Hardwood','Y',400,500);
INSERT INTO apartment
VALUES (apartment_sequence2.NEXTVAL,2,'R','Y','Carpet','N',400,500);
INSERT INTO apartment
VALUES (apartment_sequence2.NEXTVAL,3,'V','Y','Hardwood','Y',500,700);
INSERT INTO apartment
VALUES (apartment_sequence2.NEXTVAL,3,'R','Y','Hardwood','Y',500,700);

INSERT INTO staff
VALUES('SA'||staff_sequence.NEXTVAL,'Joe','White','Assistant','T','M',TO_DATE('7/8/82','mm/dd/yy'),24000);
INSERT INTO staff
VALUES('SA'||staff_sequence.NEXTVAL,'Ann','Tremble','Assistant','T','F',TO_DATE('6/12/81','mm/dd/yy'),26000);
INSERT INTO staff
VALUES('SA'||staff_sequence.NEXTVAL,'Terry','Ford','Manager','R','M',TO_DATE('10/20/67','mm/dd/yy'),53000);
INSERT INTO staff
VALUES('SA'||staff_sequence.NEXTVAL,'Susan','Brandon','Supervisor','R','F',TO_DATE('3/10/77','mm/dd/yy'),46000);
INSERT INTO staff
VALUES('SA'||staff_sequence.NEXTVAL,'Julia','Roberts','Assistant','T','F',TO_DATE('9/12/82','mm/dd/yy'),28000);

INSERT INTO rental VALUES
(rental_sequence.NEXTVAL,TO_DATE('05/12/2016','mm/dd/yyyy'),'O','One',TO_DATE('6/1/2016','mm/dd/yyyy'),TO_DATE('5/31/2017','mm/dd/yyyy'),'SA200',201);
INSERT INTO rental VALUES
(rental_sequence.NEXTVAL,TO_DATE('05/21/2016','mm/dd/yyyy'),'O','Six',TO_DATE('6/1/2016','mm/dd/yyyy'),TO_DATE('11/30/2016','mm/dd/yyyy'),'SA220',102);
INSERT INTO rental VALUES
(rental_sequence.NEXTVAL,TO_DATE('10/12/2016','mm/dd/yyyy'),'O','Six',TO_DATE('11/1/2016','mm/dd/yyyy'),TO_DATE('4/30/2017','mm/dd/yyyy'),'SA240',203);
INSERT INTO rental VALUES
(rental_sequence.NEXTVAL,TO_DATE('03/6/2017','mm/dd/yyyy'),'O','One',TO_DATE('4/1/2017','mm/dd/yyyy'),TO_DATE('3/31/2018','mm/dd/yyyy'),'SA210',101);
INSERT INTO rental VALUES
(rental_sequence.NEXTVAL,TO_DATE('4/15/2017','mm/dd/yyyy'),'O','One',TO_DATE('5/1/2017','mm/dd/yyyy'),TO_DATE('4/30/2018','mm/dd/yyyy'),'SA220',104);
INSERT INTO rental VALUES
(rental_sequence.NEXTVAL,TO_DATE('7/15/2017','mm/dd/yyyy'),'S','One',TO_DATE('8/1/2017','mm/dd/yyyy'),TO_DATE('7/31/2018','mm/dd/yyyy'),'SA200',100);

INSERT INTO tenant VALUES
(123456789,'Jack Robin',TO_DATE('6/21/1960','mm/dd/yyyy'),'M','4173452323','4175556565','Kraft Inc.','M','[email protected]',650,'Equifax',100101);
INSERT INTO tenant VALUES
(723556089,'Mary Stackles',TO_DATE('8/2/1980','mm/dd/yyyy'),'S','4175453320','4176667565','Kraft Inc.','F','[email protected]',675,'Experian',100102);
INSERT INTO tenant VALUES
(450452267,'Ramu Reddy',TO_DATE('4/11/1962','mm/dd/yyyy'),'M','4178362323','4172220565','MSU','M','[email protected]',755,'Equifax',100103);
INSERT INTO tenant VALUES
(223056180,'Marion Black',TO_DATE('5/25/1981','mm/dd/yyyy'),'S','4174257766', '4176772364', 'MSU','M','[email protected]',720,'Equifax',100104);
INSERT INTO tenant VALUES
(173662690,'Venessa Williams',TO_DATE('3/12/1970','mm/dd/yyyy'),'M','4175557878', '4173362565','Kraft Inc.','F','[email protected]',740,'Experian',100105);

INSERT INTO tenant_auto VALUES
(123456789,'SYK332','Ford','Taurus',1999,'Red',0);
INSERT INTO tenant_auto VALUES
(123456789,'TTS430','Volvo','GL 740',1990,'Green',45.50);
INSERT INTO tenant_auto VALUES
(723556089,'ABC260','Toyota','Lexus',2000,'Maroon',0);
INSERT INTO tenant_auto VALUES
(450452267,'LLT322','Honda','Accord',2001,'Blue',0);
INSERT INTO tenant_auto VALUES
(450452267,'KYK100','Toyota','Camry',1999,'Black',45.50);
INSERT INTO tenant_auto VALUES
(223056180,'FLT232','Honda','Civic',1999,'Red',0);
INSERT INTO tenant_auto VALUES
(173662690,'LLT668','Volvo','GL 980',2000,'Velvet',0);

INSERT INTO tenant_family VALUES
(123456789,444663434,'Kay Robin','Y','N','F',TO_DATE('6/21/1965','mm/dd/yyyy'));
INSERT INTO tenant_family VALUES
(450452267,222664343,'Sarla Reddy','Y','N','F',TO_DATE('6/11/1965','mm/dd/yyyy'));
INSERT INTO tenant_family VALUES
(450452267,222663434,'Anjali Reddy','N','Y','F',TO_DATE('8/10/1990','mm/dd/yyyy'));
INSERT INTO tenant_family VALUES
(173662690,111444663,'Terry Williams','Y','N','F',TO_DATE('3/21/1968','mm/dd/yyyy'));
INSERT INTO tenant_family VALUES
(173662690,242446634,'Tom Williams','N','Y','M',TO_DATE('5/20/1991','mm/dd/yyyy'));

INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('5/12/2016','mm/dd/yyyy'),500,1234567890123456,'visa',TO_DATE('12/19','mm/yy'),100101);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('6/30/2016','mm/dd/yyyy'),500,1234567890123456,'visa',TO_DATE('12/19','mm/yy'),100101);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('7/30/2016','mm/dd/yyyy'),500,1234567890123456,'visa',TO_DATE('12/19','mm/yy'),100101);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('8/30/2016','mm/dd/yyyy'),500,1234567890123456,'visa',TO_DATE('12/19','mm/yy'),100101);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('9/30/2016','mm/dd/yyyy'),500,1234567890123456,'mastercard',TO_DATE('12/19','mm/yy'),100101);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('10/30/2016','mm/dd/yyyy'),500,1234567890123456,'mastercard',TO_DATE('12/19','mm/yy'),100101);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('11/30/2016','mm/dd/yyyy'),500,1234567890123456,'visa',TO_DATE('12/19','mm/yy'),100101);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('12/30/2016','mm/dd/yyyy'),500,1234567890123456,'visa',TO_DATE('12/19','mm/yy'),100101);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('1/30/2017','mm/dd/yyyy'),500,1234567890123456,'visa',TO_DATE('12/19','mm/yy'),100101);

INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('5/21/2016','mm/dd/yyyy'),300,3343567890123456,'mastercard',TO_DATE('10/20','mm/yy'),100102);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('6/30/2016','mm/dd/yyyy'),300,3343567890123456,'mastercard',TO_DATE('10/20','mm/yy'),100102);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('7/30/2016','mm/dd/yyyy'),300,3343567890123456,'mastercard',TO_DATE('10/20','mm/yy'),100102);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('8/30/2016','mm/dd/yyyy'),300,3343567890123456,'mastercard',TO_DATE('10/20','mm/yy'),100102);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('9/30/2016','mm/dd/yyyy'),300,3343567890123456,'mastercard',TO_DATE('10/20','mm/yy'),100102);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('10/30/2016','mm/dd/yyyy'),300,3343567890123456,'mastercard',TO_DATE('10/20','mm/yy'),100102);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('11/30/2016','mm/dd/yyyy'),300,3343567890123456,'mastercard',TO_DATE('10/20','mm/yy'),100102);

INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('10/12/2016','mm/dd/yyyy'),700,8654567890123296,'discover',TO_DATE('11/20','mm/yy'),100103);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('11/30/2016','mm/dd/yyyy'),700,8654567890123296,'discover',TO_DATE('11/20','mm/yy'),100103);

INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('3/6/2017','mm/dd/yyyy'),500,7766567890123203,'visa',TO_DATE('09/19','mm/yy'),100104);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('4/30/2017','mm/dd/yyyy'),300,7766567890123203,'visa',TO_DATE('09/19','mm/yy'),100104);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('5/30/2017','mm/dd/yyyy'),300,7766567890123203,'visa',TO_DATE('09/19','mm/yy'),100104);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('6/30/2017','mm/dd/yyyy'),300,7766567890123203,'visa',TO_DATE('09/19','mm/yy'),100104);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('7/30/2017','mm/dd/yyyy'),300,7766567890123203,'visa',TO_DATE('09/19','mm/yy'),100104);

INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('4/15/2017','mm/dd/yyyy'),700,6599567890126211,'visa',TO_DATE('12/21','mm/yy'),100105);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('5/30/2017','mm/dd/yyyy'),400,6599567890126211,'visa',TO_DATE('12/21','mm/yy'),100105);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('6/30/2017','mm/dd/yyyy'),400,6599567890126211,'discover',TO_DATE('12/20','mm/yy'),100105);
INSERT INTO rental_invoice VALUES
(rental_invoice_sequence.NEXTVAL,TO_DATE('7/30/2017','mm/dd/yyyy'),400,6599567890126211,'discover',TO_DATE('12/20','mm/yy'),100105);

INSERT INTO complaints VALUES
(complaints_sequence.NEXTVAL,TO_DATE('12/12/2017','mm/dd/yyyy'),'kitchen sink clogged',NULL,100103,203,'F');
INSERT INTO complaints VALUES
(complaints_sequence.NEXTVAL,TO_DATE('8/17/2018','mm/dd/yyyy'),'water heater not working',NULL,100105,104,'F');
INSERT INTO complaints VALUES
(complaints_sequence.NEXTVAL,TO_DATE('9/17/2018','mm/dd/yyyy'),'room heater does not work',NULL,100105,104,NULL);
INSERT INTO complaints VALUES
(complaints_sequence.NEXTVAL,TO_DATE('9/17/2018','mm/dd/yyyy'),NULL,'air conditioning not working',NULL,103,NULL);
INSERT INTO complaints VALUES
(complaints_sequence.NEXTVAL,TO_DATE('10/20/2018','mm/dd/yyyy'),'car parking spots not clear',NULL,100103,203,NULL);
INSERT INTO complaints VALUES
(complaints_sequence.NEXTVAL,TO_DATE('11/8/2018','mm/dd/yyyy'),'dryer not working',NULL,100104,101,'F');
INSERT INTO complaints VALUES
(complaints_sequence.NEXTVAL,TO_DATE('11/16/2018','mm/dd/yyyy'),NULL,'washer not working',NULL,202,NULL);

INSERT INTO contractor
VALUES('C'||contractor_sequence.NEXTVAL,'Tony Home Repairs','Electric,Plumbing','727 W Sunshine Street','Springfield','MO',65804,'[email protected]','4178829223');
INSERT INTO contractor
VALUES('C'||contractor_sequence.NEXTVAL,'Mr Fix It','Electric,Plumbing,Remodeling','102 S Cox Ave','Springfield','MO',65802,'[email protected]','7611522515');
INSERT INTO contractor
VALUES('C'||contractor_sequence.NEXTVAL,'Sunny Home Solutions','Electric,Plumbing,Remodeling','2215 N Sexton Dr','Nixa','MO',65714,'[email protected]','4174251155');
INSERT INTO contractor
VALUES('C'||contractor_sequence.NEXTVAL,'Affordable Repairs','Electric,Plumbing','115 W Oak Ave','Springdale','AR',72764,'[email protected]','8629205252');

INSERT INTO workorder
VALUES('C'||workorder_sequence.NEXTVAL,TO_DATE('12/13/2017','mm/dd/yyyy'),'kitchen sink clogged','Plumbing','Y',10010,'C1011');
INSERT INTO workorder
VALUES('C'||workorder_sequence.NEXTVAL,TO_DATE('8/18/2017','mm/dd/yyyy'),'water heater not working','Plumbing','N',10011,'C1012');
INSERT INTO workorder
VALUES('C'||workorder_sequence.NEXTVAL,TO_DATE('9/20/2018','mm/dd/yyyy'),'room heating does not work','Plumbing','N',10012,'C1011');
INSERT INTO workorder
VALUES('C'||workorder_sequence.NEXTVAL,TO_DATE('9/19/2018','mm/dd/yyyy'),'air conditioning not working','Electric','Y',10013,'C1012');
INSERT INTO workorder
VALUES('C'||workorder_sequence.NEXTVAL,TO_DATE('10/21/2018','mm/dd/yyyy'),'car parking spots not clear - repaint parking','Remodeling','N',10014,'C1013');
INSERT INTO workorder
VALUES('C'||workorder_sequence.NEXTVAL,TO_DATE('11/11/2018','mm/dd/yyyy'),'dryer not working - no drying','Electric','Y',10015,'C1011');
INSERT INTO workorder
VALUES('C'||workorder_sequence.NEXTVAL,TO_DATE('11/17/2018','mm/dd/yyyy'),'washer not working - regular setting does not work','Electric','N',10016,'C1014');


COMMIT;

CREATE OR REPLACE PROCEDURE w8_4_staff_apartment_details IS
CURSOR apt_staff IS
SELECT first_name, last_name, position, apt_no
FROM staff, rental
;

SELECT apt_no
FROM rental;
DBMS_OUTPUT.put_line('Staff apartment details');
DBMS_OUTPUT.put_line('-------------------------');
DBMS_OUTPUT.put_line('Apartment #');


END;

SELECT staff.first_name, staff.last_name, staff.position, rental.apt_no, rental.staff_no
FROM rental,staff
WHERE staff.staff_no = rental.staff_no;

Question IS

Superflex Apartment database: CREATE a web page TO display a list OF apartments that have been rented BY various staff(USE HTML tabletag). The web page either displayed FOR each staff either the apartment NUMBER OR a message “No Apartment Rented”  IN CASE the staff has no associated rental. The web page layout IS shown below, WITH attributes FROM database IN italics.


I can handle the HTML portion i just need help TO SET up the proper join?