mytest Database

mytest is a sample database, containing random data, so that you can play with it.

The original scripts to create and fill in the tables can be found in the downloaded tar file of rsql, in the mytest directory.
They can also be downloaded below in this page.

The tables in mytest are:

Create mytest database

Before running the scripts, you must create the database with the command:

CREATE DATABASE mytest;

Run the scripts

Use the RSQL Client rcli to run the scripts.

Execute them in the following order:

view script download sql script
mytest_country_create.sql (download)
mytest_country_fill.sql (download)
mytest_product_create.sql (download)
mytest_product_fill.sql (download)
mytest_customer_create.sql (download)
mytest_customer_fill.sql (download)
mytest_order_create.sql (download)
mytest_order_fill.sql (download)

Country and product tables contains only a few records.

By default, 100000 customers are created, with random names, birthdate, etc.

By default, 200000 orders are created, with random data, containing a few random items.

You can read and modify the scripts, to change the number of customers or orders to create.

Training

This sample database is for training purpose.

You can select, delete or update records. You can also create indexes on useful columns.

If you want to recreate the database, just drop all the tables and run the scripts again.

The records created by the scripts are different for each execution, as they are randomly generated.

If you want to create your own tables and quickly fill them with random data, you can also use the functions:

Example of queries on mytest database:

select cy.cid, cy.name, sum(i.price)
from items i, orders o, customer c, country cy
where o.oid = i.oid and c.custid = o.custid and cy.cid = c.country_id
group by cy.cid, cy.name

select sum(price) total from (
    select cy.cid, cy.name, sum(i.price) price
    from items i, orders o, customer c, country cy
    where o.oid = i.oid and c.custid = o.custid and cy.cid = c.country_id
    group by cy.cid, cy.name
) t


select count(*) as [count], sum(total) as [orders total] from orders

select count(*) as [count], sum(price) as [items total] from items