SQL data manipulation language

SELECT statement

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

Console

Execute

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

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

    Scheme

Console

Execute

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

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

    Scheme

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).

Console

Execute

SELECT *
FROM PC
ORDER BY 3;

SELECT * FROM PC ORDER BY 3;

    Scheme

Sorting by two columns

Console

Execute

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

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

    Scheme

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:

Console

Execute

SELECT DISTINCT speed, ram 
FROM PC 
WHERE price < 500 
ORDER BY 2 DESC;

SELECT DISTINCT speed, ram FROM PC WHERE price < 500 ORDER BY 2 DESC;

    Scheme

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: