After inserting information into a table,
you can use the SELECT statement to display some or all the information
in the table.
The SELECT statement is the most complex of
all SQL statements. This statement is composed of the following main
clauses:
- The SELECT clause, which specifies those columns that contain
the data.
- The FROM clause, which specifies the table or tables that contain
the columns with the data.
- The WHERE clause, which supplies conditions that determine which
rows of data are retrieved.
In addition to these main clauses, several
other clauses that affect the final form of returned data are described
in the SQL programming and DB2® for
i SQL reference topic
collections.
- To see the values that you inserted into the
INVENTORY_LIST table, type SELECT and press F4 (Prompt).
The following display is shown.
Specify SELECT Statement
Type SELECT statement information. Press F4 for a list.
FROM tables . . . . . . . . SAMPLECOLL.INVENTORY_LIST____________________
SELECT columns . . . . . . . *____________________________________________
WHERE conditions . . . . . . _____________________________________________
GROUP BY columns . . . . . . _____________________________________________
HAVING conditions . . . . . _____________________________________________
ORDER BY columns . . . . . . _____________________________________________
FOR UPDATE OF columns . . . _____________________________________________
Bottom
Type choices, press Enter.
DISTINCT rows in result table . . . . . . . . . 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
- Type the table name in the FROM
tables field on the display. To select all columns from
the table, type * for the SELECT columns field
on the display.
- Press Enter and the statement runs to select all of the
data for all of the columns in the table. The following output is
shown.
Display Data
Data width . . . . . . : 71
Position to line . . . . . Shift to column . . . . . .
....+....1....+....2....+....3....+....4....+....5....+....6....+....7.
ITEM ITEM UNIT QUANTITY LAST NUMBER
NUMBER NAME COST ON ORDER ORDERED
HAND DATE
153047 Pencils, red 10.00 25 - 20
229740 Lined tablets 1.50 120 - 20
544931 ***UNKNOWN*** 5.00 - - 20
303476 Paper clips 2.00 100 - 20
559343 Envelopes, legal 3.00 500 - 20
291124 Envelopes, standard .00 - - 20
775298 Chairs, secretary 225.00 6 - 20
073956 Pens, black 20.00 25 - 20
******** End of data ********
F3=Exit F12=Cancel F19=Left F20=Right F21=Split
The column headings that were defined with the LABEL
ON statement are shown. The ITEM_NAME column for the third entry contains
the default value that was specified in the CREATE TABLE statement.
The QUANTITY_ON_HAND column contains a null value for the rows where
no value was inserted. The LAST_ORDER_DATE column contains all null
values because that column is not in any of the INSERT statements
and the column was not defined to have a default value. Similarly,
the ORDER_QUANTITY column contains the default value for all rows.
This
statement can be entered on the Enter SQL Statements display as:
SELECT *
FROM SAMPLECOLL.INVENTORY_LIST
- To limit the number of columns returned by the SELECT statement,
the columns you want to see must be specified. To restrict the number
of output rows returned, the WHERE clause is used. To see only the
items that cost more than 10 dollars, and only have the values for
the columns ITEM_NUMBER, UNIT_COST, and ITEM_NAME returned, type SELECT and
press F4 (Prompt). The Specify SELECT Statement display is shown.
Specify SELECT Statement
Type SELECT statement information. Press F4 for a list.
FROM tables . . . . . . . . SAMPLECOLL.INVENTORY_LIST____________________
SELECT columns . . . . . . . ITEM_NUMBER, UNIT_COST, ITEM_NAME____________
WHERE conditions . . . . . . UNIT_COST > 10.00____________________________
GROUP BY columns . . . . . . _____________________________________________
HAVING conditions . . . . . _____________________________________________
ORDER BY columns . . . . . . _____________________________________________
FOR UPDATE OF columns . . . _____________________________________________
Bottom
Type choices, press Enter.
DISTINCT rows in result table . . . . . . . . . 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
Although only one line is initially shown
for each prompt on the Specify SELECT Statement display, you can add
more lines to any of the input areas on the top part of the display
by pressing F6 (Insert line). F6 can be used if more columns need
to be entered in the SELECT columns list or if a longer, more complex
WHERE condition is needed.
- Complete the information on the display, as
shown.
- Press Enter to run the SELECT statement.
The following output is shown.
Display Data
Data width . . . . . . : 41
Position to line . . . . . Shift to column . . . . . .
....+....1....+....2....+....3....+....4.
ITEM UNIT ITEM
NUMBER COST NAME
775298 225.00 Chairs, secretary
073956 20.00 Pens, black
******** End of data ********
F3=Exit F12=Cancel F19=Left F20=Right F21=Split
The only rows returned are those whose data values satisfy
the condition specified in the WHERE clause. Furthermore, the only
data values returned are from the columns you explicitly specified
in the SELECT clause. Data values of columns other than those explicitly
identified are not returned.
This statement can be entered on the Enter SQL Statements
display as:
SELECT ITEM_NUMBER,UNIT_COST,ITEM_NAME
FROM SAMPLECOLL.INVENTORY_LIST
WHERE UNIT_COST > 10.00