{"id":104,"date":"2019-10-03T14:38:22","date_gmt":"2019-10-03T07:38:22","guid":{"rendered":"https:\/\/mariadbtutorial.com\/?page_id=104"},"modified":"2020-04-11T23:24:13","modified_gmt":"2020-04-11T16:24:13","slug":"mariadb-sample-database","status":"publish","type":"page","link":"https:\/\/www.mariadbtutorial.com\/getting-started\/mariadb-sample-database\/","title":{"rendered":"MariaDB Sample Database"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about a MariaDB sample database nation<\/code> and how to load the sample database into the MariaDB server.<\/p>\n\n\n\n

Introduction to the MariaDB sample database<\/h2>\n\n\n\n

The nation<\/code> database has six tables that store basic information on countries, regions, languages, and statistics.<\/p>\n\n\n\n

The following picture illustrates the nation<\/code> sample database:<\/p>\n\n\n\n

\"mariadb<\/figure>\n\n\n\n

The countries table<\/h3>\n\n\n\n

The countries<\/code> 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.<\/p>\n\n\n\n

\"countries\"<\/figure>\n\n\n
create<\/span> table<\/span> countries (\n    country_id int<\/span> auto_increment,\n    name<\/span> varchar<\/span>(50<\/span>) not<\/span> null<\/span>,\n    area decimal<\/span>(10<\/span>,2<\/span>) not<\/span> null<\/span>,\n    national_day date<\/span>,\n    country_code2 char<\/span>(2<\/span>) not<\/span> null<\/span> unique<\/span>,\n    country_code3 char<\/span>(3<\/span>) not<\/span> null<\/span> unique<\/span>,\n    region_id int<\/span> not<\/span> null<\/span>,\n    foreign<\/span> key<\/span>(region_id) \n  \treferences<\/span> regions(region_id),\n    primary key<\/span>(country_id)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The country_stats table<\/h3>\n\n\n\n
The country_stats<\/code> table stores country’s statistics by year such as population and GDP.<\/p>\n\n\n\n
\"country_stats\"<\/figure>\n\n\n
create<\/span> table<\/span> country_stats(\n    country_id int<\/span>,\n    year<\/span> int<\/span>,\n    population int<\/span>,\n    gdp decimal<\/span>(15<\/span>,0<\/span>),\n    primary key<\/span> (country_id, year<\/span>),\n    foreign<\/span> key<\/span>(country_id)\n\treferences<\/span> countries(country_id)\n);\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The country_languages table<\/h3>\n\n\n\n
Each country may have one or more languages. The country_languages<\/code> table stores the relationships between countries<\/code> and languages<\/code> tables.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n
create<\/span> table<\/span> country_languages(\n    country_id int<\/span>,\n    language_id int<\/span>,\n    official boolean<\/span> not<\/span> null<\/span>,\n    primary key<\/span> (country_id, language_id),\n    foreign<\/span> key<\/span>(country_id) \n\treferences<\/span> countries(country_id),\n    foreign<\/span> key<\/span>(language_id) \n\treferences<\/span> languages(language_id)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The languages table<\/h3>\n\n\n\n
The languages<\/code>table stores the names of languages.<\/p>\n\n\n\n
\"languages\"<\/figure>\n\n\n
create<\/span> table<\/span> languages(\n    language_id int<\/span> auto_increment,\n    language<\/span> varchar<\/span>(50<\/span>) not<\/span> null<\/span>,\n    primary key<\/span> (language_id)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The continents table<\/h3>\n\n\n\n
The contients<\/code> table store information on the continents.<\/p>\n\n\n\n
\"continents\"<\/figure>\n\n\n
create<\/span> table<\/span> continents(\n    continent_id int<\/span> auto_increment,\n    name<\/span> varchar<\/span>(255<\/span>) not<\/span> null<\/span>,\n    primary key<\/span>(continent_id)\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The regions table<\/h3>\n\n\n\n
The regions<\/code> table stores information on regions. A region belongs to one continent. And one continent may have multiple regions.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n
create<\/span> table<\/span> regions(\n    region_id int<\/span> auto_increment,\n    name<\/span> varchar<\/span>(100<\/span>) not<\/span> null<\/span>,\n    continent_id INT<\/span> NOT<\/span> NULL<\/span>,\n    primary key<\/span>(region_id),\n    foreign<\/span> key<\/span>(continent_id) \n        references<\/span> continents(continent_id) \n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In addition to the tables related to nations, the sample database has two additional tables vips<\/code> and guests<\/code>. These simple tables are for demonstration in various tutorials such as joins<\/a>, union<\/a>, intersect<\/a>, and except<\/a>.<\/p>\n\n\n\n
The vips<\/code> table:<\/p>\n\n\n
create<\/span> table<\/span> vips(\n    vip_id int<\/span> primary key<\/span>,\n    name<\/span> varchar<\/span>(100<\/span>) not<\/span> null<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The guests<\/code> table:<\/p>\n\n\n
create<\/span> table<\/span> guests(\n    guest_id int<\/span> primary key<\/span>,\n    name<\/span> varchar<\/span>(100<\/span>) not<\/span> null<\/span>\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Load the MariaDB sample database<\/h3>\n\n\n\n
To load the MariaDB sample database, you follow these steps:<\/p>\n\n\n\n
First, download the sample database file:<\/p>\n\n\n\n
Download MariaDB Sample Database<\/a><\/p>\n\n\n\n
Next, unzip the file to a directory e.g., c:\\mariadb\\nation.sql<\/code><\/p>\n\n\n\n
After that, connect to the MariaDB server<\/a> with the root<\/code> user account, type the password and press the  Enter<\/code> keyboard.<\/p>\n\n\n\n
mysql -u root -p\nEnter password: ********<\/code><\/pre>\n\n\n\n
Then, load the database by using the source command:<\/p>\n\n\n
mysql>source c:\\mariadb\\nation.sql<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, select the nation<\/code> database and display tables of the database:<\/p>\n\n\n
mysql> use<\/span> nation;\nDatabase changed\nmysql> show<\/span> tables<\/span>;\n+-------------------+<\/span>\n| Tables_in_nation  |\n+-------------------+<\/span>\n| continents        |\n| countries         |\n| country_languages |\n| country_stats     |\n| guests            |\n| languages         |\n| region_areas      |\n| regions           |\n| vips              |\n+-------------------+<\/span>\n9 rows in set<\/span> (0.00<\/span> sec)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this tutorial, you have learned about a MariaDB sample database nation<\/code> and how to load the sample database into the MariaDB server.<\/p>\n
\n\t
\n\t\t
Was this tutorial helpful ?<\/div>\n\t\t
\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
\n\t\t
\n\t\t\t
<\/div>\n\t\t\t\n\t\t\t