MariaDB Sample Database

Summary: in this tutorial, you will learn about a MariaDB sample database nation and how to load the sample database into the MariaDB server.

Introduction to the MariaDB sample database

The nation database has six tables that store basic information on countries, regions, languages, and statistics.

The following picture illustrates the nation sample database:

The countries table

The countries table stores basic information on countries including the country name, the area in km2, independent year, 2-digit and 3-digit country codes, and region.

create table countries (
    country_id int auto_increment,
    name varchar(50) not null,
    area decimal(10,2) not null,
    national_day date,
    country_code2 char(2) not null unique,
    country_code3 char(3) not null unique,
    region_id int not null,
    foreign key(region_id) 
  	references regions(region_id),
    primary key(country_id)
);Code language: SQL (Structured Query Language) (sql)

The country_stats table

The country_stats table stores country’s statistics by year such as population and GDP.

create table country_stats(
    country_id int,
    year int,
    population int,
    gdp decimal(15,0),
    primary key (country_id, year),
    foreign key(country_id)
	references countries(country_id)
);
Code language: SQL (Structured Query Language) (sql)

The country_languages table

Each country may have one or more languages. The country_languages table stores the relationships between countries and languages tables.

create table country_languages(
    country_id int,
    language_id int,
    official boolean not null,
    primary key (country_id, language_id),
    foreign key(country_id) 
	references countries(country_id),
    foreign key(language_id) 
	references languages(language_id)
);Code language: SQL (Structured Query Language) (sql)

The languages table

The languagestable stores the names of languages.

create table languages(
    language_id int auto_increment,
    language varchar(50) not null,
    primary key (language_id)
);Code language: SQL (Structured Query Language) (sql)

The continents table

The contients table store information on the continents.

create table continents(
    continent_id int auto_increment,
    name varchar(255) not null,
    primary key(continent_id)
);Code language: SQL (Structured Query Language) (sql)

The regions table

The regions table stores information on regions. A region belongs to one continent. And one continent may have multiple regions.

create table regions(
    region_id int auto_increment,
    name varchar(100) not null,
    continent_id INT NOT NULL,
    primary key(region_id),
    foreign key(continent_id) 
        references continents(continent_id) 
);Code language: SQL (Structured Query Language) (sql)

In addition to the tables related to nations, the sample database has two additional tables vips and guests. These simple tables are for demonstration in various tutorials such as joins, union, intersect, and except.

The vips table:

create table vips(
    vip_id int primary key,
    name varchar(100) not null
);Code language: SQL (Structured Query Language) (sql)

The guests table:

create table guests(
    guest_id int primary key,
    name varchar(100) not null
);Code language: SQL (Structured Query Language) (sql)

Load the MariaDB sample database

To load the MariaDB sample database, you follow these steps:

First, download the sample database file:

Download MariaDB Sample Database

Next, unzip the file to a directory e.g., c:\mariadb\nation.sql

After that, connect to the MariaDB server with the root user account, type the password and press the  Enter keyboard.

mysql -u root -p
Enter password: ********

Then, load the database by using the source command:

mysql>source c:\mariadb\nation.sqlCode language: SQL (Structured Query Language) (sql)

Finally, select the nation database and display tables of the database:

mysql> use nation;
Database changed
mysql> show tables;
+-------------------+
| Tables_in_nation  |
+-------------------+
| continents        |
| countries         |
| country_languages |
| country_stats     |
| guests            |
| languages         |
| region_areas      |
| regions           |
| vips              |
+-------------------+
9 rows in set (0.00 sec)Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned about a MariaDB sample database nation and how to load the sample database into the MariaDB server.