How to do the below in one SQL query?

Question

SQL

There is a task:

Make a list of Products from the Production.Product table and display the following characteristics:
1. Category, Subcategory, Product ID
2. Full name of the product, with separators => (Name; Product number; Color; Size)
3. Full name of the product for display on the web page:
* Limit the full name of the product to 30 characters
* If the string is longer, put '...' at the end
* Replace all spaces with '%20"
4. How much the product is selling (in months). Consider that the product may
5. Find and display the average cost of goods in this category / subcategory 6.
Based on this, determine the price category of the goods
* If the product is 50% or more more expensive than the average price in the category - 'High Price'
* If the product is 30% or more cheaper than the average price in the category - 'LowPrice'
* For the rest - 'Medium price'

I have no idea how to do all this in 1 sql query. I will be glad if someone will prompt.
Table https://dataedo.com/samples/html/AdventureWorks/do...