You can query a single table in a database in many ways. You can tailor a
SELECT statement to perform the following actions:
- Retrieve all or specific columns
- 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
kpvarkeys 