The Transact-SQL language has one basic statement for retrieving information from a database: the SELECT statement. With this statement, it is possible to query information from one or more tables of a database (or even from multiple databases). The result of a SELECT statement is another table, also known as a result set. The simplest… [Continue Reading]
Queries
WHERE Clause – SELECT Statement
Often, it is necessary to define one or more conditions that limit the selected rows. The WHERE clause specifies a Boolean expression (an expression that returns a value of TRUE or FALSE) that is tested for each row to be returned (potentially). If the expression is true, then the row is returned; if it is… [Continue Reading]
Boolean operators AND, OR & NOT – WHERE Clause
WHERE clause conditions can either be simple or contain multiple conditions. Multiple conditions can be built using the Boolean operators AND, OR, and NOT. The behavior of these operators was described in section “NULL Values” using truth tables. If two conditions are connected by the AND operator, rows are retrieved for which both conditions are… [Continue Reading]
IN and BETWEEN Operators – WHERE Clause
An IN operator allows the specification of two or more expressions to be used for a query search. The result of the condition returns TRUE if the value of the corresponding column equals one of the expressions specified by the IN predicate. Example 6.9 shows the use of the IN operator. Example 6.9 Get all… [Continue Reading]
NULL Values, LIKE Operator in WHERE Clause
A NULL in the CREATE TABLE statement specifies that a special value called NULL (which usually represents unknown or not applicable values) is allowed in the column. These values differ from all other values in a database. The WHERE clause of a SELECT statement generally returns rows for which the comparison evaluates to TRUE. The… [Continue Reading]
GROUP BY and HAVING Clauses – SELECT Statement
The GROUP BY clause defines one or more columns as a group such that all rows within any group have the same values for those columns. Example 6.22 shows the simple use of the GROUP BY clause in a SELECT statement. Example 6.22 Get all jobs of the employees: The result is In Example 6.22,… [Continue Reading]
Aggregate Functions in SELECT Statement
Aggregate functions are functions that are used to get summary values. All aggregate functions can be divided into several groups: Convenient aggregate functions Statistical aggregate functions User-defined aggregate functions Analytic aggregate functions The first three types are described in the following sections, while analytic aggregate functions are explained in detail in a different chapter. Convenient… [Continue Reading]
ORDER BY Clause – SQL Server
The ORDER BY clause defines the particular order of the rows in the result of a query. This clause has the following syntax: The col_name column defines the order. col_number is an alternative specification that identifies the column by its ordinal position in the sequence of all columns in the SELECT list (1 for the… [Continue Reading]
SELECT Statement and IDENTITY Property
The IDENTITY property allows you to specify a counter of values for a specific column of a table. Columns with numeric data types, such as TINYINT, SMALLINT, INT, and BIGINT, can have this property. The Database Engine generates values for such columns sequentially, starting with an initial value. Therefore, you can use the IDENTITY property… [Continue Reading]
CREATE SEQUENCE Statement
Using the IDENTITY property has several significant disadvantages, the most important of which are the following: You can use it only with the specified table. You cannot obtain the new value before using it. You can specify the IDENTITY property only when the column is created. For these reasons, SQL Server 2012 introduces sequences, which… [Continue Reading]
