Execute the following script in Query Editor to create tables with different collation and construct queries with collation conflict resolution:

use tempdb
go

select ContactID, FirstName, LastName
into ContactAS
from AdventureWorks.Person.Contact
go

select ContactID,
FirstName = FirstName COLLATE SQL_Latin1_General_CP1_CI_AI,
LastName = LastName COLLATE SQL_Latin1_General_CP1_CI_AI
into ContactAI
from AdventureWorks.Person.Contact
go

-- Collation conflict
select [Name] = s.FirstName + ' '+i.LastName
from ContactAS s
join ContactAI i
on s.ContactID = i.ContactID
order by [Name]
go
-- Error message: Cannot resolve collation conflict for column 1 in SELECT statement.

-- Collation conflict resolved
select [Name] = s.FirstName + ' '+i.LastName COLLATE DATABASE_DEFAULT
from ContactAS s
join ContactAI i
on s.ContactID = i.ContactID
order by [Name]
go

select [Name] = s.FirstName + ' '+i.LastName COLLATE SQL_Latin1_General_CP1_CI_AI
from ContactAS s
join ContactAI i
on s.ContactID = i.ContactID
order by [Name]
go