1.
SELECT: Retrieve data from a database.
Example:
SELECT column1, column2, …
FROM table_name;
2.
WHERE: Filter records.
Example:
SELECT column1, column2, …
FROM table_name
WHERE condition;
3.
ORDER BY: Sort the result set.
Example:
SELECT column1, column2, …
FROM table_name
ORDER BY column1 [ASC|DESC];
4.
GROUP BY: Group rows that have the same values in specified columns.
Example:
SELECT column1, COUNT(column2) FROM table_name
GROUP BY column1;
5.
HAVING: Filter groups based on a condition.
Example:
SELECT column1, COUNT(column2) FROM table_name
GROUP BY column1
HAVING COUNT(column2) > 5;
Join Operations
1.
INNER JOIN: Select records that have matching values in both tables.
Example:
SELECT table1.column1, table2.column2 FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;
2.
LEFT JOIN: Select all records from the left table, and matched records from the right table.
Example:
SELECT table1.column1, table2.column2
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;
3.
RIGHT JOIN: Select all records from the right table, and matched records from the left table.
Example:
SELECT table1.column1, table2.column2
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;
4.
FULL OUTER JOIN: Select all records when there is a match in either left or right table.
Example:
SELECT table1.column1, table2.column2
FROM table1
FULL OUTER JOIN table2
ON table1.common_column = table2.common_column;
Subqueries
5.
Subquery in SELECT: Use a subquery in the SELECT statement.
Example:
SELECT column1,
(SELECT MAX(column2) FROM table2 WHERE table2.id = table1.id) AS max_value
FROM table1;
6.
Subquery in WHERE: Use a subquery in the WHERE clause.
Example:
SELECT column1, column2
FROM table1
WHERE column1 IN (SELECT column1 FROM table2 WHERE condition);
Aggregation Functions
1.
SUM: Calculate the sum of a numeric column.
Example:
SELECT SUM(column1)
FROM table_name;
2.
AVG: Calculate the average value of a numeric column.
Example:
SELECT AVG(column1)
FROM table_name;
3.
MIN and MAX: Find the minimum and maximum value of a column.
Example:
SELECT MIN(column1), MAX(column1)
FROM table_name;
4.
COUNT: Count the number of rows.
Example:
SELECT COUNT(*)
FROM table_name;
Advanced SQL Syntax
1.
CASE: Create conditional expressions.
Example:
SELECT column1,
CASE
WHEN condition1 THEN ‘Result1’
WHEN condition2 THEN ‘Result2’
ELSE ‘Result3’
END AS new_column
FROM table_name;
2.
UNION: Combine the result sets of two queries.
Example:
SELECT column1, column2
FROM table1
UNION
SELECT column1, column2
FROM table2;
3.
UNION ALL: Combine the result sets of two queries, including duplicates.
Example:
SELECT column1, column2
FROM table1
UNION ALL
SELECT column1, column2
FROM table2;
4.
COALESCE: Return the first non-null value in a list.
Example:
SELECT column1, COALESCE(column2, ‘Default Value’)
FROM table_name;
Window Functions
1.
ROW_NUMBER: Assign a unique number to each row.
Example:
SELECT column1, column2,
ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_num
FROM table_name;
2.
RANK: Assign a rank to each row within a partition.
Example:
SELECT column1, column2,
RANK() OVER (PARTITION BY column1 ORDER BY column2) AS rank
FROM table_name;
3.
LEAD and LAG: Access data from subsequent or preceding rows.
Example:
SELECT column1, column2,
LAG(column2, 1) OVER (PARTITION BY column1 ORDER BY column2) AS prev_value,
LEAD(column2, 1) OVER (PARTITION BY column1 ORDER BY column2) AS next_value
FROM table_name;
