Structured Query Language (SQL) - Formula Sheet

Relational Algebra Operations and Formulas

Relational algebra is a procedural query language used to model relational database operations. Each formula uses specific operators and operands to produce a new relation:

1. Selection (Row Filtering)

σ_c(R)

• σ (Sigma): The selection operator, representing horizontal row filtering.
• c: The conditional expression (selection criteria) evaluated on the attributes of the relation.
• R: The input relation (table) being queried.

2. Projection (Column Filtering)

Π_L(R)

• Π (Pi): The projection operator, representing vertical column filtering.
• L: The list of attributes (columns) to retain in the resulting table.
• R: The input relation (table) being projected.

3. Set Union

R ∪ S

• ∪: The union operator, which merges rows from both tables and removes duplicates.
• R, S: Union-compatible input relations (having the same number of attributes with matching data types in corresponding columns).

4. Set Difference

R − S

• −: The set difference operator, retaining rows unique to the first relation.
• R: The primary relation from which rows are selected.
• S: The secondary relation whose matching rows are excluded from the result.

5. Set Intersection

R ∩ S

• ∩: The intersection operator, which filters rows present in both tables.
• R, S: Union-compatible input relations.

6. Cartesian Product

R × S

• ×: The cross-product operator, combining all columns and creating every possible row combination.
• Degree of result: Degree(R) + Degree(S) (the total number of attributes is the sum of both tables' attribute counts).
• Cardinality of result: Cardinality(R) × Cardinality(S) (the total number of resulting rows is the product of both tables' row counts).

SQL Aggregate and Calculation Formulas

Aggregate functions operate on a collection of values from a column to return a single computed value:

1. Average (AVG) Formula

AVG(A) = (Sum of all non-null values of attribute A) / (Total count of non-null values of attribute A)

• AVG(A): The average value of attribute A across the filtered rows.
• A: The numeric attribute/column being averaged (null values are ignored).

2. Row Count Formula

COUNT(column_name)

Returns the total number of non-null values in the specified column, while COUNT(*) returns the total number of rows in the table (including null values).