1. INNER , Lest , Right Joins
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
INNER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
2. FULL OUTER JOIN
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
FULL OUTER JOIN Departments
ON Employees.DepartmentID = Departments.DepartmentID;
3. CROSS JOIN
SELECT Employees.EmployeeName, Departments.DepartmentName
FROM Employees
CROSS JOIN Departments;
4. SELF JOIN
SELECT A.EmployeeName AS Employee, B.EmployeeName AS Manager
FROM Employees A
LEFT JOIN Employees B
ON A.ManagerID = B.EmployeeID;
5. Windonw Functions
SELECT
EmployeeName,
DepartmentID,
Salary,
ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RowNum,
RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS RankNum,
DENSE_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DenseRankNum
FROM Employees;
6. Aggregate Window Functions (SUM, AVG, COUNT, MAX, MIN)
SELECT employee_id, department_name, salary,
SUM(salary) OVER(PARTITION BY department_name ORDER BY employee_id) AS “Total”,
AVG(salary) OVER(PARTITION BY department_name ORDER BY employee_id) AS “Average”,
COUNT(salary) OVER(PARTITION BY department_name ORDER BY employee_id) AS “Count”,
MIN(salary) OVER(PARTITION BY department_name ORDER BY employee_id) AS “Min”,
MAX(salary) OVER(PARTITION BY department_name ORDER BY employee_id) AS “Max”
FROM employees_data
7. Ranking Window Functions
SELECT employee_id, department_name, salary,
ROW_NUMBER() OVER(PARTITION BY department_name ORDER BY salary DESC) AS “Row_Num”,
RANK() OVER(PARTITION BY department_name ORDER BY salary DESC) AS “Rank”,
DENSE_RANK() OVER(PARTITION BY department_name ORDER BY salary DESC) AS “Dense_Rank”,
PERCENT_RANK() OVER(PARTITION BY department_name ORDER BY salary DESC) AS “Percent_Rank”,
FROM employees_data
8. Value/Analytic Window Functions
SELECT employee_id, department_name, salary,
LEAD(salary, 1) OVER(PARTITION BY department_name ORDER BY salary DESC) AS “Lead_Val”,
LAG(salary, 1) OVER(PARTITION BY department_name ORDER BY salary DESC) AS “Lag_Val”,
FIRST_VALUE(salary) OVER(PARTITION BY department_name ORDER BY salary DESC) AS “First_Val”,
LAST_VALUE(salary) OVER(PARTITION BY department_name ORDER BY salary DESC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS “Last_Val”
FROM employees_data
9. GROUP BY with HAVING
SELECT department_name,
AVG(salary) AS average_salary
FROM employees_data GROUP BY
department_name HAVING AVG(salary) > 60000;
10. DAX Query
// 1. Calculated Measure: Total Sales
Total_Sales = SUM(Sales[SaleAmount])
// 1. Total Industrial Output Measure
Total_Industrial_Output = SUM(‘nagpur_agri_industrial_data_10y'[Industrial_Output_million_INR])
// 2. Average Crop Yield Measure
Average_Crop_Yield = AVERAGE(‘nagpur_agri_industrial_data_10y'[Crop_Yield_tons_per_hectare])
// 3. Total Employment across Districts
Total_Employment = SUM(‘nagpur_agri_industrial_data_10y'[Employment])
// 4. Industrial Output for Nagpur District only (using CALCULATE)
Nagpur_Industrial_Output =
CALCULATE(
SUM(‘nagpur_agri_industrial_data_10y'[Industrial_Output_million_INR]),
‘nagpur_agri_industrial_data_10y'[District] = “Nagpur”
)
// 5. Calculated Column: Total Energy Consumption per Employee
Energy_Per_Employee =
DIVIDE(
‘nagpur_agri_industrial_data_10y'[Energy_Consumption_MWh],
‘nagpur_agri_industrial_data_10y'[Employment]
)
11. DAX Query View
EVALUATE
‘nagpur_agri_industrial_data_10y’
EVALUATE
FILTER(
‘nagpur_agri_industrial_data_10y’,
‘nagpur_agri_industrial_data_10y'[District] = “Nagpur”
)
EVALUATE
‘nagpur_agri_industrial_data_10y’
ORDER BY ‘nagpur_agri_industrial_data_10y'[Industrial_Output_million_INR] DESC