first, the traditional set operation and SQL statement implementation
the traditional set operations include four operations: and (∪), intersection (∩), Difference (-), Generalized Cartesian (X).
and Operations (Union)
the corresponding SQL statement:
SELECT * FROM R Union select * from S;
intersection operation (Intersect)
the corresponding SQL statement:
SELECT * FROM R Intersect select * from S;
difference operation (Except)
the corresponding SQL statement:
SELECT * from R Except select * from S;
Note: The relationship R and the relationship s have the same target N for both the intersection and the difference, and the corresponding attributes are taken from the same domain.
Cartesian product RxS operations (Extended Cartesian product)
the corresponding SQL statement:
Select * from R,s;
Ii. Specialized relational operations
The specialized relational operations consist of four operations i.e. selection (σ), projection (Π), connection (∞), and Division (÷),
Is the basis of relational database data maintenance, query, statistics and other operations.
Select (Selection)
With relation R, the operation of finding a tuple that satisfies a given condition F in relation R is called selection. Remember as Σf (R).
whichFis a conditional expression with a value of "true" or "false." Σf (R) is a tuple selected from the relationship R to make the conditional expression F true.
The corresponding SQL statement:
Selectr. Study number, R. course name, R. Score from RWHERE score >85
projection (Projection)
There is a relationship r, in the relationship R, a number of the specified attribute columns to form a new relationship is called a projection, recorded as Πa (R),
Where a is the list of column names for the property column you want to select.
The corresponding SQL statement:
If the relational expression is: Chat name, quantity (R)
SELECTName, quantityFrom R;
connection (join)
From the two generalized Cartesian product of the relationship R and S of the n,m purpose, the tuple that satisfies the given condition F is composed of a new relationship called R and S, which is recorded as R∞fs (F=aθb). Where A and B are the equal and comparable attribute columns on R and S, and θ is the arithmetic comparer (>,≥,<,≤,=,≠). That is, the connection operation is to select a set of tuples satisfying certain connection conditions from the Cartesian product of two relations, and the result of the connection is a (K1+K2) element relationship. The corresponding SQL statement:
Select * from R,s where r.a=s.c;
except (division)
Given the relationship R (x, y) and S (y) where x, Y is a set of properties (which can also be a single attribute), y in Y and s in R are attributes (sets) with the same name and can have different property names.
But must originate from the same domain set. When solving R÷s, group R by the value of X, and then examine each group, such as Y in a group that contains all Y in S, then take the value of x in that group as a tuple in the relationship p, otherwise it is not taken.
The quotient of r÷s equals the relationship p. Corresponds to the SQL language:
SELECT DISTINCT a.name from R as A WHERENot EXISTS
(SELECT * from S as B WHERENot EXISTS(SELECT * from R as C where C.name=a.name and b.city=c.city))
Groupbya. Name
Relational algebra SQL implementation
