HousingProject.sql

/*

		Author: Lateef Adewale
		ProjectTask: Cleaning Data in SQL Queries
*/



			select *
			from dbo.NashVilleHousing

-- Standardize Date Format


		Select saleDate, CONVERT(Date,SaleDate) SaleDateConverted
		From dbo.NashvilleHousing

		Update NashvilleHousing
		SET SaleDate = CONVERT(Date,SaleDate)

-- Checking to see if the query above worked

		select PropertyAddress,
		LandUse,
		SaleDate,
		SaleDateConverted 
		From dbo.NashVilleHousing

		ALTER TABLE NashvilleHousing
		Add SaleDateConverted Date;

		Update NashvilleHousing
		SET SaleDateConverted = CONVERT(Date,SaleDate)

		Select * from dbo.NashVilleHousing

-- Populate Property Address data

		Select *
		From dbo.NashvilleHousing
		--Where PropertyAddress is null
		order by ParcelID


		Select a.ParcelID, a.PropertyAddress, b.ParcelID, b.PropertyAddress, ISNULL(a.PropertyAddress,b.PropertyAddress)
		From dbo.NashvilleHousing a
		JOIN dbo.NashvilleHousing 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 dbo.NashvilleHousing a
		JOIN dbo.NashvilleHousing b
			on a.ParcelID = b.ParcelID
			AND a.[UniqueID ] <> b.[UniqueID ]
		Where a.PropertyAddress is null

 -- Breaking out Address into Individual Columns (Address, City, State)


		Select PropertyAddress
		From dbo.NashvilleHousing
		--Where PropertyAddress is null
		--order by ParcelID

		SELECT
		SUBSTRING(PropertyAddress, 1, CHARINDEX(',', PropertyAddress) -1 ) as Address
		, SUBSTRING(PropertyAddress, CHARINDEX(',', PropertyAddress) + 1 , LEN(PropertyAddress)) as Address

		From dbo.NashvilleHousing


		ALTER TABLE NashvilleHousing
		Add PropertySplitAddress Nvarchar(255);

		select * from dbo.NashVilleHousing


		Update NashvilleHousing
		SET PropertySplitAddress = SUBSTRING(PropertyAddress, 1, CHARINDEX(',', PropertyAddress) -1 )

		ALTER TABLE NashvilleHousing
		Add PropertySplitCity Nvarchar(255);

		Update NashvilleHousing
		SET PropertySplitCity = SUBSTRING(PropertyAddress, CHARINDEX(',', PropertyAddress) + 1 , LEN(PropertyAddress))



		Select OwnerAddress
		From dbo.NashvilleHousing


		Select
		PARSENAME(REPLACE(OwnerAddress, ',', '.') , 3)
		,PARSENAME(REPLACE(OwnerAddress, ',', '.') , 2)
		,PARSENAME(REPLACE(OwnerAddress, ',', '.') , 1)
		From dbo.NashvilleHousing



		ALTER TABLE NashvilleHousing
		Add OwnerSplitAddress Nvarchar(255);

		Update NashvilleHousing
		SET OwnerSplitAddress = PARSENAME(REPLACE(OwnerAddress, ',', '.') , 3)


		ALTER TABLE NashvilleHousing
		Add OwnerSplitCity Nvarchar(255);

		Update NashvilleHousing
		SET OwnerSplitCity = PARSENAME(REPLACE(OwnerAddress, ',', '.') , 2)



		ALTER TABLE NashvilleHousing
		Add OwnerSplitState Nvarchar(255);

		Update NashvilleHousing
		SET OwnerSplitState = PARSENAME(REPLACE(OwnerAddress, ',', '.') , 1)



		Select *
		From dbo.NashvilleHousing



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


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




		Select SoldAsVacant
	, CASE When SoldAsVacant = 'Y' THEN 'Yes'
	   When SoldAsVacant = 'N' THEN 'No'
	   ELSE SoldAsVacant
	   END
		From dbo.NashvilleHousing


		Update NashvilleHousing
		SET SoldAsVacant = CASE When SoldAsVacant = 'Y' THEN 'Yes'
	    When SoldAsVacant = 'N' THEN 'No'
	    ELSE SoldAsVacant
	    END


-- Using CTE to remove duplicate rows


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

		From dbo.NashvilleHousing
		--order by ParcelID
		)
		Select *
		From RowNumCTE
		Where row_num > 1
		Order by PropertyAddress


--Checking to see all rows

		Select *
		From dbo.NashvilleHousing

-- Deleting Unused Columns


		ALTER TABLE dbo.NashvilleHousing
		DROP COLUMN OwnerAddress, TaxDistrict, PropertyAddress, SaleDate

-- Checking Final Data


		Select *
		From dbo.NashvilleHousing