Subqueries, Aggregate Functions, Group By, Having Clause, and Set Operations

1. Subqueries

  • A query inside another query.
  • Use it to get results for the main query.
  • Types:
    • Single-row: Returns one value.
    • Multi-row: Returns multiple values.
    • Correlated: Uses data from the outer query.
  • Example:
    “Find employees earning more than the average salary.” SELECT Name FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);

2. Aggregate Functions

  • Used to calculate values for a group of rows.
  • Common Functions:
    • COUNT(): Counts rows.
    • SUM(): Adds numbers.
    • AVG(): Finds the average.
    • MAX(): Finds the highest value.
    • MIN(): Finds the lowest value.
  • Example:
    “Count total employees and find the average salary.” SELECT COUNT(*), AVG(Salary) FROM Employees;

3. GROUP BY Clause

  • Groups rows with the same value in a column.
  • Use it with aggregate functions.
  • Example:
    “Find the average salary for each department.” SELECT Department, AVG(Salary) FROM Employees GROUP BY Department;

4. HAVING Clause

  • Filters groups after using GROUP BY.
  • Similar to WHERE, but works on groups.
  • Example:
    “Show departments with an average salary above 50,000.” SELECT Department, AVG(Salary) FROM Employees GROUP BY Department HAVING AVG(Salary) > 50000;

5. Set Operations

  • Combine results from two queries.
  • Types:
    • UNION: Combines results, removes duplicates.
    • UNION ALL: Combines results, keeps duplicates.
    • INTERSECT: Shows only matching rows.
    • EXCEPT: Shows rows in the first query but not in the second.
  • Example:
    “Combine employee names from two tables, no duplicates.” SELECT Name FROM Employees_A UNION SELECT Name FROM Employees_B;

Quick Memory Tips

  1. Subqueries: Query inside a query.
  2. Aggregate Functions: COUNT(), SUM(), AVG(), MAX(), MIN().
  3. GROUP BY: Groups rows with the same value.
  4. HAVING: Filters groups (like WHERE but for groups).
  5. Set Operations: Combine query results (UNION, INTERSECT, EXCEPT).