SQL Server - SQL Technical test its been a few years since i used SQL i mostly use R and sas now and i have an interview thats states sql understanding is required
The job role is for Risk Assessment Model Developer
The job role states -
•   The role holder will have the ability to leverage  analytics to support the development of models and interpretation of regulations, statutes, and conduct matters in business context and Intelligence, delivering complex information in a way that is simple and easy to understand.
Im comfortable with my skills when im pulling data from a formatted database or live system  but it seems on this test they want knowledge on taking unformatted data and showing indepth formatted MI as a result.
Formatting will be my downfall here if i cant get up to date as i had the easy life of using sas eg where the tools really helps me with my  informatting
Below is all i have been given as what to expect and prep for so any help would be appreciated
What to expect?
• The questions are technical and include database interrogation.
• The data is on separate lines which needs to be concatenated into different columns. The length and end aren’t the same.
• Look up or refresh - order by Rand() limit function
I don't fully follow all of your example questions, but knowing what the position is you're interviewing for may help in us giving you advice on what to study and how deep to go down the rabbit hole on certain topics. For example a Database Administrator role would certainly need to study more technical management things about databases such as backups, Transaction Log and growth, SQL security, maybe HA/DR features like Availability Groups, etc. Whereas a Data Analyst role would probably not need to know any of that stuff, but have a basic understanding of the Data Query Language (DQL) in SQL Server and general database structure. And a Database Developer would have some kind of skillset in between both roles, in addition to things neither role necessarily needs to know.
I'm going to assume since your existing skillset involves R, then you're probably applying to a position somewhere around Data Analyst. In that case, a quick review of W3Schools SQL Walkthrough is a good refresher on the basics of DQL and SQL in general. It also goes through some of the functions in SQL Server so that'll be good to know for some of the potential questions.
You should also review and make sure you understand the fundamentals of keys and referential integrity.
You should also make sure you thoroughly understand the different types of JOINS in SQL Server, which the previous W3Schools link will go through, but this webpage on SQL Server Joins also does a good breakdown specifically on them, with visual representations of each one. Understanding how to use JOINS in a relational structure are very important. The only type of JOIN that doesn't cover is the CROSS JOIN but you can read up on it here.
You should also understand CROSS APPLY and OUTER APPLY as well, which you can read up on in this article. Though they're not as common and probably slightly less important than understanding the previous JOIN types, so don't stress as much over them.
It sounds like one of the example questions involves transforming rows into columns which means you should understand how to use the PIVOT and UNPIVOT operators. (Quick tip, use PIVOT to turn row values into columns, and UNPIVOT to turn columns into row values.) Here's a good walkthrough with examples on using the PIVOT operator.
Understanding Window Functions is a very useful thing but also a little bit of an advanced topic depending on the position you're applying for. Particularly DENSE_RANK(), RANK(), and ROW_NUMBER() are good ones to know. More information and examples here.
If you update your post with more details such as the specific job role you're applying for, I'll update my answer with more relevant information accordingly.