Over the years, I found that having a small sample database is very helpful to test out my queries. Most of the times, the real database is too big and complex to use it. And, it is not easy to duplicate it. Here is my sample database.

Database design

Create database

-- DROP DATABASE Test;
CREATE DATABASE Test;

Create tables

USE Test;
CREATE TABLE Emails(
        Contact_id INT NOT NULL,
        Email NCHAR(50) NOT NULL,
        Label NCHAR(50) NOT NULL,
        CONSTRAINT PK_Emails PRIMARY KEY (Contact_id, Email)
    );
 
CREATE TABLE Contacts(
        id INT AUTO_INCREMENT PRIMARY KEY, 
        Firstname NCHAR(50) NOT NULL, 
        Lastname NCHAR(50) NOT NULL
    );
    -- Auto increment by 1 starting from 2.
    ALTER TABLE Contacts AUTO_INCREMENT=2;

Add foreign keys

ALTER TABLE Emails ADD CONSTRAINT FK_Emails_Contacts 
    FOREIGN KEY(Contact_id) REFERENCES Contacts (id);

Insert data

There is a foreign key between Contacts and Emails tables. In order to add an email, a contact must exists.

USE Test;
INSERT INTO Contacts(Firstname, Lastname) VALUES('Joe', 'Smith');
    INSERT INTO Emails(Contact_id, Email, Label)
        VALUES(LAST_INSERT_ID(), 'joe.smith@OpenWritings.net', 'personal');
 
INSERT INTO Contacts(Firstname, Lastname) VALUES('Anne', 'Bond');
    INSERT INTO Emails(Contact_id, Email, Label)
        VALUES(LAST_INSERT_ID(), 'anne.bond@OpenWritings.net', 'work'); 
 
INSERT INTO Contacts(Firstname, Lastname) VALUES('James', 'Bond');
    INSERT INTO Emails(Contact_id, Email, Label)
        VALUES(LAST_INSERT_ID(), 'james.bond@OpenWritings.net', 'work');

Display tables content

SELECT * FROM Contacts;
SELECT * FROM Emails;

Output

MariaDB [Test]> SELECT * FROM Contacts;
+----+-----------+----------+
| id | Firstname | Lastname |
+----+-----------+----------+
|  2 | Joe       | Smith    |
|  3 | Anne      | Bond     |
|  4 | James     | Bond     |
+----+-----------+----------+
3 rows in set (0.000 sec)
 
 
MariaDB [Test]> SELECT * FROM Emails;
+------------+-----------------------------+----------+
| Contact_id | Email                       | Label    |
+------------+-----------------------------+----------+
|          2 | joe.smith@OpenWritings.net  | personal |
|          3 | anne.bond@OpenWritings.net  | work     |
|          4 | james.bond@OpenWritings.net | work     |
+------------+-----------------------------+----------+
3 rows in set (0.000 sec)

Github

Complete SQL script can be found at https://github.com/xuanngo2001/mysql/blob/master/sample-db/mysql-sample-db.sql

Here is how you can run it from command line.

# Adapt parameter values to your setup.
mysql -hLOCALHOST -uROOT -pPASSWORD < mysql-sample-db.sql

MySQL

About the author

Xuan Ngo is the founder of OpenWritings.net. He currently lives in Montreal, Canada. He loves to write about programming and open source subjects.

MySQL - Sample database