Northwind

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

Accessing the Database

Assuming that Northwind.mdb is already known to the ODBC control panel, you can just load the Database Access Kit application package into Mathematica, open the database, and assign a name 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 database.

DataSourceDataTypes[dbselect]

Getting Basic Information

By leaving out DataSourceEvaluate, you can verify how Mathematica assembles the SQL command from Mathematica functions.

SQLSelect[SQLTable["Products"]]

The next command uses this SQLSelect function to read in the first product.

DataSourceEvaluate[dbselect,  SQLSelect[SQLTable["Products"]],  
        ShowColumnHeadings  ->  True][[2]]

Let's see which products are less than 8 dollars.

lowcostproducts = 
      DataSourceEvaluate[dbselect,  
          SQLSelect[
            SQLTable["products"], {SQLColumn["ProductName"],  
              SQLColumn["UnitPrice"]},  
            0  <  SQLColumn["UnitPrice"]  <  8], 
          ShowColumnHeadings -> True] // TableForm

This result is identical to the one obtained by passing the SQL commands directly.

DataSourceEvaluate[
        dbselect, "SELECT ALL ProductName, UnitPrice FROM products WHERE \
(UnitPrice BETWEEN 0 and 8)"] // TableForm

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

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

Load the package Graphics`Graphics`. Now you can create a bar chart of the number of items at hand.

<< Graphics`Graphics`

BarChart[
        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

The next command queries where the company has been shipping products.

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

Load the WorldPlot` package to display the result graphically.

<< Miscellaneous`WorldPlot`

Color the countries that Northwind ships to in red,  other countries in light gray.

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

WorldPlot[{World, shadefunc}];