2.4. Exercises: The SQL Sequel

In the previous chapter’s exercises, we set up a database to store information about seeds. Let’s expand that idea now that we know all about foreign keys!

2.4.1. Create a New Schema

To get started, let’s set up a new schema in MySQL Workbench and call it garden. In the last chapter, we learned we can do this by clicking on New Schema, naming it garden, accepting the defaults, and clicking Apply.

2.4.2. Import New Tables

The starter data for these exercises is in the Github repo under lesson-15-exercises. To import the data in our .csv files, we need to create three empty tables. Once you have created your tables, you need to use the Table Import Wizard.

Note

The instructions state that you should be creating each table in separate query tabs. If it works better for you and your workflow, feel free to create the three tables in one query tab!

2.4.2.1. The plant Table

Our first table is plant. plant needs to have four columns:

  1. plant_id, which will be the primary key for our database.
  2. plant_name, which will contain the name of the plant.
  3. zone is an integer for the hardiness zone where our plant will grow best.
  4. season is a string that specifies the season when the plant blooms.

In a new query tab, use CREATE TABLE to make the plant table. The data for the plant table is in the plants.csv file. You can download it directly from Github.

2.4.2.2. The seeds Table

Our second table is seeds. seeds needs to have five columns:

  1. seed_id, which will auto increment.
  2. expiration_date, which will be of the DATE type.
  3. quantity is an integer for the number of seeds we have available.
  4. reorder of type BOOL and tells us if we need to reorder that type of seed.
  5. plant_id is the foreign key that connects seeds to plant.

In a new query tab, use CREATE TABLE to make the seeds table. The data for the seeds table is in the seeds.csv file. You can download it directly from Github.

2.4.2.3. The garden_bed Table

Our third table is garden_bed. garden_bed needs to have four columns:

  1. space_number, which will increment as we plant new plants.
  2. date_planted of type DATE for the day that we started the plant.
  3. doing_well of type BOOL and tells us if the plant is healthy and growing.
  4. plant_id is the foreign key that connects garden_bed to plant.

In a new query tab, use CREATE TABLE to make the garden_bed table. The data for the garden_bed table is in the garden.csv file. You can download it directly from Github.

2.4.3. Try Out Some Joins!

Before we jump in with our subqueries and more complex queries, let’s practice our joins from the previous chapter. After you write each query, look at the result set and reflect on why that information might be helpful. If you needed this database to manage your own garden, how would you use the result set?

2.4.3.1. Inner Join

Use an inner join on seeds and garden_bed to see which plants we have seeds for and are in our garden bed.

Check your solution

2.4.3.2. Left Join

Write a query that joins seeds and garden_bed with a left join to see all of the seeds we have and any matching plants in the garden bed.

2.4.3.3. Right Join

Write a query that joins seeds and garden_bed with a right join to see all the plants in the garden bed and any matching seeds we have.

Check your solution

2.4.3.4. Full Join

Write a query that joins seeds and garden_bed with a full join.

Note

In the previous chapter, we learned that MySQL does not support full joins. Review the section on full joins to see how this could be done!

2.4.4. Sub-Queries and Complex Queries

  1. When we were writing our joins, you may have noticed that the information that was most helpful to you (the plant_name) was missing from the result set! Write a query that gets the name of the plant by joining the plant table on the result set of the inner join query above. Hint: Open the query tab with the inner join query and copy it into a new query tab to start. Once you have your inner join setup in a new query tab, it will be easier to write your subquery.

    Check your solution

  2. Let’s say our plant table is so large that we have no idea what the plant_id for a hosta is. All we know is that there is definitely a row for hostas in the plant table. Write a query that will insert a new row into our seeds table. This new row needs to show that we received 100 hosta seeds that will expire on 08/05/2020 (so no need to reorder!) and for the plant_id, use a query inside the INSERT statement to get the appropriate ID for hostas. Hint: In order to get the plant_id of a hosta, you can use the following query in one line SELECT plant_id FROM plant WHERE (plant_name LIKE 'Hosta') inside the VALUES of the INSERT statement.

2.4.5. Bonus Missions

  1. Revisit your query with a full join and try using UNION ALL as opposed to UNION. How does the result set differ?
  2. Now that we can get the plant_name of plants that we have seeds for and are in the garden bed, try using COUNT() to see how many plants are in both places.