In my '
The Ice Cream Shop' post I provided a query to create a simple database system for storing ice cream flavors and ice cream brands. I then provided queries to perform the following searches on our database:
- Select all brands that sell a particular ice cream flavor
- Select all flavors offered by a particular brand
Now we want to expand our Ice Cream Shop database to allow for the following actions/features:
- Keep track of ice cream inventory. We want to know how much ice cream we have for each flavor and brand. We also want a run a report to tell us which ice cream's are low.
- Keep track of ice cream distributors. We want to be able to find distributors that sell specific flavors and brands of ice cream.
- Keep track of ice cream sales. We want to be able to keep track of which flavors and brands of ice cream were sold. We need to know the sale date, how many scoops were sold and what they sold for.
Like last time, I did the work of creating the tables and inserting the records (there was a lot of records in this one).
Download the IceCreamStore Revisited Database:
IceCreamStore Revisited for SQL Server 2008
IceCreamStore Revisited for SQL Server 2008 R2
Retrieving ice cream inventory that is low:
To do this, we will employ a few techniques: table aliasing, multiple inner joins and conditional where statements.
USE IceCreamStore;
GO
-- Retrieve the low ice cream inventory. Inventory is low when it is less than or equal to 1 tub.
SELECT F.Name AS 'Flavor', B.Name AS 'Brand', I.stock AS 'Inventory'
FROM Inventory AS I
INNER JOIN xBrandFlavorInventory AS XI
ON XI.inventoryID = I.inventoryID
INNER JOIN Flavors AS F
ON XI.flavorID = F.flavorID
INNER JOIN Brands AS B
ON XI.brandID = B.brandID
WHERE I.stock <= 1;
Our inner joins in the query above link the tables in this order:
What it is doing is linking the Inventory table to the xBrandFlavorInventory table which acts as the 'middle-man' to our Flavors and Brands table.
Retrieving distributors that sell an ice cream flavor/brand combination:
This query employs the following techniques: table aliasing, multiple inner joins and conditional where statements:
USE IceCreamStore;
GO
-- Search distributors for a flavor and brand combination.
SELECT D.Name AS 'Distributor'
FROM Distributors AS D
INNER JOIN xBrandFlavorDistributors AS XD
ON XD.distributorID = D.distributorID
INNER JOIN Flavors AS F
ON xd.flavorID = F.flavorID
INNER JOIN Brands AS B
ON xd.brandID = B.brandID
WHERE F.Name = 'Caramel Ice Cream Bars' AND B.Name = 'Tip-Top';
On line 13, in our WHERE statement, 'Caramel Ice Cream Bars' is our ice cream flavor and 'Tip-Top' is our brand name.
Gathering sales for a specific month:
This query employs the following techniques: table aliasing, multiple inner joins, conditional where statements and SQL Server Functions.
-- Get all the sales for a specific month.
SELECT F.Name AS 'Flavor', B.Name AS 'Brand', S.saleDate AS 'Sale Date', S.scoops AS 'Scroops', S.price AS 'Price'
FROM Sales AS S
INNER JOIN xBrandFlavorSales AS XS
ON XS.saleID = S.saleID
INNER JOIN Flavors AS F
ON XS.flavorID = F.flavorID
INNER JOIN Brands AS B
ON XS.brandID = B.brandID
WHERE DATEPART(MONTH, S.saleDate) = '03';
In the query above, specifically on line 10, our WHERE statement is using an SQL Server Function called DATEPART(). This function
pulls a
part of a
date form a date or datetime data type. We can then use the value returned by the DATEPART() function to do some conditional checking. In our case we are seeing if the MONTH is equal to '03', or the numeric equivalent to March.
