Data query. The result is always in the table form.
SELECT [ TOP lines | TOP percent PERCENT ] select_list
[ INTO new_table ]
[ WHERE search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]
|lines ||Limit the number of received rows.|
|percent ||Limit the number of received rows expressed in percentage (0-100).|
|select_list ||List of columns with the query results (expressions separated by comma) or the '*' character (means all columns).|
|new_table ||Name of new table that was created as a result of the query.|
|table_source ||List of tables used in the query (tables separated by comma).|
|search_condition ||Logical expression stating which rows of source tables to include into the query result (if WHERE is not stated then it means all records).|
|order_expression ||Specifies the column according to which the query result is sorted.|
|ASC ||Sorting is ascending (default)|
|DESC ||Sorting is descending|
The syntax of the SELECT statement is de facto much more complex and it contains for example clauses GROUP BY and HAVING that enable to perform group operations (summaries, averages, counts) above groups of records.
Obtains all columns for all records from the table data.
SELECT * FROM data
Obtains all columns for the first 10 records from the table data (sorted by the time).
SELECT TOP 10 * FROM data ORDER BY time
Obtains all columns for records that meet the specified condition (the time is greater than 2-Jul-2003 10:02:00) from the table data, the result is sorted ascending according to the column time. Both notations are equivalent (the time with the precision to ms can be stated in both formats).
SELECT * FROM data WHERE time>'2003-07-02 10:02:00' ORDER BY time
SELECT * FROM data WHERE time>'07/02/2003 10:02:00.000' ORDER BY time
Obtains selected columns that meet the specified condition for records (the value 'value' is in the range from 20 to 80) from the table data, the result is sorted ascending according to the column time. Both notations are equivalent.
SELECT time, value FROM data WHERE value > 20 AND value < 80 ORDER BY time
SELECT time, value FROM data WHERE value BETWEEN 20 AND 80 ORDER BY time
Obtains the number of records, the sum of values and the average value for the records that meet the specified condition from the table data where calculated columns are named by the specified names.
SELECT COUNT(*) AS Count, SUM(value) AS Suma, AVG(value) AS Average FROM data WHERE value BETWEEN 20 AND 80
Obtains the column time and the calculated column (the value 'value' is rounded to the integer number) of all records from the table data.
SELECT time, ROUND(value,0) AS Round_0 FROM data
Obtains all columns for none record (WHERE 1=0 is always invalid) from the table data and stores the result into a new table data_copy. It means that only the table structure is copied. The clause WHERE can be specified, whether and which records to copy into the new table.
SELECT * INTO data_copy FROM data WHERE 1=0