1. UNION: When using the "union" keyword to combine tables, ensure that the tables have the same schema, and the data types of corresponding columns match. If there are differences, the combination may not be accurate. Use "union" wisely, and if you want to keep duplicates, use "union all." Example: Combine new employee records with employee records from another table:

SELECT new_id, new_name, new_addr

FROM NewEmployee

UNION SELECT

EmployeeID, FirstName, LastName

FROM table_one

1. CASE STATEMENTS: Like "if" and "else" statements in coding, "case" statements create a new column containing the desired result based on given conditions. Examples:

SELECT FirstName, LastName, Age,

CASE

WHEN Age > 30 THEN 'Experienced'

WHEN Age <= 30 THEN 'Young'

ELSE 'Baby'

END

FROM table_one

ORDER BY Age

1. HAVING CLAUSE: Use the HAVING clause to filter results of a GROUP BY clause based on aggregate function results, such as AVG, SUM, COUNT, MIN, or MAX. Example:

SELECT department, AVG(salary)

FROM employees

GROUP BY department

HAVING AVG(salary) > 50000;

1. UPDATE and DELETE data in a table: When updating or deleting data in a table, use comma-separated values in a SET statement. Examples:

   Update:

UPDATE employees

SET salary = 60000

WHERE department = 'Sales';

Delete:

DELETE FROM employees

WHERE department = 'HR';

1. ALIAS: Use an alias to rename columns or tables for easier reference in SQL statements. Examples:

   example 1:

SELECT FirstName + ' ' + LastName AS FullNAME

FROM table_one to

example 2:

SELECT to.JobTitle, AVG(Salary) as avgSalary

FROM table_one to

JOIN table_two tt

ON to.EmployeeID=tt.EmployeeID

GROUP BY JobTitle

1. PARTITION: Partitioning divides a result set into smaller, more manageable subsets based on one or more columns. This enables more detailed grouping and analysis of data and separate calculations and aggregations to be performed on each partition. Example using partition:

SELECT FirstName, LastName, Gender, Salary,

COUNT(Gender) OVER (PARTITION BY Gender) as allGender

FROM table_one to JOIN table_two tt

ON to.EmployeeID=tt.EmployeeID

Example using order by:

SELECT Gender, COUNT(Gender)

FROM table_one to

JOIN table_two tt

ON to.EmployeeID=tt.EmployeeID

GROUP BY Gender

Note: When I utilized table_one as well as table_two, the terms 'to' and 'tt' serve as aliases for table_one and table_two. For instance, EmployeeSalary (ES) and EmployeeManager (EM) are just alternative names used to simplify our work or act as aliases for their corresponding tables.