Sale My House SQL Assay

In this assay i made both Table Schema and ERD Diargram for a real estate company software's Data Base according to client's requirements

ERD

Table Schema

SQL Script

Prerequisites for the script

in order to run the script you need to make sure you dont have DB named [Sale My House DB] if so choose diffrent name and run

Sale-My-House/FinalyAssay.sql

Lines 1 to 5 in a4beabf

	use master;
	--drop DATABASE [Sale My House DB]; --If already exicts drop and recreate
	create database [Sale My House DB]; --Creates the DB
	use [Sale My House DB];

Right after this it will run the script to build the tables isert initial data and run 5 queries

Queries

1. Revenue of the company from each house type yearly grouped

   Sale-My-House/FinalyAssay.sql

   Lines 261 to 274 in a4beabf

   	Select
   	[House Types].[House Type ID],
   	[House Types].[Type Name] as 'House type',
   	Sum(Sales.[Final Price]-Sales.[Requested price]) As 'Revenue',
   	Year(Sales.[Purchesed date]) as 'Year'
   	from
   	Sales inner join Houses on Houses.[House ID] = Sales.[House ID]
   	inner join [House Types] on Houses.[House Type]=[House Types].[House Type ID]
   	group by
   	[House Types].[Type Name],[House Types].[House Type ID],Year(Sales.[Purchesed date])
   	having
   	Sum(Sales.[Final Price]-Sales.[Requested price]) <> 0
   	order by
   	[House Types].[Type Name]

2. Display custumers who sold house and bought more expensive house

   Sale-My-House/FinalyAssay.sql

   Lines 278 to 317 in a4beabf

   	go
   	Create View MinSold as
   	Select
   	cust.[Customer ID],
   	min(Sales.[Final Price]) as 'Min Sold'
   	from
   	Customers cust inner join Sales on Sales.[Seller ID]=Cust.[Customer ID]
   	where
   	Sales.[Final Price] <> 0
   	group by
   	[Customer ID]
   	go
   	go
   	Create View MaxBought as
   	Select
   	cust.[Customer ID],
   	Max(Sales.[Final Price]) as 'Max Bought'
   	from
   	Customers cust inner join Sales on Sales.[Buyer ID]=Cust.[Customer ID]
   	where
   	Sales.[Final Price] <>0
   	group by
   	[Customer ID]
   	go
   	go
   	Select
   	MaxBought.[Customer ID] as 'Bought much more expensive house' ,
   	[Max Bought]-[Min Sold] as 'Price Diffrence'
   	from
   	MaxBought inner join MinSold on MaxBought.[Customer ID]=MinSold.[Customer ID]
   	where
   	MaxBought.[Max Bought]>MinSold.[Min Sold]
   	go
   	go
   	drop view MinSold
   	drop view MaxBought
   	go

3. Display the average of each Neigberhood order by Most expensive

   Sale-My-House/FinalyAssay.sql

   Lines 321 to 336 in a4beabf

   	select
   	NGBD.[Name] as 'Neigberhood',
   	Cities.Name as 'City',
   	NGBD.[Neigberhood ID] ,
   	avg(Sales.[Final Price]) as 'Average selling price' ,
   	count(*) as 'Number of Sales'
   	from
   	Sales inner join Houses on Houses.[House ID]=Sales.[House ID]
   	inner join Neigberhoods NGBD on NGBD.[Neigberhood ID]=Houses.Neigberhood
   	inner join Cities on NGBD.[City ID] =Cities.[City ID]
   	where
   	[Final Price] >0
   	group by
   	NGBD.[Name],NGBD.[Neigberhood ID],Cities.Name
   	order by
   	[Average selling price] desc

4. Display the best employee by revenue for each year

   Sale-My-House/FinalyAssay.sql

   Lines 339 to 373 in a4beabf

   	go
   	create View YealryRevenueOfEmp as --YROE
   	(select SM.[Sales Man ID],
   	year (Sales.[Purchesed date]) as 'The Year' ,
   	sum (Sales.[Final Price]-Sales.[Requested price]) as 'Revenue'
   	from [Sales Men] SM inner join Sales on Sales.[Sales Man ID] =SM.[Sales Man ID]
   	where Sales.[Final Price] <> 0
   	group by year (Sales.[Purchesed date]) ,
   	SM.[Sales Man ID])
   	go
   	go
   	create View Year_and_max as
   	(select YROE.[The Year] as 'The Year' ,max(YROE.Revenue) as 'Maximum Revenue'
   	from YealryRevenueOfEmp YROE
   	group by YROE.[The Year])
   	go
   	go
   	select Year_and_max.[The Year],
   	YealryRevenueOfEmp.[Sales Man ID],
   	max(Year_and_max.[Maximum Revenue]) as 'Maximum Revenue'
   	from Year_and_max left join YealryRevenueOfEmp on YealryRevenueOfEmp.[The Year] = Year_and_max.[The Year]
   	where YealryRevenueOfEmp.Revenue = Year_and_max.[Maximum Revenue]
   	group by Year_and_max.[The Year],YealryRevenueOfEmp.[Sales Man ID]
   	order by Year_and_max.[The Year]
   	go
   	go
   	drop view Year_and_max
   	go
   	go
   	drop view YealryRevenueOfEmp
   	go

5. Offer a bigger house to a custumer which bought a house from 2 years ago and farther in the same city

   Sale-My-House/FinalyAssay.sql

   Lines 376 to 430 in a4beabf

   	go
   	create view NotRecentlyBought as
   	( select
   	Customers.[Customer ID],
   	max(Sales.[Purchesed date]) as 'Last Sale',
   	Houses.[House ID]
   	from Sales inner join Customers on Customers.[Customer ID]=Sales.[Buyer ID]
   	inner join Houses on Sales.[House ID]=Houses.[House ID]
   	group by Customers.[Customer ID],Houses.[House ID]
   	having datediff (day,max(Sales.[Purchesed date]),getdate())>2*365
   	)
   	go
   	go
   	create view AvilabeHouses as
   	( select Houses.[House ID],Houses.[Owner ID]
   	from Houses inner join Sales on Sales.[House ID]=Houses.[House ID]
   	where Sales.[Purchesed date] is null
   	)
   	go
   	go
   	select NotRecentlyBought.[Customer ID],
   	AvilabeHouses.[House ID] as 'Offerd house',
   	AvilabeHouses.[Owner ID] as 'Current owner of house',
   	PrevHous.[House ID] 'Previously boght',
   	avil.Size as 'Offerd Size',
   	PrevHous.Size 'Previous size',
   	Cities.[Name] as 'City',
   	avilN.[Name] as 'Neigberhood'
   	from
   	AvilabeHouses inner join NotRecentlyBought on AvilabeHouses.[Owner ID]!=NotRecentlyBought.[Customer ID]
   	inner join Houses avil on avil.[House ID]=AvilabeHouses.[House ID]
   	inner join Houses PrevHous on PrevHous.[House ID]=NotRecentlyBought.[House ID]
   	inner join Neigberhoods avilN on avilN.[Neigberhood ID]=avil.Neigberhood
   	inner join Neigberhoods PrevN on PrevN.[Neigberhood ID]=PrevHous.Neigberhood
   	inner join Cities on avilN.[City ID]=Cities.[City ID]
   	where
   	PrevHous.Size<avil.Size and
   	PrevN.[City ID]=avilN.[City ID]
   	group by
   	NotRecentlyBought.[Customer ID],
   	AvilabeHouses.[Owner ID],
   	AvilabeHouses.[House ID],
   	PrevHous.Size,PrevHous.[House ID],
   	avil.Size,Cities.[Name],
   	avilN.[Name]
   	order by
   	NotRecentlyBought.[Customer ID]
   	go
   	go
   	drop view NotRecentlyBought
   	drop view AvilabeHouses
   	go