MySQL example database: The Coffee Break Database from Sun’s JDBC tutorial

MySQL database FAQ: Can you share an example MySQL database schema (i.e., a MySQL database create script).

For what I do I often need a sample database. For my current writing I decided to use the "coffeebreak" database defined in Sun's JDBC tutorial. If it saves you any time in your work, here are the commands. I've only tested these with MySQL, but they should work with most other databases.

Before worrying about the schema, here are three steps that you'll want to do first:

• Login to your MySQL database (mysql -u root -p).
• Create the database (CREATE DATABASE COFFEEBREAK;).
• Specify that you want to use this database(USE COFFEEBREAK;).

Now that you're using the MySQL COFFEEBREAK database, let's populate its two tables (COFFEES, SUPPLIERS), and give it some sample data. Just copy and paste these commands into your MySQL command line, and you should be ready to go.

-- COFFEES

CREATE TABLE COFFEES (
COF_NAME VARCHAR(32),
 SUP_ID INTEGER,
 PRICE FLOAT,
 SALES INTEGER,
 TOTAL INTEGER
);

INSERT INTO COFFEES VALUES ('Colombian', 101, 7.99, 0, 0);
INSERT INTO COFFEES VALUES ('French_Roast', 49, 8.99, 0, 0);
INSERT INTO COFFEES VALUES ('Espresso', 150, 9.99, 0, 0);
INSERT INTO COFFEES VALUES ('Colombian_Decaf', 101, 8.99, 0, 0);
INSERT INTO COFFEES VALUES ('French_Roast_Decaf', 49, 9.99, 0, 0);

-- SUPPLIERS

CREATE TABLE SUPPLIERS (
SUP_ID INTEGER, 
SUP_NAME VARCHAR(40),
STREET VARCHAR(40), 
CITY VARCHAR(20),
STATE CHAR(2), 
ZIP CHAR(5)
);

INSERT INTO SUPPLIERS 
  VALUES (101, 'Acme, Inc.', '99 Market Street', 'Groundsville', 'CA', '95199');
INSERT INTO SUPPLIERS 
  VALUES (49,  'Superior Coffee', '1 Party Place', 'Mendocino',  'CA', '95460');
INSERT INTO SUPPLIERS 
  VALUES (150, 'The High Ground', '100 Coffee Lane', 'Meadows',  'CA', '93966');

Assuming everything works okay, you'll be able to run queries and see output like this:

mysql> select * from coffees;
+--------------------+--------+-------+-------+-------+
| COF_NAME           | SUP_ID | PRICE | SALES | TOTAL |
+--------------------+--------+-------+-------+-------+
| Colombian          |    101 |  7.99 |     0 |     0 |
| French_Roast       |     49 |  8.99 |     0 |     0 |
| Espresso           |    150 |  9.99 |     0 |     0 |
| Colombian_Decaf    |    101 |  8.99 |     0 |     0 |
| French_Roast_Decaf |     49 |  9.99 |     0 |     0 |
+--------------------+--------+-------+-------+-------+
5 rows in set (0.00 sec)

mysql>
mysql> select * from suppliers;
+--------+-----------------+------------------+--------------+-------+-------+
| SUP_ID | SUP_NAME        | STREET           | CITY         | STATE | ZIP   |
+--------+-----------------+------------------+--------------+-------+-------+
|    101 | Acme, Inc.      | 99 Market Street | Groundsville | CA    | 95199 |
|     49 | Superior Coffee | 1 Party Place    | Mendocino    | CA    | 95460 |
|    150 | The High Ground | 100 Coffee Lane  | Meadows      | CA    | 93966 |
+--------+-----------------+------------------+--------------+-------+-------+
3 rows in set (0.00 sec)

Many thanks to Sun Microsystems for the tables. I hope they don't mind my sharing them in this manner.