Northwind

Northwind is the example database that ships with Microsoft Access. It contains the business data for a fictional gogourmet food company with worldwide sales. In this example, I connect to the database, collect some summary information like inventory and take a look at the global distribution of customers.

Accessing the database

In this example, I assume that Northwind is already known to the ODBC control panel. I just load the Database Access application package into Mathematica, open the data base and assign a file handle to it.

Needs["DatabaseAccess`"]
dbselect  =  OpenDataSource["Northwind"]

DataSourceTables[] lists the available data tables in Northwind.

DataSourceTables[dbselect,  TableType -> "table"] // 
      TableForm

DataSourceDataTypes[] gives the types used in the data base.

DataSourceDataTypes[dbselect]

Getting basic information

Here you see how Mathematica assembles the SQL command from Mathematica functions.

SQLSelect[SQLTable["Products"]]

Next I read in the complete Products table.

products = 
      DataSourceEvaluate[dbselect,  SQLSelect[SQLTable["Products"]],  
          ShowColumnHeadings  ->  True] // TableForm

Let's see which ones are actually cheaper than 10 dollars.

lowcostproducts = 
      DataSourceEvaluate[dbselect,  
          SQLSelect[SQLTable["products"],  
            0  <  SQLColumn["UnitPrice"]  <  10]] // 
        TableForm

The following calculates the value of the complete inventory at sales prices.

totalinventory = 
      Plus @@ (DataSourceEvaluate[dbselect,  
              SQLSelect[SQLTable["products"], 
                SQLColumn["UnitPrice"] ]]*
            DataSourceEvaluate[dbselect,  
              SQLSelect[SQLTable["products"], 
                SQLColumn["UnitsInStock"] ]])

Here I create a bar chart of the number of items at hand.

BarChart[
      inventorybreakdown = 
        Take[Flatten[
            DataSourceEvaluate[dbselect,  
              SQLSelect[SQLTable["products"], 
                SQLColumn["UnitsInStock"] ]]], 10], 
      BarLabels -> (StyleForm[#, FontFamily -> "TimesRoman", 
                FontSize -> 9] &) /@ 
          Take[Flatten[
              DataSourceEvaluate[dbselect,  
                SQLSelect[SQLTable["products"], 
                  SQLColumn["ProductName"] ]]], 10], 
      BarOrientation -> Horizontal, PlotRange -> All]

World logistics

In the next line I look where the company has been shipping products.

shipcountries = 
      Union[Flatten[
          DataSourceEvaluate[dbselect,  
            SQLSelect[SQLTable["Orders"], 
              SQLColumn["ShipCountry"]]]]]

And use the WorldPlot` package to display the result graphically.

<< Miscellaneous`WorldPlot`

shadefunc[country_] :=
   If[MemberQ[shipcountries, country], RGBColor[1,0,0],GrayLevel[0.6] ]

The color function causescountries Northwind ships into red,  other countries in light gray.

WorldPlot[{World, shadefunc}];