Once we have created tables and loaded them with data, we need to retrieve this data. This website introduces the fundamental retrieval statements from two languages, SQL and Relational Algebra. Show
Retrieving data from one tableRetrieval with SQLIn SQL, to retrieve data stored in our tables, we use the SELECT statement. The result of this statement is always in the form of a table that we can view with our database client software or use with programming languages to build dynamic web pages or desktop applications. While the result may look like a table, it is not stored in the database like the named tables are. The result of a SELECT statement can also be used as part of another statement. Basic syntax of |
Tom | Jewett | 714-555-1212 | 10200 Slater | 92708 |
Alvaro | Monge | 562-333-4141 | 2145 Main | 90840 |
Wayne | Dick | 562-777-3030 | 1250 Bellflower | 90840 |
WHERE
clause using boolean expressions. Our first statement is thus refined as shown in the figure below. SELECT *
FROM customers
WHERE cZipCode = '90840';
Customers in zip code 90840Alvaro | Monge | 562-333-4141 | 2145 Main | 90840 |
Wayne | Dick | 562-777-3030 | 1250 Bellflower | 90840 |
'90840'
. While not illustrated in this example and unlike SQL keywords, literal strings and strings stored in the database are case sensitive; thus, 'Long Beach'
is a different string than 'long beach'
. SELECT
clause, each column name separated by a comma. The figure below shows this refinement and its corresponding result set. SELECT cLastName, cFirstName, cPhone
FROM customers
WHERE cZipCode = '90840';
Columns from SELECTMonge | Alvaro | 562-333-4141 |
Dick | Wayne | 562-777-3030 |
ORDER BY
clause to specify the order in which to retrieve the results. Once again, this ordering does not change the meaning of the results; the result set does not change, all it changes is the order in which the rows are displayed. This final refinement and its result are shown below. SELECT cLastName, cFirstName, cPhone
FROM customers
WHERE cZipCode = '90840'
ORDER BY cLastName ASC, cFirstName ASC;
Rows in orderDick | Wayne | 562-777-3030 |
Monge | Alvaro | 562-333-4141 |
ASC
is used to order the rows in ascending values, which is the default ordering so the keyword is not necessary and is shown here for completeness. To order rows in descending values, use the keyword DESC
. In the statement above, rows are first ordered in ascending value of the last name and in case of ties (two or more customers with the same name), then the rows are
ordered in ascending value of the first name. Retrieval with relational algebra
SQL is a declarative language. As such, SQL is used to declare what is to be retrieved from the database. In our SQL statement above, we did not specify how to retrieve the result. In an imperative language, we do specify the steps to take to solve a problem, such as how to retrieve a result from a database. Thus, it is the responsibility of the database system to determine how to retrieve what is declared in SQL. In relational database systems, this is commonly done by translating SQL into Relational Algebra.
Like all algebras, RA applies operators to operands to produce results of the same type as the operands. RA operands are relations and thus the results are also relations. Furthermore, like all algebras, the results of operators can be used as operands in building more complex expressions. We introduce two of the RA operators following the example and refinements above for SQL.
RA operators: σ and π
To retrieve a single relation in RA, we only need to use its name. The common notation in the relational model is to use uppercase letters for relation scheme (R, S, T, U, etc) and lowercase letters for relations (r, s, t, u, etc). Thus, the simplest RA expression is to retrieve all columns and every row of a relation is just the name of the relation: r
The two RA operators introduced here are σ, the select operator, and π, the project operator.
- The select (RA) operator specified by the symbol σ picks tuples that satisfy a predicate; thus, serving a similar purpose as the SQL
WHERE
clause. This RA select operator σ is unary taking a single relation or RA expression as its operand. The predicate, θ, to specify which tuples are required is written as a subscript of the operator, giving the syntax ofσθe
, where e is a RA expression.The scheme of the result of
σθr
is R—the same scheme we started with—since the entire tuple is selected, as long as the tuple satisfies the predicate. The result of this operation includes all tuples of relation r that satisfy the predicate θ—that is, θ evaluates to true. - The project (RA) operator specified by the symbol π picks attributes, confusingly like the SQL
SELECT
clause. It is also a unary operator that takes a single relation or expression as its operand and the attributes to retrieve are specified as a a subscheme, X (subset of its operand). The syntax isπXe
where, as before, e is a RA expression. Following are additional properties of the project operator.- For X to be a subscheme of R, it must be a subset of the attributes in R that preserves the assignment rule from R (that is, each attribute of X must have the same domain as its corresponding attribute in R).
- The scheme of the result of πXr is X. The tuples resulting from this operation are tuples of the original relation, r, but cut down to the attributes contained in X.
- If X is a super key of r, then there will be the same number of tuples in the
result as there were to begin with in r. If X is not a super key of r, then any duplicate (non-distinct) tuples are eliminated from the result, ensuring the result is always a set. This is unlike SQL where the result of a
SELECT
statement with aWHERE
clause is a multiset.
- As with other algebras, we can use function composition by applying the project operator to the
result of the select operator from the previous set to get:
πXσθr
RA Example: customers in a specified zip code
Given the above RA syntax, we can now use RA to create expressions that match the SQL statements from above which retrieve the customers who live in zip code 90840.
- The first step is to retrieve all customers. This is done by a RA expression that consists of just the name of the relation, thus the RA expression
customers
is the equivalent of the first SQL statement above. Its scheme is the same as the Customers scheme. - To retrieve the equivalent result set as the SQL statement in refinement #2, we apply the σ operator to the result set of our previous expression:
Again, the scheme of the result set is the same as the Customers scheme.σcZipCode='90840'customers
- Now, applying function composition here, we can retrieve just the columns we desire from the result set of the previous expression to get the RA expression that retrieves the equivalent result set as the SQL statement in refinement #3:
πcLastName, cFirstName, cPhone σcZipCode='90840'customers
- Note that, in RA the results of
expressions are strictly sets of tuples, thus, there is no way to specify the order of tuples in a result set. This is unlike SQL and its
ORDER BY
caluse.