1. Subqueries (Query inside another query)
- Single-row → Returns one value
- Multi-row → Returns multiple values
- Correlated → Uses values from outer query
Example:
SELECT Name
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);
(Employees earning more than average salary)
2. Aggregate Functions (Work on groups of rows)
| Function | Purpose |
|---|---|
| COUNT() | Count rows |
| SUM() | Add values |
| AVG() | Find average |
| MAX() | Highest value |
| MIN() | Lowest value |
Example:
SELECT COUNT(*), AVG(Salary) FROM Employees;
(Count employees + find average salary)
3. GROUP BY (Group rows by column value)
- Used with aggregate functions
Example:
SELECT Department, AVG(Salary)
FROM Employees
GROUP BY Department;
(Average salary per department)
4. HAVING (Filter groups after GROUP BY)
- Like
WHERE, but works on groups
Example:
SELECT Department, AVG(Salary)
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 50000;
(Departments with avg salary > 50,000)
5. Set Operations (Combine results of queries)
| Operation | Description |
|---|---|
| UNION | Combine results, remove duplicates |
| UNION ALL | Combine results, keep duplicates |
| INTERSECT | Common rows only |
| EXCEPT | Rows in 1st query, not in 2nd |
Example:
SELECT Name FROM Employees_A
UNION
SELECT Name FROM Employees_B;
(Combine employee names from 2 tables, no duplicates)