Lesson 2: Creating Tables in SQL

You must create your tables before you can enter data into them. Use the Create Table command.


Syntax:

Create table tablename using filename
(fieldname fieldtype(length),
fieldname fieldtype(length),
fieldname fieldtype(length));

Explanation:

• Table names cannot exceed 20 characters.
• Table names must be unique within a database.
• Field names must be unique within a table.
• You may specify the data file to use. If you do not specify a data file, Scalable SQL will create one, using a .dat extension.
• The list of fields must be enclosed in parentheses.
• You must specify the field type.

Examples: 
Char -- a character string
Float -- a number
Date -- a date field
Logical -- a logical field

• You must specify the field length.
• The field length must be enclosed in parentheses.
• You must separate field definitions with commas.
• You must end each SQL statement with a semicolon.

Example:
Create tables TrnVendor and TrnAPDoc. We will use these tables in the exercises that follow: 

1. In the SQL text box, type:

Create table TrnVendor using 'TVendor.dat'
(VendId char(10),
Name char(30),
Add1 char(30),
City char(30),
State char(3),
CurrBal float(8),
ExpAcct char(10));

Create table TrnAPDoc using 'TAPDoc.dat'
( VendId char(10),
RefNbr char(10),
DocDate date(4),
OrigDocAmt float(8),
CuryDocBal float(8),
OpenDoc logical(2)); 
 

2. Execute the commands. To create the first table, click on First, the Run button located to the right of the SQL text box.
3. You should receive the following message:

Scalable SQL informative status = -105. The CREATE statement completed successfully.

4. Click on Next to create the second table.
5. You should receive the following message:

Scalable SQL informative status = -105. The CREATE statement completed successfully. 

Click on Stop to return to the original screen.