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:
- country
- product
- customer
- order
- items
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:
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