SQL400 for Beginners Part 3

Hi Guys,

I am back with 3rd tutorial of SQL400. After going through this blog, I hope all of you would be having basic understanding of SQL400

SQL ORDER BY

‘ORDER BY’ is used to list the output in a particular order. This could be in ascending order, in descending order, or could be based on either numerical value or text value.

The syntax for an ORDER BY statement is as follows:

SELECT "column_name" FROM "table_name" [WHERE "condition"] ORDER BY"column_name" [ASC, DESC]

It is possible to order by more than one column.

ORDER BY "column_name1" [ASC, DESC], "column_name2" [ASC, DESC]

SELECT * FROM TBLSTR ORDER BY STRNAM

The above statement will select all records from file TBLSTR and sort them based upon the ascending values of column STRNAM

SQL ALIAS

SQL aliases can be used with database tables and with database table columns, depending on the task to be performed.

SQL aliases are used to make the output of SQL queries easy to read and more meaningful

ALIAS can be of two types:

• Aliases for columns
• Aliases for tables

Aliases for columns:

SELECT ASOSTORE FROM STRREL

SELECT ASOSTORE AS ASSOCIATED_STORE FROM STRREL 

As evident, the second query using an ALIAS for the column ‘Store Data’ is more readable and meaningful.

Aliases for tables:

SELECT A.STNAME, A.STCLASS,STMARK, STFNAME, STCITY  FROM SCHMST A INNER JOIN SCHPERI1 ON A.STENROLLN = STENROLL1 

LEFT OUTER JOIN SCHMST1 ON STENROLLN = STENROLL1 WHERE STENROLLN >= 'SE001‘

The above query uses a prefix ‘A’ for identifying the fields/columns of file SCHMST. Other parts of the query are already explained.

SQL AS

The keyword AS is used to assign an alias to the column or a table. It is inserted between the column name and the column alias or between the table name and the table alias. This adds a more meaningful column header when the output is displayed

The syntax for using AS is as follows:

SELECT "table_alias"."column_name1" AS "column_alias" FROM "table_name" AS "table_alias“

The example of AS function will be displayed alongwith fucntions Current_Date / Current_Time further section

SQL CURRENT DATE

CURRENT_DATE function is used to retrieve the current system date as an object of data type DATE

Similarly, the function CURRENT_TIME is used to fetch the current system-recorded time in an object of data type TIME

SELECT CURRENT_DATE as Todaydate FROM SCHMST

The above query will fetch current system date and display it on the screen under a new column header ‘Todaydate’.

SQL CURRENT TIME

SELECT CURRENT_TIME AS CURRENTTIME FROM SCHMST

The above query will fetch current system time and display it on the screen under a new column header ‘Currenttime’.

SQL DATE ARITHMETIC

The SQL statements can perform arithmetic operations at field/column level to filter out the more relevant records.

The common arithmetic operations used are – Greater than (>), Greater  or Equal to (>=), Lesser than (<), Lesser  or Equal to (<=) etc.

SELECT  *  FROM SCHMST WHERE STADMDAT >'990101'

The above query will fetch all records from file SCHMST which have the date field STADMDAT greater than 990101 in YYMMDD format

CREATE ALIAS

The CREATE ALIAS statement defines an alias on a table, view, or member of a database file at the current server

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared.

The syntax for the same is:

CREATE ALIAS MYLIB.MYMBR2_ALIAS FOR MYLIB.MYFILE (MBR2)

CREATE ALIAS LIB1/ALIAS_SCHMST2 FOR LIB2/SCHMST (SCHMST)

The above statement allows the file SCHMST in Library LIB2 to be referred as file ALIAS_SCHMST2 in library LIB1

INSERTING VALUES IN ALIAS

INSERT INTO *LIBL/ALIAS_SCHMST2 VALUES('AASHISH', 'SE023', 'XI‘,090126)

Once the Alias is created, the new name can be used to refer to the original file. All changes made to the new Alias name will actually update the original file

SQL LABEL ON

Normally, the column name is used as the column heading when the output of a SELECT statement is shown in interactive SQL. By using the LABEL ON statement, one can create a more descriptive label for the column name.

LABEL ON statement is used to change the column headings. Even though the column name is descriptive, it is easier to read if the column heading shows each part of the name on a single line. It also allows you to see more columns of data on a single display.

Ex – LABEL ON TABLE SCHMST IS ‘SCHOOL MASTER TABLE’

The description of the file in the view is changed to ‘SCHOOL MASTER TABLE’

LABEL ON works with PF and LF as well

SQL object using COMMENT ON

The comment statement allows to store some comments about tables, views or columns in the data dictionary. Syntax –

COMMENT ON TABLE SOME_TABLE IS ‘MEANINGFUL COMMENT FOR TABLE';

Ex - COMMENT ON TABLE SCHMST IS 'School Master table.  Each row in this table represents one student .'

The above statement assigns a more descriptive comment for the file SCHMST

COMMENT ON works with PF and LF as well

SQL TABLE DEFINITION

—  Changing a column

—  Adding a column

—  Allowable conversions

—  Deleting a column

Changing a table definition

Once a table is created in the database, there are many occasions where one may wish to change the structure of the table.

In general, the SQL syntax for ALTER TABLE is

ALTER TABLE "table_name"
[alter specification]

Some of the common changes are mentioned below:

# ALTER TABLE Add Column

# ALTER TABLE Modify Column

# ALTER TABLE Rename Column

# ALTER TABLE Drop Column

# ALTER TABLE Add Index

# ALTER TABLE Drop Index

Etc…

Changing a table definition Adding column..

The SQL syntax for ALTER TABLE Add Column is

ALTER TABLE "table_name"
ADD "column 1" "Data Type"

Example -

ALTER TABLE SCHMST ADD COLUMN Gender char(1)

The above query adds a new column ‘GENDER’  in the file SCHMST in library LIB2

Changing a table definition Changing column..

The SQL syntax for ALTER TABLE Modify Column is:

ALTER TABLE "table_name"
MODIFY "column 1" "New Data Type"

Example -

ALTER TABLE SCHMST ALTER COLUMN GENDER SET DATA TYPE CHARACTER ( 3) CCSID 37 

 

The above query changes the attributes of the column GENDER to Character of length 3

Changing a table definition Dropping column..

The SQL syntax for ALTER TABLE Drop Column is

ALTER TABLE "table_name"
DROP "column 1"

Example -

ALTER TABLE SCHMST DROP COLUMN GENDER CASCADE

The above query drops (deletes) the column GENDER from the table SCHMST

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

I would be covering SQL Joins in next blog...

Till then happy learning.