The CARDINALITY function counts the number of elements that a collection contains. You can use the CARDINALITY function with simple or nested collections. Any duplicates in a collection are counted as individual elements. Figure 212 shows a query that returns, for every row in the manager table, department values and the number of elements in each direct_reports collection.
SELECT department, CARDINALITY(direct_reports) FROM manager
department marketing 5 department engineering 7 department publications 4 department accounting 3
You can also evaluate the number of elements in a collection from within a predicate expression, as Figure 214 shows.
SELECT department, CARDINALITY(direct_reports) FROM manager WHERE CARDINALITY(direct_reports) < 6 GROUP BY department
department accounting 3 department marketing 5 department publications 4