-- =============================================
-- Author:   FunctionX
-- Database: RealEState2
-- =============================================
IF EXISTS (
  SELECT * 
    FROM sys.databases 
   WHERE name = N'RealEState2'
)
  DROP DATABASE RealEState2
GO
CREATE DATABASE RealEState2;
GO

-- =============================================
-- Author:   FunctionX
-- Database: RealEState2
-- Table:    PropertyTypes
-- =============================================
USE RealEState2;
GO

CREATE SCHEMA Listing;
GO

CREATE TABLE Listing.PropertyTypes
(
  PropertyTypeID int identity(1,1) NOT NULL,
  PropertyType varchar(20)
);
GO
INSERT INTO Listing.PropertyTypes(PropertyType)
VALUES(N'Condominium');
GO
INSERT INTO Listing.PropertyTypes(PropertyType)
VALUES(N'Single Family');
GO
INSERT INTO Listing.PropertyTypes(PropertyType)
VALUES(N'Townhouse');
GO
INSERT INTO Listing.PropertyTypes(PropertyType)
VALUES(N'Unknown');
GO

-- =============================================
-- Author:   FunctionX
-- Database: RealEState2
-- Table:    Conditions
-- =============================================
USE RealEState2;
GO
CREATE TABLE Listing.Conditions
(
  ConditionID int identity(1,1) NOT NULL,
  Condition varchar(20)
);
GO
INSERT INTO Listing.Conditions(Condition)
VALUES(N'Excellent');
GO
INSERT INTO Listing.Conditions(Condition)
VALUES(N'Good');
GO
INSERT INTO Listing.Conditions(Condition)
VALUES(N'Bad Shape');
GO
INSERT INTO Listing.Conditions(Condition)
VALUES(N'Highly Damaged');
GO

-- =============================================
-- Author:   FunctionX
-- Database: RealEState2
-- Table:    Properties
-- =============================================
CREATE TABLE Listing.Properties
(
  PropertyID int identity(1,1) NOT NULL,
  PropertyNumber char(6),
  [Address] varchar(100),
  City varchar(50),
  [State] char(2),
  ZIPCode varchar(12),
  PropertyTypeID int,
  ConditionID int,
  Bedrooms smallint,
  Bathrooms float,
  FinishedBasement bit,
  IndoorGarage bit,
  Stories smallint,
  YearBuilt smallint,
  MarketValue money
);
GO

INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'524880', N'1640 Lombardo Ave', N'Silver Spring', N'MD',
  N'20904', 2, 2, 4, 2.5, 3, 1, 3, 1995, 495880.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'688364', N'10315 North Hacht Rd', N'College Park', N'MD',
       N'20747', 2, 1, 4, 3.5, 3,
       1, 2, 2000, 620724.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, FinishedBasement,
  Stories, MarketValue)
VALUES(N'611464', N'6366 Lolita Drive', N'Laurel', N'MD',
       N'20707', 2, 2, 1, 2, 422625.00);
GO
INSERT INTO Listing.Properties([Address], City, PropertyTypeID,
  Bedrooms, MarketValue)
VALUES(N'9002 Palasko Hwy', N'Tysons Corner',
       1, 2, 422895.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [State],
  ZIPCode, Bedrooms, YearBuilt, MarketValue)
VALUES(N'420115', N'DC',
       N'20011', 2, 1982, 312555);
GO     
INSERT INTO Listing.Properties(PropertyNumber, City, ZIPCode,
 PropertyTypeID, Bedrooms, YearBuilt, MarketValue)
VALUES(N'917203', N'Alexandria', N'22024',
       2, 3, 1965, 345660.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  PropertyTypeID, ConditionID, Bedrooms, Bathrooms, MarketValue)
VALUES(N'200417', N'4140 Holisto Crt', N'Germantown', N'MD',
       1, 1, 2, 1, 215495.00);
GO
INSERT INTO Listing.Properties(City, [State], PropertyTypeID, ConditionID,
  Bedrooms, Bathrooms,  YearBuilt, MarketValue)
VALUES(N'Rockville', N'MD', 1, 2, 2, 2, 1996, 436885.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'927474', N'9522 Lockwood Rd', N'Chevy Chase', N'MD',
       N'20852', 3, 3, 3, 2.5, 3, 0, 3,
       1992, 415665.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'207850', N'14250 Parkdoll Rd', N'Rockville', N'MD',
       N'20854', 3, 2, 3, 2.5, 2, 1, 2,
       1988, 325995.00);
GO    
INSERT INTO Listing.Properties(City, PropertyTypeID, Bedrooms,
  YearBuilt, MarketValue)
VALUES(N'Washington', 3, 4, 1975, 366775.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  YearBuilt, MarketValue)
VALUES(N'288540', N'10340 Helmes Street #408', N'Silver Spring', N'MD',
       N'20906', 1, 2, 1, 1, 2000, 242775.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'247472', N'1008 Coppen Street', N'Silver Spring', N'MD',
       N'20906', 2, 1,
       3, 3, 3, 1, 3, 1996, 625450.00);
GO
INSERT INTO Listing.Properties(City, ZIPCode, PropertyTypeID, 
   Stories, YearBuilt, MarketValue)
VALUES(N'Chevy Chase', N'20956', 2, 
       3, 2001, 525450.00);
GO
INSERT INTO Listing.Properties([Address], City, [State],
  PropertyTypeID, ConditionID, Bedrooms, MarketValue)
VALUES(N'686 Herod Ave #D04', N'Takoma Park', N'MD',
       1, 1, 2, 360885.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'297446', N'14005 Sniders Blvd', N'Laurel', N'MD',
       N'20707', 3, 4,
       4, 1.5, 3, 1, 2, 2002, 412885.00);
GO
INSERT INTO Listing.Properties(City, ZIPCode, ConditionID, Bedrooms,
  Stories, YearBuilt)
VALUES(N'Silver Spring', N'20905', 2,
       4, 2, 1965);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'924792', N'680 Prushia Rd', N'Washington', N'DC',
       N'20008', 2, 2,
       5, 3.5, 3, 0, 3, 2000, 555885.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'294796', N'14688 Parrison Street', N'College Park', N'MD',
       N'20742', 2, 1,
       5, 2.5, 2, 1, 2, 1995, 485995.00);
GO
INSERT INTO Listing.Properties(City, [State], PropertyTypeID, ConditionID,
  Bedrooms, Bathrooms,  YearBuilt, MarketValue)
VALUES(N'Rockville', N'MD', 1, 2, 1, 1, 1996, 418885.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  YearBuilt, MarketValue)
VALUES(N'811155', N'10340 Helmes Street #1012', N'Silver Spring',
       'MD', N'20906', 1, 2,
       1, 1, 2000, 252775.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'447597', N'4201 Vilamar Ave', N'Hyattsville', N'MD',
       N'20782', 3, 1,
       3, 2, 2, 1, 3, 1992, 365880.00);
GO
INSERT INTO Listing.Properties([Address], ZIPCode, Bathrooms)
VALUES(N'1622 Rombard Str', 20904, 2.5);
GO
INSERT INTO Listing.Properties(City, [State], PropertyTypeID, ConditionID,
  Bedrooms, Bathrooms,  YearBuilt, MarketValue)
VALUES(N'Rockville', N'MD', 1, 2, 1, 1, 1996, 420555.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'297415', N'980 Phorwick Street', N'Washington', N'DC',
       N'20004', 2, 2,
       4, 3.5, 3, 3, 1, 2004, 735475.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'475974', N'9015 Marvin Crow Ave', N'Gaithersburg', N'MD',
       N'20872', 2, 4,
       4, 2.5, 3, 1, 1, 1965, 615775.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'836642', N'3016 Feldman Court', N'Rockville', N'MD',
       N'20954', 2, 3,
       5, 3, 3, 1, 3, 1960, 528555.00);
GO
INSERT INTO Listing.Properties([Address], City, ZIPCode, PropertyTypeID,
  Bedrooms, Bathrooms, YearBuilt, MarketValue)
VALUES(N'2444 Arielson Rd', N'Rockville', N'20854', 
	1, 2, 1, 1996, 475555.00);
GO

INSERT INTO Listing.Properties(City, [State], PropertyTypeID, Stories)
VALUES(N'Rockville', N'MD',
       3, 1);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'208304', N'7307 Everett Hwy', N'Washington', N'DC',
       N'20012', 3, 1,
       2, 2.5, 2, 0, 4, 2006, 420550.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms,
  Bathrooms, YearBuilt, MarketValue)
VALUES(N'644114', N'10340 Helmes Street#1006', N'Silver Spring',
       'MD', N'20906', 1, 2,
       2, 2, 2000, 258445.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'937966', N'7303 Warfield Court', N'Tysons Corner', N'VA',
       '22131', 2, 2,
       3, 2.5, 3, 1, 4, 2006, 825775.00);
GO
INSERT INTO Listing.Properties(City, ZIPCode, ConditionID, Bedrooms, 
  Stories, YearBuilt)
VALUES(N'Fairfax', N'22232', 2, 3, 3, 1985);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'297497', N'12401 Conniard Ave', N'Takoma Park', N'MD',
       N'20910', 3, 2,
       3, 2.5, 3, 1, 3, 2004, 280775.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, City, ZIPCode,
  PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  YearBuilt, Stories, MarketValue)
VALUES(N'855255', N'Laurel', N'20707', 2,
       4, 3, 2, 1962, 2, 342805.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'469750', N'6124 Falk Rd', N'Arlington', N'VA',
       '22031', 2, 4,
       4, 3.5, 3, 1, 1, 1982, 635995.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'826927', N'5121 Riehl Ace', N'Fairfax', N'VA',
       '22232', 3, 1,
       3, 1.5, 2, 0, 1, 2002, 325620.00);
GO
INSERT INTO Listing.Properties(City, ZIPCode, PropertyTypeID, Bedrooms, 
  Bathrooms, MarketValue)
VALUES(N'Silver Spring', N'20906', 1, 2, 2, 335655.00);
GO

INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'287064 ', N'9533 Pensulian Rd', N'Silver Spring', N'MD',
       N'20904', 2, 3,
       3, 1.5, 3, 1, 2, 1992, 485775.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, City, ZIPCode,
  PropertyTypeID, ConditionID, Bedrooms, YearBuilt, Stories)
VALUES(N'724001 ', N'705 Helios Ave', N'20004',
       3, 3, 3, 1974, 4);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'209275', N'944 Fryer Ave', N'Chevy Chase', N'MD',
       N'20852', 2, 1,
       5, 2.5, 3, 0, 2, 2002, 625665.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'204759', N'1950 Galego Street', N'Germantown', N'MD',
       N'20874', 2, 1,
       4, 3.5, 2, 1, 4, 2007, 428665.00);
GO
INSERT INTO Listing.Properties(PropertyNumber, [Address], City, [State],
  ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
  FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES(N'937259', N'12366 Fowler Ave', N'Alexandria', N'VA',
       '22031', 3, 2,
       3, 1.5, 3, 1, 3, 2007, 402815.00);
GO