Cart

Chinook Database Sample

Table of Contents

All our products include SQL scripts and database files for the Chinook sample, for all supported database types. They are installed by default in the C:/Program Files (x86)/XtractPro/<app>/samples setup folder, and replicated to your C:/Users/<username>/AppData/Local/Xtractor/samples user directory.

Chinook is a medium-size database typical to small businesses. It’s great for data modeling and many types of queries, including real-time analytics with data visualizations.

The open-source project was originally created by Luis Rocha, with scripts for SQL Server, SQL Server Compact, Oracle, MySQL, PostgreSQL, SQLite, and DB2, using real data from an iTunes library. According to the copyright requirements, a license note is including in any Chinook script coming from this shared work.

Chinook ER Data Models

This Structural Model is automatically generated and open on screen once you connect to a Chinook sample database, from Data Xtractor or Model Xtractor. There are several other ER model diagram automatically created as well.

MySQL-Chinook-Data-Model

The database keeps an inventory and tracks the sales from a small music store.

  • inventory – the database keeps information on albums produced by artists. Albums have tracks, grouped by genre or media type. Playlists can also group several tracks together.
  • billing – the store’s employees sell tracks to customers. Transactions are recorded in the invoice and invoice line tables.

Chinook Queries and Charts

In Data Xtractor, Visual Xtractor and Query Xtractor, in the Queries – Demo Queries folder, click on add demo queries. A popup opens up with all sorts of demo queries that can be automatically generated for you. We’ll be concerned here only with the Chinook Tutorial set, and we will cover the rest at the Employees sample. When Chinook is recognized as current database connection, by its table names, we have dozens of internal demos that can be specifically generated for this samples, in any supported database kind.

Here is the Annual Sales of Selected Countries demo query, generated under the Queries > Demo Queries > Chinook Tutorial > Crosstab Pivot Queries folder, in results mode:

Annual Sales Demo Query

And these are the two generated SQL queries in SQLite: first is an emulated PIVOT query, second was used internally to get the column header values for the cross-table.

-- Stacked bar chart on pivot query with total annual sales in three selected countries.

SELECT CAST(strftime('%Y', invoice_."InvoiceDate") AS BIGINT) AS "Year",
   SUM(CASE WHEN invoice_."BillingCountry" = 'USA'
      THEN invoice_."Total" END) AS "USA",
   SUM(CASE WHEN invoice_."BillingCountry" = 'United Kingdom'
      THEN invoice_."Total" END) AS "United Kingdom",
   SUM(CASE WHEN invoice_."BillingCountry" = 'Canada'
      THEN invoice_."Total" END) AS "Canada"
FROM "Invoice" AS invoice_
WHERE invoice_."BillingCountry" IN ('USA', 'United Kingdom', 'Canada')
GROUP BY 1
ORDER BY 1 DESC

-- Internal query used to get the pivot values:

SELECT invoice_."BillingCountry" AS "Country"
FROM "Invoice" AS invoice_
WHERE invoice_."BillingCountry" IN ('USA', 'United Kingdom', 'Canada')
GROUP BY 1
ORDER BY 1 DESC

-- 3 pivot values: USA, United Kingdom, Canada

How to Install the Chinook Database

For any supported database type, here are short instructions on how you can use a free third-party application, to install and connect to a Chinook sample database from our applications. Each database type has a link to the instructions for a general connection from our apps.

  • SQLite – Chinook – SQLite.db is a ready-to-use database file created from the Chinook – SQLite.sql script. This sample may be connected by default in a new project.
  • Firebird – Chinook – Firebird.fdb is a ready-to-use database file created from the Chinook – Firebird.sql script. Use localhost and default port 3050. Append Chartset=UTF8; to Other Properties, as this is a UTF-8 database. Connect with admin’s sysdba username, and masterkey password. This sample may be connected by default in a new project.
  • Microsoft SQL Server CE – there is a Chinook – Microsoft SQL Server CE.sdf ready-to-use database file, created from the Chinook  – Microsoft SQL Server CE.sql script. Just open the SDF file directly, with nothing else. No password required. As access to SQL CE is always free, we may have this database sample installed by default when you create a new empty project.
  • Microsoft Access – there is a Chinook – Microsoft Access.accdb ready-to-use MS Access file in the samples directory. Connect from Access or any Xtractor app, with no password. No script either, but the database has been created with the similar Chinook structure and data.
  • Snowflake – load and run the Chinook – Snowflake.sql script from the Snowflake web UI. This will create a Chinook database with tables and other objects, and populate the small tables. From the UI, manually load four CSV files for the large tables, in this order: Track, Invoice, InvoiceLine, PlaylistTrack. For the CSV format, skip the first line and optionally surround text fields bu double quotes. Connect with either a username-password, SSO, OAuth or Key-Pair. Use the Chinook database with the default PUBLIC schema. Can optionally set the role to ACCOUNTADMIN and the warehouse to COMPUTE_WH.
  • MySQL – load and run the Chinook – MySQL.sql script from MySQL Workbench or other free database manager. Use localhost and default port 3306 (if MariaDB is not using already the same number). Connect with admin’s root username, and a saved password. Use Chinook for both database and schema.
  • MariaDB – load and run the Chinook – MariaDB.sql script from HeidiSQL or other free database manager. Use localhost and default port 3306 (if MySQL is not using already the same number). Connect with admin’s root username, and queryX30 password. Use Chinook for both database and schema.
  • Amazon Aurora – create an Aurora database in AWS (Amazon Web Services), with remote access. Connect to the database, then load and run the Chinook – Amazon Aurora.sql script from MySQL Workbench or other free database manager. Use Chinook for both database and schema.
  • PostgreSQL – from pgAdmin or other free database manager, create a new Chinook database connecting with admin’s postgres username. Load and run the Chinook – PostgreSQL.sql script. Use localhost and default port 5432 to connect to the database.
  • Amazon Redshift – create a Redshift cluster and database in AWS (Amazon Web Services), with remote access. Connect to the database, then load and run the Chinook – Amazon Redshift.sql script from SQL Workbench or other free database manager. Use Chinook for the database name.
  • Microsoft SQL Server – from SSMS (SQL Server Management Studio) or other free database manager, create a Chinook database under the default dbo schema. Load and run the Chinook – Microsoft SQL Server.sql script, to create the tables and populate with data.
  • Microsoft Azure SQL Database – just like for SQL Server, from SSMS (SQL Server Management Studio) or other free database manager, create a Chinook database under the default dbo schema. Load and run the Chinook – Microsoft SQL Server.sql script, to create the tables and populate with data..
  • Oracle – from SQL Developer or other free database manager, create a Chinook username and schema, then connect with this username. Load and run the Chinook – Oracle.sql script.
  • IBM DB2 – from SQL Workbench, DBeaver or other free database manager, create a Chinook database. Load and run the Chinook – IBM Db2.sql script.
  • Sybase/SAP ASE – load and run the Chinook – SAP ASE.sql script from I-SQL, DBeaver or other free database manager. Append chartset=utf8; to Other Properties, as this is a UTF-8 database.
  • Sybase/SAP SQL Anywhere – open directly the ready-to-use Chinook – Sybase SAP SQL Anywhere.db database file, prepared from the Chinook – Sybase SAP SQL Anywhere.sql script. Use the Chinook – Sybase SAP SQL Anywhere service and database names. Connect with admin’s DBA username and sys password.