The select operator is used to retrieve particular rows, or tuples, from a relation that satisfy some test. For example, let’s say you wanted the complete records for every student majoring in computer science. This could be accomplished with the following query:
Select from Students where Major = “CS”
The relation resulting from this query is presented in . Notice that this new relation contains the same attributes as its parent relation, “Students”. However, only the tuples that satisfied the condition Major = “CS” are reproduced.
In this text, select queries will be specified using the following general format:
Select from relation where attributeoperatorvalue
The fields “relation” and “attribute” stand for the names of actual relations and attributes. The field “value” will be replaced by either a number or string of characters; depending on the type of data stored in the named attribute.
The “operator” field will be replaced by a comparison operator, such as:
The last three fields of the select command (attribute operator value) form a condition test. This test will be either unambiguously true or false for each tuple of the relation being queried. Those tuples for which the condition is true are copied to the resulting answer relation. Tuples for which the condition is false are not copied.
Results of Select from Students where Major = “CS”
When comparing a number (numeric value) to an attribute like “age” that also contains numbers, the comparison operators work in the expected way. For example, 15 < 17 is true, while 28 > 28 is false. Comparisons involving character strings are made relative to lexicographic, or dictionary, order. Hence, “A” comes before “B”, which comes before “C”, etc. Under this scheme, “<” is interpreted to mean “precedes” and “>” means “follows”, while “=” means “the same as”. So, “A” < “B” is true, as would be “A” < “Apple”. The expression “B” > “C” would be false.
While this approach for handling character strings certainly makes sense, it sometimes takes a bit of getting used to. For example, given a relation that contains grade information represented as “A”, “B”, “C”, etc., the condition to select all grades higher than “C” would be
Grades < “C”
not Grades > “C” due to the fact that comparisons are done using lexicographic order where “A” and “B” precede “C”.
One final point about “select”, which really applies to all relational operators. Since “select” produces a relation, it is often useful to give this relation a name so that it can be referred to elsewhere. The format used in this book for doing so is:
Relation ← relational_query
For example, the relational expression:
Louisiana_Students ← Select from Students where State = “LA”
creates a table named “Louisiana_Students” of all students who reside in Louisiana. The Louisiana_Students relation could be used in other queries, such as the following, which generates a table of male students who live in Louisiana:
Male_Louisiana_Students ← Select from Louisiana_Students where Sex = “M”
In summary, the “select” operator creates a new relation that consists of selected rows, or tuples, of an existing relation. The rows are selected based on the outcome of a condition test. Remember to use the “select” operator when you are interested in retrieving “complete records” about a subset of the entries stored in a relation.
Exercises for
Using the relations presented in and , develop relational queries to solve each of the following problems.
Generate a relation, based on the Students relation of , that contains entries for all students under age 21.
Generate a relation, based on the Faculty relation of , that contains entries for all faculty members in the Math department.
Generate a relation, based on the Students relation of , that contains entries for all female CS students.
Generate a relation, based on the Students relation of , that contains entries for all male CS math majors who are between the ages of 19 and 23, inclusive.