UoL CS Notes
Menu

SQL Queries - Misc.

COMP207 Lectures

Order of Execution

SQL queries have the following order of execution:

6. SELECT
1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. ORDER BY

Views

Views are saved queries that you can use as virtual tables.

Consider the following tables:

  • Employees

    birthday first_name family_name e_id
    1990-11-10 Anne Smith 1
    2000-02-05 David Jones 2
    1995-05-09 William Taylor 3

  • Transactions

    t_id c_id e_id
    1 3 1
    2 6 1
    3 19 3

Say we completed a complex query and we want to save it. We can add a line to the query for the table to be saved as a VIEW:

CREATE VIEW Employee_transaction_count AS
SELECT first_name, e_id, COUNT(t_id)
FROM Employees NATURAL JOIN Transactions
GROUP BY first_name, e_id;

This would save the following VIEW:

  • Employee_transaction_count

    first_name e_id COUNT(t_id)
    Anne 1 2
    William 3 1

Views as Virtual Tables

To query a VIEW you would do it like any other query:

SELECT first_name, COUNT(t_id)
FROM Employee_transaction_count;

This would create the following result:

first_name COUNT(t_id)
Anne 2
William 1

Restrictions of Views

  • Data in views can’t be modified.
    • Entries can’t be updated, deleted from or inserted to conventionally.
    • Triggers can do this but this is not covered in this module.

  • Views can be replaced with updated versions:

      CREATE OR REPLACE VIEW Employee_transaction_count AS
  SELECT first_name, COUNT(t_id)
  FROM Employees NATURAL JOIN Transactions
  GROUP BY first_name, e_id;

  • Views can be removed:

      DROP VIEW Employee_transaction_count;

Relational Algebra

This is mathematics over a relational set of tables. As it is in a table you use set semantics as there is no order and no duplicates.

This is very important for optimisation.

Projection ($\pi$)

$\pi_\text{attribute list}(R)=$ restricts $R$ to attributes in the attribute list.

As an example we can have the following expression:

\[\pi_\text{family_name,birthday} (\text{Employees})\]

This would produce the following output:

family_name birthday
Smith 1990-11-10
Jones 2000-02-05
Taylor 1995-05-09

This is the same as the following SQL query:

SELECT DISTINCT family_name, birthday
FROM Employees;

We are using DISTINCT due to the set semantics of the expression.

Renaming ($\rho$)

$\rho_{A1\rightarrow B1,A2\rightarrow B2,\ldots}=$ renames attribute $A1$ to $B1$, attribute $A2$ to $B2$ and so on.

As an example we can have the following expression:

\[\rho_\text{birthday}\rightarrow\text{bday}(\text{Employees})\]

This would give the following table:

bday first_name family_name
1990-11-19 Anne Smith
2000-02-05 David Jones
1995-05-09 William Taylor

This is the same as the following SQL query:

SELECT DISTINCT birthday AS bdday,first_name,family_name
FROM Employees;

Selection ($\sigma$)

$\sigma_\text{condition}(R) =$ set of all tuples in $R$ that satisfy the condition.

As an example we can have the following expression:

\[\sigma_\text{first_name='Anne'}(\text{Employees})\]

Which gives the following table:

birthday first_name family_name
1990-11-10 Anne Smith

This is the same as the following SQL query:

SELECT DISTINCT *
FROM Employees
WHERE first_name='Anne';

In SQL you should remember that you select using the WHERE keyword.{:.info}

Cartesian Product ($\times$)

$R_1\times R_2=$ pairs each tuple in $R_1$ with each tuple in $R_2$.

Consider the following tables:

  • Modules

    code year sem
    COMP105 1 1
    COMP201 2 1

  • Lecturers

    name module
    John COMP105
    Sebastian COMP201

An example relational algebra expression could be:

\[\text{Modules}\times\text{Lecturers}\]

Which gives the following table:

code year sem name module
COMP105 1 1 John COMP105
COMP105 1 1 Sebastian COMP201
COMP201 2 1 John COMP105
COMP201 2 1 Sebastian COMP201

This is the same as the following query:

SELECT DISTINCT *
FROM Modules, Lecturers;

Natural Join ($\bowtie$)

$R_1\bowtie R_2=$ pairs of each tuple in $R_1$ with each tuple in $R_2$ with matching common attributes.

As an example we could have the following expression:

\[\text{Employees}\bowtie\text{Transactions}\]

This would give the following table:

birthday first_name family_name e_id t_id c_id
1990-11-10 Anne Smith 1 1 3
1990-11-10 Anne Smith 1 2 6
1995-05-09 William Taylor 3 3 19

This is the same as the following SQL query:

SELECT DISTINCT *
FROM Employees NATURAL JOIN
Transactions;

Additional Functions

  1. Left/right semi-join ($\ltimes/\rtimes)$ - Returns the rows from the left/right table that would be matched to a row on the other side in a natural join.
  2. GROUP BY - This function is part of relational algebra but is complex to define.

See SQL Queries (Optional Part) for additional examples of semi-join and GROUP BY.

Combined Example

With the following schema:

R(a,b)
S(c,d)
T(e,z)

Consider the following relational algebra example:

\[\rho_{a\rightarrow z}(R\times S)\bowtie T\]

You could write this in the following SQL query:

SELECT *
FROM (SELECT a AS z, b c, d
      FROM (SELECT *
            FROM R, S)) NATURAL JOIN T;