A couple students asked a question about Item 9 on the quiz: why is the answer 5, not 4? The tables in question were PETS:
+----------------------+----------+----------+ | owner | dog | numYears | +----------------------+----------+----------+ | Laura Ingalls Wilder | Jack | 5 | | Veronica Mars | Backup | 3 | | Robert Barone | Chamsky | 9 | | Laura Ingalls Wilder | Bandit | 2 | | Fourth Doctor | K9 | 4 | | Sarah Jane Smith | K9 | 8 | | Raj Koothrappali | Cinnamon | 5 | | Emily Elizabeth | Clifford | 6 | +----------------------+----------+----------+
and CHARACTERS:
+-----------------------------+----------------------+-------+ | show | character | debut | +-----------------------------+----------------------+-------+ | Little House on the Prairie | Laura Ingalls Wilder | 1974 | | Clifford the Big Red Dog | Emily Elizabeth | 2000 | | Doctor Who | Fourth Doctor | 1974 | | Doctor Who | Sarah Jane Smith | 1973 | | The Mandalorian | Greef Karga | 2019 | +-----------------------------+----------------------+-------+
The query in question was this:
select count(*) from pets join characters on owner=character;
The students asked, “why doesn’t this give you 4 rows, since table rows must be unique and therefore Laura Ingalls Wilder can’t appear twice?”
But realize that this query isn’t a “select owner” or “select distinct(owner)“; rather, it’s a “select *“. So therefore, you’re counting the entire number of rows returned by the join, not the number of distinct owner names (or any other attribute).
If you get rid of the count(*) and just use * to see the whole table of results, you get:
+----------------------+----------+----------+-----------------------------+----------------------+-------+ | owner | dog | numYears | show | character | debut | +----------------------+----------+----------+-----------------------------+----------------------+-------+ | Laura Ingalls Wilder | Jack | 5 | Little House on the Prairie | Laura Ingalls Wilder | 1974 | | Laura Ingalls Wilder | Bandit | 2 | Little House on the Prairie | Laura Ingalls Wilder | 1974 | | Fourth Doctor | K9 | 4 | Doctor Who | Fourth Doctor | 1974 | | Sarah Jane Smith | K9 | 8 | Doctor Who | Sarah Jane Smith | 1973 | | Emily Elizabeth | Clifford | 6 | Clifford the Big Red Dog | Emily Elizabeth | 2000 | +----------------------+----------+----------+-----------------------------+----------------------+-------+
As you can see: 5 rows, with no duplicate rows.
