Now that we have finished “normalizing” our fruits, we can write much better queries against the data.

We have two tables now to work with: ParseFruit and NameFruit. ParseFruit is our original table and we have a new table called NameFruit.

We could query these table using subqueries or joins.

In this next example, we’ll use a subquery. We want a list of Sally’s fruit. We use the results of the subquery to build the WHERE clause. We’ll query our new table NameFruit and show the list of fruits for Sally.

SELECT NameId, Fruit FROM
   NameFruit WHERE NameId = (SELECT Id FROM ParseFruit WHERE Name = 'Sally')

-- select everyone with a apple
SELECT ParseFruit.Name, NameFruit.Fruit
FROM ParseFruit INNER JOIN NameFruit 
ON ParseFruit.Id = NameFruit.NameId
WHERE NameFruit.Fruit = 'apple'

Here is another example.

-- what fruit does Sally have?
SELECT ParseFruit.Name, NameFruit.Fruit
FROM ParseFruit INNER JOIN NameFruit 
ON ParseFruit.Id = NameFruit.NameId
WHERE ParseFruit.Name = 'Sally'

Below is the result of the above query.

Here is another way to ask who has an apple. This way only uses our original table ParseFruit and our original columns FruitList and Name. We will “search” through the strings in ListFruit for the occurrence of “apple” by using LIKE. It sounds like it will work fine, but we get different results. We see Linda in the list. She does not have an apple. She has a pineapple, however.

SELECT ParseFruit.Name, ParseFruit.ListFruit
FROM ParseFruit
WHERE ListFruit LIKE '%apple%'

Here are two more example using a join.

-- list of people with 3 fruits (without using FruitFreq column)
SELECT ParseFruit.Name, COUNT(NameFruit.Fruit) AS 'Number of Fruits'
FROM ParseFruit INNER JOIN NameFruit 
ON ParseFruit.Id = NameFruit.NameId
GROUP BY ParseFruit.Name
HAVING COUNT(NameFruit.Fruit) = 3

-- list of people with 3 fruits (without using FruitFreq column)
SELECT ParseFruit.Id, ParseFruit.Name, COUNT(NameFruit.Fruit) AS 'Number of Fruits'
FROM ParseFruit INNER JOIN NameFruit 
ON ParseFruit.Id = NameFruit.NameId
GROUP BY ParseFruit.Id, ParseFruit.Name
HAVING COUNT(NameFruit.Fruit) = 3