Multiple Table Queries -Select statement examples

 

Multiple-Table queries

 

You can make multiple-table queries shorter and more readable by assigning

aliases to the tables in a SELECT statement. An alias is a word that immediately

follows the name of a table in the FROM clause. You can use it wherever

the table name would be used, for instance, as a prefix to the column names

in the other clauses.

Query

SELECT s.stock_num, s.manu_code, s.description,

s.unit_price, s.unit, c.catalog_num,

c.cat_descr, c.cat_advert, m.lead_time

FROM stock s, catalog c, manufact m

WHERE s.stock_num = c.stock_num

AND s.manu_code = c.manu_code

AND s.manu_code = m.manu_code

AND s.manu_code IN (‘HRO’, ‘HSK’)

AND s.stock_num BETWEEN 100 AND 301

ORDER BY catalog_num

 

 

.

 

Query

SELECT stock.stock_num, stock.manu_code, stock.description,

stock.unit_price, stock.unit, catalog.catalog_num,

catalog.cat_descr, catalog.cat_advert,

manufact.lead_time

FROM stock, catalog, manufact

WHERE stock.stock_num = catalog.stock_num

AND stock.manu_code = catalog.manu_code

AND stock.manu_code = manufact.manu_code

AND stock.manu_code IN (‘HRO’, ‘HSK’)

AND stock.stock_num BETWEEN 100 AND 301

ORDER BY catalog_num

 

 

Query Results

stock_num 110

manu_code HRO

description helmet

unit_price $260.00

unit case

catalog_num 10033

lead_time 4

stock_num 110

manu_code HSK

description helmet

unit_price $308.00

unit each

catalog_num 10034

cat_descr

Aerodynamic (teardrop) helmet covered with anti-drag fabric.

Credited with shaving 2 seconds/mile from winner’s time in

Tour de France time-trial. 7.5 oz.

cat_advert Teardrop Design Endorsed by Yellow Jerseys,

You Can Time the Difference

lead_time 5

stock_num 205

manu_code HRO

description 3 golf balls

unit_price $312.00

unit each

catalog_num 10048

cat_descr

Combination fluorescent yellow and standard white.

cat_advert HiFlier Golf Balls: Case Includes Fluorescent

Yellow and Standard White

lead_time 4

stock_num 301

manu_code HRO

description running shoes

unit_price $42.50

unit each

catalog_num 10050

cat_descr

Engineered for serious training with exceptional stability.

Fabulous shock absorption. Great durability. Specify

mens/womens, size.

cat_advert Pronators and Supinators Take Heart: A Serious

Training Shoe For Runners Who Need Motion Control

lead_time 4

Query

SELECT order_num, lname, fname, phone

FROM masterdb@central:customer c, sales@western:orders o

WHERE c.customer_num = o.customer_num

AND order_num <= 1010

By assigning the aliases c and o to the long database@system:table names,

masterdb@central:customer and sales@western:orders, respectively, you

can use the aliases to shorten the expression in the WHERE clause and retrieve

the data as Query Result 2-79 shows.

Query Result

order_num lname fname phone

1001 Higgins Anthony 415-368-1100

1002 Pauli Ludwig 408-789-8075

1003 Higgins Anthony 415-368-1100

1004 Watson George 415-389-8789

1005 Parmelee Jean 415-534-8822

1006 Lawson Margaret 415-887-7235

1007 Sipes Arnold 415-245-4578

1008 Jaeger Roy 415-743-3611

1009 Keyes Frances 408-277-7245

1010 Grant Alfred 415-356-1123

Composing Simple SELECT Statements

 

Some Query Shortcuts

The INTO TEMP Clause

By adding an INTO TEMP clause to your SELECT statement, you can

temporarily save the results of a multiple-table query in a separate table that

you can query or manipulate without modifying the database. Temporary

tables are dropped when you end your SQL session or when your program or

report terminates.

Query  creates a temporary table called stockman and stores the results

of the query in it. Because all columns in a temporary table must have names,

the alias adj_price is required.

Query

SELECT DISTINCT stock_num, manu_name, description,

unit_price, unit_price * 1.05 adj_price

FROM stock, manufact

WHERE manufact.manu_code = stock.manu_code

INTO TEMP stockman

You can query on this table and join it with other tables, which avoids a

multiple sort and lets you move more quickly through the database.

.

stock_num manu_name description unit_price adj_price

Query Result

1 Hero baseball gloves $250.00 $262.5000

1 Husky baseball gloves $800.00 $840.0000

1 Smith baseball gloves $450.00 $472.5000

2 Hero baseball $126.00 $132.3000

3 Husky baseball bat $240.00 $252.0000

4 Hero football $480.00 $504.0000

4 Husky football $960.00 $1008.0000

…

306 Shimara tandem adapter $190.00 $199.5000

307 ProCycle infant jogger $250.00 $262.5000

308 ProCycle twin jogger $280.00 $294.0000

309 Hero ear drops $40.00 $42.0000

309 Shimara ear drops $40.00 $42.0000

310 Anza kick board $84.00 $88.2000

310 Shimara kick board $80.00 $84.0000

311 Shimara water gloves $48.00 $50.4000

312 Hero racer goggles $72.00 $75.6000

312 Shimara racer goggles $96.00 $100.8000

313 Anza swim cap $60.00 $63.0000

313 Shimara swim cap $72.00 $75.6000

 

INFORMIX SQL – Select statement – Selecting specific columns

Selecting Specific Columns

 

Often all you want to see is the data in one or more specific columns.

Again, the formula is to use the SELECT and FROM clauses, specify the

columns and table, and perhaps order the data in ascending or descending

order with an ORDER BY clause.

 

Selecting Specific Columns

If you want to find all the customer numbers in the orders table, use a statement

such as the one in Query 2-12.

Query 2-12

SELECT customer_num FROM orders

Query Result 2-12 shows how the statement simply selects all data in the

customer_num column in the orders table and lists the customer numbers on

all the orders, including duplicates.

The output includes several duplicates because some customers have placed

more than one order. Sometimes you want to see duplicate rows in a projection.

At other times, you want to see only the distinct values, not how often

each value appears.

Query Result 2-12

customer_num

104

101

104

106

106

112

117

110

111

115

104

117

104

106

110

119

120

121

122

123

124

126

127

l

Selecting Specific Columns

To suppress duplicate rows, include the keyword DISTINCT or its synonym

UNIQUE at the start of the select list, as Query 2-13 shows.

Query 2-13

SELECT DISTINCT customer_num FROM orders

SELECT UNIQUE customer_num FROM orders

 

To produce a more readable list, Query 2-13 limits the display to show each

customer number in the orders table only once, as Query Result 2-13 shows.

customer_num Query Result 2-13

101

104

106

110

111

112

115

116

117

119

120

121

122

123

124

126

127

 

Selecting Specific Columns

Suppose you are handling a customer call, and you want to locate purchase

order number DM354331. To list all the purchase order numbers in the orders

table, use a statement such as the one that Query 2-14 shows.

Query 2-14

SELECT po_num FROM orders

Query Result 2-14 shows how the statement retrieves data in the po_num

column in the orders table.

Query Result 2-14

po_num

B77836

9270

B77890

8006

2865

Q13557

278693

LZ230

4745

429Q

B77897

278701

B77930

8052

MA003

PC6782

DM354331

S22942

Z55709

W2286

C3288

W9925

KF2961

 

Selecting Specific Columns

However, the list is not in a very useful order. You can add an ORDER BY

clause to sort the column data in ascending order and make it easier to find

that particular po_num, as Query Result 2-15 shows.

 

Query 2-15

SELECT po_num FROM orders

ORDER BY po_num

po_num Query Result 2-15

278693

278701

2865

429Q

4745

8006

8052

9270

B77836

B77890

B77897

B77930

C3288

DM354331

KF2961

LZ230

MA003

PC6782

Q13557

S22942

W2286

W9925

Z55709

 

 

Selecting Specific Columns

To select multiple columns from a table, list them in the select list in the

SELECT clause. Query 2-16 shows that the order in which the columns are

selected is the order in which they are produced, from left to right.

 

Query 2-16

SELECT paid_date, ship_date, order_date,

customer_num, order_num, po_num

FROM orders

ORDER BY paid_date, order_date, customer_num

 

you can use the ORDER BY clause to sort the data in ascending or descending order and

perform nested sorts. Query Result 2-16 shows ascending order.

 

Query Result 2-16 paid_date ship_date order_date customer_num order_num po_num

05/30/1994 05/22/1994 106 1004 8006

05/30/1994 112 1006 Q13557

06/05/1994 05/31/1994 117 1007 278693

06/29/1994 06/18/1994 117 1012 278701

07/12/1994 06/29/1994 119 1016 PC6782

07/13/1994 07/09/1994 120 1017 DM354331

06/03/1994 05/26/1994 05/21/1994 101 1002 9270

06/14/1994 05/23/1994 05/22/1994 104 1003 B77890

06/21/1994 06/09/1994 05/24/1994 116 1005 2865

07/10/1994 07/03/1994 06/25/1994 106 1014 8052

07/21/1994 07/06/1994 06/07/1994 110 1008 LZ230

07/22/1994 06/01/1994 05/20/1994 104 1001 B77836

07/31/1994 07/10/1994 06/22/1994 104 1013 B77930

08/06/1994 07/13/1994 07/10/1994 121 1018 S22942

08/06/1994 07/16/1994 07/11/1994 122 1019 Z55709

08/21/1994 06/21/1994 06/14/1994 111 1009 4745

08/22/1994 06/29/1994 06/17/1994 115 1010 429Q

08/22/1994 07/25/1994 07/23/1994 124 1021 C3288

08/22/1994 07/30/1994 07/24/1994 127 1023 KF2961

08/29/1994 07/03/1994 06/18/1994 104 1011 B77897

08/31/1994 07/16/1994 06/27/1994 110 1015 MA003

09/02/1994 07/30/1994 07/24/1994 126 1022 W9925

09/20/1994 07/16/1994 07/11/1994 123 1020 W2286

 

Selecting Specific Columns

When you use SELECT and ORDER BY on several columns in a table, you

might find it helpful to use integers to refer to the position of the columns in

the ORDER BY clause.The statements in Query 2-17 retrieve and display the

same data, as Query Result 2-17 shows.

Query 2-17

SELECT customer_num, order_num, po_num, order_date

FROM orders

ORDER BY 4, 1

SELECT customer_num, order_num, po_num, order_date

FROM orders

ORDER BY order_date, customer_num

Query Result 2-17

customer_num order_num po_num order_date

104 1001 B77836 05/20/1994

101 1002 9270 05/21/1994

104 1003 B77890 05/22/1994

106 1004 8006 05/22/1994

116 1005 2865 05/24/1994

112 1006 Q13557 05/30/1994

117 1007 278693 05/31/1994

110 1008 LZ230 06/07/1994

111 1009 4745 06/14/1994

115 1010 429Q 06/17/1994

104 1011 B77897 06/18/1994

117 1012 278701 06/18/1994

104 1013 B77930 06/22/1994

106 1014 8052 06/25/1994

110 1015 MA003 06/27/1994

119 1016 PC6782 06/29/1994

120 1017 DM354331 07/09/1994

121 1018 S22942 07/10/1994

122 1019 Z55709 07/11/1994

123 1020 W2286 07/11/1994

124 1021 C3288 07/23/1994

126 1022 W9925 07/24/1994

127 1023 KF2961 07/24/1994

 

Selecting Specific Columns

You can include the DESC keyword in the ORDER BY clause when you assign

integers to column names, as Query 2-18 shows.

Query 2-18

SELECT customer_num, order_num, po_num, order_date

FROM orders

ORDER BY 4 DESC, 1

In this case, data is first sorted in descending order by order_date and in

ascending order by customer_num.

ORDER BY and Non-English Data

By default, Informix database servers use the U.S. English language environment,

called a locale, for database data. The U.S. English locale specifies data

sorted in code-set order. This default locale uses the ISO 8859-1 code set.

If your database contains non-English data, the ORDER BY clause should

return data in the order appropriate to that language. Query 2-19 uses a

SELECT statement with an ORDER BY clause to search the table, abonnés, and

to order the selected information by the data in the nom column.

Query 2-19

SELECT numéro,nom,prénom

FROM abonnés

ORDER BY nom;

The collation order for the results of this query can vary, depending on the

following system variations:

n Whether the nom column is CHAR or NCHAR data type. The

database server sorts data in CHAR columns by the order the

characters appear in the code set. The database server sorts data in

NCHAR columns by the order the characters are listed in the collation

portion of the locale. Store non-English data in NCHAR (or

NVARCHAR) columns to obtain results sorted by the language.

n Whether the database server is using the correct non-English locale

when accessing the database. To use a non-English locale, you must

set the CLIENT_LOCALE and DB_LOCALE environment variables to

the appropriate locale name.

GLS

Selecting Specific Columns

For Query 2-19 to return expected results, the nom column should be NCHAR

data type in a database that uses a French locale. Other operations, such as

less than, greater than, or equal to, are also affected by the user-specified

locale. Refer to the Guide to GLS Functionality for more information on non-

English data and locales.

Query Result 2-19a and Query Result 2-19b show two sample sets of output.

Query Result 2-19a follows the ISO 8859-1 code-set order, which ranks uppercase

letters before lowercase letters and moves the names that start with an

accented character (Ålesund, Étaix, Ötker, and Øverst) to the end of the list.

numéro nom prénom

Query Result 2-19a

13612 Azevedo Edouardo Freire

13606 Dupré Michèle Françoise

13607 Hammer Gerhard

13602 Hämmerle Greta

13604 LaForêt Jean-Noël

13610 LeMaître Héloïse

13613 Llanero Gloria Dolores

13603 Montaña José Antonio

13611 Oatfield Emily

13609 Tiramisù Paolo Alfredo

13600 da Sousa João Lourenço Antunes

13615 di Girolamo Giuseppe

13601 Ålesund Sverre

13608 Étaix Émile

13605 Ötker Hans-Jürgen

13614 Øverst Per-Anders

Query Result 2-19b

numéro nom prénom

13601 Ålesund Sverre

13612 Azevedo Edouardo Freire

13600 da Sousa João Lourenço Antunes

13615 di Girolamo Giuseppe

13606 Dupré Michèle Françoise

13608 Étaix Émile

13607 Hammer Gerhard

13602 Hämmerle Greta

13604 LaForêt Jean-Noël

13610 LeMaître Héloïse

13613 Llanero Gloria Dolores

13603 Montaña José Antonio

13611 Oatfield Emily

13605 Ötker Hans-Jürgen

13614 Øverst Per-Anders

13609 Tiramisù Paolo Alfredo

 

 

Selecting Specific Columns

Query Result 2-19b shows that when the appropriate locale file is referenced

by the data server, names starting with non-English characters (Ålesund,

Étaix, Ötker, and Øverst) are collated differently than they are in the ISO

8859-1 code set. They are sorted correctly for the locale. It does not distinguish

between uppercase and lowercase letters. ¨

Selecting Substrings

To select part of the value of a CHARACTER column, include a substring in the

select list. Suppose your marketing department is planning a mailing to your

customers and wants a rough idea of their geographical distribution based

on zip codes. You could write a query similar to the one Query 2-20 shows.

Query 2-20

SELECT zipcode[1,3], customer_num

FROM customer

ORDER BY zipcode

 

Using the WHERE Clause

Query 2-20 uses a substring to select the first three characters of the zipcode

column (which identify the state) and the full customer_num, and lists them

in ascending order by zip code, as Query Result 2-20 shows.

Using the WHERE Clause

Add a WHERE clause to a SELECT statement if you want to see only those

orders that a particular customer placed or the calls that a particular customer

service representative entered.

You can use the WHERE clause to set up a comparison condition or a join

condition. This section demonstrates only the first use. Join conditions are

described in a later section and in the next chapter.

The set of rows returned by a SELECT statement is its active set. A singleton

SELECT statement returns a single row. Use a cursor to retrieve multiple rows

in INFORMIX-4GL or an SQL API. In NewEra, you use a SuperTable to handle

multiple-row retrieval. See Chapter 5, “Programming with SQL,” and

Chapter 6, “Modifying Data Through SQL Programs.”

Query Result 2-20

zipcode customer_num

021 125

080 119

085 122

198 121

322 123

604 127

740 124

802 126

850 128

850 120

940 105

940 112

940 113

940 115

940 104

940 116

940 110

940 114

940 106

940 108

940 117

940 111

940 101

940 109

941 102

943 103

943 107

946 118

————————————————————————————————————-

 

Single-Table SELECT Statements-Informix SQL (contd)

You can query a single table in a database in many ways. You can tailor a

SELECT statement to perform the following actions:

1. Retrieve all or specific columns
2. Retrieve all or specific rows

3. Perform computations or other functions on the retrieved data

4. Order the data in various ways

 

Selecting All Columns and Rows

The most basic SELECT statement contains only the two required clauses,

SELECT and FROM.

 

Using the Asterisk Symbol (*)

Query 2-5a specifies all the columns in the manufact table in a select list. A

select list is a list of the column names or expressions that you want to project from a table.

 

 

Query 2-5a

SELECT manu_code, manu_name, lead_time

FROM manufact

Query 2-5b uses the wildcard asterisk symbol (*), which is shorthand for the

select list. The * represents the names of all the columns in the table. You can use the asterisk symbol (*) when you want all the columns, in their defined order.

 

Query 2-5b

SELECT * FROM manufact

 

Composing Simple SELECT Statements 2-11

Selecting All Columns and Rows

Query 2-5a and Query 2-5b are equivalent and display the same results; that is, a list of every column and row in the manufact table.

Reordering the Columns

Query 2-6 shows how you can change the order in which the columns are

listed by changing their order in your select list.

Query 2-6

SELECT manu_name, manu_code, lead_time

FROM manufact

Query Result 2-6 includes the same columns as the previous query result, but because the columns are specified in a different order, the display is also different.

 

Query Result 2-5 manu_code manu_name lead_time

SMT Smith 3

ANZ Anza 5

NRG Norge 7

HSK Husky 5

HRO Hero 4

SHM Shimara 30

KAR Karsten 21

NKL Nikolus 8

PRC ProCycle 9

Query Result 2-6 manu_name manu_code lead_time

Smith SMT 3

Anza ANZ 5

Norge NRG 7

Husky HSK 5

Hero HRO 4

Shimara SHM 30

Karsten KAR 21

Nikolus NKL 8

ProCycle PRC 9

 

Selecting All Columns and Rows

Sorting the Rows

You can add an ORDER BY clause to your SELECT statement to direct the system to sort the data in a specific order.You must include the columns that you want to use in the ORDER BY clause in the select list either explicitly or implicitly.

 

An explicit select list, shown in Query 2-7a, includes all the column names

that you want to retrieve.

Query 2-7a

SELECT manu_code, manu_name, lead_time

FROM manufact

ORDER BY lead_time

An implicit select list uses the asterisk symbol (*), as Query 2-7b shows.

Query 2-7b

SELECT * FROM manufact

ORDER BY lead_time

Query 2-7a and Query 2-7b produce the same display. Query Result 2-7

shows a list of every column and row in the manufact table, in order of

lead_time.

Ascending Order

The retrieved data is sorted and displayed, by default, in ascending order.

Ascending order is uppercase A to lowercase z for CHARACTER data types,

and lowest to highest value for numeric data types. DATE and DATETIME

data is sorted from earliest to latest, and INTERVAL data is ordered from

shortest to longest span of time.

Query Result 2-7

manu_code manu_name lead_time

SMT Smith 3

HRO Hero 4

HSK Husky 5

ANZ Anza 5

NRG Norge 7

NKL Nikolus 8

PRC ProCycle 9

KAR Karsten 21

SHM Shimara 30

 

 

 

 

 

Composing Simple SELECT Statements 2-11

Selecting All Columns and Rows Descending Order

Descending order is the opposite of ascending order, from lowercase z to

uppercase A for character types and highest to lowest for numeric data types.

DATE and DATETIME data is sorted from latest to earliest, and INTERVAL data is ordered from longest to shortest span of time. Query 2-8 shows an example of descending order.

Query 2-8

SELECT * FROM manufact

ORDER BY lead_time DESC

The keyword DESC following a column name causes the retrieved data to be

sorted in descending order, as Query Result 2-8 shows.

You can specify any column (except TEXT or BYTE) in the ORDER BY clause,

and the database server sorts the data based on the values in that column.

Sorting on Multiple Columns

You can also ORDER BY two or more columns, creating a nested sort. The

default is still ascending, and the column that is listed first in the ORDER BY

clause takes precedence.

manu_code manu_name lead_time

Query Result 2-8

SHM Shimara 30

KAR Karsten 21

PRC ProCycle 9

NKL Nikolus 8

NRG Norge 7

HSK Husky 5

ANZ Anza 5

HRO Hero 4

SMT Smith 3

 

Selecting All Columns and Rows

Query 2-9 and Query 2-10 and corresponding query results show nested

sorts. To modify the order in which selected data is displayed, change the

order of the two columns that are named in the ORDER BY clause.

Query 2-9

SELECT * FROM stock

ORDER BY manu_code, unit_price

In Query Result 2-9, the manu_code column data appears in alphabetical

order and, within each set of rows with the same manu_code (for example,

ANZ, HRO), the unit_price is listed in ascending order.

Query Result 2-9

stock_num manu_code description unit_price unit unit_descr

5 ANZ tennis racquet $19.80 each each

9 ANZ volleyball net $20.00 each each

6 ANZ tennis ball $48.00 case 24 cans/case

313 ANZ swim cap $60.00 box 12/box

201 ANZ golf shoes $75.00 each each

310 ANZ kick board $84.00 case 12/case

301 ANZ running shoes $95.00 each each

304 ANZ watch $170.00 box 10/box

110 ANZ helmet $244.00 case 4/case

205 ANZ 3 golf balls $312.00 case 24/case

8 ANZ volleyball $840.00 case 24/case

302 HRO ice pack $4.50 each each

309 HRO ear drops $40.00 case 20/case

…

113 SHM 18-spd, assmbld $685.90 each each

5 SMT tennis racquet $25.00 each each

6 SMT tennis ball $36.00 case 24 cans/case

1 SMT baseball gloves $450.00 case 10 gloves/case

Composing Simple SELECT Statements 2-11

Selecting All Columns and Rows

Query 2-10 shows the reversed order of the columns in the ORDER BY clause.

Query 2-10

SELECT * FROM stock

ORDER BY unit_price, manu_code

In Query Result 2-10, the data appears in ascending order of unit_price and,

where two or more rows have the same unit_price (for example, $20.00,

$48.00, $312.00), the manu_code is in alphabetical order.

Query Result 2-10

stock_num manu_code description unit_price unit unit_descr

302 HRO ice pack $4.50 each each

302 KAR ice pack $5.00 each each

5 ANZ tennis racquet $19.80 each each

9 ANZ volleyball net $20.00 each each

103 PRC frnt derailleur $20.00 each each

106 PRC bicycle stem $23.00 each each

5 SMT tennis racquet $25.00 each each

…

301 HRO running shoes $42.50 each each

204 KAR putter $45.00 each each

108 SHM crankset $45.00 each each

6 ANZ tennis ball $48.00 case 24 cans/case

305 HRO first-aid kit $48.00 case 4/case

303 PRC socks $48.00 box 24 pairs/box

311 SHM water gloves $48.00 box 4 pairs/box

…

110 HSK helmet $308.00 case 4/case

205 ANZ 3 golf balls $312.00 case 24/case

205 HRO 3 golf balls $312.00 case 24/case

205 NKL 3 golf balls $312.00 case 24/case

1 SMT baseball gloves $450.00 case 10 gloves/case

4 HRO football $480.00 case 24/case

102 PRC bicycle brakes $480.00 case 4 sets/case

111 SHM 10-spd, assmbld $499.99 each each

112 SHM 12-spd, assmbld $549.00 each each

7 HRO basketball $600.00 case 24/case

203 NKL irons/wedge $670.00 case 2 sets/case

113 SHM 18-spd, assmbld $685.90 each each

1 HSK baseball gloves $800.00 case 10 gloves/case

8 ANZ volleyball $840.00 case 24/case

4 HSK football $960.00 case 24/case

 

Selecting Specific Columns

The order of the columns in the ORDER BY clause is important, and so is the

position of the DESC keyword. Although the statements in Query 2-11 contain the same components in the ORDER BY clause, each produces a different result (not shown).

Query 2-11

SELECT * FROM stock

ORDER BY manu_code, unit_price DESC

SELECT * FROM stock

ORDER BY unit_price, manu_code DESC

SELECT * FROM stock

ORDER BY manu_code DESC, unit_price

SELECT * FROM stock

ORDER BY unit_price DESC, manu_code

 

RELATIONAL OPERATION -SELECT STATEMENT

 

Relational Operations

 

A relational operation involves manipulating one or more tables, or relations, to result in another table. The three kinds of relational operations are selection,projection, and join. This chapter includes examples of selection, projection,and simple joining.

Selection and Projection

In relational terminology, selection is defined as taking the horizontal subset of rows of a single table that satisfies a particular condition. This kind of SELECT

statement returns some of the rows and all of the columns in a table. Selection is implemented through the WHERE clause of a SELECT statement, as Query

2-1 shows.

Query 2-1

SELECT * FROM customer

WHERE state = ‘NJ’

 

Query Result 2-1 contains the same number of columns as the customer table,but only a subset of its rows. Because the data in the selected columns does

not fit on one line of the DB-Access or INFORMIX-SQL Interactive Schema Editor (ISED) screen, the data is displayed vertically instead of horizontally.

 

Query Result 2-1

customer_num 119

fname Bob

lname Shorter

company The Triathletes Club

address1 2405 Kings Highway

address2

city Cherry Hill

state NJ

zipcode 08002

phone 609-663-6079

customer_num 122

fname Cathy

lname O‘Brian

company The Sporting Life

address1 543d Nassau

address2

city Princeton

state NJ

zipcode 08540

phone 609-342-0054

 

Composing Simple SELECT Statements

 

Some Basic Concepts

In relational terminology, projection is defined as taking a vertical subset from the columns of a single table that retains the unique rows. This kind of SELECT statement returns some of the columns and all of the rows in a table.

 

Projection is implemented through the select list in the SELECT clause of a SELECT statement, as Query 2-2 shows.

Query 2-2

SELECT UNIQUE city, state, zipcode

FROM customer

 

Query Result 2-2 contains the same number of rows as the customer table,but it projects only a subset of the columns in the table.

 

Query Result 2-2

city state zipcode

Bartlesville OK 74006

Blue Island NY 60406

Brighton MA 02135

Cherry Hill NJ 08002

Denver CO 80219

Jacksonville FL 32256

Los Altos CA 94022

Menlo Park CA 94025

Mountain View CA 94040

Mountain View CA 94063

Oakland CA 94609

Palo Alto CA 94303

Palo Alto CA 94304

Phoenix AZ 85008

Phoenix AZ 85016

Princeton NJ 08540

Redwood City CA 94026

Redwood City CA 94062

Redwood City CA 94063

San Francisco CA 94117

Sunnyvale CA 94085

Sunnyvale CA 94086

Wilmington DE 19898

 

l

 

Some Basic Concepts

 

The most common kind of SELECT statement uses both selection and projection. A query of this kind, shown in Query 2-3, returns some of the rows and some of the columns in a table.

 

Query 2-3

SELECT UNIQUE city, state, zipcode

FROM customer

WHERE state = ‘NJ’

 

Query Result 2-3 contains a subset of the rows and a subset of the columns in the customer table.

Query Result 2-3

city state zipcode

 

Cherry Hill NJ 08002

Princeton NJ 08540

 

Joining

A join occurs when two or more tables are connected by one or more columns in common, creating a new table of results. The query in the example uses a subset of the items and stock tables to illustrate the concept of a join, as shows.

 

Query 2-4 joins the customer and state tables.

 

Query 2-4

SELECT UNIQUE city, state, zipcode, sname

FROM customer, state

WHERE customer.state = state.code

A Join Between Two Tables

SELECT unique item_num, order_num, stock.stock_num, description

FROM items, stock

WHERE items.stock_num = stock.stock_num

item_num order_num stock_num

1 1001 1

1 1002 4

2 1002 3

3 1003 5

1 1005 5

items Table (example)

stock_num manu_code description

1 HRO baseball gloves

1 HSK baseball gloves

2 HRO baseball

4 HSK football

5 NRG tennis racquet

stock Table (example)

item_num order_num stock_num description

1 1001 1 baseball gloves

1 1002 4 football

3 1003 5 tennis racquet

1 1005 5 tennis racquet

 

The Forms of SELECT Query Result 2-4 consists of specified rows and columns from both the customer and state tables.

The Forms of SELECT

Although the syntax remains the same across all Informix products, the form of a SELECT statement and the location and formatting of the resulting output depends on the application.

 

Query Result 2-4

city state zipcode sname

Bartlesville OK 74006 Oklahoma

Blue Island NY 60406 New York

Brighton MA 02135 Massachusetts

Cherry Hill NJ 08002 New Jersey

Denver CO 80219 Colorado

Jacksonville FL 32256 Florida

Los Altos CA 94022 California

Menlo Park CA 94025 California

Mountain View CA 94040 California

Mountain View CA 94063 California

Oakland CA 94609 California

Palo Alto CA 94303 California

Palo Alto CA 94304 California

Phoenix AZ 85008 Arizona

Phoenix AZ 85016 Arizona

Princeton NJ 08540 New Jersey

Redwood City CA 94026 California

Redwood City CA 94062 California

Redwood City CA 94063 California

San Francisco CA 94117 California

Sunnyvale CA 94085 California

Sunnyvale CA 94086 California

Wilmington DE 19898 Delaware

Special Data Types

The examples in this use the INFORMIX-OnLine Dynamic Server database server, which enables database applications to include the data types VARCHAR, TEXT, and BYTE.

With the DB-Access or INFORMIX-SQL Interactive Editor, when you issue a SELECT statement that includes one of these three data types, the results of the query are displayed differently.

If you execute a query on a VARCHAR column, the entire VARCHAR value is displayed, just as CHARACTER values are displayed.

If you select a TEXT column, the contents of the TEXT column are displayed, and you can scroll through them.

If you query on a BYTE column, the words <BYTE value> are displayed instead of the actual value.

 

SELECT STATEMENT -..Continued

SELECT STATEMENT –  Continued

 In a ISQL select statement only SELECT and FROM statements are mandatory. Other clauses like

WHERE,GROUP BY,HAVING,ORDER BY are optional.

    How to use expressions in ISQL SELECT Statement?

Expressions combine many arithmetic operators, they can be used in SELECT, WHERE and ORDER BY Clauses of the ISQL SELECT Statement.

Here we will explain how to use expressions in the SQL SELECT Statement. About using expressions in WHERE and ORDER BY clause, they will be explained in their respective sections.

The operators are evaluated in a specific order of precedence, when more than one arithmetic operator is used in an expression. The order of evaluation is: parentheses, division, multiplication, addition, and subtraction. The evaluation is performed from the left to the right of the expression.

For example: If we want to display the desc and stk_qty combined together, the ISQL Select Statement would be like

SELECT desc,” “,stk_qty from m_stock;

Output:

Desc                                                  Stk_qty

Paint		1000
MS Sheet		2000
Rubber Gasket		5000
Welding rode		500
Cotton waste		100

 

 

ISQL COMMANDS …Continued

ISQL commands are instructions used to communicate with the database to perform specific task that work with data.  Normally SQL commands are grouped into four major categories depending on their functionality:

Data Definition Language (DDL) – These SQL commands are used for creating, modifying, and dropping the structure of database objects. The commands are CREATE, ALTER, DROP, RENAME, and TRUNCATE.

• Data Manipulation Language (DML) – These SQL commands are used for storing, retrieving, modifying, and deleting data. These commands are SELECT, INSERT, UPDATE, and DELETE.
• Transaction Control Language (TCL) – These SQL commands are used for managing changes affecting the data. These commands are COMMIT, ROLLBACK, and SAVEPOINT.
• Data Control Language (DCL)– These SQL commands are used for providing security to database objects. These commands are GRANT and REVOKE.// //

ISQL SELECT Statement

The most commonly used ISQL command is SELECT statement. The ISQL SELECT statement is used to query or retrieve data from a table in the database. A query may retrieve information from specified columns or from all of the columns in the table. To create a simple ISQL SELECT Statement, you must specify the column(s) name and the table name. The whole query is called SQL SELECT Statement.

Syntax of  ISQL SELECT Statement:

SELECT column_list FROM table-name
[WHERE Clause]
[GROUP BY clause]
[HAVING clause]
[ORDER BY clause];

• table-name is the name of the table from which the information is retrieved.
• column_list includes one or more columns from which data is retrieved.
• The code within the brackets is optional.

          Simple select statement

• Select id,item_cd,desc,unit_cd,stk_qty,stk_rate from m_stock where item_code <= “1010005””
• Order by item_code

The output of the above select statment :-

id	Item_code	desc	Unit_cd	Stk_qty	Stk_rate
100	1010001	Paint	01	1000	50.00
101	1010002	MS   Sheet	02	2000	1000.00
102	1010003	Rubber   Gasket	03	5000	100.00
103	1010004	Welding   rode	03	500	30.00
104	1010005	Cotton   waste	04	100	30.00

 

INFORMIX SQL -Introduction

INFORMIX-SQL

Informix-SQL is the relational database management system that developers choose to create custom applications. Based on RDSQL,Informix SQL has the tools to create create  and maintain databases,design custom screens and menus and produce custom formatted reports. It also provides an interactive,application-building environment that permits interactive manipulation of database using the SQL language. Through the menu-creating features of INFORMIX-4GL ,your 4gl programs can invoke applications built with INFORMIX-SQL and make them part of a broader application. INFORMIX SQL can enhance your programming with INFORMIX-4gl by providing you the ability to test your database queries and operations from the Programmer’s Environment through the use of the interactive query language.

Informix SQL is compatabile with standard SQL by default. Informix SQL utility menu ísql’  has menu driven user friendly feature as below so that the ordinary user can create a Query,create database,create table,create form,indexes,create report forms,drop database,drop table etc,database information(privileges etc)

For Full practical trials we create a database called stores and tables called m_stock,m_trans,m_order,customer.

Some of important  Concepts /statements are:-

Select,

Insert,

Updata,

                   Delete

…Wait for next session……

INFORMIX 4GL SAMPLE REPORT PROGRAM

Reports

Getting information out of the database and formatting it for printing remains a central purpose for most database applications. One of the reports in the demonstration application creates mailing labels for selected customer rows.That have been ordered by zip code. The corresponding program excerpts consist of two parts:the function print_labels that is called from the REPORT Menu and selects the data and non-procedural report labels_report that describes how the data should be formatted.

FUNCTION print_labels()

Define where_part Char(200),

query_text char(250),

file_name char(20)

display form customer

call clear_menu()

display “Customer Labels:” at 1,1

message user query by example to select customer list”

CONSTRUCT where_part on customer.* from customer.*

let query_text=”select  *  from customer where “,where_part clipped,

“ order by zipcode”

prepare statement_1 from query_text

declare label_list cursor for statement_1

clear screen

prompt “Enter file name for labels >” for file_name

clear screen

message “Printing mailing labels to”,filename clipped,”—-Please Wait”

start report labels_report to file_name

foreach label_list into p_customer.*

output to report labels_report(p_customer.*)

end foreach

finish report labels_report

message “Labels printed to “, file_name clipped

end function

After  the  query  by example processing,the program prompts the user to enter the name of a file to contain the labels. It then displays a message that the labels are being printed. The report writing is governed by the next five lines and consists of three steps. The START REPORT statement initiates the report writing  process  and designates the file to contain the labels. The next stage is a FOREACH loop that takes one row from the  SELECT statement at a time and delivers it to the report. The last step is the  FINISH REPORT statement that handles any end of report processing.

 

REPORT labels_report (r)

Define  r  record like customer.*

Output

Top margin 0

Bottom margin 0

Page length 6

Format

On every row

Skip to top of page

print r.fname clipped

print r.company

print  r.address1

if r.address2  is  not null then

print r.address2

end  if

print  r.city clipped,”, ,” ,r.state,2 spaces,r.zipcode

end report

The report routine is named labels_report. There are two sections to this routine:the OUTPUT section and the FORMAT section. The OUTPUT section describes some output  parameters: the margins and the page length.

The FORMAT section describes how the report should be organised on the page For each row that is handed to the report, the report will skip to the top of the next page as defined in the OUTPUT section. The first row of each label contains the  first name , a space, and the last name of the customer. The second row of the label is the customer’s company and the third is the first address line.If a second address line exists, it will be the fourth line. The  last line contains,in usual manner for labels, the city, a comma and a space, the state,two spaces, and the zip code.

This is a simple report and only hints at the power of the report writing capability of INFORMIX-4GL.  You can write reports with special first-page formatting,headers on subsequent pages, and aggregate values such as percentages,sum,averages,maximums and minimums not only for the entire report but for groups of rows within the report. Your reports can be sent directly to the printer.

INFORMIX 4GL SAMPLE PROGRAM-QUERY BY EXAMPLE

Query  BY Example

There are several places in the complete application where the user must select a particular customer. Since it is not known in advance what criteria the user might have for selection, the code provides a function query_customer that allows the user to select the customer by merely filling in a form. With only a few lines of code, a program can allow the user to enter whatever data is known about the customer,display all customers  satisfying  the given criteria, and prompt the user to select one of the customers displayed. For the purpose of this overview, you can imagine that the code has displayed the form shown below:-

Customer Form

Number                     [f000                 ]

Owner                       [f001                                           ]

Company                  [f002                                           ]

Address                    [f003                                               ]

[f004                                               ]

City                           [f005                        ]   state:[a0]  Zip Code [f006]

Telephone               [f007                                                ]

The query_customer function has four sections. The first section given below defines the variables that will be used,clears the form of any entries left over from previous activities, and displays a message on the second screen line instructing the user to enter query specifications:

FUNCTION query_customer()

DEFINE where_text char(200),

query_text Char(250),

answer char(1),

chosen,exist INTEGER

CLEAR FORM

CALL clear_menu()

MESSAGE “ ENTER CRITERIA FOR SELCTION”

The second section below turns the query by example input from the user into an executable RDSQL statement in four steps:

1.   Informix-4gl constructs a string where      where_part from the user input.For the input illustrated earlier, the      CONSTRUCT statement automatically assigns the following string to      where_part.
2. The      code creates a larger string by appending where_part to the end of the      string “select * from customer where”and calls the results query_text. The      CLIPPED function removes all training blanks.
3. The      code associates the string query_text with the statement identifier      statement_1.

• The program      names customer_set as the cursor(a pointer) to the current row, if any      that results from executing the SELECT statement.

CONSTRUCT where_part ON customer.* from customer.*

Let query_text=”select * from customer where”,where-part CLIPPED

PREPARE statement_1 from query_text

DECLARE customer_set CURSOR FOR statement_1

The critical part of this program is the non-procedural CONSTRUCT statement. Like the INPUT statement, the CONSTRUCT statement allows the user to move from field to field and to enter all sorts of data,data ranges,pattern matches, and alternatives in each field. Only when the user pressed the ACCEPT key does the program control pass to the next line of code.

The third section of the query_customer function below presents the user with the rows found by executing the query.

MESSAGE “”

LET chosen = FALSE

LET exist =FALSE

FOREACH customer_set into p_customer.*

LET exist = TRUE

DISPLAY BY NAME p_customer.*

Prompt “press ‘y’ to select customer or Return to view next ustomer:”

for CHAR answer

IF answer MATCHES “[Yy]” THEN

LET chosen = TRUE

Exit FOREACH

END IF

END FOREACH

The third section begins by erasing the message line and setting two flags

To FALSE. The flag chosen signals whether the user has chosen a row,while the flag exist signals whether any rows were found at all. The FOREACH statement opens the cursor customer_set and starts a loop that

Performs a series of fetches from the database,displaying the rows returned one at a time on the screen. The user is prompted to type ‘y’ to select the displayed row or to press [RETURN] to view the next row. If the user presses ‘y’ or Y , the flag chosen is set to TRUE and the program leaves the loop. Otherwise, the loop is repeated with the next row until no more rows are left.

The final section of the query_customer function tests the flags,writes appropriate messages and returns TRUE only if the user selects a raw.

 

IF NOT exist THEN

MESSAGE “ No customer satisfies query”

Let p_customer.customer_num = NULL

RETURN FALSE

END IF

IF NOT chosen THEN

CLEAR FORM

Let p_customer.customer_num =NULL

MESSAGE “No selection made”

RETURN FALSE

END IF

RETURN TRUE

END FUNCTION

 

 

INFORMIX 4GL SAMPLE PROGRAM-statehelp function

The  Statehelp  Function

 

The statehelp function tests whether the entry in the State field is valid.The function is

Calculated automatically after the user moves the cursor out of the State field :-

FUNCTION statehelp()

Select  count(*)  into  idx from state where code =p_customer.state

If idx =1 then

Return

End  if

Open window  w_state at 8,40

With form “state_list” attribute (Border,red,FORM LINE 2)

Call   set_count(state_cnt)

Display ARRAY p_state to s_state.*

Let idx = arr_curr()

Close window w_state

Let p_customer.state = p_state(idx].code

Display by name p_customer.state attribute (Yellow)

END FUNCTION

The statehelp function uses a Select statement to test whether the current entry in the State field

Exists in a state table. If the entry exists, the function ends,otherwise the function displays the state selection window shown in earlier screen by performing the following operations:

1.  Opens a window that displays  a form containing the s_state screen array.
2. Displays the values in the p_state program array in the screen array.

The user can make a selection by moving  the cursor to a state code and pressing the ACCEPT key.