SET ECHO ON;
/************************************************************************/
INSERT INTO zipadd
(zipid, zip, state, city)
VALUES
(1201, 90007, 'CA', 'LOS ANGELES');
INSERT INTO zipadd
(zipid, zip, state, city)
VALUES
(1202, 90008, 'CA', 'ROSEMEAD');
INSERT INTO zipadd
(zipid, zip, state, city)
VALUES
(1203, 90210, 'CA', 'BEVERLY HILLS');
INSERT INTO zipadd
(zipid, zip, state, city)
VALUES
(1204, 90407, 'CA', 'PASADENA')
INSERT INTO zipadd
(zipid, zip, state, city)
VALUES
(1205, 90417, 'CA', 'TORRANCE');
INSERT INTO zipadd
(zipid, zip, state, city)
VALUES
(1206, 90427, 'CA', 'ORANGE');
/************************************************************************/
INSERT INTO streetdesig
(desid, description)
VALUES
(001, 'ARTERIAL');
INSERT INTO streetdesig
(desid, description)
VALUES
(002, 'HIGHWAY');
INSERT INTO streetdesig
(desid, description)
VALUES
(003, 'FREEWAY');
INSERT INTO streetdesig
(desid, description)
VALUES
(004, 'TWO LANE STREET');
/************************************************************************/
INSERT INTO jurisdiction
(jurisid, jurisname, lastname, firstname, middlei, street, phone, email, zipid)
VALUES
(12010, 'LOS ANGELES DTN', 'GRAHAM', 'BELL', 'T', 'ADAMS AVE', '3237375833', 'alib83@hot.com', 1201);
INSERT INTO jurisdiction
(jurisid, jurisname, lastname, firstname, middlei, street, phone, email, zipid)
VALUES
(12011, 'LOS FELEX DTN', 'HAM', 'BOLT', 'I', 'BANADAMS ST', '6236378833', 'abnd@hot.com', 1202);
INSERT INTO jurisdiction
(jurisid, jurisname, lastname, firstname, middlei, street, phone, email, zipid)
VALUES
(12012, 'ANGELES DTN', 'GAM', 'ELL', 'I', 'SAADAM AVE', '9234575833', 'SAD3@hot.com', 1205);
/************************************************************************/
INSERT INTO usage
(usageid, usage)
VALUES
(001, 'SCHOOL');
INSERT INTO usage
(usageid, usage)
VALUES
(002, 'GAS STATION');
INSERT INTO usage
(usageid, usage)
VALUES
(003, 'GAS REFINERY');
/************************************************************************/
INSERT INTO type
(typeid, type)
VALUES
(01, 'CURRENT');
INSERT INTO type
(typeid, type)
VALUES
(02, 'HISTORICAL');
INSERT INTO type
(typeid, type)
VALUES
(03, 'SUBSEQUENT');
/************************************************************************/
INSERT INTO contaminants
(contid, description)
VALUES
(001, 'SULPHUR');
INSERT INTO contaminants
(contid, description)
VALUES
(002, 'AMMONIA');
INSERT INTO contaminants
(contid, description)
VALUES
(003, 'CARBON');
INSERT INTO contaminants
(contid, description)
VALUES
(004, 'NAPTHA');
/************************************************************************/
INSERT INTO zoning
(zoneid, code, description, jurisid)
VALUES
(129000, 'HIU9', 'RES AREA', 12011);
INSERT INTO zoning
(zoneid, code, description, jurisid)
VALUES
(129010, 'ERU0229', 'SCHOOL AREA', 12012);
/************************************************************************/
INSERT INTO broker
(brokerid, lastname, firstname, middlei, email, phone, street, zipid)
VALUES
(001, 'BREGTSG', 'LINKON', 'Y', 'bks@sdfji.com', '2329229999', 'SERANGOON RD', 1201);
INSERT INTO broker
(brokerid, lastname, firstname, middlei, email, phone, street, zipid)
VALUES
(002, 'BREER', 'INKON', 'Y', 'breer@sdfsdfji.com', '2323222439', 'GOON RD', 1202);
/************************************************************************/
INSERT INTO owner
(ownerid, lastname, firstname, middlei, email, phone, street, zipid)
VALUES
(122001, 'BRETT', 'KON', 'Y', 'brett@sdfsdfji.com', '2335222439', 'GOON RD', 1202);
INSERT INTO owner
(ownerid, lastname, firstname, middlei, email, phone, street, zipid)
VALUES
(121001, 'REER', 'INK', 'Y', 'reer@sdfsdfji.com', '2323786439', 'HANGOON RD', 1201);
/************************************************************************/
INSERT INTO employee
(employeeid, lastname, firstname, middlei, email, phone, street, ssn, zipid)
VALUES
(121, 'JOHNNY', 'SANDLES', 'D', 'JON@sdfsdfji.com', '2312486439', 'BOONON RD', '889802225', 1201);
INSERT INTO employee
(employeeid, lastname, firstname, middlei, email, phone, street, ssn, zipid)
VALUES
(122, 'JOHN', 'SAND', 'D', 'YUOR@sdfsdfji.com', '2312484439', 'BOONON RD', '885502225', 1202);
/************************************************************************/
INSERT INTO developer
(developerid, lastname, firstname, middlei, email, phone, street, zipid)
VALUES
(1201, 'TOMMY', 'SANDLS', 'D', 'TOM@sdfsdfji.com', '2332386439', 'JUNON RD', 1203);
INSERT INTO developer
(developerid, lastname, firstname, middlei, email, phone, street, zipid)
VALUES
(1202, 'GRETT', 'GREAD', 'D', 'FRES@sdfsdfji.com', '2134484439', 'DERAD RD', 1204);
/************************************************************************/
INSERT INTO property
(PropID, Sold, Phase1, Phase2, Phase3, RailAccess, APN, MapCoords, PropSize, AssesNum, sitename, GPD, devpot, Incentives, Comments, streetAddress, zipid, desid, zoneid, ownerid)
VALUES
(121210, 'Y', 'Y', 'Y', 'Y', 'Y', '23492020-09', 'H45,J77', '89321SQFT', 21094320, 'GHJREWE', 'DSFKDKFDSFSD', 80.32, 'AFKJIAJEKSAIEWWEQ', 'IFUEWIKSCKJDEW', 'TIEIEWR TD', 1201, 001, 129000, 122001);
/************************************************************************/
INSERT INTO envstat
(PropID, ContLevel, Contamination)
VALUES
(121210, 'H', 'LAND');
INSERT INTO envstat
(PropID, ContLevel, Contamination)
VALUES
(121210, 'L', 'WATER');
/************************************************************************/
INSERT INTO usageitem
(PropID, TypeID, usageid)
VALUES
(121210, 01, 001);
INSERT INTO usageitem
(PropID, TypeID, usageid)
VALUES
(121210, 02, 003);
/************************************************************************/
INSERT INTO contitem
(PropID, ContID)
VALUES
(121210, 001);
INSERT INTO contitem
(PropID, ContID)
VALUES
(121210, 004);
/************************************************************************/
INSERT INTO devsearchresult
(devsearchresid, searchdate, developerid, PropID)
VALUES
(101010, '12-AUG-98', 1211, 121210);
/************************************************************************/
INSERT INTO taxinfo
(taxid, taxyear, assessed, percentage, taxamount, status, tra, exempt, improvement, landamount, PropID)
VALUES
('121811', 1998, 122187.08, 80, 27832.98, 'Y', 237622, 'N', 231211.99, 21972.99, 121210);
/************************************************************************/
INSERT INTO fininfo
(finid, buyer, seller, lender, lasttrans, saledate, saleamnt, firsttd, addL, docno, dolsqft, loantype, prevdate, prevamnt, PropID)
VALUES
('SAD221', 'SADSAJHD', 'ADKJIWDJKS', 'SAFDQWESA', 'ADSFDSAFSA', '12-AUG-91', 1928749.88, 324214.88, 32143432.12, 42121, 'FIXED', '12-AUG-87', 2347202, 121210);
/************************************************************************/
INSERT INTO empactivity
(actid, actdate, employeeid, PropID)
VALUES
(12111, '31-JAN-99', 121, 121210);
/************************************************************************
Sample Queries Follow:
************************************************************************/
/***************QUERY 1 *******************/
SELECT ZIPADD.ZIPID, JURISNAME, ZIPADD.ZIP
FROM JURISDICTION, ZIPADD
WHERE JURISDICTION.ZIPID=ZIPADD.ZIPID;
SQL>
SQL> ZIPID JURISNAME ZIP
2 1201 LOS ANGELES DTN 90007
3 1202 LOS FELEX DTN 90008
4 1205 ANGELES DTN 90417
3 rows selected
SQL>
/*************QUERY 2 *******************/
SELECT PROPERTY.PROPID, PROPERTY.SOLD, devsearchresid, searchdate, developerid
FROM PROPERTY, DEVSEARCHRESULT
WHERE PROPERTY.PropID=DEVSEARCHRESULT.PropID
AND
PROPID IN
(SELECT PROPID FROM ENVSTAT
WHERE ContLevel ='H');
SQL>
SQL> PROPERTY.PROPID PROPERTY.SOLD devsearchresid searchdate developerid
2 121210 Y 101010 12-AUG-1998 121
1 row selected
SQL>
/*******************QUERY 3 *****************/
SELECT property.PropID, contaminant
from property, contaminants
where property.propid=contitem.propid
and where contitem.contitem=contaminants.contitem
SQL> property.propid contaminant
2 121210 SULPHUR
3 121210 NAPTHA
2 rows selected
SQL>