ADVANCED SQL TOPICS - IBM Mainframe

In this section we will take up those topics and commands of the Data Definition and Data Manipulation Languages, which are not discussed in the previous chapter.

Data Definition Language

As we have seen in the previous chapter the main data definition language commands are:

  • CREATE TABLE
  • DROP TABLE
  • ALTER TABLE
  • CREATE VIEW
  • DROP VIEW
  • CREATE INDEX
  • DROP INDEX

In this section we will consider the two commands related to INDEXes that is creating them and deleting them. The Data definition commands related to VIEWS will be discussed in the section on views.

Like base tables, indexes are created and dropped using the SQL DDLs. These are the only SQL statements that refer to indexes. The data manipulation Languages does not include any references to the indexes.

This is because the decision as to use a particular index in responding to a particular SQL query is made not by the user but by DB2. The general form of the CREATE INDEX statement is:

general form of the CREATE INDEX statement

The optional 'other parameters' have to do with physical storage matters as in the CREATE TABLE. Each 'order' specification is either ASC(ascending)or DSC(descending), ASC being the default. The left to right sequence of naming columns in the CREATE INDEX statement corresponds to major-to-minor ordering in the usual way.

For example, the statement 'CREATE INDEX X ON T (P, Q DSC, R)' creates and index called X on the base table T in which entries are ordered by the ascending R-value within descending Q-value within ascending R-value. The columns P, Q and R need not be contiguous, nor need they all be of the same data type, nor need they be all fixed length or varying length.

Once created the index is automatically managed by the data manager to reflect the updates on the base table, till the index is dropped.

The 'UNIQUE' option in the CREATE INDEX statement specifies that no two rows in the indexed base table will be allowed to take the same value for the indexed column or column combination at the same time, or in other words no duplicates will be allowed. DB2 will reject any attempt to introduce a duplicate value.

An attempt to create a UNIQUE index on a non-empty table, which already violates the uniqueness, will fail. Two Nulls are considered to be equal to each other for unique indexing purposes. Thus if a given column has nulls allowed, that is NOT NULL is not specified, a UNIQUE index on that column will permit at the most one null to appear at any given time.

Indexes like the base tables can be created and dropped at any time. Any number of indexes can be built on a single base table.

Indexes can be dropped explicitly using the DROP INDEX command. Whenever the base table is dropped the indexes for that table is automatically dropped. The syntax of the DROP INDEX command is: DROP INDEX index-name. The index is destroyed or removed form the catalog. If an existing package depends on the dropped index, DB2 will do the automatic recompilation of the package the next time it is invoked.

Data Manipulation Language

As seen in the previous chapter, the 4 major DML statements are SELECT, INSERT, UPDATE and DELETE. We will use the suppler and parts database. The database has three tables a suppler table, a parts table and a shipment table as follows:

Data Manipulation Language

We will start with the simple queries that we have seen in the previous chapter. The query 'get the supplier numbers and status for suppliers in Paris' will be expressed in SQL as follows:

suppliers in Paris' will be expressed in SQL as follows

Here one important thing that is to be noted is that, the result of the query is another table, a table derived from the given tables in the database. In other words, the user of a relational system is always operating in the simple tabular framework. This fact, that the result of a query is another table is referred to as the 'closure' property of the relational systems.

The closure property means that since the result of a SELECT operation is another table, it is possible to apply another SELECT operation on that result. It also means that the SELECT operations can be nested. The syntax of the SELECT statement with most of the options is given below:

syntax of the SELECT statement

The significance of each of the options will become clear as we proceed with the examples. For getting all the supplier numbers from the shipment table the SQL statement will be something like this:

SQL statement will be something like this

And the result will be as follows. Notice the duplication of the supplier numbers in the result. DB2 does not eliminate the duplicates from the result of a SELECT statement unless the user explicitly requests for that using the key word DISTINCT.

SQL statement will be something like this

Even though in this example DISTINCT is used to eliminate the duplicate scalar values distinct can be used to eliminate 'duplicate rows' also. The alternative to DISTINCT is ALL, which is also the default. Retrieval of Computed Values

The SQL statements can be used for retrieving the computed values without any problems. For example consider the following query 'For all parts get the weight of the part in grams (the weight given in the table being in pounds)'. This is achieved by the following SQL statement:

following SQL statement

The SELECT statement can also include general scalar expressions, involving scalar operators such as '+' or '-' and scalar function such as SUBSTR, instead of or as well as column names. But only the column names in the SELECT statement will give rise to name columns in the result as ca be seen from the above example. Retrieval Using * '*' is used to get all the columns of a particular table. For example the SQL, SELECT * FROM S' will give an entire copy of the table S. The star or '*' is the shorthand for the list of all column names in the table(s), in the left-to-right order in which the columns appear in the table(s). Thus the SELECT statements

SELECT statements

The '*' notation is a very useful mechanism in the case of interactive queries but potentially dangerous in the case of embedded SQLs, because the meaning of '*' may change if the SELECT statement is recompiled and some definitional change has occurred in the interim.

Qualified Retrieval

Consider the query 'Get the supplier numbers and names of the suppliers in Paris with status >20.

The SQL will be something like this:

Qualified Retrieval

The conditional expression following the WHERE clause can consist of a simple comparison or it can consist of multiple comparisons and other kinds of conditional expressions all combined together using AND, OR and NOT and parentheses if required to indicate a desired order of evaluation.

Retrieval with Ordering

For getting the result in a particular order we use the ORDER BY clause. Consider the query ' Get all the supplier details in the descending order of status'. The SQL statement will be as follows:

Retrieval with Ordering

One important thing to remember here is that, the column(s) on which the ORDER BY is specified should be part of the result table. It is possible to identify columns in the ORDER BY clause by column number instead of column name, that is by the ordinal, left-to-right, position of the column in question within the result table.

This feature makes it possible to order the result on the basis of a column, which does not have a name as in the example shown below:

order the result on the basis of a column

order the result on the basis of a column

SELECT using BETWEEN

BETWEEN can be used to get those items that fall within a range. For example consider the query' Get all the details of the parts whose weight is in the range of 14 and 17 both inclusive'. The SQL will be:

SELECT using BETWEEN

The same result can be obtained by using two individual comparisons connected together using an AND. The following SQL will also get the same result as above:

SELECT using BETWEEN

Another interesting point is that we can specify NOT BETWEEN. For example the query

NOT BETWEEN

SELECT using IN

Get the part details whose weights are 12 and 17.

SELECT using IN

Like between IN is also shorthand and the same results can be achieved by using by the following

same results can be achieved by using by the following

Another similarity between IN and BETWEEN is that here also we can specify NOT IN.

Retrieval using LIKE

Like is a very powerful clause and also very useful. For example if you want to get all the part names beginning with 'C use like as follows:

Retrieval using LIKE

In general the LIKE clause takes the form scalar-expression LIKE literal [ESCAPE character], where the scalar expression represents the value of the string. In the literal the '-' character stands for any single character, '%' stands for any sequence of n characters and all other characters stand for themselves. The following are some of the examples of the usage of LIKE:

  • NAME LIKE '%al%' - Will evaluate to true if NAME contains the string 'al' anywhere inside it.
  • S# LIKE 'S_' - Will evaluate to true if S# is exactly 3 characters long and the first letter is 'S
  • PNAME LIKE *%c_' - Will evaluate to true if PNAME is more than 2 characters long and the last but one character is 'c'.
  • NAME LIKE _%' ESCAPE 'V - Will evaluate to true if NAME begins with an underscore character

If the ESCAPE clause and a character is specified it means that, the special interpretation given to the literal characters '-' and '*%' can be disabled. For example in the last example the backslash character 'V has been specified as the ESCAPE character, which means that the special interpretation given to '-' and '%' can be disabled by preceding such characters with a backslash. NOT LIKE is also available. For example, CITY NOT LIKE '%E%' will evaluate to true if CITY does not contain an 'E'.

Operations using NULLS

For retrieving rows where some of the columns have been defined as NULLs there is a special comparison operator of the form IS [NOT] NULL.

For example suppose the STATUS of supplier SI is Null. So in order to get the supplier details we use the following SQL:

It is important to note that the syntax is ' STATUS IS NULL' and not 'STATUS = NULL' which is illegal. This is because nothing, not even null itself is considered equal to null. Another point is that it is illegal to include NULL in the SELECT clause like SELECT NULL.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

IBM Mainframe Topics