2.5.3 ⁂Sample SQL Tables

The sample SQL database called “samples” is created using:

CREATE DATABASE samples;

A sample user called “sampleuser” with the password “sample-passwd” is created using:

GRANT SELECT ON samples.* TO 'sampleuser'@'localhost' IDENTIFIED BY 'sample-passwd';

Remember to switch to this database before you try creating the tables in it:

USE samples;

If you like, you can download samples.sql and add this sample database (including the tables below) using:

$ mysql -u root -p < samples.sql

(Alternatively, you can use one of the GUI MySQL tools, such as MySQL Workbench.)

The tables included in this sample database are analogous to the CSV file s described in §2.5.1 Sample CSV Files. As with those files, some of the SQL tables include UTF-8 characters, so you'll also need to use:

\usepackage[utf8]{inputenc}
\usepackage[T1]{fontenc}

(or just fontspec [76] for XeLaTeX) and make sure your text editor is set to UTF-8 encoding.

The tables are defined as follows:

books
This table is created using:
```
CREATE TABLE books
(
 id INT PRIMARY KEY AUTO_INCREMENT,
 title VARCHAR(64),
 author VARCHAR(32),
 format ENUM('paperback', 'hardback', 'ebook'),
 price DECIMAL(5,2)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
```
The data is added to the table via:
INSERT INTO books (title, author, format, price) VALUES ('The Adventures of Duck and Goose', 'Sir Quackalot', 'paperback', 10.99); INSERT INTO books (title, author, format, price) VALUES ('The Return of Duck and Goose', 'Sir Quackalot', 'paperback', 11.99); INSERT INTO books (title, author, format, price) VALUES ('More Fun with Duck and Goose', 'Sir Quackalot', 'paperback', 12.99); INSERT INTO books (title, author, format, price) VALUES ('Duck and Goose on Holiday', 'Sir Quackalot', 'paperback', 11.99); INSERT INTO books (title, author, format, price) VALUES ('The Return of Duck and Goose', 'Sir Quackalot', 'hardback', 19.99); INSERT INTO books (title, author, format, price) VALUES ('The Adventures of Duck and Goose', 'Sir Quackalot', 'hardback', 18.99); INSERT INTO books (title, author, format, price) VALUES ('My Friend is a Duck', 'A. Parrot', 'paperback', 14.99); INSERT INTO books (title, author, format, price) VALUES ( 'Annotated Notes on the ‘Duck and Goose’ chronicles', 'Prof Macaw', 'ebook', 8.99); INSERT INTO books (title, author, format, price) VALUES ('‘Duck and Goose’ Cheat Sheet for Students', 'Polly Parrot', 'ebook', 5.99); INSERT INTO books (title, author, format, price) VALUES ('‘Duck and Goose’: an allegory for modern times?', 'Bor Ing', 'hardback', 59.99);
people
This table is created using:
```
CREATE TABLE people
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  forenames VARCHAR(32) NOT NULL,
  surname VARCHAR(32) NOT NULL,
  title VARCHAR(8),
  address1 VARCHAR(32) NOT NULL,
  address2 VARCHAR(32),
  town VARCHAR(32) NOT NULL,
  county VARCHAR(32),
  country CHAR(2) NOT NULL,
  postcode VARCHAR(32),
  subscribed BIT(1) DEFAULT 0,
  gender ENUM('male', 'female'),
  dob DATE,
  INDEX(id, surname)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
```
The data is added to the table via:
INSERT INTO people (forenames, surname, title, address1, address2, town, county, country, postcode, subscribed, gender, dob) VALUES ('Polly', 'Parrot', 'Miss', '42 The Lane', NULL, 'Some Town', 'Noshire', 'gb', 'AB1 2XY', 1, 'female', '1970-12-31'); INSERT INTO people (forenames, surname, title, address1, address2, town, county, country, postcode, subscribed, gender, dob) VALUES ('Mabel', 'Canary', 'Mrs', '24 The Street', 'Some Village', 'Some Town', 'Noshire', 'gb', 'AB1 2YZ', 0, 'female', '1968-01-23'); INSERT INTO people (forenames, surname, title, address1, address2, town, county, country, postcode, subscribed, gender, dob) VALUES ('Zöe', 'Zebra', 'Ms', '856 The Avenue', NULL, 'Some City', 'CA', 'us', '123456', 1, 'female', '1989-07-16'); INSERT INTO people (forenames, surname, title, address1, address2, town, county, country, postcode, subscribed, gender, dob) VALUES ('José', 'Arara', NULL, 'Nenhuma Rua', NULL, 'São Paulo', NULL, 'br', '123457', 1, 'male', '1991-05-30'); INSERT INTO people (forenames, surname, title, address1, address2, town, county, country, postcode, subscribed, gender, dob) VALUES ('Dickie', 'Duck', 'Mr', '1 The Street', 'Another Village', 'Some City', 'Imagineshire', 'gb', 'YZ1 2AB', 0, 'male', '1952-11-25'); INSERT INTO people (forenames, surname, title, address1, address2, town, county, country, postcode, subscribed, gender, dob) VALUES ('Fred', 'Canary', 'Mr', '24 The Street', 'Some Village', 'Some Town', 'Noshire', 'gb', 'AB1 2YZ', 1, 'male', '1967-08-04');

countries

This is a list of country codes. The table is created using:

CREATE TABLE countries
(
  code CHAR(2) PRIMARY KEY NOT NULL,
  name VARCHAR(64) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

This table contains 249 entries. For brevity, only the rows that contain codes referenced in the people table are shown below:

INSERT INTO countries (code, name)
  VALUES ('br', 'Brazil');
INSERT INTO countries (code, name)
  VALUES ('gb', 'United Kingdom');
INSERT INTO countries (code, name)
  VALUES ('us', 'United States');

You can view or download the complete list from the examples web page.

ordergroups This is a list of order groups. The table is created using:

CREATE TABLE ordergroups
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  customerid INT NOT NULL REFERENCES people (id),
  discount DECIMAL(5,2),
  postage DECIMAL(5,2)
) ENGINE=MyISAM;

The data is added to the table via:

INSERT INTO ordergroups (customerid, discount, postage)
 VALUES (2, 0.0, 5.0);
INSERT INTO ordergroups (customerid, discount, postage)
 VALUES (4, 2.5, 20.0);
INSERT INTO ordergroups (customerid, discount, postage)
 VALUES (1, 0.0, 5.0);

orders

This is a list of partial orders. The table is created using:

CREATE TABLE orders
(
  id INT PRIMARY KEY AUTO_INCREMENT,
  groupid INT NOT NULL REFERENCES ordergroups (id),
  bookid INT NOT NULL REFERENCES books (id),
  quantity INT NOT NULL
) ENGINE=MyISAM;

The data is added to the table via:

INSERT INTO orders (groupid, bookid, quantity)
  VALUES (1, 6, 1);
INSERT INTO orders (groupid, bookid, quantity)
  VALUES (1, 1, 4);
INSERT INTO orders (groupid, bookid, quantity)
  VALUES (2, 10, 1);
INSERT INTO orders (groupid, bookid, quantity)
  VALUES (2, 7, 20);
INSERT INTO orders (groupid, bookid, quantity)
  VALUES (2, 8, 1);
INSERT INTO orders (groupid, bookid, quantity)
  VALUES (3, 1, 4);
INSERT INTO orders (groupid, bookid, quantity)
  VALUES (3, 6, 5);
INSERT INTO orders (groupid, bookid, quantity)
  VALUES (3, 7, 2);

This data can be fetched via datatooltk. For example, to fetch the data from the books table:

$ datatooltk --output books.dbtex --sqluser sampleuser --sqldb samples --sql "SELECT * FROM books"

If you prefer to use arara you can use

% arara: datatooltk: {
% arara: --> output: books.dbtex,
% arara: --> sqluser: sampleuser,
% arara: --> sqldb: samples,
% arara: --> sql: "SELECT * FROM books" }

The resulting file books.dbtex can now be loaded in your document via:

\DTLloaddbtex{\books}{books.dbtex}

If required, the column headers can be set using \DTLsetheader. For example:

\DTLsetheader{\books}{id}{ID}
\DTLsetheader{\books}{title}{Title}
\DTLsetheader{\books}{author}{Author}
\DTLsetheader{\books}{format}{Format}
\DTLsetheader{\books}{price}{Price (\pounds)}

Remember you can use the SELECT statement to sort or filter the data or join with another table. For example, if you only want to fetch customers in the UK and order them by their surname:

$ datatooltk --output people.dbtex --sqluser sampleuser --sqldb samples --sql "SELECT * FROM people WHERE country='gb' ORDER BY surname"

⇦

⇧

⇨

This book is also available as A4 PDF or 12.8cm x 9.6cm PDF or paperback (ISBN 978-1-909440-07-4).