CBAD 393 Practice 1

Author

Dr. Mitchell Church

Exploring Northwind

The Northwind database is a classic, relational database schema originally created by Microsoft to serve as a tutorial and demonstration blueprint for its database products, most notably MS Access and SQL Server. It contains the operational data of “Northwind Traders,” a mythical international specialty foods import and export company. Because it mimics the real-world complexities of a small-to-medium business enterprise, it has become an industry-standard sandbox for students, developers, and data analysts to learn relational database design, practice writing SQL queries, and test database administration techniques.

Northwind captures comprehensive data on products, categories, suppliers, customers, employees, and shippers, all unified through a central sales order pipeline. It is an ideal environment for masteringconcepts like multi-table joins, aggregations, and database normalization.

Why learn how to query databases like Northwind?

For an administrator or manager in any corporate field, data is key to understanding the organization. Learning how to write queries against a system like Northwind is highly valuable for several key reasons:

  • Speed and Autonomy: Querying operational data directly allows managers to answer urgent business questions without waiting days for a specialized data analyst to build a report.

  • Financial and Inventory Forensics: Modern business operations rely tightly on accurate tracking of supplier costs, customer orders, and shipping timelines. If inventory is mismanaged or sales margins are calculated incorrectly, a company loses revenue. Querying a database like Northwind teaches you how to conduct internal data forensics—verifying that the order pipeline matches final billing and inventory outputs.

  • Proactive Strategy and Dashboards: Relying on pre-packaged PDF reports limits you to only looking backward. By mastering basic SQL and connecting it to visualization tools (like ggplot or PowerBI), a manager gains the power to design and interpret their own real-time supply chain and sales operations dashboards.

Ultimately, knowing how to query Northwind bridges the gap between raw transactional data and high-level strategy. Knowing how to find your way around a resource like this transforms you from a passive consumer of reports into a data-driven leader.

Database Connection

First, we load some required packages and establish a connection to the Northwind SQLite database. Once this chunk is run, RStudio’s “Connections” pane will index the tables, making the database much easier to work with.

library(tidyverse)
library(DBI)
library(RSQLite)

# Connect to the database
mydb <- dbConnect(RSQLite::SQLite(), "../northwind.db")

Database Exploration

This practice is mostly about learning how to explore a SQL database.

You can view the database schema for Northwind by clicking the northwind_schema.png image located in your files menu in the lower right of the screen.

Tip

Watch the accompanying video on Moodle so that you understand what you are seeing when looking at this picture.

##In addition to the schema, these next commands are also useful to find your way around.
#Use this command to view tables in a database
dbListTables(mydb)
 [1] "Categories"          "Customers"           "EmployeeTerritories"
 [4] "Employees"           "Order Details"       "Orders"             
 [7] "Products"            "Regions"             "Shippers"           
[10] "Suppliers"           "Territories"         "sqlite_sequence"    
##Use this command to view fields in a table, for example products.
dbListFields(mydb, "products")
 [1] "ProductID"       "ProductName"     "SupplierID"      "CategoryID"     
 [5] "QuantityPerUnit" "UnitPrice"       "UnitsInStock"    "UnitsOnOrder"   
 [9] "ReorderLevel"    "Discontinued"

Key SQL Concepts for this Practice

SQL Clauses to Know

This first homework focuses on SELECT, FROM and WHERE commands in SQL.

  • SELECT is used to pull “fields” out of the database and display them.
  • FROM tells SQL which database table to pull the fields from.
  • WHERE allows you to filter the results based on a variety of conditions.

Here is our first full SQL query. It uses SELECT and FROM to pull LastNames from the Employees table. In this example, “LastName” is the field and Employees is the table. I knew the names of these variables from using the schema and the database exploration commands above. You NEVER need to guess about a variable name in a database.

-- This is a specific "SQL" block. It is designed to make working with SQL easy.
-- That is why the keywords are colored. You can hit "tab" inside this block to auto-complete a variable name.

-- Here is our first full SQL query. LIMIT 5 makes it return just the first 5 results.

SELECT LastName 
FROM Employees
LIMIT 5
5 records
LastName
Davolio
Fuller
Leverling
Peacock
Buchanan

Here’s another one that SELECTS three fields, LastName, FirstName and the person to whom they report. When using SELECT, fields are always separated by commas. You can also put as much blank space as you want in your queries to make them easier to read. I’ve done this below, splitting up SELECT and FROM.

SELECT LastName, FirstName, ReportsTo 
FROM Employees
LIMIT 5;
5 records
LastName FirstName ReportsTo
Davolio Nancy 2
Fuller Andrew NA
Leverling Janet 2
Peacock Margaret 2
Buchanan Steven 2

Adding the WHERE to filter results

Now we just need to add WHERE. We will learn different types of filters gradually. Here are some examples. Run them and see what they do. Play around with the examples A bit until you thoroughly understand them.

-- Using WHERE to select text that matches exactly with "="
SELECT ProductName
FROM Products
WHERE ProductName = "Longlife Tofu"
1 records
ProductName
Longlife Tofu 
-- With BETWEEN to select a range of numeric data
SELECT OrderId, UnitPrice, Quantity
FROM "Order Details"
WHERE OrderId BETWEEN 10000 AND 20000
LIMIT 5
5 records
OrderID UnitPrice Quantity
10248 14.0 12
10248 9.8 10
10248 34.8 5
10249 18.6 9
10249 42.4 40 
-- Using LIKE to match a partial term. This matches all IDs that begin with 21.
SELECT OrderId, UnitPrice, Quantity
FROM "Order Details"
WHERE OrderId LIKE "21%"
LIMIT 5
5 records
OrderID UnitPrice Quantity
21000 19 24
21000 39 3
21000 36 9
21000 97 38
21000 38 27 
-- Selecting two specific values with "IN".
SELECT ContactName, Country 
FROM Customers
WHERE Country IN ("Canada", "Mexico")
8 records
ContactName Country
Ana Trujillo Mexico
Antonio Moreno Mexico
Elizabeth Lincoln Canada
Francisco Chang Mexico
Yoshi Tannamuri Canada
Jean Fresnière Canada
Guillermo Fernández Mexico
Miguel Angel Paolino Mexico

Introduction to GGPLOT

Pulling the data from the database is interesting, but the real power of Posit cloud and other similar software like Python etc. Comes from being able to then graph and visualize our results, all within the same script.

To do this, we will use a very powerful package called ggplot. ggplot2 is a powerful and flexible R package for creating a wide range of static and dynamic graphics. It follows a grammar of graphics approach, meaning it combines independent components like data, aesthetics, and geometric objects to build complex visualizations.

Note

Basic Structure of a ggplot A typical ggplot consists of the following layers: ggplot(): Creates the base of the plot, specifying the data to be used. Aesthetic mappings (aes()): Determine how variables in the data are mapped to visual properties (x, y, color, size, etc.).

Geometries (geom_): Specify the type of graphical object to use

ggplot example

Are there more women or men in this database? We will plot gender on the “x-axis”. Two things are important to note here. The first is that the data for the plot comes from an object we will save using a SQL block. If you want to change the plot, you need to change this query in the SQL block and run that block again.

-- If you look at the code file, you'll notice this block has an output.var option, which saves the data to a dataframe object called myquery
-- you can click on myquery in your environment window (on the right side of the screen) to see what data it contains.
SELECT Country 
FROM Suppliers

Now that we have our data, we use an R code block and ggplot to graph it. Your variable names that you use MUST MATCH your data. We are plotting Country, not “country”, for example. Also, you can’t plot any variables that aren’t in your data so make sure your query works correctly and you have all the data you need before you start graphing.

ggplot is not some obscure tool. It is extremely popular in data analysis fields. You can google or ChatGPT any part of this code and get tons of examples and video explanations.

# Notice our data is myquery, and we put Country on the x axis. Try changing the aes so that country is on the x axis (x = Country) and you'll see why I chose to put it on y.
ggplot(data = myquery,
       aes(y = Country)) +
  geom_bar()

Now that you’ve had a chance to read about these concepts, open your 393_practice1.qmd file to start practicing on your own.