Northwind sample database

Install the PostgreSQL-compatible version of the Northwind dataset on the YugabyteDB distributed SQL database.

You can install and use the Northwind sample database using:

• A local installation of YugabyteDB. To install YugabyteDB, refer to Quick Start.
• Using cloud shell or a client shell to connect to a cluster in YugabyteDB Aeon. Refer to Connect to clusters in YugabyteDB Aeon. To get started with YugabyteDB Aeon, refer to Quick Start.

In either case, you use the YugabyteDB SQL shell (ysqlsh) CLI to interact with YugabyteDB using YSQL.

About the Northwind sample database

The Northwind database is a sample database that was originally created by Microsoft and used as the basis for their tutorials in a variety of database products for decades. The Northwind database contains the sales data for a fictitious company called “Northwind Traders,” which imports and exports specialty foods from around the world. The Northwind database is an excellent tutorial schema for a small-business ERP, with customers, orders, inventory, purchasing, suppliers, shipping, employees, and single-entry accounting. The Northwind database has since been ported to a variety of non-Microsoft databases, including PostgreSQL.

The Northwind dataset includes sample data for the following.

• Suppliers: Suppliers and vendors of Northwind
• Customers: Customers who buy products from Northwind
• Employees: Employee details of Northwind traders
• Products: Product information
• Shippers: The details of the shippers who ship the products from the traders to the end-customers
• Orders and Order_Details: Sales Order transactions taking place between the customers & the company

The Northwind sample database includes 14 tables and the table relationships are showcased in the following entity relationship diagram.

Install the Northwind sample database

The Northwind SQL scripts reside in the share folder of your YugabyteDB or client shell installation. They can also be found in the sample directory of the YugabyteDB GitHub repository. The following files will be used for this exercise:

• northwind_ddl.sql — Creates tables and other database objects
• northwind_data.sql — Loads the sample data

Follow the steps here to install the Northwind sample database.

Open the YSQL shell

If you are using a local installation of YugabyteDB, run the ysqlsh command from the yugabyte root directory.

$ ./bin/ysqlsh

If you are connecting to YugabyteDB Aeon, open the ysqlsh cloud shell, or run the YSQL connection string for your cluster.

Create the Northwind database

To create the Northwind database, run the following CREATE DATABASE statement.

CREATE DATABASE northwind;

Confirm that you have the Northwind database by listing the databases on your cluster.

yugabyte=# \l

Connect to the Northwind database.

yugabyte=# \c northwind

Build the tables and objects

To build the tables and database objects, execute the northwind_ddl.sql SQL script.

northwind=# \i share/northwind_ddl.sql

You can verify that all 14 tables have been created by running the \d command.

northwind=# \d

                List of relations
 Schema |          Name          | Type  | Owner
--------+------------------------+-------+-------
 public | categories             | table | admin
 public | customer_customer_demo | table | admin
 public | customer_demographics  | table | admin
 public | customers              | table | admin
 public | employee_territories   | table | admin
 public | employees              | table | admin
 public | order_details          | table | admin
 public | orders                 | table | admin
 public | products               | table | admin
 public | region                 | table | admin
 public | shippers               | table | admin
 public | suppliers              | table | admin
 public | territories            | table | admin
 public | us_states              | table | admin
(14 rows)

Load the sample data

To load the northwind database with sample data, run the \i command to execute commands in the northwind_data.sql file.

northwind=# \i share/northwind_data.sql

To verify that you have some data to work with, you can run a simple SELECT statement to pull data from the customers table.

northwind=# SELECT * FROM customers LIMIT 2;

 customer_id |       company_name        | contact_name |    contact_title    |      address       |   city    | region | postal_code | country |     phone     |     fax
-------------+---------------------------+--------------+---------------------+--------------------+-----------+--------+-------------+---------+---------------+-------------
 FAMIA       | Familia Arquibaldo        | Aria Cruz    | Marketing Assistant | Rua Orós, 92       | Sao Paulo | SP     | 05442-030   | Brazil  | (11) 555-9857 |
 VINET       | Vins et alcools Chevalier | Paul Henriot | Accounting Manager  | 59 rue de l'Abbaye | Reims     |        | 51100       | France  | 26.47.15.10   | 26.47.15.11
(2 rows)

Explore the Northwind database

That’s it! You are now ready to start exploring the Northwind database and YugabyteDB features using the command line or your favorite PostgreSQL tool.