<aside> 💡 Composition

• Data Manipulation Language (DML)
• Data Definition Language (DDL)
• Data Control Language (DCL)

Also includes:

• View definition
• Integrity & Referential Constraints
• Transactions

Important: SQL is based on bags (duplicates) not sets (no duplicates).

• Different than Relational Algebra </aside>

1. Aggregations

Functions that return a single value from a bag of tuples:

→ AVG(col) → Return the average col value.

→ MIN(col) → Return minimum col value.

→ MAX(col) → Return maximum col value.

→ SUM(col) → Return sum of values in col.

→ COUNT(col) → Return # of values for col.

Can almost only be used in the SELECT output list

COUNT, SUM, AVG supports DISTINCT

2. Group By

Project tuples into subsets and calculate aggregates against each subset.

Non-aggregated values in SELECT output clause must appear in GROUP BY clause.

<aside> 💡 HAVING: Filters results based on aggregation computation.

Like a WHERE clause for a GROUP BY

</aside>

3. String / Date / Time Operations

• LIKE is used for string matching.
  • String-matching operators
    • '%' Matches any substring (including empty strings).
    • '_' Match any one character