SELECT statement

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

SELECT * FROM PC;

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.