Deal of the Day

Friday 1 July, 2011

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.



No comments:

Post a Comment