SQL Essentials: Aliases, LIKE, Datetime, NULLs, Aggregates, Group By, Having, and Order By

Aliases, Derived Tables, and Output Readability

• Derived tables: temporary tables created within a query to simplify multi-table queries and correlated subqueries. They exist only for the duration of the query and do not modify the underlying database tables.
• Alias concept: give temporary nicknames to tables or derived results to improve readability of output and queries. Alias scope is limited to the query execution; it does not alter the actual data.
• Example of derived table with alias:

  SELECT t2.department, t2.total
  FROM (
      SELECT department_id AS department, COUNT(*) AS total
      FROM Employees
      GROUP BY department_id
  ) AS t2
  WHERE t2.total > 5;

• Practical implications: aliases help readability and maintenance; they do not change data state or database schema.

WHERE Clause and Logical Expressions

• Purpose: filter rows by evaluating a logical expression for each row; only rows for which the expression is true are included in the output.
• Evaluation model: row-by-row filtering as the table is scanned.
• Examples of expression forms:
  • String comparisons: e.g., name = 'Alice'
  • Numeric ranges: e.g., age BETWEEN 18 AND 65
  • Membership in a set: e.g., department_id IN (10, 20, 30)
• Logical operators:
  • AND means all conditions must be true: $ext{cond}<em>1 \land ext{cond}</em>2$
  • OR means at least one condition must be true: $ext{cond}<em>1 \lor ext{cond}</em>2$
• Precedence and grouping:
  • AND is evaluated before OR; use parentheses to enforce a desired order of evaluation.
  • Example: ( ext{age} > 18 \land ext{status} = 'active') \lor ext{role} = 'admin'}
• NULL handling in WHERE: NULLs are treated as unknown in comparisons; explicit checks using IS NULL or IS NOT NULL are common.

SELECT Clause and the Star (o)aster

• Star option: SELECT * means all columns from the specified table(s).
• Use case: handy when you are unsure about column names or when you want all columns in output.
• Caution: SELECT * can impact performance and readability in large tables or complex queries; prefer explicit column lists when possible.

LIKE Operator: Pattern Matching for Strings

• Purpose: match strings against a pattern.
• Wildcards:
  • Single-character wildcard: underscore (_) — replaces exactly one character.
  • Multi-character wildcard: percent sign (%) — replaces zero or more characters.
• Examples:
  • Single-character example: LIKE 'pink_an' matches strings that start with 'pink', have exactly one character in the middle, and end with 'an'. The single character can be any letter.
  • Multi-character example: LIKE 'PINK%' matches strings that start with 'PINK' followed by any characters of any length.
• Practical guidance: use _ when you know the exact length of the unknown segment; use % when the length is unknown or variable.

DATETIME Functions: Working with Date and Time

• Date-time values can include both date and time components.
• Useful functions:
  • Extract date part from a datetime: $ext{DATE(timestamp)}$
  • Calculate difference between two dates: $ext{DATEDIFF}(date1, date2)$ (returns the number of days between dates)
  • Current date: $ext{CURRENT<em>DATE}$ or $ext{CURRENT</em>DATE()}$
  • Extract year: $ext{YEAR}(datetime)$
  • Extract month: $ext{MONTH}(datetime)$
• Practical use: compute ages, tenure, duration between events, or time-based filtering.

NULLs and NOT NULL: Handling Missing or Special Values

• NOT NULL constraint: enforces that a column cannot contain NULL values.
• NULL semantics in WHERE: NULL represents missing data or an unknown value, and comparisons with NULL generally yield UNKNOWN (not TRUE or FALSE).
• Filtering with NULLs:
  • To find missing values: $ext{col IS NULL}$
  • To find non-missing values: $ext{col IS NOT NULL}$
• Important caution: NULL can carry domain-specific meaning (e.g., an intentionally unknown value or a placeholder). In queries, you may need to interpret such NULLs according to business rules.
• Demo note: in some examples, NULLs convey special meanings beyond simply “missing data”; use context-specific interpretation when filtering.

AGGREGATE FUNCTIONS: Reducing Rows to Summary Values

• Definition: aggregate functions operate on a group of rows and yield a single value per group.
• Common aggregate functions: $ext{COUNT}( ext{col}), ext{SUM}( ext{col}), ext{MAX}( ext{col}), ext{MIN}( ext{col}), ext{AVG}( ext{col})$
• Regular vs aggregate functions:
  • Regular functions operate per row (e.g., calculation on a single value).
  • Aggregate functions operate on groups of rows and produce one value per group.
• Example: count the number of employees born before 01/01/1980 and compute their average salary.
• Example syntax:

  SELECT COUNT(*) AS num_employees, AVG(salary) AS avg_salary
  FROM Employees
  WHERE birth_date < '1980-01-01';

• When to use: aggregate functions are typically used with GROUP BY to produce per-group summaries.

GROUP BY: Grouping Rows for Aggregation

• Purpose: partition rows into groups based on one or more attributes, then apply aggregate functions to each group.
• Syntax order: WHERE -> GROUP BY -> HAVING -> SELECT (and any aggregates returned per group).
• Examples:
  • Group by department to get per-department counts and average salary:
    sql SELECT department_id, COUNT(*) AS num_employees, AVG(salary) AS avg_salary FROM Employees GROUP BY department_id;
  • Include the grouping key in the SELECT so you can see which results map to which group.
• GROUP BY scope: after GROUP BY, individual records do not matter for the final output; only grouped (aggregate) values are considered in SELECT and HAVING.

HAVING: Filtering Groups After Aggregation

• HAVING filters groups created by GROUP BY based on conditions on aggregate data.
• It cannot be used without a GROUP BY (in practice, HAVING without GROUP BY behaves like a WHERE on aggregates, which is generally meaningless).
• Example: keep only departments with at least 5 employees:

  SELECT department_id, COUNT(*) AS num_employees
  FROM Employees
  GROUP BY department_id
  HAVING COUNT(*) >= 5;

• HAVING operates on aggregate results, not on individual row data.
• Practical note: after grouping, you should rely on aggregate quantities in SELECT and HAVING, unless you group by a subset that preserves some non-aggregated fields.

SELECT, GROUP BY, and HAVING: Practical Guidance and Common Pitfalls

• When you see phrases like “for each department” or “per group,” this signals a GROUP BY usage.
• If you group by an attribute, you should usually include that attribute in the SELECT list so you can identify which group each result corresponds to.
• NULL and NOT NULL in grouped results: consider how NULL values are treated in grouping keys and aggregates. -Common pitfalls to avoid:
  • Selecting non-aggregated columns that are not in the GROUP BY list.
  • Using HAVING on non-aggregated columns.
  • Placing non-aggregated expressions in SELECT when grouping is intended only for aggregates.

ORDER BY: Sorting Output for Presentation

• Purpose: sort the result set by one or more expressions.
• Default order: ascending (ASC).
• Descending option: use DESC to sort in descending order.
• Example:

  SELECT department_id, COUNT(*) AS num_employees, AVG(salary) AS avg_salary
  FROM Employees
  GROUP BY department_id
  ORDER BY avg_salary DESC;

• Note: ORDER BY is a display/sort preference; it does not affect the grouping or aggregation logic.

Full Syntax and Execution Flow: Putting It All Together

• Minimal syntax: SELECT FROM
• All other clauses are optional: WHERE, GROUP BY, HAVING, ORDER BY.
• Key rules and relationships:
  • HAVING cannot be used without a GROUP BY and should apply to aggregate quantities.
  • WHERE filters rows before grouping; it cannot include aggregate functions (in standard practice).
  • HAVING filters groups after aggregation; it can reference aggregated values (e.g., COUNT(*), AVG(salary)).
  • SELECT can include both regular expressions and aggregate expressions; when grouping, non-aggregated SELECT items should be those by which you group (or included in the GROUP BY).
  • Aliases in SQL: in MySQL, an alias defined in the SELECT clause can be used in GROUP BY and HAVING, but not in WHERE.
• Execution order (logical flow, not literal order):
  1) FROM (and JOINs, if any) to determine the source tables
  2) WHERE to filter rows before grouping
  3) GROUP BY to form groups
  4) HAVING to filter groups based on aggregates
  5) SELECT to determine output columns (including aggregates and any aliases)
  6) ORDER BY to arrange the final display
• Practical note on alias scope in MySQL:
  • Aliases defined in the SELECT clause can be used in GROUP BY and HAVING, but attempting to use them in WHERE will not work.
  • After grouping, you can refer to generated quantities by their aliases in the SELECT and in HAVING (and sometimes in GROUP BY as allowed by the dialect).

Real-World Connections and Best Practices

• Data readability and integrity:
  • Aliases and derived tables improve readability without changing data; they should be used to clarify complex queries.
• Performance considerations:
  • Avoid SELECT * in production queries; specify only needed columns to reduce I/O.
  • Use appropriate indexes on columns used in WHERE, GROUP BY, and JOIN conditions to improve performance.
• Design and ethics:
  • Understand that filters (WHERE, HAVING) do not “fix” data quality; they simply shape what is presented.
  • Be cautious when filtering on NULLs; missing data patterns may reflect data collection issues or business rules.
• Foundational principles:
  • SQL query execution mirrors a pipeline: filter rows, aggregate, and then project and order results.
  • Aggregates emphasize the importance of grouping and the distinction between row-level operations and group-level summaries.

Summary of Key Takeaways

• Aliases are temporary for readability, not data modification.
• The WHERE clause filters rows before aggregation; HAVING filters groups after aggregation.
• LIKE provides pattern matching with two wildcards: _ (single character) and % (any length).
• Datetime functions enable extraction and arithmetic on dates (DATE, DATEDIFF, YEAR, MONTH, CURRENT_DATE).
• NULL handling requires explicit checks (IS NULL / IS NOT NULL) and awareness of domain meaning.
• Aggregate functions (COUNT, SUM, MAX, MIN, AVG) collapse rows into per-group summaries, often used with GROUP BY.
• The typical execution order is FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY, with WHERE not able to use aggregates while HAVING operates on aggregates.
• In MySQL, SELECT aliases can be used in GROUP BY and HAVING but not in WHERE; this scope rule affects how you write queries and how you debug them.