Structured Query Language

Overview:

This module enables the student to understand the concept SQL.

Outline:

Topics covered,

· SQL/400

· Creation of Table

· Updating of Table

· Deletion of Table

· Embedded SQL

Objective:

By the end of this module, the student should be able to:

Understand the concept of SQL/400
Understand the concept of DDL
Understand the concept of DML

Structured Query Language:

Structured Query Language (SQL) is an industry-standard language for defining and manipulating data contained in a relational database. It is used for all tasks to relational data management. For example: creating tables, maintain tables, querying tables for information, modifying the data, granting privileges to users, and so on.

SQL includes Data Definition Language (DDL) statements to retrieve and update the contents of a database

Characteristics of SQL

o Extremely flexible

o Free form syntax

o Each SQL statement is parsed by the RDBMS before execution, to check for syntax and to optimize the request.

o SQL statements can be written in variety of ways – e.g. Dynamic execution, Static execution embedded in an HLL program.

Dynamic execution of an SQL statement means that you construct a statement as a text string and have the SQL routines interpret and execute the text string. Thus the structure of the statement is changeable.

Verity

An alternative way of executing SQL statements known as static execution lets you code HLL variables within an SQL statement, thus providing the ability to change some of the selection between subsequent execution of the same statements. With static statement, a statement’s structure always remains the same. Only thing changes are values of the program variables.

Advantages of SQL

o While using SQL, the programming can specify what data is needed but need not specify how to retrieve it. That is, SQL is coded without data navigational instructions.

o Increased acceptance and availability of SQL enable end users to deal with different Database Management Systems including Oracle, Microsoft SQL server, and DB2 on other platforms (e.g. IBM Mainframes and Windows NT).

o The application written in SQL can be easily ported across Database Management Systems.

o SQL is independent in a way that it is implemented internally.

o Though the language is simple to use, it can handle complex queries.

o The language has strong theoretical base, and there is no ambiguity about the way a query will interpret the data and produce the result.

o SQL can be used to define data structure control access to the data and delete, insert and modify occurrences of the data. It is not merely a query language.

o SQL has set-at-a-time feature. That is one SQL statement affects more than one record.

DDL: Data Definition Language

· Tables

Tables are the basic building blocks of any RDBMS. A table consists of a row of column headings and rows (records) of data. We can create, modify and delete tables using data definition language (DDL).

o CREATE TABLE: Create table command creates a new table.

Syntax: CREATE TABLE <Table Name>(Field1, Field2,..)

e.g. CREATE TABLE EMPLOYEE (EMPNO INTEGER, EMPNM CHAR(20))

o ALTER TABLE: Alter table command modifies structure of a table. It allows user to add new field or to modify existing filed.

Syntax: ALTER TABLE <Table Name>(Field1, Field2,..)

e.g. ALTER TABLE EMPLOYEE ADD (SALARY INTEGER)

o DROP TABLE: Drop table command deletes a table. All dependent objects including views, indexes and foreign key constraints defined in other base tables that reference the table being dropped are also deleted .

Syntax: DROP TABLE <Table Name>

e.g. DROP TABLE EMPLOYEE

DML: Data Manipulation Language

The four main DML statements are:

· INSERT: You can use the INSERT statement to add new rows to a table or view one of the following ways:

o Specifying values in the INSERT statement for columns of the single row to be added.

Syntax: INSERT INTO<Table Name>VALUES(Value1,Value2….)

or INSERT INTO <Table Name> (Field1, Field2,…) VALUES (Value1,Value2,…)

e.g. INSERT INTO EMPLOYEE (EMPNO,EMPNM,SALARY) VALUES (1001, ‘SANJAY’,.’20000’)

o Specifying proper syntax to add multiple rows into the target table. A multi row insert copies data from one table to another. Although the Insert statement can add rows to only a single table, the inserted rows can be constructed from more than one table.

Syntax: INSERT INTO<Table Name>FROM(Table Name>

or INSERT INTO <Table Name> (Field1, Field2,…) SELECT (field1,Field2,…)

e.g. INSERT INTO EMPLOYEE (EMPNO,EMPNM,SALARY) SELECT EMPNO, EMPNM, SALARY FROM OLDFILE

If SQL finds an error while running the INSERT statement, it stops inserting data.

UPDATE: Update command is used to modify data in existing table. It can affect more than one record at a time. With the UPDATE statement, you can change the value of one or more columns in each row that satisfies the search condition of the WHERE clause. The result of the UPDATE statement is one or more changed column values in zero or more rows of a table (depending on how many rows satisfy the search condition specified in the WHERE clause). Use the SET clause to specify a new value for each column you want to update. The SET clause names the columns you want updated and provides the values you want them changed to. The value you specify can be:

o A column name: Replace the column’s current value with the contents of another column in the same row.

o A constant: Replace the column’s current value with the value provided in the SET clause.

o A null value: Replace the column’s current value with null value, using the keyword NULL. The column must be defined as capable of containing a null value when table is created or an error occurs.

o A host variable: Replace the column’s current value with the contents of a host variable.

o An expression: Replace the column’s current value with the value that results from an expression from an expression. The expression can contain any of the values in this list.

To identify the rows to be updated, use the WHERE clause:

o To update a single row, use a WHERE clause that selects only one row.

o To update several rows, use a WHERE clause that selects only the rows you want to update.

You can omit the WHERE clause. If you do, SQL updates each row in the table or view with the values you supply.

Syntax: UPDATE<Table Name>SET <Field Name>=<Value> WHERE <Field Name>=<Condition>

e.g. UPDATE, EMPLOYEE SET SALARY=22000 WHERE EMPNO=1001

· DELETE: Delete command deletes existing record/s from a table. The result of the DELETE statement is the removal of zero or more rows of a table (depending on how many rows satisfy the search condition specified in the WHERE clause). If you omit the WHERE clause from a DELET statement, SQL removes all the rows of the table.

Syntax: DELETE FROM <Table Name>WHERE<Field>=<Condition>

e.g. DELETE FROM EMPLOYEE WHERE EMPNO = 1001

· SELECT: Simple SELECT statement retrieves records from a database. Retrieval an be from more than one table at the same time. ‘SELECT’ statement is powerful and can take quite complex forms. Mastering it is the key to using SQL successfully.

Select statement is composed of three main clauses:

o The SELECT clause which specifies those columns containing the desired data. You can specify that only column be retrieved, or as many as 8000 columns. The value of each column you name is retrieved in the order specified in the SELECT clause. If you want to retrieve all columns (in the same order as they appear in the row), use an asterisk (*) instead of naming the columns.

o The FROM clause, which specifies the table or tables containing the columns with the desired data.

o The WHERE clause, which supplies conditions that determine which rows of data are retrieved.

SQL allows you to get information from columns contained in more than one table. This operation is called a join operation. In SQL, a join operation is specified by placing the names of those tables you want to join together into the same FROM clause of a SELECT statement. Whenever the same column name exists in two or more tables being joined, the column name must be qualified by the table name to specify which column is really being referenced.

When SQL evaluates a select statement, several rows might qualify to be in the result table, depending on the number of rows that satisfy the select-statement’s search condition. Some of the rows in the result table might be duplicates. You can specify that you do not want any duplicates by using the DISTINCT keyword, followed by the list os column names. DISTINCT means you want to select only the unique rows.

Syntax: SELECT[*/ <Field List>FROM<Table Name>

Asterisk (*) will display all the fields (columns) in that table.

e.g. SELECT *FROM EMPLOYEE

or SELECT EMPNO, EMPNM FROM EMPLOYEE

· Predicates

An SQL predicate is a logical condition that is true, false or unknown for a given row. A predicate is unknown if it involves a comparison and one or both of the values being compared is null. A Select statement’s result table contains only those rows for which the WHERE clause search condition is true. If the search condition is false or unknown, the row is omitted

· WHERE clause

WHERE clause is used in SELECT statement to retrieve records, which satisfy a given condition.

Syntax: SELECT FROM <Table Name>WHERE<Condition>

e.g. SELECT FROM EMPLOYEE WHERE EMPNO = 0101

The simplest WHERE clause contains an SQL search condition that is a single SQL predicate, as above.

A WHERE clause with a primary key column lets you use SQL for single row operations. The WHERE clause also may contain a search condition that is two or more SQL predicates connected with ‘AND’ or ‘OR’.

o Multiple Search Conditions within a WHERE Clause:

You saw how to qualify a request using one search condition. You can qualify your request further by coding a search condition that includes several predicates. The search condition, is the negated value of the specified search condition. A WHERE clause can have as many predicates as you want.

AND says that, for a row to qualify, the row must satisfy both predicates of the search condition. For example to find out which employees in department D21 were hired after December 31,1987, you would specify:

WHERE WORKDEPT=’D21’AND HIREDATE>’1987-12-31’

OR says that, for a row to qualify, the row can satisfy the condition set by either or both predicates of the search condition. For example , to find out which employees are in either department C01 or D11, you could specify (1):

WHERE WORKPB01U01D =’C01’ OR WORKDEPT=’D11’

NOT says that, to qualify, a row must not meet the criteria set by the search condition or predicate that follows the NOT. For example, to find all employees in department E11 except those with a job code equal to analyst, you could specify :

WHERE WORKDEPT=’E11’ AND NOT JOB=’ANALYST’

When SQL evaluates search conditions that contain these connectors, it does so in a specific order. SQL first evaluates the NOT clauses, next evaluates the AND clauses, and then the OR clauses. You can change the order of evaluation by using parentheses. The search conditions enclosed in parentheses are evaluated first. For example, to select all employees in departments E11 and E21 who have education levels greater than 12,you could specify:

WHERE EDLEVEL>12 AND

(WORKDEPT=’E11’ OR WORKDEPT=’E12’)

The parentheses determine the meaning of the search condition. In this example, you want all rows that have a :

WORKDEPT value of E11 or E12, and

EDLEVEL value greater than 12

If you did not use parentheses:

WHERE EDLEVEL > 12 AND WORKDEPT=’E11’ OR WORKDEPT=’E21’

Your result is different. The selected rows are rows that have:

WORKDEPT =E11 and EDLEVEL.12, or

WORKDEPT=E21, regardless of the EDLEVEL value

(1) You could also use IN to specify this request: WHERE WORKDEPT IN (‘C01’. ‘D11).

Keywords for Use in Search Conditions

A search condition can contain any of the keywords BETWEEN, AND, …, IN, IS NULL, and LIKE.

BETWEEN ..AND..is used to specify a search condition that is satisfied by any value that falls on or between two other values. For example. To find all employees who were hired in 1987, you could use this:

…WHERE HIREDATE.>=,’1987-01-01’AND’ 1987-12-31’

IN says you are interested in rows in which the value of the specified expression is among the values you listed. Fort example, to find the names of all employees in departments A00, C01, and E21,you could specify:

…WHERE WORKDEPT IN (‘A00’, ‘C01’, ‘E21’)

LIKE says you are interested in rows in which column value is similar to the value you supply. The value on the left is an expression of character or graphic data. The value on the right must be character data if the expression on string, or host variable on, the right must be graphic. It can be a graphic string or a graphic host variable.

When you use LIKE, SQL searches for a character string similar to the one you specify. The degree of similarity is determined by special character % used in the string that you include in the search condition:

%. A percent sign stands for an unknown string of 0 or more characters. If the percent sign starts the search string, then SQL allows 0 or more character(s) to precede the matching, value in the column. Otherwise, the search string must begin in the first position of the column.

….WHERE CITY LIKE ‘Na%’.

How to work with SQL:

Write command STRSQL on command line and press enter as given below:

Work with Members Using PDM S653278B

File . . . . . . MYSQL

Library . . . . PB01U01S Position to . . . . .

Type options, press Enter.

2=Edit 3=Copy 4=Delete 5=Display 6=Print 7=Rename

8=Display description 9=Save 13=Change text 14=Compile 15=Create module...

Opt Member Type Text

(No records in file)

Parameters or command

===> STRSQL

F3=Exit F4=Prompt F5=Refresh F6=Create

F9=Retrieve F10=Command entry F23=More options F24=More keys

Enter SQL Statements

Type SQL statement, press Enter.

> rollback d1

Token D1 was not valid. Valid tokens: WORK HOLD.

> rollback d1

Token D1 was not valid. Valid tokens: WORK HOLD.

> rollback d1

Token D1 was not valid. Valid tokens: WORK HOLD.

> rollback d1

Token D1 was not valid. Valid tokens: WORK HOLD.

Session was saved and started again.

Current connection is to relational database S653278B.

> SELECT

Prompting was cancelled by the user.

===> SELECT

Bottom

F3=Exit F4=Prompt F6=Insert line F9=Retrieve F10=Copy line

F12=Cancel F13=Services F24=More keys

PRESS F4

Specify SELECT Statement

Type SELECT statement information. Press F4 for a list.

FROM files . . . . . . . . . PB01U01D/EMP

SELECT fields . . . . . . . *

WHERE conditions . . . . . .

GROUP BY fields . . . . . .

HAVING conditions . . . . .

ORDER BY fields . . . . . .

FOR UPDATE OF fields . . . .

Bottom

Type choices, press Enter.

DISTINCT records in result file . . . . . . . . N Y=Yes, N=No

UNION with another SELECT . . . . . . . . . . . N Y=Yes, N=No

Specify additional options . . . . . . . . . . . N Y=Yes, N=No

F3=Exit F4=Prompt F5=Refresh F6=Insert line F9=Specify subquery

F10=Copy line F12=Cancel F14=Delete line F15=Split line F24=More keys

HIT ENTER, Which shows message ’Query Running’ and executes all records in Physical File.

Display Data

Data width . . . . . . : 18

Position to line . . . . . Shift to column . . . . . .

....+....1....+...

ENO ENM ADDR

101 ABC NNN

102 GGG HHH

104 KKK RRR

******** End of data ********

Bottom

F3=Exit F12=Cancel F19=Left F20=Right F21=Split

Specify SELECT Statement

Type SELECT statement information. Press F4 for a list.

FROM files . . . . . . . . . PB01U01D /EMP

SELECT fields . . . . . . . ENO,ENM

WHERE conditions . . . . . . ENO=101

GROUP BY fields . . . . . .

HAVING conditions . . . . .

ORDER BY fields . . . . . .

FOR UPDATE OF fields . . . .

Bottom

Type choices, press Enter.

DISTINCT records in result file . . . . . . . . N Y=Yes, N=No

UNION with another SELECT . . . . . . . . . . . N Y=Yes, N=No

Specify additional options . . . . . . . . . . . N Y=Yes, N=No

F3=Exit F4=Prompt F5=Refresh F6=Insert line F9=Specify subquery

F10=Copy line F12=Cancel F14=Delete line F15=Split line F24=More keys

HIT ENTER WHICH GIVES YOU FOLLOWING OUTPUT

Display Data

Data width . . . . . . : 11

Position to line . . . . . Shift to column . . . . . .

....+....1.

ENO ENM

101 ABC

******** End of data ********

Bottom

F3=Exit F12=Cancel F19=Left F20=Right F21=Split

Now how to delete record from table,

Specify DELETE Statement

Type choices, press Enter.

File . . . . . . . . . . . EMP Name, F4 for list

Library . . . . . . . . PB01U01D Name, F4 for list

Correlation . . . . . . . Name

Type WHERE conditions, press Enter.

ENO = 104

Bottom

Type choices, press Enter.

WITH isolation level . . . 1 1=Current level, 2=NC (NONE)

3=UR (CHG), 4=CS, 5=RS (ALL)

6=RR

F3=Exit F4=Prompt F5=Refresh F6=Insert line F9=Specify subquery

F10=Copy line F12=Cancel F14=Delete line F15=Split line F24=More keys

Which shows result as given below,

Enter SQL Statements

Type SQL statement, press Enter.

> SELECT * FROM EMP

SELECT statement run complete.

> SELECT * FROM EMP/PB01U01D

PB01U01D in EMP type *FILE not found.

> SELECT * FROM PB01U01D/EMP

SELECT statement run complete.

> SELECT ENO,ENM FROM PB01U01D/EMP

SELECT statement run complete.

> SELECT ENO,ENM FROM PB01U01D/EMP WHERE ENO=101

SELECT statement run complete.

> DELETE FROM PB01U01D/EMP WHERE ENO=104

1 rows deleted from EMP in PB01U01D .

===>

Bottom

F3=Exit F4=Prompt F6=Insert line F9=Retrieve F10=Copy line

F12=Cancel F13=Services F24=More keys

How to insert a record in Physical File:

Enter SQL Statements

Type SQL statement, press Enter.

> SELECT * FROM PB01U01D/EMP

SELECT statement run complete.

> SELECT ENO,ENM FROM PB01U01D/EMP

SELECT statement run complete.

> SELECT ENO,ENM FROM PB01U01D/EMP WHERE ENO=101

SELECT statement run complete.

> DELETE FROM PB01U01D/EMP WHERE ENO=104

1 rows deleted from EMP in PB01U01D .

Session was saved and started again.

Current connection is to relational database S653278B.

> DELETE FROM PB01U01D/EMP WHERE eno < 100 and eno >102

Row not found for DELETE.

===> INSERT

Bottom

F3=Exit F4=Prompt F6=Insert line F9=Retrieve F10=Copy line

F12=Cancel F13=Services F24=More keys

Press F4:

Specify INSERT Statement

Type choices, press Enter.

INTO file . . . . . . . . EMP Name, F4 for list

Library . . . . . . . . PB01U01D Name, F4 for list

Select fields to insert

INTO . . . . . . . . . N Y=Yes, N=No

Insertion method . . . . 1 1=Input VALUES

2=Subselect

Type choices, press Enter.

WITH isolation level . . 1 1=Current level, 2=NC (NONE)

3=UR (CHG), 4=CS, 5=RS (ALL)

6=RR

F3=Exit F4=Prompt F5=Refresh F12=Cancel F20=Display entire name

F21=Display statement

PRESS ENTER

Specify INSERT Statement

Type values to insert, press Enter.

Field Value

ENO 103

ENM ‘DUKE’

ADDR ‘PUNE’

Bottom

F3=Exit F5=Refresh F6=Insert line F10=Copy line F11=Display type

F12=Cancel F14=Delete line F15=Split line F24=More keys

HIT ENTER

Enter SQL Statements

Type SQL statement, press Enter.

> INSERT INTO PB01U01D/EMP VALUES(103, WWW, RRR)

Column RRR not in specified tables.

> INSERT INTO PB01U01D/EMP VALUES(103, WWW, RRR)

Column RRR not in specified tables.

> INSERT INTO PB01U01D/EMP VALUES(103, WWW)

Column WWW not in specified tables.

> INSERT INTO PB01U01D/EMP VALUES(103)

Statement contains wrong number of values.

> INSERT INTO PB01U01D/EMP VALUES(103, 'RR', 'PP')

1 rows inserted in EMP in PB01U01D .

> INSERT INTO PB01U01D/EMP VALUES(104, 'DUKE', 'PUNE')

1 rows inserted in EMP in PB01U01D .

===>

Bottom

F3=Exit F4=Prompt F6=Insert line F9=Retrieve F10=Copy line

F12=Cancel F13=Services F24=More keys

Check that above record is inserted in EMP, use select statement on command line to check it,

‘SELECT * FROM PB01U01D/EMP’

Following is the output

Display Data

Data width . . . . . . : 18

Position to line . . . . . Shift to column . . . . . .

....+....1....+...

ENO ENM ADDR

101 ABC NNN

102 GGG HHH

103 RR PP

104 DUKE PUNE

******** End of data ********

Bottom

F3=Exit F12=Cancel F19=Left F20=Right F21=Split

How to update record from Physical file,

Enter SQL Statements

Type SQL statement, press Enter.

> RUNQRY

Token RUNQRY was not valid. Valid tokens: ( END SET CALL DROP FREE H

> RUNQURY

Token RUNQURY was not valid. Valid tokens: ( END SET CALL DROP FREE

> RUNQRY

Token RUNQRY was not valid. Valid tokens: ( END SET CALL DROP FREE H

> SELECT * FROM PB01U01D/EMP

SELECT statement run complete.

> UPDATE PB01U01D/EMP SET ENM = 'SSS' WHERE ENO =104

1 rows updated in EMP in PB01U01D .

> UPDATE

Prompting was cancelled by the user.

===> UPDATE

Bottom

F3=Exit F4=Prompt F6=Insert line F9=Retrieve F10=Copy line

F12=Cancel F13=Services F24=More keys

PRESS F4

Specify UPDATE Statement

Type choices, press Enter.

File . . . . . . . . . EMP Name, F4 for list

Library . . . . . . PB01U01D Name, F4 for list

Correlation . . . . . Name

F3=Exit F4=Prompt F5=Refresh F12=Cancel F20=Display entire name

F21=Display statement

PRESS ENTER

Specify UPDATE Statement

Type choices, press Enter.

File . . . . . . . . . EMP Name, F4 for list

Library . . . . . . PB01U01D Name, F4 for list

Correlation . . . . . Name

Type information, press Enter.

Field Value

ENO

ENM 'TTT'

ADDR

Bottom

F3=Exit F4=Prompt F5=Refresh F6=Insert line F10=Copy line

F11=Display type F12=Cancel F14=Delete line F24=More keys

HIT ENTER

Specify UPDATE Statement

Type WHERE conditions, press Enter. Press F4 for a list.

ENO = 104

Bottom

Type choices, press Enter.

WITH isolation level . . . 1 1=Current level, 2=NC (NONE)

3=UR (CHG), 4=CS, 5=RS (ALL)

6=RR

F3=Exit F4=Prompt F5=Refresh F6=Insert line F9=Specify subquery

F10=Copy line F12=Cancel F14=Delete line F15=Split line F24=More keys

PRESS ENTER

Enter SQL Statements

Type SQL statement, press Enter.

> RUNQRY

Token RUNQRY was not valid. Valid tokens: ( END SET CALL DROP FREE H

> SELECT * FROM PB01U01D/EMP

SELECT statement run complete.

> UPDATE PB01U01D/EMP SET ENM = 'SSS' WHERE ENO =104

1 rows updated in EMP in PB01U01D .

> UPDATE PB01U01D/EMP SET ENM = 'TTT' WHERE ENO = 104

1 rows updated in EMP in PB01U01D .

===>

Bottom

F3=Exit F4=Prompt F6=Insert line F9=Retrieve F10=Copy line

F12=Cancel F13=Services F24=More keys

SEE THE OUTPUT USING SELECT STATEMENT.

In last section how to create a table with constraints in SQL,

· Types of constraints:

o Domain integrity constraints: These constraints set a range and any violation that takes place will prevent the user performing the manipulation. There are two types of Domain integrity constraints.
§ Not Null: This constraint does not allow null values in a field or set of fields.

Syntax: CREATE TABLE <Table Name>(<Field><Data Type>NOT NULL, <Field 2><Data Type> NOT NULL)

e.g. CREATE TABLE EMPLOYEE (EMPNO INTEGER NOT NULL, EMPNM CHAR(25) NOT NULL)

§ Check: These constraints allow only particular set of values. Every record should satisfy the condition defined in Check constraint.

Syntax: CREATE TABLE <Table Name>(<Field><Data Type>NOT NULL, <Field 2><Data Type> CONSTRAINT<Constraint Name>CHECK (Cond.))

e.g. CREATE TABLE EMPLOYEE (EMPNO INTEGER NOT NULL, EMPNM CHAR(25) NOT NULL, SALARY INTEGER CONSTRAINT CSAL CHECK(SALARY . 15000))

o Entity integrity constraints: Entity is any data stored in a database. Each entity represents a table in a database and each record in that table represents instance of that entity. By using entity integrity constraints, each record in the table can be uniquely defined. There are two types of Entity integrity constraints.
§ Unique: This constraint does not allow duplicate values in a column or set of columns. But it allows null values. This constraint can be defined at both-column as well as row level.

Syntax: CREATE TABLE <Table Name>(<Field><Data Type>NOT NULL, <Field 2><Data Type> CONSTRAINT<Constraint Name> UNIQUE)

Or CREATE TABLE <Table Name>(<Field><Data Type>NOT NULL, <Field 2><Data Type> CONSTRAINT<Constraint Name> UNIQUE)(<Field1>))

e.g. CREATE TABLE EMPLOYEE (EMPNO INTEGER CONSTRAINT CEMP UNIQUE, EMPNM CHAR(25) NOT NULL,

Or CREATE TABLE EMPLOYEE (EMPNO INTEGER EMPNM CHAR(25) NOT NULL CONSTRAINT CEMP UNIQUE(EMPNO))

§ Primary Key: This constrain does not allow duplicate as well as null values. A table an have only one primary key. If primary key is assigned to more than one column then it is said to be composite key. Primary key can be defined at both-column as well as row level.

Syntax: CREATE TABLE <Table Name> (<Field1><Data Type> NOT NULL, <Field 2> <Data Type> CONSTRAINT <Constraint Name> PRIMARY KEY)

Or CREATE TABLE <Table Name>(<Field1><Data Type>NOT NULL, <Field 2><Data Type> CONSTRAINT<Constraint Name> PRIMARY KEY) (<Field1>))

e.g. CREATE TABLE EMPLOYEE (EMPNO INTEGER CONSTRAINT CEMP PRIMARY KEY, EMPNM CHAR(25) NOT NULL,

Or CREATE TABLE EMPLOYEE (EMPNO INTEGER EMPNM CHAR(25) NOT NULL CONSTRAINT CEMP PRIMARY KEY(EMPNO))

o Referential integrity constraints: These constraints are used to establish Parent-Child relationship between two tables having common column. To establish this relationship, a column in the parent table should be defined as primary key. and same column in the child table should be foreign key. The foreign key column in the child should refer to corresponding column in the parent table.

Syntax: CREATE TABLE <Child Table Name> (<Field1><Data Type> NOT NULL, <Field 2> <Data Type> CONSTRAINT <Constraint Name> REFERENCES<Parent Table Name(<Field>)>)

Or CREATE TABLE <Child Table Name>(<Field1><Data Type>NOT NULL, FOREIGN KEY <Field 2><Data Type> CONSTRAINT<Constraint Name> REFERENCES<Parent Table Name (<Field>)>)

e.g. CREATE TABLE EMPLOYEE (EMPNO INTEGER DEPTID EMPNM CHAR(25) CONSTRAINT CDID REFERENCES DEPT –MAST (DEPTID))

Or CREATE TABLE EMPLOYEE (EMPNO INTEGER FOREIGN KEY DEPTID CHAR(25) CONSTRAINT CDID REFERENCES DEPT-MAST (DEPTID))

· Drop Constraints: Constraints defined on a table can be dropped with the help of alter table command with Drop Constraint keyword.

Syntax:ALTER TABLE <Table Name> DROP CONSTRAINT <Constraint Name>

e.g. ALTER TABLE EMPLOYEE DROP CONSTRAINT CDID

Enter SQL Statements

Type SQL statement, press Enter.

> SELECT * FROM PB01U01D/EMP

SELECT statement run complete.

> UPDATE PB01U01D/EMP SET ENM = 'SSS' WHERE ENO =104

1 rows updated in EMP in PB01U01D .

> UPDATE

Prompting was cancelled by the user.

> UPDATE

Prompting was cancelled by the user.

> UPDATE PB01U01D/EMP SET ENM = 'TTT' WHERE ENO = 104

1 rows updated in EMP in PB01U01D .

===> CREATE

Bottom

F3=Exit F4=Prompt F6=Insert line F9=Retrieve F10=Copy line

F12=Cancel F13=Services F24=More keys

PRESS F4 and select option 5.

Select CREATE Statement

Select one of the following:

1. CREATE ALIAS

2. CREATE COLLECTION

3. CREATE INDEX

4. CREATE PROCEDURE

5. CREATE TABLE

6. CREATE VIEW

Selection

F3=Exit F12=Cancel

PRESS ENTER AND FILL THE FOLLOWING INFORMATION-

Specify CREATE TABLE Statement

Type information, press Enter.

File . . . . . . . . . . DEPT Name

Library . . . . . . . PB01U01D Name, F4 for list

Nulls: 1=NULL, 2=NOT NULL, 3=NOT NULL WITH DEFAULT

Field FOR Field Type Length Scale Nulls

DID NUMERIC 3 0 3

DNM CHAR 5 3

HOD CHAR 7 3

Bottom

File CONSTRAINT . . . . . . . . . . . . . Y Y=Yes, N=No

Distributed File . . . . . . . . . . . . . N Y=Yes, N=No

F3=Exit F4=Prompt F5=Refresh F6=Insert line F10=Copy line

F11=Display more attributes F12=Cancel F14=Delete line F24=More keys

PRESS ENTER

Specify CREATE TABLE Statement

Type constraint option, press Enter.

1=Add 2=Change 4=Drop

Opt Type Constraint Fields

Bottom

F3=Exit F5=Refresh F12=Cancel F20=Display entire name

F21=Display statement

No constraints defined, press Enter to add a constraint

PRESS ENTER

Specify CREATE TABLE Statement

Type constraint information, press Enter.

Type . . . . . . . . 1 1=PRIMARY KEY, 2=UNIQUE

3=FOREIGN KEY, 4=CHECK

Constraint . . . . . PRIMARY KEY Name, Blank to generate

F3=Exit F4=Prompt F5=Refresh F12=Cancel F20=Display entire name

F21=Display statement

PRESS ENTER

Specify CREATE TABLE Statement

Type constraint information, press Enter.

Type . . . . . . . . 1 1=PRIMARY KEY, 2=UNIQUE

3=FOREIGN KEY, 4=CHECK

Constraint . . . . . Name, Blank to generate

Fields . . . . . . . DID Name, F4 for list

Bottom

F3=Exit F4=Prompt F5=Refresh F6=Insert line F10=Copy line

F12=Cancel F14=Delete line F20=Display entire name F24=More keys

PRESS ENTER TO GET FOLLOWING SCREEN

Specify CREATE TABLE Statement

Type constraint option, press Enter.

1=Add 2=Change 4=Drop

Opt Type Constraint Fields

PRIMARY KEY DID

Bottom

F3=Exit F5=Refresh F12=Cancel F20=Display entire name

F21=Display statement

HIT ENTER

Enter SQL Statements

Type SQL statement, press Enter.

> UPDATE PB01U01D/EMP SET ENM = 'TTT' WHERE ENO = 104

1 rows updated in EMP in PB01U01D.

> CREATE

Prompting was cancelled by the user.

> CREATE TABLE PB01U01D /DEPT (DID NUMERIC (3 , 0) NOT NULL WITH DEFAULT,

DNM CHAR (5 ) NOT NULL WITH DEFAULT, HOD CHAR (7 ) NOT NULL WITH

DEFAULT, PRIMARY KEY (DID))

Table DEPT in PB01U01D created but could not be journaled.

===>

Bottom

F3=Exit F4=Prompt F6=Insert line F9=Retrieve F10=Copy line

F12=Cancel F13=Services F24=More keys

NOW CHECK THE TABLE USING SELECT QUERY.

In this way try insert, update and delete command on DEPT1 table.

Ø Functions

A function is an operation denoted by a function name followed by one or more operands, which are enclosed in parentheses. The operands of functions are called arguments. Most functions have a single argument that is specified by an expression. The result of a function is a single value derived by applying the function to the result of the expression.

· General rules

o For AVG and SUM functions, arguments must be of type numeri

o Except COUNT (*) the argument may be preceded by the key word DISTINCT to eliminate

duplicate records before the function is actually applied to the selected field. The default value is ALL.

o The function COUNT (*) is used in all records without any duplicate elimination and hence

DISTINCT is not applicable to this function.

o Any Nulls in the selected field are eliminated before the function is applied regardless of whether

DISTINCT is used or not. But in case of COUNT (*) all the Nulls are treated as normal values.

o In case of computers using ASCII character set, the order is Digits, Uppercase letters, Lowercase letters, while in case of computers, using EBCDIC query using ORDER BY clause, with MAX and MIN functions, can produce different results in the two systems.

· Common functions

o COUNT() and COUNT(*):COUNT () function is used to count number of values in a field, while COUNT (*) function is used to count number of records of the query result.

Syntax: SELECT COUNT(*/Field Name) FROM <Table Name> WHERE <Condition> GROUP BY <Field Name>

e.g. SELECT COUNT(*) FROM EMPLOYEE

or SELECT COUNT (EMPNO)FROM EMPLOYEE WHERE DEPT=’ACC’

or SELECT COUNT (EMPNO) FROM EMPLOYEE GROUP BY DEPT.

o SUM: SUM function is used to calculate sum of values in a field.

Syntax: SELECT SUM(Field Name) FROM <Table Name> WHERE <Condition> GROUP BY <Field Name>

e.g. SELECT COUNT (SALARY)FROM EMPLOYEE WHERE DEPT=’ACC’

or SELECT COUNT (SALARY) FROM EMPLOYEE GROUP BY DEPT.

o AVG: AVG function is used to calculate

Syntax: SELECT AVG (<Field Name>) FROM <Table Name> WHERE <Condition> GROUP BY <Field Name>

e.g. SELECT AVG (SALARY)FROM EMPLOYEE WHERE DEPT=’ACC’

or SELECT AVG (SALARY) FROM EMPLOYEE GROUP BY DEPT.

o MAX:MAX function is used to find minimum of value in a field.

Syntax: SELECT MAX (<Field Name>) FROM <Table Name> WHERE <Condition> GROUP BY <Field Name>

e.g. SELECT MIN (SALARY)FROM EMPLOYEE WHERE DEPT=’ACC’

or SELECT MIN (SALARY) FROM EMPLOYEE GROUP BY DEPT.

Above assignment completes the simple command of SQL/400. Following section includes the embedded SQL i.e. SQL in RPG programming.

Embedded SQL:

Unrestricted dynamic execution of Insert, Update or Delete is dangerous. Ad hoc entry of statements can be error prone. More over situations in an application may arise where a single SQL command or a set of SQL commands alone will not be able to meet the requirements. Some such situations are:

Reading data from a file and conditionally using it to insert/update rows.
Processing data from multiple files to produce a report.
Utilizing user input to query.

Traditional programming languages are better equipped to handle such requirements as they support constructs for control flow. Report formatting and screen handling etc. that are not supported by SQL.

‘Embedded SQL’ in conventional programming languages provides SQL statements from HLL: You code all embedded SQL statement in C spec and before each SQL statement; you code /EXEC SQL in positions 7 through 15. After each SQL statement you code /END-EXEC in delimiters must have a + in position 7.

In SQL, a program variable coded in an embedded SQL statement is referred to as host variable. Host variables should be declared before the first use in SQL and always should start with a ‘:’ within a SQL The syntax of an SQL statement, when using interactive SQL, differs slightly from the format of the same statement when it is embedded in a program.

SQL statements can be embedded in high-level languages, dynamically prepared and run, or run interactively.

For a static SELECT statement (one embedded in an SQL program), an INTO clause must be specified before the FROM clause. The INTO clause names the host variables (variables in your program used to contain retrieved column values). The value of the first result column specified in the SELECT clause is put into the first host variable named in the INTO clause; the second value is put into the second host variable, and so on.

The select-statement embedded in the INSERT statement is no different from the select-statement you use to retrieve data.

Syntax for Embedded SQL is

/EXEC SQL

----------------

/END-EXEC

Following is the example for select statement in embedded SQL

Physical file name : EMP

RPG Program name : PM1

Source Type : SQLRPG

Description : ‘Simple SQL program’

Step 1: EMP is Physical file

Columns . . . : 1 71 Edit PB01U01D/MYSQL

SEU==> EMP

*************** Beginning of data *************************************

0000.01 A R EMPR

0001.00 A ENO 3S 0

0003.00 A ENM 5A

0003.01 A ADDR 5A

0004.00 A K ENO

****************** End of data ****************************************

Step 2: In embedded SQL RPG program for select statement is given below-

Columns . . . : 1 71 Edit PB01U01D/MYSQL

SEU==> PM1

*************** Beginning of data *************************************

0001.00 C Z-ADD50 N1 30

0002.00 C MOVEL*BLANK NM 5

0002.01 C MOVEL*BLANK AD 5

0003.00 *

0004.00 C/EXEC SQL

0005.00 C+ SELECT ENM,ADDR INTO :NM,:AD FROM EMP WHERE ENO = 102

0006.00 C/END-EXEC

0007.01 C NM DSPLY

0007.02 C AD DSPLY

0008.00 C SETON LR

****************** End of data ****************************************

In above example we are defining variables N1, NM, AD to move or select values from physical file and make it displayed using simple RPG.

Using a Cursor:

When SQL runs a select statement, the resulting rows comprise the result table. A cursor provides a way access a result table. It is used within an SQL program to maintain a position in the result table. SQL uses a cursor to work with the rows in the result table although each must have a unique name.

A DECLARE CURSOR statement to open and close the cursor for use within the program. The cursor must be opened before any rows can be retrieved.

A FETCH statement to retrieve rows from the cursor’s result table or to position the cursor on another row. The step is iterated till all rows are fetched and processed.

An UPDATE… WHERE CURRENT OF statement to update the current row of a cursor.

A DELETE … WHERE CURRENT OF statement to delete the current row of a cursor.

Steps for using a cursor:

Define the cursor: The DECLARE CURSOR statement names a cursor and specifies a select-statement. The select-statement defines a set of rows that, conceptually, make up the result table.

/EXEC SQL

+ DECLARE cursor-name CURSOR FOR

+SELECT column-1, column- 2 …

+ FROM table-name , …

+ WHERE … (optional)

/END-EXEC

Open the Cursor: To begin processing the rows of the result table, issue the

OPEN statement. When your program issues the OPEN statement, SQL processes the select – statement within the DECLARE CURSOR statement to identify a set of rows, called a result table (1), using the current value of any host variables specified in the select statement.

/EXEC SQL

+ OPEN cursor – name

/END–EXEC

Retrieve a Row Using a Cursor: To move the contents of a selected row into your program’s host variables use the FETCH statement. The SELECT statement within the DECLARE CURSOR statement identifies rows that contain the column values your program wants. However, SQL does not retrieve any data for your application program until the FETCH statement is issued.

When your program issues the FETCH statement, SQL uses the current cursor position as a starting point to locate the requested row in the result table. This changes that row to the current row. If an INTO clause was specified, SQL moves the current row’s contents into your program’s host variables. This sequence is repeated each time the FETCH statement is issued. SQL maintains the position of the current row (that is, the cursor points to the current row) until the next FETCH statement for the cursor is issued.

/EXEC SQL

+ FETCH cursor – name

+ INTO : host variable -1 [, : Host variable – 2] …

/END – EXEC

Specify What to Do When End- of – Data Is Reached: To find out when the end of the result table is reached, test the SQLCOD field for a value of 100 or test the SQLSTATE field for a value of ‘02000’ (that is, end – of – data). This condition and your program issues a subsequent FETCH. Your program should anticipate an end- of – data condition whenever a cursor is used to fetch a row, and should be prepared to handle this situation when it occurs.

Close the Cursor: If you processed the rows of a result table for a serial cursor, and you want to use the cursor again, issue a CLOSE statement to close the cursor when the job ends.

SQLCOD

The database manager sets the SQLCOD values after each SQL statement is executed. An application can check the SQLCOD value to determine whether the last SQL statement was successful. SQL does not communicate directly with end user but rather returns error codes to the application program when an error occurs.

There are many other SQL messages, but they are not listed here. Detailed descriptions of all DB2 for OS/400 messages, including SQLCODE s, are available on-line and can be displayed and printed from the Display Message Description display. You can access this display by using the CL command Display Message Description (DSPMSGD).

SQLCODE are returned in the SQLCA (SQL communication area) structure. SQLSTATE is an additional return code that provides application programs with common return codes for common error conditions found among the IBM relational, database systems. Every SQLCOD has a corresponding message in message file QSQLMSG in library QSYS.

Status of an embedded SQL execution is returned in a variable SQLCOD.

SQLCOD value 0 = successful execution
SQLCOD value 100 = not found
Negative SQLCOD value = error.

Code related to CURSOR:

Columns . . . : 1 71 Edit PB01U01S/MYSQL

SEU==> PM3

FMT FX .....FFilenameIPEAF........L..I........Device+......KExit++Entry+A....U

*************** Beginning of data *************************************

0000.02 FDSP1 CF E WORKSTN

0000.03 *

0000.04 C/EXEC SQL

0000.05 C+ DECLARE C1 CURSOR FOR

0000.06 C+ SELECT ENO,ENM,ADDR FROM EMP

0000.07 C/END-EXEC

0000.08 *

0000.09 C/EXEC SQL

0000.10 C+ OPEN C1

0000.11 C/END-EXEC

0000.12 *

0000.13 C SQLCOD DOWEQ0

0000.14 *

0000.15 C/EXEC SQL

0000.16 C+ FETCH C1 INTO :NO, :NM, :AD

0000.17 C/END-EXEC

0000.18 *

0000.19 C EXFMTREC3

0000.20 C SQLCOD IFEQ 100

0000.21 C GOTO A

0000.22 C ENDIF

0000.23 C ENDDO

0000.24 *

0000.25 C/EXEC SQL

0000.26 C+ CLOSE C1

****************** End of data ****************************************

F3=Exit F4=Prompt F5=Refresh F12=Cancel F13=How to use this display

F24=More keys

AS/400 Forum

Thursday, December 12, 2013

Day-22.

Structured Query Language

Outline:

Using a Cursor:

No comments:

Post a Comment