(Tutorial : AS400) Chapter 4 - SQL

Now that we've created a library and saw some basic AS/400 features let's have a look at how we can create and manipulate tables using SQL.

You must create the DEMO library and define it has your current library to proceed with this chapter.

Type the following Start SQL command on the system prompt:

STRSQL

This way you begin a SQL session in AS/400. This application accepts most of the common SQL syntax (CREATE,INSERT, DELETE, SELECT, DROP, etc).

Creating a table with SQL:
From the SQL command line we're going to create the CLIENTS table and then insert some rows. For this tutorial let's assume that all phone numbers have, at most, 9 digits.

Press F3 to leave the table view and type the following commands. Type:

CREATE LIBRARY DEMO/CLIENTS( id_cli numeric(10) PRIMARY KEY, name_cli char(50), birth_cli date, phone_cli numeric(9) )

Note: You can place the code in separate lines (like in the example) or in a continued fashion (without indentation) as long as you don't press the Enter key before you finish the command.

A message will appear saying that the table was created.

If you want to see more empty command lines just press Page Down or move the mouse scroll down. Now Type:

SELECT * FROM CLIENTS

This way you can see that the table was created and it has no information in it. So let's insert some new rows.

Press F3 to leave the table view and type the following commands.

INSERT INTO CLIENTS (ID_CLI, NAME_CLI, BIRTH_CLI, PHONE_CLI) VALUES (1, 'Mary', '12/09/1967', '999999999')

INSERT INTO CLIENTS (ID_CLI, NAME_CLI, BIRTH_CLI, PHONE_CLI) VALUES (2, 'Tom', '09/01/1979', '123456789')

Do the SELECT command again to check if the rows were correctly inserted

Press F3 to leave the SQL command line. A set of options will appear. On the field you should write the option number. Try typing a number outside the option range (for instance 5) and then press Enter. As one would expect, an error message appears on the bottom of the screen. Try pressing F3 or inserting another value. It won't work because the screen is blocked. You will probably find some blocking errors and when you do press Esc the screen will go back to normal.

Now select the option 1 to exit the application, saving the session.


SQL Built-In Functions:
There are some very useful SQL built-in functions on the AS/400 system.

Basic Functions
If you know SQL you may already be familiar with these functions.
MAX -Returns the maximum value from a set of pre-defined values.
MIN -Returns the minimum value from a set of pre-defined values.
AVG -Returns the average value of a set of pre-defined values.
SUM -Returns the sum of a set of pre-defined values.
COUNT -Returns the number of elements in a set of pre-defined values.

Example (returns the maximum ID_CLI value from all the rows in the table CLIENTS):
SELECT MAX(ID_CLI) FROM CLIENTS


Numeric Functions
ABS(N) -Returns the absolute value of N.
CEILING(N) -Returns the rounding of N to the unit above.
FLOOR(N) -Returns the rounding of N to the unit below.


String Functions
CHAR(N) -Returns the the string representation N.
CHAR_LENGTH(S) -Returns the length of a string.
CONCAT(S1, S2) -Concatenates S1 with S2.
SUBSTR(S, I, L) -Returns a substring of S, starting at index I of lenght L.
LOWER(S) -Returns the lowercase representation of S.
UPPER(S) -Returns the uppercase representation of S.
TRIM(S) -Removes spaces from the beggining and and of S.


You Might Also Want To Visit The Following Tutorial Posts:
AS/400 For Dummies
AS/400 Tutorial
SQL/400 Tutorial
Query/400 Tutorial