SQL Commands

This section contains a selection of important keywords that are sufficient for simple queries. Further commands can be found in the literature.

 

SELECT und FROM - Basic data query modules

SELECT *

FROM <table>

The asterisk (*) in SELECT * instructs the database to return all columns of the table specified in the FROM clause. The order of the columns in the output corresponds to the display in the database.

If only certain columns are to be output, they can be separated by commas. The order of the columns can be defined as desired:

SELECT <column1>, <column2>, <column3>, ...

FROM <table>

Query without repetitions:

If not all columns are selected, it may happen that some records look the same:

SELECT AMOUNT FROM CHECKS

AMOUNT

------

150

245,34

150

25

25,1

With the keyword DISTINCT, such duplications can be avoided and identical data rows can only be output once.

SELECT DISTINCT AMOUNT FROM CHECKS

AMOUNT

------

150

245,34

25

25,1

 

WHERE

Previously, all data records in a table were output. However, if a particular element or group of elements is searched in a database, one or more conditions are required. These conditions are contained in the WHERE clause.

SELECT <column1>, <column2>, <column3>, ...

FROM <table>

WHERE <search condition>

NAME

FRAME

 MATER­IAL MILAGE TYPE
TREK 2300 23 CARBON FIBER 3500 RACING
BURLEY 22 STEEL 2000 TANDEM
GIANT 19 STEEL 1500 COM­MUTER
FUJI 20 STEEL 500 TOURING
SPECIA­LIZED 16 STEEL 100 MOUN­TAIN
CANNON­DALE 23 ALUMI­NUM 3000 RACING

If you want to select a specific bicycle from the table, enter the following statement:

SELECT NAME, FRAME, MATERIAL, MILAGE, TYPE

FROM BICYCLE

WHERE NAME = 'BURLEY'

NAME

FRAME

 MATER­IAL MILAGE TYPE
BURLEY 22 STEEL 2000 TANDEM

All bicycles with a mileage greater than 1500:

SELECT NAME, FRAME, MATERIAL, MILAGE, TYPE

FROM BICYCLE

WHERE MILAGE > 1500

 

ORDER BY

The SELECT FROM statement only generates a list in which the results of the query are displayed in the order in which the data was entered.

With the ORDER BY clause, the results can be put in a certain order. For example, the following instruction arranges the list of bicycles according to mileage:

SELECT NAME, FRAME, MATERIAL, MILAGE, TYPE

FROM BICYCLE

WHERE MILAGE >= 1500

ORDER BY MILAGE DESC

NAME

FRAME

 MATER­IAL MILAGE TYPE
TREK 2300 23 CARBON FIBER 3500 RACING
CANNON­DALE 23 ALUMI­NUM 3000 RACING
BURLEY 22 STEEL 2000 TANDEM
GIANT 19 STEEL 1500 COM­MUTER

The keyword DESC indicates that you want to sort in descending order. In ascending order, either the keyword is omitted or ASC is used.

If several expressions have to be sorted, separate them with commas. The table is then sorted in this order.

 

Aggregate functions

Aggregate or group functions return a value based on the values in a column. The most frequently used aggregate functions (display color) are:

  • COUNT
  • SUM
  • AVG
  • MAX
  • MIN

For example, the following expression is used to determine the number of records in a table:

SELECT COUNT(*)

FROM BICYCLE

You can also use the keyword DISTINCT:

SELECT COUNT(DISTINCT TYPE)

FROM BICYCLE

This expression counts all values that occur in the TYPE column, but does not count the same values twice. The result 5 would be applied to the following table. RACING exists twice, but is only counted once.

NAME

FRAME

 MATER­IAL MILAGE TYPE
TREK 2300 23 CARBON FIBER 3500 RACING
BURLEY 22 STEEL 2000 TANDEM
GIANT 19 STEEL 1500 COM­MUTER
FUJI 20 STEEL 500 TOURING
SPECIA­LIZED 16 STEEL 100 MOUN­TAIN
CANNON­DALE 23 ALUMI­NUM 3000 RACING

The number of bicycles made of STEEL is determined as follows: mountain

SELECT COUNT(*)

FROM BICYCLE

WHERE MATERIAL = 'STEEL'

The queries in the MILAGE column of the table above can be demonstrated very well:

SELECT

COUNT(NAME) AS Quantity,

SUM(NAME) AS Total,

AVG(NAME) AS Average,

MAX(NAME) AS Maximum,

MIN(NAME) AS Minimum

FROM BICYCLE

Quant­ity Total Average Maximum Min­imum
6 10600 1766,66 3500 100

If further information, such as MATERIAL or TYPE, is to be added, it must be included in the GROUP BY clause. Otherwise the query leads to an error.

 

GROUP BY

The GROUP BY clause specifies the columns to be used for grouping. This allows queries to be applied to individual groups rather than to the entire result.

For example, if you want to determine how many bicycles there are per material, use the following expression:

SELECT MATERIAL, COUNT(NAME) AS QUANTITY

FROM BICYCLE

GROUP BY MATERIAL

The result is displayed:

MATERIAL QUANTITY
CARBON FIBER 1
STEEL 4
ALUMINUM 1

 

HAVING

Like the WHERE clause, the HAVING clause can further restrict the result of the GROUP BY command. Aggregate functions can also be used in the restriction:

SELECT MATERIAL, COUNT(Name) AS QUANTITY

FROM BICYCLE

GROUP BY MATERIAL

HAVING COUNT(Name) > 1

MATERIAL QUANTITY
STEEL 4
Command Description
Queries
SELECT Retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL Server. The full syntax of the SELECT statement is complex, but the main clauses can be summarized as:
  • [ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> ] } ]
  • SELECT select_list [ INTO new_table ]
  • [ FROM table_source ] [ WHERE search_condition ]
  • [ GROUP BY group_by_expression ]
  • [ HAVING search_condition ]
  • [ ORDER BY order_expression [ ASC | DESC ] ]
SELECT - GROUP BY A SELECT statement clause that divides the query result into groups of rows, usually for the purpose of performing one or more aggregations on each group. The SELECT statement returns one row per group.

GROUP BY {

column-expression

| ROLLUP ( <group_by_expression> [ ,...n ] )

| CUBE ( <group_by_expression> [ ,...n ] )

| GROUPING SETS ( <grouping_set> [ ,...n ] )

| () --calculates the grand total

} [ ,...n ]
SELECT - HAVING Specifies a search condition for a group or an aggregate. HAVING can be used only with the SELECT statement. HAVING is typically used with a GROUP BY clause. When GROUP BY is not used, there is an implicit single, aggregated group.

[ HAVING <search condition> ]
SELECT - ORDER BY Sorts data returned by a query in SQL Server. Use this clause to:
  • Order the result set of a query by the specified column list and, optionally, limit the rows returned to a specified range. The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.
  • Determine the order in which ranking function values are applied to the result set.

ORDER BY order_by_expression

[ COLLATE collation_name ]

[ ASC | DESC ]

[ ,...n ]

[ <offset_fetch> ]
FROM In Transact-SQL, the FROM clause is available on the following statements:
  • DELETE
  • UPDATE
  • SELECT

The FROM clause is usually required on the SELECT statement. The exception is when no table columns are listed, and the only items listed are literals or variables or arithmetic expressions.

[ FROM { <table_source> } [ ,...n ] ]
WHERE Specifies the search condition for the rows returned by the query.

[ WHERE <search_condition> ]
Functions
AVG This function returns the average of the values in a group. It ignores null values.

AVG ( [ ALL | DISTINCT ] expression )

[ OVER ( [ partition_by_clause ] order_by_clause ) ]
COUNT This function returns the number of items found in a group. COUNT always returns an int data type value.

COUNT ( { [ [ ALL | DISTINCT ] expression ] | * } )
MAX Returns the maximum value in the expression.

MAX( [ ALL | DISTINCT ] expression )
MIN Returns the minimum value in the expression.

MIN ( [ ALL | DISTINCT ] expression )
STDEV Returns the statistical standard deviation of all values in the specified expression.

STDEV ( [ ALL | DISTINCT ] expression )
SUM Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.

SUM ( [ ALL | DISTINCT ] expression )
VAR Returns the statistical variance of all values in the specified expression.

VAR ( [ ALL | DISTINCT ] expression )
Statements
Backup and restore The backup and restore statements provide ways to create backups and restore from backups.
  • BACKUP
  • RESTORE
Data Definition Language Data Definition Language (DDL) statements defines data structures. Use these statements to create, alter, or drop data structures in a database.
  • ALTER
  • Collations
  • CREATE
  • DROP
  • DISABLE TRIGGER
  • ENABLE TRIGGER
  • RENAME
  • UPDATE STATISTICS
Data Manipulation Language Data Manipulation Language (DML) affect the information stored in the database. Use these statements to insert, update, and change the rows in the database.
  • BULK INSERT
  • DELETE
  • INSERT
  • UPDATE
  • MERGE
  • TRUNCATE TABLE
Permissions statements Permissions statements determine which users and logins can access data and perform operations.
Service Broker statements Service Broker is a feature that provides native support for messaging and queuing applications.
Session settings SET statements determine how the current session handles run time settings.
Data types
bigint

int

smallint

tinyint

 Exact-number data types that use integer data. To save space in the database, use the smallest data type that can reliably contain all possible values. For example, tinyint would be sufficient for a person's age because no one lives to be more than 255 years old. But tinyint would not be sufficient for a building's age because a building can be more than 255 years old.
  • -2^63 (-9.223.372.036.854.775.808) to 2^63-1 (9.223.372.036.854.775.807)
  • -2^31 (-2.147.483.648) to 2^31-1 (2.147.483.647)
  • -2^15 (-32,768) to 2^15-1 (32,767)
  • 0 to 255
numeric Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The ISO synonyms for decimal are dec and dec(p, s). numeric is functionally identical to decimal.
bit An integer data type that can take a value of 1, 0, or NULL.
money

smallmoney

 Data types that represent monetary or currency values.
  • -922.337.203.685.477,5808 to 922.337.203.685.477,5807
  • -214.748,3648 to 214.748,3647
float

real

 Approximate-number data types for use with floating point numeric data. Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.
  • - 1,79E+308 to -2,23E-308, 0 and 2,23E-308 to 1,79E+308
  • - 3,40E + 38 to -1,18E - 38, 0 and 1,18E - 38 to 3,40E + 38
date Defines a date in SQL Server.

Default string literal format: YYYY-MM-DD

0001-01-01 through 9999-12-31
time Defines a time of a day. The time is without time zone awareness and is based on a 24-hour clock.

Default string literal format: hh:mm:ss[.nnnnnnn]
datetime Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.
char char [ ( n ) ] Fixed-size string data. n defines the string size in bytes and must be a value from 1 through 8,000.
text Variable-length non-Unicode data in the code page of the server and with a maximum string length of 2^31-1 (2,147,483,647). When the server code page uses double-byte characters, the storage is still 2,147,483,647 bytes. Depending on the character string, the storage size may be less than 2,147,483,647 bytes.

[Source: docs.microsoft.com (Transact-SQL Reference)]