--create database Practice

--use Practice

select * from SalesData

--Q1 Concatenate Firstname and LastNames and call the column FullName
select concat([FirstName], ' ', [LastName]) AS FullName
from SalesData

-- Concat just puts two values together with a space placed in between with ' '.




--Q2A Extract the user name from email address and alias it UserName
Select [Email], LEFT([Email], charindex('@', [Email])-1) AS UserName
from SalesData;

--The -1 removes the @ from the username 
--LEFT like in Excel extracts characters from the left by a certain number. 
--The number length is decided by charindex which finds the first @ it can find and returns the number position. 

--Q2B Extract the Domain name from email address and alias it DomainName
Select [Email], RIGHT([Email], LEN([Email]) - charindex('@', [Email])) AS DomainName
from SalesData;

--RIGHT extracts characters from the right by a specific number
--By LEN - CHARINDEX this means everything before @ is removed and RIGHT is only counting to the character before the @




--Q3 Find the age of each costomer from DOB
select [DOB], DATEDIFF(YEAR, [DOB], GETDATE()) AS AGE
from SalesData;

--DATEDIFF calculates the difference in (here YEAR) between a date and another date. 
-- GETDATE() finds the current date for comparison.




--Q4 Impliment the following price structure in Amount column. 
--If the amount is <=20 decrease by 20% if the amount is between 21 and 50 increase by 5%, if about is between 51 and 80 reduce by £20 for other leave the amount as they are
select [Amount],
	CASE
		WHEN [Amount] <= 20 THEN [Amount]*0.8
		WHEN [Amount] >= 21 AND [Amount] <= 50 THEN [Amount]*1.05
		WHEN [Amount] >= 51 AND [Amount] <=80 THEN [Amount] - 20
		ELSE [Amount] --No effect
	END AS Altered_Amount
from SalesData

-- CASE and WHEN and ELSE is just like an IF statment in Excel or in Python. If anything gets passed the WHEN, then THEN occurs into the new column. 
-- This ELSE part just add the original column as the outcome, this means no effect.





--Q5 Concatenate Firstname and lastNames Initial eg Mark K
select concat([FirstName], ' ', LEFT([LastName],1)) AS First_Initial
from SalesData

--Just use LEFT to select the first letter of the first name.



--Q6 Concatenate ID and Last Name
select concat([LastName], ' ', [ID]) AS Name_ID
from SalesData




--Q7 Changing amount based off colour
select [Amount], [Color],
	CASE
		WHEN [Color] = 'Black'  THEN [Amount] + 10
		WHEN [Color] = 'Silver' THEN [Amount]*0.80
		WHEN [Color] = 'Multi'  THEN [Amount] + 20
		WHEN [Color] = 'White'  THEN [Amount] + 21
		ELSE  [Amount]
	END AS Altered_Color_Amount
from SalesData

--By adding [Amount] to ELSE, this means other colours have no effect from this statement.





--Q8 Show all data where colour is BLUE or Multi and ID between 10 and 60
select * from SalesData
WHERE ([Color] = 'Blue' OR [Color] = 'Multi') AND [ID] BETWEEN 10 and 60;

--The brackets that hold the colour filter are needed to ensure they occur first, then the later ID statment. This is because AND has a higher precedence than OR. 





--Q9 Filter Color to  Black 
select * from SalesData
WHERE ([Color] = 'Black');





--Q10 write a query to return the sum of the amount
select SUM([Amount]) AS Total_Amount
from SalesData;





--Q11 Find total amount by  Product_Subcategory
select 
[Product_Subcategory],
SUM([Amount]) AS Total_Amount
from SalesData
GROUP BY [Product_Subcategory];





--Q12 Find total amount by  Product_Subcategory, colour and Product_Subcategory
-- I assume the question meant to say Product_name instead of subcategory twice???
select 
[Product_Subcategory],
[Product_name],
[Color],
SUM([Amount]) AS Total_Amount
from SalesData
GROUP BY [Product_Subcategory], [Product_name], [Color];