-
Notifications
You must be signed in to change notification settings - Fork 0
/
Cleaning Nashville-housing-data.sql using PostgreSQL
148 lines (143 loc) · 3.65 KB
/
Cleaning Nashville-housing-data.sql using PostgreSQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
--selecting all
select
*
from
nashville_housing_data.housing_data
--checking soldasvacant column there is some inconsistent data there is Y for Yes and N for No .
select distinct soldasvacant
from
nashville_housing_data.housing_data
----replacing y with yes in solasvacant column and replacing N with No in solasvacant column
update
nashville_housing_data.housing_data
set soldasvacant =
case when soldasvacant = 'Y'
THEN 'Yes'
when soldasvacant = 'N'
THEN 'No'
else soldasvacant
END
------checking updates
select
distinct soldasvacant
from
nashville_housing_data.housing_data
----
select distinct landuse
from
nashville_housing_data.housing_data
--replacing 'VACANT RESIENTIAL LAND' with 'VACANT RESIDENTIAL LAND' in solasvacant column
update
nashville_housing_data.housing_data
set landuse =
case
when landuse = 'VACANT RESIENTIAL LAND'
then 'VACANT RESIDENTIAL LAND'
else landuse
END
----checking updates for landuse
select distinct
landuse
from
nashville_housing_data.housing_data
------checking column propertyaddress is null
select *
from
nashville_housing_data.housing_data
where
propertyaddress is null
order by parcelid
---each paqrcelid has only one propertyaddress but there more than one unit could have same address if they have same parcelid.using selfjoin tp populate them
select
a.parcelid, b.parcelid,
a.propertyaddress, b.propertyaddress
from
nashville_housing_data.housing_data as a
join
nashville_housing_data.housing_data as b
on a.parcelid = b.parcelid
and a.uniqueid <> b.uniqueid
where a.propertyaddress is null
order by a.parcelid
---updating column propertyaddress
update nashville_housing_data.housing_data as a
set propertyaddress = b.propertyaddress
from
nashville_housing_data.housing_data as b
where a.parcelid = b.parcelid
and a.uniqueid <> b.uniqueid
and a.propertyaddress is null
---checking updates
select *
from
nashville_housing_data.housing_data
where
propertyaddress is null
order by parcelid
----standarizing date
select
to_date(saledate,'DD Mon YY') AS saledate
from
nashville_housing_data.housing_data
----
------checking updates
select
saledate
from
nashville_housing_data.housing_data
---udating standarized date
update nashville_housing_data.housing_data
set
saledate = to_date(saledate,'DD Mon YY')
----breaking out propertyaddress into invidual columns (address,city,state)
select
split_part(propertyaddress, ',', 1),
split_part(propertyaddress, ',', 2)
from
nashville_housing_data.housing_data
---updating propertysplitaddress
alter table
nashville_housing_data.housing_data
add split_propertyaddress nchar(300)
---
update nashville_housing_data.housing_data
set
split_propertyaddress = split_part(propertyaddress, ',', 1)
----updating propertysplitcity
alter table
nashville_housing_data.housing_data
add propertysplitcity nchar(300)
-----
update nashville_housing_data.housing_data
set propertysplitcity = split_part(propertyaddress, ',', 2)
---checking updates
select
*
from
nashville_housing_data.housing_data
----updating owneraddress into ownersplitaddress
alter table
nashville_housing_data.housing_data
add ownersplitaddress nchar(300)
---
update nashville_housing_data.housing_data
set ownersplitaddress = split_part(owneraddress, ',', 1)
----updating owneraddress into ownersplitcity
alter table
nashville_housing_data.housing_data
add ownersplitcity nchar(300)
---
update nashville_housing_data.housing_data
set ownersplitcity = split_part(owneraddress, ',', 2)
----updating owneraddress into ownersplitstate
alter table
nashville_housing_data.housing_data
add ownersplitstate nchar(300)
---
update nashville_housing_data.housing_data
set ownersplitstate = split_part(owneraddress, ',', 3)
------checking updates
select
*
from
nashville_housing_data.housing_data