USE OLTP
--Question 1. 
--a.	Query the countries_view view, making note of the duplicate rows. 
SELECT * FROM countries_view

--b.	Modify the countries view to remove duplicates. 
go
ALTER VIEW countries_view as
SELECT distinct country,city
FROM countries
go

--c.	Test that the duplicates are no longer there.
SELECT * FROM countries_view

--Question 2.
--a.	Profile retailer_type in the sales_view table
SELECT retailer_type,count(*) as number_of_occurances
FROM sales_view
GROUP BY retailer_type
ORDER BY retailer_type,number_of_occurances

--b. Use the various techniques already discussed to fix errors in the retailer_type column in the sales view.
--We need to trim spaces from the data, remove non-printable characters and replace 'Direct Mark.' 
--with 'Direct Marketing' and 'Dept. Store' with 'Department Store'
go
ALTER VIEW sales_view AS
SELECT date
       ,replace(replace(trim(retailer_city),'Lodon','London'),'Londonn','London') as retailer_city
       ,order_method_type,urgent
	   ,replace(replace(dbo.clean(trim(retailer_type)),'Direct Mark.','Direct Marketing'),'Dept. Store','Department Store')
	    as retailer_type
	   ,product_code,sale_price,quantity_sold
FROM sales
go

--c. Check the profiler to make sure the new view has reduced the errors.
SELECT retailer_type,count(*) as number_of_occurances
FROM sales_view
GROUP BY retailer_type
ORDER BY retailer_type,number_of_occurances

--Question 3 
--a. Modifty the sales_clean() multi-statement table-valued function and make the changes to the retailer type 
--column that we had done to the view in the previous question.
go
ALTER FUNCTION sales_clean()
RETURNS @clean_data TABLE ( --we can define the structure of the table we want to return
Date date
,Retailer_city char(120)
,Order_method_type char(120)
,Urgent char(5)
,Retailer_type char(120)
,Product_code char(5) 
,Sale_price money
,Quantity_sold int) as

BEGIN --the sales_clean() function
	--Populate the table
	INSERT INTO @clean_data
	SELECT * FROM sales

	--Clean-up the customer_city column
	--Get rid of spaces
	UPDATE @clean_data
	SET retailer_city = trim(retailer_city)
	--Correct 'Lodon' and 'Londonn'
	UPDATE @clean_data
	SET retailer_city = 'London'
	WHERE retailer_city in ('Lodon','Londonn')
    --Add any additional clean-up for customer_city here!

	--Add Clean-up for retailer_type
	UPDATE @clean_data
	SET retailer_type = dbo.clean(trim(retailer_type))
	
	--Correct 'Direct Mark.' to 'Direct Marketing' and 'Dept. Store' to 'Department Store'
	UPDATE @clean_data
	SET retailer_type = 'Direct Marketing'
	WHERE retailer_type in ('Direct Mark.')

	-- and 'Dept. Store' to 'Department Store'
	UPDATE @clean_data
	SET retailer_type = 'Department Store'
	WHERE retailer_type in ('Dept. Store')

	--When you are done, return back to the user
	return 

END --the sales_clean() function
go

--b. Check the profiler to make sure the new view has reduced the errors.
SELECT retailer_type,count(*) as number_of_occurances
FROM sales_clean()
GROUP BY retailer_type
ORDER BY retailer_type,number_of_occurances