DEV Community

Kiron Roy
Kiron Roy

Posted on • Updated on

Home Improvement Database

I created a simple database using SQL Server Management Studio. The database tracks home improvements throughout the years. The database only has one table called dbo.RenovationsToHouse

Table Creation

-- Create a table

create table dbo.RenovationsToHouse
(
   CompanyName varchar(50) not null
   ,DescriptionOfWork varchar(200) not null
   ,Category varchar(200) not null
   ,DateOfWork date not null
   ,CostOfWork money not null

);
Enter fullscreen mode Exit fullscreen mode

View the table

-- When you select everything from the table

select * from dbo.RenovationsToHouse;
Enter fullscreen mode Exit fullscreen mode

It displays a 5 column, 35 row table :

DateOfWork CompanyName DescriptionOfWork Category CostOfWork
2003-04-03 Mesa Home Improvment garage doors garage 2186.00
2003-04-09 Kuric-Graham & Associates engineering inspection for roof maintenance 300.00
2003-05-15 JB Wholesale Roofing roof materials roof 13000.00
2005-10-20 Mesa Home Improvment kitchen, living, and 2 bedroom windows windows 450.00
2006-08-28 United Construction all bathrooms bathrooms 17700.00
2010-02-02 Glazer Roofing Co. stop sky light from leaking maintenance 820.00
2010-08-26 Terminix International Termite Control maintenance 2550.00
2010-12-15 Bixby Plaza wood laminate downstairs laminate floors 4377.00
2012-05-30 Michael's Painting Company paint hallway, ceiling and walls painting 2450.00
2012-06-20 Michael's Painting Company wood laminate upstairs hall and stairs laminate floors 3350.00
2012-10-03 Royal Pane Windows replace 8 windows windows 3371.70
2012-10-10 The Vinyl Fence Company vinyl patio cover and led lights vinyl 13483.00
2013-01-17 Michael's Painting Company outside paint painting 2850.00
2013-02-07 Michael's Painting Company install vinyl fence, composite table vinyl 2020.00
2013-03-03 Michael's Painting Company backyard fence and paint fences bricks painting 2850.00
2013-07-24 Glazer Roofing Co. tile repair maintenance 800.00
2013-07-26 Michael's Painting Company popcorn ceiling removal painting 3080.00
2013-07-26 Michael's Painting Company painted upstairs painting 6000.00
2013-07-26 Michael's Painting Company wood laminate upstairs rooms laminate floors 16030.00
2013-09-17 NU Kitchens - floors & Baths downstairs and upstairs bathrooms bathrooms 14840.00
2013-10-23 Michael's Painting Company family room lights + paint all doors painting 2700.00
2013-11-11 Michael's Painting Company painted family room + kitchen painting 2350.00
2013-12-09 Michael's Painting Company garage floor garage 3000.00
2014-01-15 Discount Iron Fence & Gate iron side gates iron gates 1150.00
2014-02-15 Trio Heating & Cooling HVAC cleaning maintenance 1638.00
2014-07-11 Go Pavers pavers in the front yard landscape 7825.00
2015-04-16 California Pool and Chemical Service remodel swimming pool and backyard landscape 38350.00
2016-02-16 Florentio Sanchez planters in the back yard landscape 3575.00
2018-02-16 Family Plumber family room leak family room 560.00
2018-06-10 Garage Door Repair Service garage doors garage 485.00
2018-10-04 Michael's Painting Company laundry room laundry 1850.00
2018-10-29 Family Plumber laundry room laundry 165.00
2019-07-31 Royal Pane Windows patio door windows 4232.99
2019-08-15 Michael's Painting Company kitchen floor laminate floors 1100.00
2019-10-04 Michael's Painting Company remove bar bar 2250.00

Queries

There are some interesting queries from this one table

-- find a company

select * from dbo.RenovationsToHouse
where CompanyName like 'Michael%';
Enter fullscreen mode Exit fullscreen mode

Find by Company Name:

DateOfWork CompanyName DescriptionOfWork Category CostOfWork
2012-05-30 Michael's Painting Company paint hallway, ceiling and walls painting 2450.00
2012-06-20 Michael's Painting Company wood laminate upstairs hall and stairs laminate floors 3350.00
2013-01-17 Michael's Painting Company outside paint painting 2850.00
2013-02-07 Michael's Painting Company install vinyl fence, composite table vinyl 2020.00
2013-03-03 Michael's Painting Company backyard fence and paint fences bricks painting 2850.00
2013-07-26 Michael's Painting Company popcorn ceiling removal painting 3080.00
2013-07-26 Michael's Painting Company painted upstairs painting 6000.00
2013-07-26 Michael's Painting Company wood laminate upstairs rooms laminate floors 16030.00
2013-10-23 Michael's Painting Company family room lights + paint all doors painting 2700.00
2013-11-11 Michael's Painting Company painted family room + kitchen painting 2350.00
2013-12-09 Michael's Painting Company garage floor garage 3000.00
2018-10-04 Michael's Painting Company laundry room laundry 1850.00
2019-08-15 Michael's Painting Company kitchen floor laminate floors 1100.00
2019-10-04 Michael's Painting Company remove bar bar 2250.00
select sum(CostOfWork) 
    as 'Total cost of installing windows' 
    from dbo.RenovationsToHouse
    where Category like 'windows%';
Enter fullscreen mode Exit fullscreen mode

Find the total price by a specific category (windows):

Total cost of installing windows
1 8054.69
-- dates
-- order by months

select datename(month, DateOfWork) 
as 'Month'
  , count(*) as 'Jobs done'
from dbo.RenovationsToHouse
group 
by datename(month, DateOfWork)
  , datepart(month
  , DateOfWork)
order by datepart(month, DateOfWork);
Enter fullscreen mode Exit fullscreen mode

Order by months:

Month Jobs done
January 2
February 5
March 1
April 3
May 2
June 2
July 6
August 3
September 1
October 7
November 1
December 2
-- order by year

select datename(month, DateOfWork) 
     as 'Month'
    , count(*) as 'Jobs done'
    , year(DateOfWork) as 'Year'
from dbo.RenovationsToHouse
group by 
datename(month, DateOfWork)
, year(DateOfWork)
order by year(DateOfWork);
Enter fullscreen mode Exit fullscreen mode

Order by year:

Month Jobs done Year
April 2 2003
May 1 2003
October 1 2005
August 1 2006
August 1 2010
December 1 2010
February 1 2010
June 1 2012
May 1 2012
October 2 2012
December 1 2013
February 1 2013
January 1 2013
July 4 2013
March 1 2013
November 1 2013
October 1 2013
September 1 2013
February 1 2014
January 1 2014
July 1 2014
April 1 2015
February 1 2016
February 1 2018
June 1 2018
October 2 2018
August 1 2019
July 1 2019
October 1 2019
-- minimum cost 

select min(CostOfWork) as 'minimum price for a job'
from dbo.RenovationsToHouse;
Enter fullscreen mode Exit fullscreen mode

minimum price of a single job

minimum price
1 165.00
-- maximum price of a single job

select max(CostOfWork) as 'maximum price'
from dbo.RenovationsToHouse;
Enter fullscreen mode Exit fullscreen mode

maximum price:

maximum price
1 38350.00
-- average cost

select avg(CostOfWork) as '
Average money spent on job'
from dbo.RenovationsToHouse;
Enter fullscreen mode Exit fullscreen mode

Average money spent on a job:

Total Cost
1 5249.6768
-- total costs

select sum(CostOfWork) as 'Total Cost'
from dbo.RenovationsToHouse;
Enter fullscreen mode Exit fullscreen mode

The grand total of all the renovations:

Total Cost
1 183738.69

Discussion (3)

Collapse
cipharius profile image
Valts Liepiņš

This is great! I've wanted to start using such personal database myself.

Do you plan on adding additional tables? Also how do you plan on handling schema updates? Manually or you have some simple migrations setup in mind?

Collapse
kironroy profile image
Kiron Roy Author

For this database, I will not be adding tables.
Multiple-locations is a case where more tables would be necessary.

Then the table would look like this:

  • Location Table: each rental unit has an entry
  • Company Table: each maintenance company has an entry
  • Jobs Table: each type of job has an entry.

Each table would have a primary key and a surogate key. Example for Locations table:

  • id as an int type
  • LocationId also as an int type.

I wrote an article that goes into detail about relational tables