SELECT CONCAT(column1, column2) AS ... FROM table_name
SELECT CONCAT(string1, string2) AS ... FROM table_name

SELECT DATEDIFF(interavl, GETDATE(), date1) AS dated FROM table_name

    SELECT * FROM table_name WHERE column IN (SELECT * FROM table_name2 WHERE condition)

    SELECT *, (SELECT COUNT(*) FROM table_name2 WHERE table_name2.column = table_name.column) 
    FROM table_name

SELECT * FROM (/*sql statement*/) AS ... 
WHERE condition

WITH expression_name (column1, column2, ....)
AS
(
    --SQL statement
)
--using cte 
SELECT * FROM expression_name

CREATE FUNCTION function_name (@variable type) RETURN TABLE
AS
RETURN
(
    SELECT * FROM table_name WHERE ... = @variable
)