SQL400 for Beginners Part 2

Hi Guys....

Welcome back… Today I would share more in SQL400….

Getting Started

Interactive SQL/400 can be run from the Command line. Invoke STRSQL {Start SQL/400} and get at what looks like another command line, where we can key in SQL statements for immediate execution, and/or use the F4 Prompt function to navigate IBM DB2/400 data base, which is a version of UDB

SQL CONCEPTS

•       Using basic SQL statements and clauses

•       The SELECT clause

•       Specifying a search condition using the WHERE clause

•       ORDER BY clause

•       Date/Time arithmetic

•       Creating and using ALIAS names

•       Creating descriptive labels using the LABEL ON statement

•       Describing an SQL object using COMMENT ON 

SQL SELECT Statement

The SELECT command is used to extract relevant information from a table/file.

It has three basic parts:

1. SELECT – It is used to select one/ more/ all columns from a file

2. FROM – It is used to mention the Library/Filename from where the data is to be extracted

3. WHERE – This is used to filter out rows based upon required conditions

A simple SELECT query is as follows:

SELECT "column_name" FROM "table_name“


Select * from Cust
The SELECT command above retrieves all columns (*) from file *LIBL/CUST  and displays all rows. 
The Customer Table file CUST is picked from the topmost library in the current library list (*LIBL) which can be viewed/edited by entering EDTLIBL on the main command line(outside SQL).

SQL DISTINCT

The DISTINCT command is used to extract only the distinct values of a field/column from a table/file. 


A simple DISTINCT query is as follows:

SELECT DISTINCT  "column_name" FROM "table_name“

Select DISTINCT CustPur from Cust

This SELECT command will retrieve only one column – CUSTPUR from the file CUST.


The DISTINCT keyword will fetch all the distinct Purchase Index, i.e., all unique values of CUSTPUR will be displayed on screen without repetitions

SQL WHERE

WHERE is used to filter out the rows based upon given conditions. This helps in restricting the output data to only the relevant records.

The syntax is as follows: 
SELECT "column_name" FROM "table_name" WHERE "condition

Select CUSTNAME, CUSTCITY,FROM CUST Where CUSTCITY = 'Ohio'

Columns CUSTNAME, and CUSTCITY will be selected from file *LIBL/CUST. Only those records will be selected and displayed on screen which have the value of the field City(CUSTCITY ) as ‘Ohio’.

SQL AND OR

Compound conditions are made up of multiple simple conditions connected by AND or OR. There is no limit to the number of simple conditions that can be present in a single SQL statement. AND and OR can be used interchangeably. In addition, we may use the parenthesis sign () to indicate the order of the condition

The syntax for a compound condition is as follows:

SELECT "column_name" FROM "table_name" WHERE "simple condition" {[AND|OR] "simple condition"}

SELECT STRNAM, STPVST FROM TBLSTR WHERE STPVST = 'PA' OR (STPVST = 'TX' AND 
STRNAM = 'GNC')

Only two columns will be selected from file TBLSTR and displayed on screen – STRNAM and STPVST. The records selected will EITHER have the value of field STPVST as ‘PA’ OR the records which have both STPVST as ‘TX’ AND STRNAM as ‘GNC'

SQL IN

IN is used in an SQL statement when we know exactly the value of the returned values, we want to see for at least one of the columns.

The syntax for using the IN keyword is as follows:

SELECT "column_name" FROM "table_name" WHERE "column_name" IN ('value1', 'value2', ...)

The number of values in the parenthesis can be one or more, with each values separated by comma. Values can be numerical or characters.

SELECT STRNAM, STPVST FROM TBLSTR WHERE STPVST in ('PA’ , 'TX' , 'NY')

Only two columns will be selected from file TBLSTR and displayed on screen – STRNAM and STPVST. Only those

rows will be selected which have the value of STPVST among the ones mentioned in the parenthesis. Hence, all

the rows which have STPVST as PA or TX or NY will be selected

SQL BETWEEN

The BETWEEN keyword allows for selecting a range.

The syntax for the BETWEEN clause is as follows:

SELECT "column_name" FROM "table_name" WHERE "column_name" BETWEEN 'value1' AND 'value2'

This will select all rows whose column has a value between 'value1' and 'value2‘. 

SELECT * FROM TBLSTR WHERE STRNUM between 10 and 50

The above query will select all the rows from file TBLSTR which have the value of the field STRNUM between 10 to 50 (both inclusive)

 

SQL Wildcard

There are times when we want to match on a string pattern. To do that, we will need to employ the concept of wildcard.

In SQL, there are two wildcards:

% (percent sign) represents zero, one, or more characters.

Ex - 'ABC%': All strings that start with 'ABC‘ will be selected

_ (underscore) represents exactly one character.

Ex – ‘A_Z': All string of length 3 that start with 'A‘ and end with 'Z’ will be selected

Wildcards are used with the LIKE keyword in SQL.

Example of Wildcard Percent (%)

SELECT * FROM TBLSTR WHERE STRNAM like ‘T%’

This statement will select and display all rows which have the Store Name (STRNAM) starting with ‘T’

Example of Wildcard Underscore (_)

SELECT * from SODHDR WHERE OHOHNO like '2______4'     

This statement will select and display all rows which have the Order Number (OHOHNO) starting with ‘2’ and ending with ‘4’ with 6 characters in between them(ignore commas).

SQL LIKE

LIKE allows to do a search based on a pattern rather than specifying exactly what is desired (as in IN) or spell out a range (as in BETWEEN).

The syntax is as follows:

SELECT "column_name" FROM "table_name" WHERE "column_name" LIKE {PATTERN}

{PATTERN} often consists of wildcards.

SELECT * FROM TBLSTR WHERE STRNAM like ‘T%’

This statement will select and display all rows which have the Store Name (STRNAM) starting with ‘T’


Thats all for this session. 


Do write me @ iSeriesblogs@gmail.com for any query or you can simply post comments below.


Will tell you more on SQL400  in next blog....


Till then happy learning...