SQL syntax Language 

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.





Previous | Index | Next

Home SELECT exercises (rating stages) DML exercises Developers