SQL_Projects/SQL PortfolioProject_2_Data Cleaning_1.sql at main · rzn-git/SQL_Projects · GitHub

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
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206


select *
from PortfolioProject1.dbo.NashvilleHousing1


--Standardize Date Format


select SaleDate, convert (date,SaleDate)
from PortfolioProject1..NashvilleHousing1


update PortfolioProject1..NashvilleHousing1
set Saledate = convert (date, Saledate)


select Saledate
from PortfolioProject1.dbo.NashvilleHousing1


alter table PortfolioProject1.dbo.NashvilleHousing1
Add SaleDateConverted Date;

-- Update is not working

Update PortfolioProject1.dbo.NashvilleHousing1
SET SaleDateConverted = convert (Date,SaleDate)

select SaleDateConverted
from PortfolioProject1.dbo.NashvilleHousing1


--Populate Property Address Data


select *
from PortfolioProject1..NashvilleHousing1
--where PropertyAddress is null
order by parcelid


select a.ParcelID, a.PropertyAddress, b.ParcelID, b.PropertyAddress, ISNULL(a.PropertyAddress, b.PropertyAddress)
from PortfolioProject1..NashvilleHousing1 a
JOIN PortfolioProject1..NashvilleHousing1 b
	ON a.ParcelID = b.ParcelID
	and a.[UniqueID ]<>b.[UniqueID ]
where a.PropertyAddress is null


update a
set PropertyAddress = ISNULL(a.PropertyAddress, b.PropertyAddress)
from PortfolioProject1..NashvilleHousing1 a
JOIN PortfolioProject1..NashvilleHousing1 b
	ON a.ParcelID = b.ParcelID
	and a.[UniqueID ]<>b.[UniqueID ]
where a.PropertyAddress is null


-- Breaking out PropertyAddress into Individual Columns


Select PropertyAddress
From PortfolioProject1.dbo.NashvilleHousing1


Select
substring (PropertyAddress, 1 , CHARINDEX(',',PropertyAddress)-1) as Address
,substring (PropertyAddress, CHARINDEX(',',PropertyAddress)+1, len (PropertyAddress)) as Address
From PortfolioProject1.dbo.NashvilleHousing1

alter table PortfolioProject1.dbo.NashvilleHousing1
Add PropertySplitAddress Nvarchar(255);


Update PortfolioProject1.dbo.NashvilleHousing1
SET PropertySplitAddress = substring (PropertyAddress, 1 , CHARINDEX(',',PropertyAddress)-1)

alter table PortfolioProject1.dbo.NashvilleHousing1
Add PropertySplitCity  Nvarchar(255);


Update PortfolioProject1.dbo.NashvilleHousing1
SET PropertySplitCity  = substring (PropertyAddress, CHARINDEX(',',PropertyAddress)+1, len (PropertyAddress))


Select PropertySplitAddress, PropertySplitCity
From PortfolioProject1.dbo.NashvilleHousing1


--Breaking out OwnerAddress into Individual Columns

Select OwnerAddress
From PortfolioProject1.dbo.NashvilleHousing1


Select
Parsename (Replace(OwnerAddress, ',','.'),3)
,Parsename (Replace(OwnerAddress, ',','.'),2)
,Parsename (Replace(OwnerAddress, ',','.'),1)
From PortfolioProject1.dbo.NashvilleHousing1


alter table PortfolioProject1.dbo.NashvilleHousing1
Add OwnerSplitAddress Nvarchar(255);


Update PortfolioProject1.dbo.NashvilleHousing1
SET OwnerSplitAddress = Parsename (Replace(OwnerAddress, ',','.'),3)

alter table PortfolioProject1.dbo.NashvilleHousing1
Add OwnerSplitCity  Nvarchar(255);


Update PortfolioProject1.dbo.NashvilleHousing1
SET OwnerSplitCity  = Parsename (Replace(OwnerAddress, ',','.'),2)


alter table PortfolioProject1.dbo.NashvilleHousing1
Add OwnerSplitState Nvarchar(255);


Update PortfolioProject1.dbo.NashvilleHousing1
SET OwnerSplitState = Parsename (Replace(OwnerAddress, ',','.'),1)


Select OwnerAddress, OwnerSplitAddress, OwnerSplitCity, OwnerSplitState
From PortfolioProject1.dbo.NashvilleHousing1


-- Change Y and N to Yes and No in "Sold as Vacant" field

Select SoldAsVacant
From PortfolioProject1.dbo.NashvilleHousing1
where SoldAsVacant = 'No'

Select Distinct(SoldAsVacant), Count(SoldAsVacant)
From PortfolioProject1.dbo.NashvilleHousing1
Group by SoldAsVacant
order by 2


Select SoldAsVacant
, case	when SoldAsVacant = 'Y' Then 'Yes'
		when SoldAsVacant = 'N' Then 'No'
		Else SoldAsVacant
		end
From PortfolioProject1.dbo.NashvilleHousing1


Update PortfolioProject1.dbo.NashvilleHousing1
Set SoldAsVacant= case	when SoldAsVacant = 'Y' Then 'Yes'
		when SoldAsVacant = 'N' Then 'No'
		Else SoldAsVacant
		end


-- Remove Duplicates


Select *
From PortfolioProject1.dbo.NashvilleHousing1

with RowNumCTE as(
Select *,
	ROW_NUMBER() OVER (
	PARTITION BY ParcelID,
				 PropertyAddress,
				 SalePrice,
				 SaleDate,
				 LegalReference
				 ORDER BY UniqueID ) as row_num

From PortfolioProject1.dbo.NashvilleHousing

)

delete
from RowNumCTE
where row_num > 1

Select *
from RowNumCTE
where row_num > 1
order by PropertyAddress


-- Delete unnecessary columns

Select *
From PortfolioProject1.dbo.NashvilleHousing1


ALTER TABLE PortfolioProject1.dbo.NashvilleHousing1
DROP COLUMN OwnerAddress, TaxDistrict, PropertyAddress, SaleDate