CLAUSES

S.No Clause Name Description

1 ORDER BY Clause Specifies the sort for the result set. The ORDER BY clause is
invalid in views, inline functions, derived tables, and
subqueries, unless TOP is also specified.
2 GROUP BY Clause Specifies the groups into which output rows are to be placed and, if
aggregate functions are included in the SELECT clause
<select list>, calculates a summary value for each group.
When GROUP BY is specified, either each column in
any non-aggregate expression in the select list should be
included in the GROUP BY list, or the GROUP BY
expression must match exactly the select list expression.

Note: If the ORDER BY clause is not specified, groups returned

using the GROUP BY clause are not in any particular
order. It is recommended that you always use the
ORDER BY clause to specify a particular ordering of the
data.
3 WHERE Clause Specifies a search condition to restrict the rows returned
4 HAVING Clause Specifies a search condition for a group or an aggregate. HAVING
is usually used with the GROUP BY clause. When
GROUP BY is not used, HAVING behaves like a
WHERE clause.
5 INTO Clause Creates a new table and inserts the resulting rows from the query
into it.

The user executing a SELECT statement with the INTO clause

must have CREATE TABLE permission in the
destination database. SELECT...INTO cannot be used
with the COMPUTE.

We can use SELECT...INTO to create an identical table definition

(different table name) with no data by having a FALSE
condition in the WHERE clause.
6 SELECT Clause Specifies the columns to be returned by the query
7 FOR Clause FOR clause is used to specify either the BROWSE or the XML
option (BROWSE and XML are unrelated options).
8 FROM Clause Specifies the table(s) from which to retrieve rows. The FROM
clause is required except when the select list contains
only constants, variables, and arithmetic expressions (no
column names).
9 COMPUTE Clause Generates totals that appear as additional summary columns at the
end of the result set. When used with BY, the COMPUTE
clause generates control-breaks and subtotals in the
result set. You can specify COMPUTE BY and
COMPUTE in the same query.
10 OPTION Clause Specifies that the indicated query hint should be used throughout
the entire query. Each query hint can be specified only
once, although multiple query hints are permitted. Only
 one OPTION clause may be specified with the
statement. The query hint affects all operators in the
statement. If a UNION is involved in the main query, only
the last query involving a UNION operator can have the
OPTION clause. If one or more query hints causes the
query optimizer to not generate a valid plan, error 8622
is produced.

Caution: Because the query optimizer usually selects the best

execution plan for a query, it is recommended that
<join_hint>, <query_hint>, and <table_hint> be used only
as a last resort by experienced database administrators

OPERATORS

An operator is a symbol specifying an action that is performed on one or more expressions.

Microsoft® SQL Server™ 2000 uses these operator categories:

• Arithmetic operators

• Assignment operator

• Bitwise operators

• Comparison operators

• Logical operators

• String concatenation operator

• Unary operators

S.No Operator Name Description

1 Arithmetic operators Arithmetic operators perform mathematical operations on two
expressions of any of the data types of the numeric
data type category.

Operator Meaning
+ (Add) Addition
- Subtraction.

* Multiplication.

/ (Divide) Division
% Returns the integer remainder of a division. For
 example, 12 % 5 = 2 because the
remainder of 12 divided by 5 is 2.
The plus (+) and minus (-) can also be used to perform arithmetic
operations on datetime and smalldatetime values.
2 Assignment operator Transact-SQL has one assignment operator, the equals sign
(=). In this example, the @MyCounter variable is
created. Then, the assignment operator sets
@MyCounter to a value returned by an expression.

DECLARE @MyCounter INT

SET @MyCounter = 1

The assignment operator can also be used to establish the

relationship between a column heading and the
expression defining the values for the column. This
example displays two column headings named
FirstColumnHeading and SecondColumnHeading.
The string xyz is displayed in the
FirstColumnHeading column heading for all rows.
Then, each product ID from the Products table is
listed in the SecondColumnHeading column
heading.

USE Northwind
GO
SELECT FirstColumnHeading = 'xyz',
SecondColumnHeading = ProductID
FROM Products
GO

3 Bitwise operators Bitwise operators perform bit manipulations between two

expressions of any of the data types of the integer
data type category.

Operator Meaning
& (Bitwise AND) Bitwise AND (two operands).
| (Bitwise OR) Bitwise OR (two operands).
^ (Bitwise Bitwise exclusive OR (two operands).
Exclusi
ve OR)
The operands for bitwise operators can be any of the data
types of the integer or binary string data type
categories (except for the image data type), with
the exception that both operands cannot be any
of the data types of the binary string data type
category. The table shows the supported
operand data types.
 Left operand Right operand
binary int, smallint, or tinyint
bit int, smallint, tinyint, or bit
int int, smallint, tinyint, binary, or
varbinary
smallint int, smallint, tinyint, binary, or varbinary
tinyint int, smallint, tinyint, binary, or varbinary
varbinary int, smallint, or tinyint
4 Comparison operators Comparison operators test whether or not two expressions are
the same. Comparison operators can be used on all
expressions except expressions of the text, ntext, or
image data types.
Operator Meaning
= (Equals) Equal to
> (Greater Than) Greater than
< (Less Than) Less than
>= (Greater Than or Greater than or equal to
Equal
To)
<= (Less Than or Less than or equal to
Equal
To)
<> (Not Equal To) Not equal to
!= (Not Equal To) Not equal to (not SQL-92 standard)
!< (Not Less Not less than (not SQL-92 standard)
Than)
!> (Not Greater Not greater than (not SQL-92
Than) standard)
The result of a comparison operator has the Boolean data
type, which has three values: TRUE, FALSE, and
UNKNOWN. Expressions that return a Boolean
data type are known as Boolean expressions.
Unlike other SQL Server data types, a Boolean data
type cannot be specified as the data type of a table
column or variable, and cannot be returned in a
result set.

When SET ANSI_NULLS is ON, an operator that has one or two NULL
expressions returns UNKNOWN. When SET ANSI_NULLS is
OFF, the same rules apply, except an equals operator returns
TRUE if both expressions are NULL. For example, NULL =
NULL returns TRUE if SET ANSI_NULLS is OFF.

Expressions with Boolean data types are used in the WHERE

clause to filter the rows that qualify for the search conditions
and in control-of-flow language statements such as IF and
WHILE, for example:

USE Northwind
GO
DECLARE @MyProduct int
 SET @MyProduct = 10
IF (@MyProduct <> 0)
SELECT *
FROM Products
WHERE ProductID = @MyProduct
GO

5 Logical operators Logical operators test for the truth of some condition. Logical
operators, like comparison operators, return a
Boolean data type with a value of TRUE or FALSE.

Operator Meaning
ALL TRUE if all of a set of comparisons are
TRUE
AND TRUE if both Boolean expressions are
TRUE.
ANY TRUE if any one of a set of comparisons
are TRUE.
BETWEEN TRUE if the operand is within a range
EXISTS TRUE if a subquery contains any rows.
IN TRUE if the operand is equal to one of a
list of expressions.
LIKE TRUE if the operand matches a pattern.
NOT Reverses the value of any other Boolean
operator.
OR TRUE if either Boolean expression is
TRUE.)
SOME TRUE if some of a set of comparisons
are TRUE.
6 String concatenation The string concatenation operator allows string concatenation
operator with the addition sign (+), which is also known as
the string concatenation operator. All other string
manipulation is handled through string functions
such as SUBSTRING.

By default, an empty string is interpreted as an empty string in

INSERT or assignment statements on data of the
varchar data type. In concatenating data of the
varchar, char, or text data types, the empty string is
interpreted as an empty string. For example, 'abc' +
'' + 'def' is stored as 'abcdef'. However, if the
sp_dbcmptlevel compatibility level setting is 65,
empty constants are treated as a single blank
character and 'abc' + '' + 'def' is stored as 'abc def'
7 Unary Operators Unary operators perform an operation on only one expression
of any of the data types of the numeric data type
category.

Operator Meaning
 + (Positive) Numeric value is positive.
- (Negative) Numeric value is negative.
~ (Bitwise NOT) Returns the ones complement of the
number.
The + (Positive) and - (Negative) operators can be used on
any expression of any of the data types of the
numeric data type category. The ~ (Bitwise NOT)
operator can be used only on expressions of any of
the data types of the integer data type category.

OPERATOR PRECEDENCE

When a complex expression has multiple operators, operator precedence determines the sequence in
which the operations are performed. The order of execution can significantly affect the resulting value.

Operators have these precedence levels. An operator on higher levels is evaluated before an
operator on a lower level:

• + (Positive), - (Negative), ~ (Bitwise NOT)

• * (Multiply), / (Division), % (Modulo)

• + (Add), (+ Concatenate), - (Subtract)

• =, >, <, >=, <=, <>, !=, !>, !< (Comparison operators)

• ^ (Bitwise Exclusive OR), & (Bitwise AND), | (Bitwise OR)

• NOT

• AND

• ALL, ANY, BETWEEN, IN, LIKE, OR, SOME

• = (Assignment)

When two operators in an expression have the same operator precedence level, they are evaluated
left to right based on their position in the expression. For example, in the expression used in the SET
statement of this example, the subtraction operator is evaluated before the addition operator.

DECLARE @MyNumber int

SET @MyNumber = 4 - 2 + 27
-- Evaluates to 2 + 27 which yields an expression result of 29.
SELECT @MyNumber

FUNCTIONS
 Functions in programming languages are subroutines used to encapsulate frequently performed logic.
Any code that must perform the logic incorporated in a function can call the function rather than having to
repeat all of the function logic

Microsoft® SQL Server™ 2000 supports two types of functions:

Built-in Functions:

Operate as defined in the Transact-SQL Reference and cannot be modified. The functions can be
referenced only in Transact-SQL statements using the syntax defined in the Transact-SQL Reference.

Microsoft® SQL Server™ 2000 has built-in functions to perform certain operations. The
function categories are:

S.No Built-in Functions Name Description

1 Aggregate functions Perform operations that combine multiple values into one.
Examples are COUNT, SUM, MIN, and MAX.
2 Configuration functions Scalar functions that return information about configuration
settings
3 Cursor functions Return information about the status of a cursor
4 Date and time functions Manipulate datetime and smalldatetime values
5 Mathematical functions Perform trigonometric, geometric, and other numeric
operations
6 Meta data functions Return information on the attributes of databases and
database objects
7 Rowset functions Return rowsets that can be used in the place of a table
reference in a Transact-SQL statement
8 Security functions Return information about users and roles
9 String functions Manipulate char, varchar, nchar, nvarchar, binary, and
varbinary values
10 System functions Operate on or report on various system level options and
objects
11 System statistical Return information regarding the performance of SQL
functions Server
12 Text and image functions Manipulate text and image values

User-defined functions :

Allows us to define our own Transact-SQL functions using the CREATE FUNCTION statement.

User-defined functions take zero or more input parameters, and return a single
value. Some user-defined functions return a single, scalar data value, such as an
int, char, or decimal value.

For example, this statement creates a simple function that returns a decimal:

CREATE FUNCTION CubicVolume

 -- Input dimensions in centimeters.
(@CubeLength decimal(4,1), @CubeWidth decimal(4,1),
@CubeHeight decimal(4,1) )
RETURNS decimal(12,3) -- Cubic Centimeters.
AS
BEGIN
RETURN ( @CubeLength * @CubeWidth * @CubeHeight )
END

Types of User-Defined Functions

SQL Server 2000 supports three types of user-defined functions:

• Scalar functions

• Inline table-valued functions

• Multistatement table-valued functions

A user-defined function takes zero or more input parameters and returns either a scalar value or a
table. A function can have a maximum of 1024 input parameters. When a parameter of the function
has a default value, the keyword default DEFAULT must be specified when calling the function to
get the default value. This behavior is different from parameters with default values in stored
procedures in which omitting the parameter also implies the default value. User-defined functions
do not support output parameters.

S.No User-Defined Function Description

Name
1 Scalar function Returns a single data value of the type defined in a
RETURNS clause. All scalar data types, including
bigint and sql_variant, can be used. The
timestamp data type, user-defined data type, and
nonscalar types, such as table or cursor, are not
supported. The body of the function, defined in a
BEGIN...END block, contains the series of
Transact-SQL statements that return the value.
The return type can be any data type except text,
ntext, image, cursor, and timestamp
2 Inline table-valued Table-valued functions return a table. For an inline table-
function valued function, there is no function body; the
table is the result set of a single SELECT
statement.
3 Multistatement table- For a multistatement table-valued function, the function
valued function body, defined in a BEGIN...END block, contains
the TRANSACT-SQL statements that build and
insert rows into the table that will be returned