Section 0: Module Objectives or Competencies
| Course Objective or Competency | Module Objectives or Competency |
|---|---|
| The student will learn how to implement the relational algebra operators using SQL. | The student will be able to implement and use the relational algebra operators Union, Intersect, Difference, Select, Project, Join, Product, and Divide using SQL. |
Section 1: Union
SELECT *
FROM S1
UNION
SELECT *
FROM S2;
-- or --
SELECT *
FROM (TABLE S1 UNION TABLE S2);
Section 2: Intersect
SELECT *
FROM S1
INTERSECT
SELECT *
FROM S2;
-- in MySQL --
SELECT *
FROM S1
WHERE S1.S# IN
(SELECT S2.S#
FROM S2);
Section 3: Difference
SELECT *
FROM S1
MINUS
SELECT *
FROM S2;
-- in MySQL --
SELECT *
FROM S1
WHERE S1.S# NOT IN
(SELECT S2.S#
FROM S2);
Section 4: Select
SELECT *;
FROM S1
WHERE SNAME = 'BROWN';
Section 5: Project
SELECT S#, SNAME
FROM S1;
Section 6: Join
SELECT *
FROM S1, S2
WHERE S1.S# = S2.S#;
-- or --
SELECT *
FROM S1 INNER JOIN S2
ON S1.S# = S2.S#;
Section 7: Product
SELECT *
FROM S1, S2;
Section 8: Divide
In order to divide in SQL, you need not only a dividend table (with two columns) and a divisor table (with one column), but you also need to set up a supplemental table (one column).
- The supplemental table contains a list of distinct values associated with the attribute that appears in the dividend table but not the divisor table.
- The solution will consist of the same attribute as the supplemental table, but will have a subset of values.
SELECT <supplemental_list> FROM <supplemental_table>
WHERE NOT EXISTS
(SELECT * FROM <divisor_table>
WHERE NOT EXISTS
(SELECT * FROM <dividend_table>
WHERE <dividend_table>.<divisor_list> = <divisor_table>.<divisor_list>
AND <dividend_table>.<supplemental_list> = <supplemental_table>.<supplemental_list>));
Given:
| Variable | Table or Attribute Name |
|---|---|
| supplemental_table | distributor |
| divisor_table | product |
| dividend_table | productDistributor |
| supplemental_list | vendor |
| divisor_list | ale |
Here is the query:
SELECT vendor FROM distributor
WHERE NOT EXISTS
(SELECT * FROM product
WHERE NOT EXISTS
(SELECT * FROM productDistributor
WHERE productDistributor.ale = product.ale
AND productDistributor.vendor = distributor.vendor));
Section 9: Set Operators
MySQL doesn’t support many of the set operators.
Here is a reference sheet that discusses the relational algebra commands.
This reference sheet may be more useful, as it discusses set operators in MySQL. Here is one more approach.