The Ice Cream Shop

Today we are going to create a database that can be used to store ice cream brands and ice cream flavors. The database should be extensible enough to allow users to search for ice cream by flavor or by the brand. When they search for flavors, it should show all the brands that offer that flavor. And if they search by brand, the system should return all flavors the brand offers.

To simplify things, I have already created the tables and the data inside of them. The queries to accomplish our tasks will be at the bottom of this post.

Download the IceCreamStore Database:

---

These files are scripts you can run to create the database and insert the records.

IceCreamStore for SQL Server 2002

IceCreamStore for SQL Server 2005

IceCreamStore for SQL Server 2008

IceCreamStore for SQL Server 2008 R2

---

Selecting all the flavors for a specific brand:

To select all the flavors from a specific brand, we will use two techniques: inner joins and a cross reference table. A cross reference table is a table that exists 'between' two tables that creates a link for those tables. It usually holds the primary keys of the original two tables to create a relation between those tables.

To select all flavors for a specific brand:

1. USE IceCreamStore;
2. GO
4. -- Select all flavors from a specific brand.
5. SELECT F.Name AS 'Flavor Name'
6. FROM dbo.Flavors AS F
7. INNER JOIN dbo.xBrandsFlavors AS X
8. ON F.flavorID = X.flavorID
9. INNER JOIN dbo.Brands AS B
10. ON X.brandID = B.brandID
11. WHERE B.Name = 'Amul';

In the above query, "Amul" is the name of the brand we want to search for. The query returns all the ice cream flavors that a brand offers.

Selecting all brands that sell a particular flavor:
This query uses the same techniques as selecting the flavors for a specific brand, except it does it in reverse.

To select all brands for a particular flavor:

1. USE IceCreamStore;
2. GO
4. -- Select all brands that sell a specific flavor.
5. SELECT B.Name AS 'Brand Name'
6. FROM dbo.Brands AS B
7. INNER JOIN dbo.xBrandsFlavors AS X
8. ON B.brandID = X.brandID
9. INNER JOIN dbo.Flavors AS F
10. ON X.flavorID = F.flavorID
11. WHERE F.Name = 'Vanilla Ice Cream';

In the above query "Vanilla Ice Cream" is the ice cream flavor we want to search for. The query returns all the brands that offer that ice cream flavor.