Queries

SQL SELECT – Its Clauses and Functions – SQL Server 2012

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 […]

WHERE Clause – SELECT Statement – SQL Server 2012

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 […]

Boolean operators AND, OR & NOT – WHERE Clause – SQL Server 2012

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 […]

IN and BETWEEN Operators – WHERE Clause – SQL Server 2012

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 […]

NULL Values, LIKE Operator in WHERE Clause – SQL Server 2012

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 […]

GROUP BY and HAVING Clauses – SELECT Statement – SQL Server 2012

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:

USE sample;
SELECT job
	FROM works_on
	GROUP BY job;

USE sample;

SELECT job

FROM works_on

GROUP BY job;

The result is In Example […]

Aggregate Functions in SELECT Statement – SQL Server 2012

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 […]

ORDER BY Clause – SQL Server 2012

The ORDER BY clause defines the particular order of the rows in the result of a query. This clause has the following syntax:

ORDER BY {[col_name | col_number [ASC | DESC]]} , ...

1	ORDER BY {[col_name \| col_number [ASC \| DESC]]} , ...

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 […]

SELECT Statement and IDENTITY Property – SQL Server 2012

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 […]

CREATE SEQUENCE Statement – SQL Server 2012

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 […]