With this APL+Win Training Pack you enter the realm of Relational Databases and learn how APL+Win can perfectly fit to the Relational model.

The TOOLKIT.W3 delivered workspace contains a large set of utilities which you can use to:

• create your own Relational Databases in APL+Win
• fill your Relational Databases with APL data
• query your database with an SQL-like query language
• start doing relational queries
• create and use a sample Relational Database
• add or remove columns & tables from a Relational Database

This APL+Win Training Pack contains the first part of an extremely powerful Relational Database Engine which can outperform standard databases (Oracle, Sybase, MS Access, Paradox, etc...) by sometimes factors of more than 100!

This Relational Database Engine can let you create and exploit databases with hundreds of tables and columns and millions of rows, with remarquable performance. For example, to:

• select records where a numeric column matches a given criteria, and
• extract the found records data from a table with 500,000 rows and 4 columns

takes less than 3 seconds on my rather slow Toshiba 720 CDT portable (Pentium 133).

      Œts ª bbb„Select AllBut lilNo From lil Where lilOrdno Eq 11 ª Œts
1997 6 28 16 41 51 990
1997 6 28 16 41 54 570

The instruction above reads 10 million bytes from the hard disk (because for this particular query hits are spread out within the 500000 table records) and extracts 44899 extracted records!

If the hits had been grouped in the first 100000 records and if setting the blocking factor to 100000 the above query would give results in less than 1 second.

      Œts ª bbb„Select AllBut lilNo From lil Where lilOrdno Eq 12 ª Œts
1997 6 28 16 48 27 240
1997 6 28 16 48 28 230

And this is with non indexed columns!

Quickly discover below how to use this Relatinal Database System:

Create a new database

       Create Database 'c:\temp\new'
c:\temp\new.lc

Creating 2 tables in this database:

       Create Table 'sup' 'Suppliers'
sup Suppliers
       Create Table 'pro' 'Products'
pro Product

Creating columns in the Products and Suppliers tables:

       Create Column 'supname' NotNull PrimaryKey Char(20)
c:\temp\supname.new
      Create Column 'proname' NotNull PrimaryKey Char(12)
c:\temp\proname.new
       Create Column 'proprice'	Float
c:\temp\proprice.new
      Create Column 'prosupno' Integer ForeignKey supNo supName
c:\temp\prosupno.new

List existing tables and columns:

      List Tables
Num  Name Deleted Description
¯¯¯  ¯¯¯¯ ¯¯¯¯¯¯¯ ¯¯¯¯¯¯¯¯¯¯¯
1001 sup       21 Suppliers
1003 pro       22 Products

      List Columns
Num  Name     NotNull PKey FKey  LinkName LinkAlt Type    Width Format Records
¯¯¯  ¯¯¯¯     ¯¯¯¯¯¯¯ ¯¯¯¯ ¯¯¯¯  ¯¯¯¯¯¯¯¯ ¯¯¯¯¯¯¯ ¯¯¯¯    ¯¯¯¯¯ ¯¯¯¯¯¯ ¯¯¯¯¯¯¯
1002 supNo    NotNull                             Integer     1 I8           0
1004 proNo    NotNull                             Integer     1 I8           0
1005 supName  NotNull PKey                        Char       20 20A1         0
1006 proName  NotNull PKey                        Char       12 12A1         0
1007 proPrice                                     Float       1 F12.2        0
1008 proSupno              supNo supName        0 Integer     1 I8           0

Adding data to the Products and Suppliers tables:

     Insert Into sup Values(›'APL2000')
     Insert Into sup Values(›'Lescasse Consulting')
     proname„œ'APL+Win' 'APL Book' 'APL+Dos' 'APL+Unix' 'DLL Parser'
     proprice„10000 800 8000 12000 2000
     prosupno„1 2 1 1 2
     Insert Into pro

Using Select to extract data from tables:

      Frame Select * From sup
RecNo Name
¯¯¯¯¯ ¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯
    1 APL2000
    2 Lescasse Consulting
		
      Frame Select * From pro
RecNo Name         Price Supplier
¯¯¯¯¯ ¯¯¯¯¯¯¯¯¯¯¯¯ ¯¯¯¯¯ ¯¯¯¯¯¯¯¯
    1 APL+Win      10000        1
    2 APL Book       800        2
    3 APL+Dos       8000        1
    4 APL+Unix     12000        1
    5 DLL Parser    2000 2

      Frame Select * From pro SortedBy proSupno,-proName
RecNo Name         Price Supplier
¯¯¯¯¯ ¯¯¯¯¯¯¯¯¯¯¯¯ ¯¯¯¯¯ ¯¯¯¯¯¯¯¯
    1 APL+Win      10000        1
    4 APL+Unix     12000        1
    3 APL+Dos       8000        1
    5 DLL Parser    2000        2
    2 APL Book       800        2

      Frame Select * From pro SortedBy proSupno,-proName Where proName Contains'APL'
RecNo Name         Price Supplier
¯¯¯¯¯ ¯¯¯¯¯¯¯¯¯¯¯¯ ¯¯¯¯¯ ¯¯¯¯¯¯¯¯
    1 APL+Win      10000        1
    4 APL+Unix     12000        1
    3 APL+Dos       8000        1
    2 APL Book       800        2

Performing selection in one table and extracting from another one:

      Frame Select * From pro Where supName Eq'APL2000'
RecNo  Name         Price Supplier
¯¯¯¯¯  ¯¯¯¯¯¯¯¯¯¯¯¯ ¯¯¯¯¯ ¯¯¯¯¯¯¯¯
    1  APL+Win      10000        1
    3  APL+Dos       8000        1
    4  APL+Unix     12000        1

Looking at extracted data:

      proname
APL+Win
APL+Dos
APL+Unix

      proprice
10000 8000 12000

      (proprice>9000)šproname
APL+Win
APL+Unix

Etc.

Removing the database:

      Drop Database 'new'

Creating a sample database with one table having 1000000 (one million) records:

      Œchdir'c:\temp'
C:\aplwin

      Œts ª SampleDb 1000000 ª Œts
1997 6 28 17 58 20 30
1997 6 28 17 58 36 560

All these were just a few examples of this powerful Relational Database Engine.

Pack 9 Content