Collections
In this book, we have assumed that a column contains only one value for each row. In this section, we introduce a new concept, the cell. A cell is the intersection of a column and a row. So far, we have assumed that a cell can contain only one value. Of course, it is possible to store multiple values in a cell, such as a complete address consisting of a street name, house number, postcode, and so on, separated by commas. We interpret this value as if it consists of several values. SQL, on the other hand, still considers this value as one atomic value.
However, this changes with the adoption of OO concepts into SQL. Now, you can store sets of values in a cell, and SQL will truly regard this set as a set, not as one atomic value. Such a set is called a collection. With a collection, you could, for example, record for one player any number of phone numbers in the column PHONES.
Figure. Define the PLAYERS table so that a set of phone numbers can be stored.
CREATE TABLE PLAYERS
(PLAYERNO INTEGER PRIMARY KEY,
: :
PHONES SETOF(CHAR(13)),
LEAGUENO CHAR(4))

Explanation: The term SETOF indicates that a set of value can be stored within the column PHONES. The table itself could look as follows. (Just as in the set theory, brackets are used to indicate a set.) It is obvious that some players have two and some even have three phone numbers.
PLAYERNO ... TOWN PHONES LEAGUENO
    
6 ... Stratford {070476537, 070478888} 8467
44 ... Inglewood {070368753} 1124
83 ... Stratford {070353548, 070235634, 079344757} 1608
2 ... Stratford {070237893, 020753756} 2411
27 ... Eltham {079234857} 2513
104 ... Eltham {079987571} 7060
7 ... Stratford {070347689} ?
57 ... Stratford {070473458} 6409
39 ... Stratford {070393435} ?
112 ... Plymouth {010548745, 010256756, 015357347} 1319
8 ... Inglewood {070458458} 2983
100 ... Stratford {070494593} 6524
28 ... Midhurst {071659599} ?
95 ... Douglas {070867564, 055358458} ?
Of course, the use of collections in tables affects the other SQL statements. Here are some examples of how data can be entered in this specific column and how it can be queried with the SELECT statement.
16. Add a new player with two phone numbers.
INSERT INTO PLAYERS (PLAYERNO, ... , PHONES, ...)
VALUES (213, ..., {'071475748', '071198937'}, ...)

Explanation: The brackets specify the set of phone numbers. Within the brackets, you are allowed to include zero, one, or more values. Zero can be used when this player has no phone.
17. Assign player 44 a new phone number.
UPDATE PLAYERS
SET PHONES = {'070658347'}
WHERE PLAYERNO = 44

18. Get the numbers of the players who can be reached at the phone number 070476537.
SELECT PLAYERNO
FROM PLAYERS
WHERE '070476537' IN (PHONES)
The result is:

Explanation In this SELECT statement, a new form of the IN operator is used. Usually, a list of literals or expressions (see Section 8.6) or a subquery (see Section 8.7) is specified after the IN operator. What is given between brackets represents a set of values for both forms. The same applies to this new form because the column PHONES also represents a set of values. This form of the IN operator can be used only for collections, not for other columns.
19. Get the numbers of the players who have more than two telephone numbers.
SELECT PLAYERNO
FROM PLAYERS
WHERE CARDINALITY(PHONES) > 2
The result is:

Explanation: To determine the number of values in a collection, the CARDINALITY function can be used. When the number of values is determined, the NULL values are not counted and duplicate values count as one.
The statement could have been defined as follows:
SELECT PLAYERNO
FROM PLAYERS
WHERE 2 < (SELECT COUNT(*) FROM TABLE(PLAYERS.PHONES))
Explanation: The statement looks like an ordinary statement, except that the FROM clause in the subquery contains a new construct: TABLE(PHONES). This construct transforms the set in a table (consisting of one column) into a number of rows. The number of rows is, of course, equal to the number of values in the collection. For each player, there will be another table.
The reason this more complex solution has been added is that it offers more features than that with the CARDINALITY function.
20. Get the numbers of the players with the largest set of phone numbers.
SELECT PLAYERNO
FROM PLAYERS
WHERE CARDINALITY(PHONES) >= ALL
(SELECT CARDINALITY(PHONES)
FROM PLAYERS)
The result is:

21. Find the numbers of the players who have the same set of phone numbers as player 6.
SELECT PLAYERNO
FROM PLAYERS
WHERE PHONES =
(SELECT PHONES
FROM PLAYERS
WHERE PLAYERNO = 6)

Explanation: The statement is obvious. You can also use > and < instead of the comparison operator =. If we would use the comparison operator >, the statement would answer the question: Who has at least the same telephone numbers as player 6? However, this person can have multiple phone numbers. Less than means: Who has at least one phone number that player 6 also has?
Figure. Get a list of all phone numbers from the PLAYERS table. The list should be presented in ascending order.
Unfortunately, this question is not as simple as it seems. The following statement, for example, is not correct. The column PHONES does not return one set of values that can be ordered but returns a set consisting of sets.
SELECT PHONES
FROM PLAYERS
ORDER BY 1
First, this column must be "flattened," as it is called.
SELECT PS.PHONES
FROM THE (SELECT PHONES
FROM PLAYERS) AS PS
ORDER BY 1

Explanation In Section 10.7, in Chapter 10, "SELECTStatement: TheGROUP BYClause," we stated that the FROM clause can contain a subquery. We will make use of that feature again, but now we put the word THE in front of it. The effect is that the result of the subquery, consisting of a set with sets, is transformed into one set consisting of atomic values. Thus, the set is flattened.
The result of the subquery itself can be represented as follows:
PHONES

{070476537, 070478888}
{070368753}
{070353548, 070235634, 079344757}
{070237893, 020753756}
{079234857}
{079987571}
{070347689}
{070473458}
{070393435}
{010548745, 010256756, 015357347}
{070458458}
{070494593}
{071659599}
{070867564, 055358458}
The result after THE operator has been used looks as follows:
PHONES

070476537
070478888
070368753
070353548
070235634
079344757
070237893
020753756
079234857
079987571
070347689
070473458
070393435
010548745
010256756
015357347
070458458
070494593
071659599
070867564
055358458
Now it is a "normal" table again, consisting of one column with a set of values. This result table is called PS in the FROM clause. We ask for this column in the SELECT statement, and the values are ordered in the ORDER BY clause.
Flattening of collections offers several possibilities, as illustrated in the following examples.
23. Get the number of phone numbers of players 6 and 44 all together.
SELECT COUNT(DISTINCT PS.PHONES)
FROM THE (SELECT PHONES
FROM PLAYERS
WHERE PLAYERNO IN (6, 44)) AS PS

Explanation: The subquery itself returns two sets of phone numbers: one for player 6 and one for player 44. The THE operator flattens the two sets to one set of rows, each consisting of one atomic value. With this operator, nonduplicate values are removed automatically. That is why we use DISTINCT in the COUNT function.
24. Get the phone numbers that player 6 and 44 have in common.
SELECT PS1.PHONES
FROM THE (SELECT PHONES
FROM PLAYERS
WHERE PLAYERNO = 6) PS1
INTERSECT
SELECT PS2.PHONES
FROM THE (SELECT PHONES
FROM PLAYERS
WHERE PLAYERNO = 44) PS2
In the previous examples, the collection is defined on a column with a base data type. Userdefined data types can be used as well. Likewise, userdefined data types can also make use of collections. The following are examples of both.

Figure. Define the PLAYERS table so that a set of phone numbers can be stored; use the PHONE data type.
CREATE TYPE PHONE AS
(AREA_CODE CHAR(3),
SUBSCRIBER_NO CHAR(6))
CREATE TABLE PLAYERS
(PLAYERNO INTEGER PRIMARY KEY,
: :
PHONES SETOF(PHONE),
LEAGUENO CHAR(4))

Figure. Define the PLAYERS table so that a set of phone numbers can be stored, but define the table in such a way that the set of values is defined with the PHONES data type.
CREATE TYPE PHONES AS
(PHONE SETOF(CHAR(13))
CREATE TABLE PLAYERS
(PLAYERNO INTEGER PRIMARY KEY,
: :
PHONES PHONES,
LEAGUENO CHAR(4))

