Final answer:
Aggregate functions cannot be used on non-grouped columns in SELECT statements and typically ignore null values. The GROUP BY clause groups similar data for aggregate operations. Subqueries are nested queries for single row operations, whereas joins combine columns from multiple tables but can't always substitute subqueries.
Step-by-step explanation:
Understanding SQL Concepts
Aggregate functions in SQL are used to perform calculations on sets of values and return a single value. They have certain restrictions in SELECT statements, particularly that they cannot be used on non-grouped columns unless those columns are included within the aggregate functions. values are generally ignored by aggregate functions, except for COUNT(*), which counts rows, including those with null values.
The GROUP BY clause groups rows that have the same values in specified columns into summary rows. It is often used in conjunction with aggregate functions. The difference between WHERE and HAVING clauses is that WHERE filters rows before aggregation, while HAVING filters groups after aggregation. Therefore, HAVING is used to filter groups based on aggregate conditions.
A subquery is a query nested inside another query, and can be used when a single value is needed, or to perform row-by-row operations with the outer query. A join combines columns from one or more tables based on a related column between them. Subqueries can sometimes be replaced by joins for better performance, but subqueries are necessary when we need to perform an operation that involves a single row or value that corresponds to each row of the outer query.