Simple 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;

SELECT price, speed, hd, ram, cd, model, code
FROM PC;

Here is the result set of this query.

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;

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:

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 non negative 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;

SELECT DISTINCT speed, ram
FROM PC
ORDER BY 2 DESC;

The following result is the same for both above queries.

The result set can be sorted in ascending order (ASC is assumed by default) or in descending order (DESC keyword).

SELECT *
FROM PC
ORDER BY 3;

Sorting by two columns

SELECT DISTINCT speed, ram
FROM PC
ORDER BY ram DESC, speed DESC;

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;

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:

Suggested exercises: 1, 2, 3, 4, 5, 6, 9, 14, 31, 33, 42.

Sorting can be accomplished by the columns absent from SELECT column-list. Naturally, these columns should be presented in the output of FROM clause. For example, to deduce the model list of PCs in the order from greatest price to lowest one, you can write

select model from PC
order by price DESC;

Notice that the price itself does not be returned by the query. Elimination of duplicates produces ambiguous situation that prevents the behaviour. Thus, the query

select DISTINCT model from PC
order by price DESC;

The same reason prevents from unerror working of the following query that uses grouping

select model from PC
group by model
order by price DESC;

But if you eject ambiguity (i.e. to do sorting by an aggregate-function value for a group), the query will work:

select model from PC
group by model
order by MAX(price) DESC;