Customers and Agents Database

Given the SQL statements below:

• Develop an entity-relationship diagram that will show data relationships Using either Visio or Oracle Datamodeler.
• Develop SQL Insert statements to insert at least 3(three) example records in the Agents table and Customers table.
• Develop SQL update statements to update one record in the Agents table and one record in the Customers table.
• Develop SQL delete statements to delete one record in the Agents table and one record in the Customers table.
• Develop SQL statement drop the CustAgentList table.
• Develop 6 queries that use data contained in more than one table to answer a specific business question (must have at least one of: Count, AVG, SUM, and either Max or Min),
  • State the business question.(example: How many Agents in our system have a License status of 'Deceased' ?)
  • Show the SQL required to answer each question.
  • Show resulting answer from your data.

DROP TABLE Agents CASCADE CONSTRAINTS PURGE;
DROP TABLE ContactReason CASCADE CONSTRAINTS PURGE;
DROP TABLE CustAgentList CASCADE CONSTRAINTS PURGE;
DROP TABLE Customers CASCADE CONSTRAINTS PURGE;
DROP TABLE LicenseStatus CASCADE CONSTRAINTS PURGE;
DROP TABLE Listings CASCADE CONSTRAINTS PURGE;
DROP TABLE Properties CASCADE CONSTRAINTS PURGE;
DROP TABLE SaleStatus CASCADE CONSTRAINTS PURGE;
COMMIT;

CREATE TABLE Agents
(AgentID INTEGER NOT NULL,
FirstName NVARCHAR2(30),
LastName NVARCHAR2(30),
HireDate DATE,
BirthDate DATE,
Gender NVARCHAR2(10),
WorkPhone NVARCHAR2(20),
CellPhone NVARCHAR2(20),
HomePhone NVARCHAR2(20),
Title NVARCHAR2(20),
TaxID NVARCHAR2(20),
LicenseID NVARCHAR2(20),
LicenseDate DATE,
LicenseExpire DATE,
LicenseStatusID INTEGER,
CONSTRAINT pk_Agents PRIMARY KEY (AgentID)
);

CREATE TABLE ContactReason
(ContactReason NVARCHAR2(15) NOT NULL,
Description NVARCHAR2(50),
CONSTRAINT pk_ContactReason PRIMARY KEY (ContactReason)
);

CREATE TABLE CustAgentList
(CustomerID INTEGER NOT NULL,
AgentID INTEGER NOT NULL,
ListingID INTEGER NOT NULL,
ContactDate DATE NOT NULL,
ContactReason NVARCHAR2(15),
BidPrice NUMERIC(9),
CommissionRate NUMERIC(4,4),
CONSTRAINT pk_CustAgentList PRIMARY KEY (CustomerID, AgentID, ListingID, ContactDate)
);

CREATE TABLE Customers
(CustomerID INTEGER NOT NULL,
FirstName NVARCHAR2(30) NOT NULL,
LastName NVARCHAR2(30) NOT NULL,
Address NVARCHAR2(40),
City NVARCHAR2(30),
State NVARCHAR2(20),
Zipcode NVARCHAR2(20),
HomePhone NVARCHAR2(20),
CellPhone NVARCHAR2(20),
WorkPhone NVARCHAR2(20),
CONSTRAINT pk_customers PRIMARY KEY (CustomerID)
);

CREATE TABLE LicenseStatus
(LicenseStatusID INTEGER NOT NULL,
StatusText NVARCHAR2(25),
CONSTRAINT pk_licensestatus PRIMARY KEY (LicenseStatusID)
);

CREATE TABLE Listings
(ListingID INTEGER NOT NULL,
PropertyID INTEGER NOT NULL,
ListingAgentID INTEGER NOT NULL,
SaleStatusID INTEGER,
BeginListDate DATE,
EndListDate DATE,
AskingPrice NUMERIC(9),
CONSTRAINT pk_listings PRIMARY KEY (ListingID)
);

CREATE TABLE Properties
(PropertyID INTEGER NOT NULL,
OwnerID INTEGER NOT NULL,
Address NVARCHAR2(30) NOT NULL,
City NVARChAR2(30) NOT NULL,
State NVARCHAR2(20),
Zipcode NVARCHAR2(20),
Bedrooms INTEGER,
Bathrooms INTEGER,
Stories INTEGER,
SqFt INTEGER,
YearBuilt NUMERIC(4),
Zone NVARCHAR2(4),
LotSize NUMERIC(4,2),
Latitude NUMERIC(8,5),
Longitude NUMERIC(8,5),
CONSTRAINT pk_properties PRIMARY KEY (PropertyID)

CREATE TABLE Properties
(PropertyID INTEGER NOT NULL,
OwnerID INTEGER NOT NULL,
Address NVARCHAR2(30) NOT NULL,
City NVARChAR2(30) NOT NULL,
State NVARCHAR2(20),
Zipcode NVARCHAR2(20),
Bedrooms INTEGER,
Bathrooms INTEGER,
Stories INTEGER,
SqFt INTEGER,
YearBuilt NUMERIC(4),
Zone NVARCHAR2(4),
LotSize NUMERIC(4,2),
Latitude NUMERIC(8,5),
Longitude NUMERIC(8,5),
CONSTRAINT pk_properties PRIMARY KEY (PropertyID)
);

CREATE TABLE SaleStatus
(SaleStatusID INTEGER NOT NULL,
SaleStatus NVARCHAR2(10),
CONSTRAINT pk_salestatus PRIMARY KEY (SaleStatusID)
);
COMMIT;

INSERT INTO ContactReason VALUES ('Buy','Offer to buy a property');
INSERT INTO ContactReason VALUES ('Casual','General customer probably looking for properties');
INSERT INTO ContactReason VALUES ('Sell','Listing to sell a property');

INSERT INTO LicenseStatus VALUES (1001, 'Licensed');
INSERT INTO LicenseStatus VALUES (1002, 'Licensed NBA');
INSERT INTO LicenseStatus VALUES (1003, 'Canceled Officer');
INSERT INTO LicenseStatus VALUES (1004, 'Deceased');
INSERT INTO LicenseStatus VALUES (1005, 'Expired');
INSERT INTO LicenseStatus VALUES (1006, 'Government Service');
INSERT INTO LicenseStatus VALUES (1007, 'Military Service');
INSERT INTO LicenseStatus VALUES (1008, 'Conditional Suspension');
INSERT INTO LicenseStatus VALUES (1009, 'Restricted');
INSERT INTO LicenseStatus VALUES (1010, 'Revoked');
INSERT INTO LicenseStatus VALUES (1011, 'Flag Suspended');
INSERT INTO LicenseStatus VALUES (1012, 'Voided');
INSERT INTO LicenseStatus VALUES (1013, 'Withheld Denied');
INSERT INTO LicenseStatus VALUES (1014, '17520 FC Suspended');
INSERT INTO LicenseStatus VALUES (1015, '11350.6 W and I Suspended');
INSERT INTO LicenseStatus VALUES (1016, 'Surrendered');

INSERT INTO SaleStatus VALUES (101, 'For Sale');
INSERT INTO SaleStatus VALUES (102, 'Pending');
INSERT INTO SaleStatus VALUES (103, 'Sold');

ALTER TABLE Agents
ADD CONSTRAINT fk_Agents_License FOREIGN KEY (LicenseStatusID)
REFERENCES LicenseStatus(LicenseStatusID)
ON DELETE CASCADE;

ALTER TABLE CustAgentList
ADD CONSTRAINT fk_CustAgentList_Cust FOREIGN KEY (CustomerID)
REFERENCES Customers (CustomerID)
ON DELETE CASCADE;
ALTER TABLE CustAgentList
ADD CONSTRAINT fk_CustAgentList_Agent FOREIGN KEY (AgentID)
REFERENCES Agents (AgentID)
ON DELETE CASCADE;
ALTER TABLE CustAgentList
ADD CONSTRAINT fk_CustAgentList_Listing FOREIGN KEY (ListingID)
REFERENCES Listings (ListingID)
ON DELETE CASCADE;
ALTER TABLE CustAgentList
ADD CONSTRAINT fk_CustAgentList_Contact FOREIGN KEY (ContactReason)
REFERENCES ContactReason (ContactReason)
ON DELETE CASCADE;

ALTER TABLE Listings
ADD CONSTRAINT fk_Listings_Properties FOREIGN KEY (PropertyID)
REFERENCES Properties (PropertyID)
ON DELETE CASCADE;
ALTER TABLE Listings
ADD CONSTRAINT fk_Listings_Agents FOREIGN KEY (ListingAgentID)
REFERENCES Agents (AgentID)
ON DELETE CASCADE;
ALTER TABLE Listings
ADD CONSTRAINT fk_Listings_SaleStatus FOREIGN KEY (SaleStatusID)
REFERENCES SaleStatus (SaleStatusID)
ON DELETE CASCADE;

ALTER TABLE Properties
ADD CONSTRAINT fk_Properties_Customers FOREIGN KEY (OwnerID)
REFERENCES Customers (CustomerID)
ON DELETE CASCADE;