Difference list vs INTERSECT across multiple tables

Question

For a given database data structure:

Table      Attribute       Type                       Glossary

Species    Sp_name         C(10) P.K.                 Species name
           sp_woodtype     C(10)                      Wood Yielded by tree
           sp_maxht        I                          Max.height


Forest     Fo_name         C(10) P.K.                 Forest name
           Fo_size         I                          Forest area
           Fo loc          C(10)                      Geographical name
           Fo_comp         C(10)                      Forest owner


Tree       Tr_species      C(10) F.K. species.sp_name
           Tr_forest       C(10) F.K. forest.fo_name
           Tr_numb         I     P.K.                 Sequence number
           Tr_planted      Date                       Date of planting
           Tr_loc          C(10)                      Forest quadrant
           Tr_parent       I     F.K. tree.tr_numb    Procreating tree reference


Measure    Me_trnumb       I     F.K. tree.tr_numb
           Me_numb         I     P.K.                 Sequence number
           Me_result       I                          Test's measure
           Me_date         Date                       Measure taken on 
           Me_type         C(10)                      Type of measure

P.K. is primary key, F.K. is foreign key, C(N) Character(N) type, I Integer type

I need to select which species of trees are found in all forests, so I have tried the following, but it seems wrong:

SELECT fo_name.forest, sp_name.species
FROM forest, species;

SELECT tr_species.tree, tr_forest.tree
FROM tree;

SELECT fo_name.forest, sp_name.species
FROM forest, species
INTERSECT
SELECT tr_species.tree, tr_forest.tree
FROM tree;

Is a difference list a better solution than an INTERSECT to solve this problem?

If this is an exam question, you should solve it on your own. In either case, `INTERSECT` is not the solution. As I understand the question, you should provide a list of species, each of which are present in all forests. So, if you have 10 records in table "forest" then find those "tr_species" in table "tree" that are present in 10 different forests. Apply a suitable aggregate function on "tree", then `JOIN` to "species". Over to you. — Patrick, Jun 11 '15 at 00:02
it is an exam question of 3 years ago. in case you missed it i said revision — albiero, Jun 11 '15 at 00:03
what did you not understand from this question? there is the database and the question was Which species of trees are found in all forests? — albiero, Jun 11 '15 at 14:35
The close reason seems incorrect. It's pretty clear to me what is being asked. — Erwin Brandstetter, Jun 11 '15 at 14:57
It was closed on a knee-jerk response as homework and since it was quite broad rather than focused on a specific technical aspect. I've edited to focus it on a technical aspect and cast a reopen vote. Good luck for the exam albiero. — StuperUser, Jun 11 '15 at 16:12

score 1 · Accepted Answer · edited May 23 '17 at 12:30

It's a special case of relational division.
You can compare the count of distinct forests per tree with the total count of forests to find out:

SELECT tr_species
FROM   tree
GROUP  BY tr_species
HAVING count(DISTINCT tr_forest) = (SELECT count(*) FROM forest);

Join the result to the table species if you need more than the PK.

BTW, the data type character(10) is no good, especially not as PK column.

Any downsides of using data type "text" for storing strings?

Difference list vs INTERSECT across multiple tables

1 Answers1