AS/400 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

STRTSQL screen

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.

Inserting data into a table

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.

Function	Description
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

Function	Description
ABS(N)	Returns the absolute value of N.
COS(A) / ACOS(A) SIN(A) / ASIN(A) TAN(A) / ATAN(A)	Basic trigonometric functions.
CEILING(N) FLOOR(N)	Returns the rounding of N to the unit above/below.
DEGREES(R)	Converts a value in radians to degrees.
RADIANS(D)	Converts a value in degrees to radians.
LN(N) LOG10(N)	Returns the natural logarithm / base 10 logarithm of N.

String Functions

Function	Description
CHAR(N)	Returns the the string representation of the number 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.
RTRIM(S) LTRIM(S)	Removes spaces at the begging (right) or end (left) of S.

Date and Time Functions

Function	Description
CURDATE() CURTIME()	Returns the system's current date/time.
DATE(D) DATE(T)	Converts a string representation of a date/time into into a date/time value.
DAY(D)	Returns the day(1-31) from the date D.
WEEK(D)	Returns the week (1-54) from the date D.
MONTH(D)	Returns the month (1-12) from the date D.
YEAR(D)	Returns the year from the date D.
DAYOFWEEK(D)	Returns the week day (1-7) from the date D where 1 is Sunday.
DAYOFWEEK_ISO(D)	Returns the week day (1-7) from the date D where 1 is Monday.
DAYOFYEAR(D)	Returns the number of the day, in a year (1-366).
HOUR(T)	Returns the hour (0-24) from the time T.
MINUTE(T)	Returns the minute from the time T.
SECOND(T)	Returns the second from the time T.
MICROSECOND(T)	Returns the microsecond from the time