loading..
Русский    English
23:26

Simple SELECT statement page 1

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

Console
Execute
  1. 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:

Console
Execute
  1. SELECT price, speed, hd, ram, cd, model, code
  2. FROM PC;

Here is the result set of this query.

price speed hd ram Cd model code
600 500 5 64 12x 1232 1
850 750 14 128 40x 1121 2
600 500 5 64 12x 1233 3
850 600 14 128 40x 1121 4
850 600 8 128 40x 1121 5
950 750 20 128 50x 1233 6
400 500 10 32 12x 1232 7
350 450 8 64 24x 1232 8
350 450 10 32 24x 1232 9
350 500 10 32 12x 1260 10
980 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:

Console
Execute
  1. SELECT speed, ram
  2. 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:

Console
Execute
  1. SELECT DISTINCT speed, ram
  2. 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.

Bookmark and Share
Pages 1 2 3
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema date/time functions DATEADD DATEDIFF DATENAME DATEPART DATETIME DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates equi-join EXCEPT exercise (-2) exercise 19 More tags
The book was updated
yesterday
photographer Johannesburg . website
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100