Tutorial SQLServer 15

Joins

1 SQL Server Management Studio
2 File -> New -> Query
3
-- =============================================
-- 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 TABLE PropertyTypes
(
PropertyTypeID int identity(1,1) NOT NULL,
PropertyType varchar(20)
);
GO
INSERT INTO PropertyTypes(PropertyType)
VALUES('Condominium');
GO
INSERT INTO PropertyTypes(PropertyType)
VALUES('Single Family');
GO
INSERT INTO PropertyTypes(PropertyType)
VALUES('Townhouse');
GO
INSERT INTO PropertyTypes(PropertyType)
VALUES('Unknown');
GO
-- =============================================
-- Author: FunctionX
-- Database: RealEstate2
-- Table: Conditions
-- =============================================
USE RealEstate2;
GO
CREATE TABLE Conditions

(
ConditionID int identity(1,1) NOT NULL,
Condition varchar(20)
);
GO
INSERT INTO Conditions(Condition)
VALUES('Excellent');
GO
INSERT INTO Conditions(Condition)
VALUES('Good');
GO
INSERT INTO Conditions(Condition)
VALUES('Bad Shape');
GO
INSERT INTO Conditions(Condition)
VALUES('Mostly Damaged');
GO
-- =============================================
-- Author: FunctionX
-- Database: RealEstate2
-- Table: Properties
-- =============================================
CREATE TABLE 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 Properties(PropertyNumber, Address, City, State,
ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES('524880', '1640 Lombardo Ave', 'Silver Spring', 'MD',
'20904', 2, 2, 4, 2.5, 3, 1, 3, 1995, 495880.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
ZIPCode, PropertyTypeID, ConditionID, Bedrooms, Bathrooms,
FinishedBasement, IndoorGarage, Stories, YearBuilt, MarketValue)
VALUES('688364', '10315 North Hacht Rd', 'College Park', 'MD',
'20747', 2, 1, 4, 3.5, 3,
1, 2, 2000, 620724.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
ZIPCode, PropertyTypeID, ConditionID, FinishedBasement,
Stories, MarketValue)
VALUES('611464', '6366 Lolita Drive', 'Laurel', 'MD',
'20707', 2, 2, 1, 2, 422625.00);