SELECT statement retrieves rows from the database and has the most complex
structure among other SQL statements. Almost any database user is capable of writing
a simplest SELECT statement such as
which retrieves all records from the table-type object PC; in so doing rows and columns of the
result set have no order. To order columns of the result set they should be listed
and separated by commas in the required order after the SELECT keyword:
SELECT price, speed, hd, ram, cd, model, code
FROM Pc; |
Here is the result set of this query.
| price |
speed |
hd |
ram |
cd |
model |
code |
| 600.0 |
500 |
5 |
64 |
12x |
1232 |
1 |
| 850.0 |
750 |
14 |
128 |
40x |
1121 |
2 |
| 600.0 |
500 |
5 |
64 |
12x |
1233 |
3 |
| 850.0 |
600 |
14 |
128 |
40x |
1121 |
4 |
| 850.0 |
600 |
8 |
128 |
40x |
1121 |
5 |
| 950.0 |
750 |
20 |
128 |
50x |
1233 |
6 |
| 400.0 |
500 |
10 |
32 |
12x |
1232 |
7 |
| 350.0 |
450 |
8 |
64 |
24x |
1232 |
8 |
| 350.0 |
450 |
10 |
32 |
24x |
1232 |
9 |
| 350.0 |
500 |
10 |
32 |
12x |
1260 |
10 |
| 980.0 |
900 |
40 |
128 |
40x |
1233 |
11 |
The vertical projection of the РC table is obtained by listing the necessary fields
only. For example, to get information about the processor speed and the amount of RAM
in the computer run the following query:
| SELECT speed, ram FROM Pc; |
which returns the data:
| speed |
ram |
| 500 |
64 |
| 750 |
128 |
| 500 |
64 |
| 600 |
128 |
| 600 |
128 |
| 750 |
128 |
| 500 |
32 |
| 450 |
64 |
| 450 |
32 |
| 500 |
32 |
| 900 |
128 |
It should be noted that a vertical sample may include duplicate rows
in case where the sample does not include any potential key with the values
uniquely identify each row in the table. In the PC table, the code field is a
potential key, which is specified in addition as primary key.
Since this field is not included in the query, there are listed some
duplicate rows in the above result set (for example, rows 1 and 3).
If unique rows are needed (say, we only need different
combinations of processor speed and RAM amount, not specifications of all available
PCs), use the DISTINCT keyword:
| SELECT DISTINCT speed, ram FROM Pc; |
Here's the result set:
| speed |
ram |
| 450 |
32 |
| 450 |
64 |
| 500 |
32 |
| 500 |
64 |
| 600 |
128 |
| 750 |
128 |
| 900 |
128 |
Apart from DISTINCT, the ALL keyword, which explicitly ask for all rows, may also
be applicable. However, ALL keyword is accepted by default.
It is possible to sort out the result set by a number of columns pointed out in the
SELECT statement. For this purpose, the clause ORDER BY <list of fields> is used
which is always the latest clause in the SELECT statement.
In so doing, the sort column in list of fields may be specified as a name or a nonnegative
integer representing the position of the name in SELECT list. For example, to sort
the result set by RAM in descending order we can write
SELECT DISTINCT speed, ram
FROM Pc
ORDER BY ram DESC |
or
SELECT DISTINCT speed, ram
FROM Pc
ORDER BY 2 DESC |
The following result is the same.
| speed |
ram |
| 600 |
128 |
| 750 |
128 |
| 900 |
128 |
| 450 |
64 |
| 500 |
64 |
| 450 |
32 |
| 500 |
32 |
The result set can be sorted in ascending order (ASC is assumed by
default) or in descending order (DESC). Sorting by two columns
SELECT DISTINCT speed, ram
FROM Pc
ORDER BY ram DESC, speed DESC |
gives the following result:
| speed |
ram |
| 900 |
128 |
| 750 |
128 |
| 600 |
128 |
| 500 |
64 |
| 450 |
64 |
| 500 |
32 |
| 450 |
32 |
Horizontal restriction is realized by the clause WHERE <predicate>
after the FROM clause. Now the result set will only include the rows from the
record source for each of those the predicate returns TRUE. In other words,
the predicate for each row is checked . For example, the query "get information about processor's speed
and RAM amount for computers priced below $500" can be written as follows:
SELECT DISTINCT speed, ram
FROM Pc
WHERE price<500
ORDER BY 2 DESC |
| speed |
ram |
| 450 |
64 |
| 450 |
32 |
| 500 |
32 |
The latter query uses a comparison predicate with operator "<" (less than).
Beside this operator, the following operators may be used: "=" (equal), ">" (greater than),
">="(greater or equal), "<=" (less or equal) and "<>" (not equal). Expressions
in comparison predicates may include any columns from the tables listed in the
FROM clause. Character strings and date/time constants are enclosed in single quotation marks.
Here are some examples of simple comparison predicates:
| price < 1000 |
Price is less than $1000. |
| type = 'laptop' |
Product type is Laptop. |
| cd = '24x' |
24-speed CD-ROM. |
| color <>'y' |
Not-color printer. |
| ram - 128 >0 |
RAM amount is over 128 Mb. |
| price <= speed*2 |
Price does not exceed twice processor's speed. |
Suggested exercises:
2, 3, 4, 42.