Table of contents
No headings in the article.
- 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
- 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
- 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;
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';
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
- 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.