Start learning →

Algebra, subqueries and views

The relational algebra under SQL, queries inside queries, CASE expressions, views and set operations.

About 8 hours of study

Learning objectives

Lessons

  1. 1

    Relational algebra

    The mathematics under SQL: select σ, project π, product ×, join ⋈, union ∪, difference − and rename ρ, each with the SQL that does the same.

  2. 2

    Subqueries and WITH

    A query inside a query: compare with a value worked out by another SELECT, test membership with IN and EXISTS, correlate a subquery with each row, and name steps with WITH.

  3. 3

    CASE, views and set operations

    Turn values into labels with CASE, save a query as a view, and combine query results with UNION, INTERSECT and EXCEPT.

Module practice

Module quiz

1. Which relational algebra operation picks rows?
2. Which SQL matches π city (Student) exactly?
3. What does a view store?
4. What makes a subquery correlated?
5. The list in NOT IN (...) contains a NULL. What does the query return?
6. Which keeps repeated rows when combining two results?
7. CASE WHEN marks >= 60 THEN 'pass' WHEN marks >= 80 THEN 'star' END. What does 90 give?

‹ Keys and joinsDatabase design ›