04: Introduction to Operations

Fundamentals of Operations

Introduction to Operations

An operation is a combination of items (or values) with the goal to produce a new meaning (or value).

Operands

An operand is an item (or value) that is involved in an operation.

Operators

An operator is a symbols that is involved in an operation to combine two other items (such as values) of the operation.

Unary Operators

Introduction

A unary operator is an operator that performs its operation on only one operand. Transact-SQL has a few unary operators available.

The Positive Operator +

A number lower than 0 is referred to as negative while a number higher than is considered positive. To express that a number is positive, you can use the + unary operator. In this case, write a + sign on the left of the number. Examples are +4, +228, +90335.

The Negative Operator -

To indicate that a number is negative, you must apply the - unary operator to it. Examples are -12, -448, -32706. A value accompanied by - is referred to as negative. Here is an example:

SELECT -1250

Other Types of Operations

A Binary Operation

A binary operation is an operation that involves an operator acting on two operands. In that case, the operator is referred to as a binary operator. Arithmetic provides various binary operators

A Ternary Operation

A ternary operation is an operation that involves three operands.

Composite Operations

A composite operation consists of performing the operation from a variable to itself. Composite operations use an operator that is in fact a combination of two operators. The variable can be almost any type that supports the type of operation you want to perform.

Introduction to the Addition

Overview

The addition is a binary operation that consists of adding one item to another. The operation can be performed on two constant values. You can then use PRINT or SELECT to display the operation.

Adding Two Numbers

You can use the addtion operation to add two numbers. Here is an example:

PRINT 125 + 4088

An Addition with a Variable

You can also use the addtion operation to add a number to a constant. Here is an example:

1> DECLARE @number numeric = 9284;
2> SELECT @number AS Original;
3> DECLARE @update numeric = @number + 371385;
4> SELECT @update AS [New Value];
5> GO
Original
--------------------
                9284

(1 rows affected)
New Value
--------------------
              380669

(1 rows affected)

The Composite Addition

The composite operation uses the += operator. Using it, to add the value of a variable to itself, type the variable and insert this operation between both operands. Here is an example:

DECLARE @variable int;

SET @variable = 248;
SELECT @variable;

SET @variable += @variable;
SELECT @variable;

Once you have performed the operation, the variable holds the new value. Consider this:

Composite Operations

Adding Text

Adding a Character

As seen with numbers, you can add a character to another character or to a string. You can start by declaring a character variable using char, nchar, varchar, or nvarchar. You can then use the + operator to add it to another character or string. Consider the following example:

1> DECLARE @carbon char = 'Carbon';
2> DECLARE @carbFourteen varchar(5) = @carbon + '14';
3> SELECT @carbFourteen AS [Carbon 14];
4> GO
Carbon 14
---------
C14

(1 rows affected)

In the same way, you can add two variables that each holds a character. Here is an example:

1> DECLARE @n char = 'New';
2> DECLARE @y char = 'York';
3> DECLARE @NewYork nchar(5) = @n + @y;
4> SELECT @NewYork AS [New York];
5> GO
New York
--------
NY

(1 rows affected)

Adding Strings

As done with numbers, you can add a string to a character or to another string. You can first declare a string variable using char(), nchar(), varchar(), or nvarchar(). You can then use the + operator to add it to another character or string. You can also declare two string variables and add them. Here is an example:

1> DECLARE @first NVARCHAR(12) = N'Gertrude ';
2> DECLARE @last NVARCHAR(12) = N'Monay';
3> DECLARE @full NVARCHAR(24) = @first + @last;
4> SELECT @first AS [First Name];
5> SELECT @last AS [Last Name];
6> SELECT @full AS [Full Name];
7> GO
First Name
------------
Gertrude

(1 rows affected)
Last Name
------------
Monay

(1 rows affected)
Full Name
------------------------
Gertrude Monay

(1 rows affected)

String Concatenations

String concatenation consists of adding two string. There are various ways to perform this operation. One techniques consists of using the following formula:

CONCAT(value_1, value_2, ..., value_n)

Based on this formula, write CONCAT(). In the parentheses, type eache desired item. Separater them with comas. An item can be a number, a character, a string, or the name of a variable. Here are examples:

1> DECLARE @staffCode numeric = 735608;
2> DECLARE @name nvarchar (25) = N'Paul Bertrand Yamaguchi';
3> DECLARE @sal decimal = 74668;
4> PRINT CONCAT(N'Employee #: ', @staffCode);
5> PRINT CONCAT(N'Employee Name: ', @name);
6> PRINT CONCAT(N'Yearly Salary: ', @sal);
7> GO
Employee #: 735608
Employee Name: Paul Bertrand Yamaguchi
Yearly Salary: 74668

Compound Addition on Strings

You can add a string variable to itself. This is done using the SET or the SELECT operator. Here is an example:

1> DECLARE @name NVARCHAR(22) = N'Gertrude';
2> SET @name = @name + N' ';
3> SET @name = @name + N'Monay';
4> SELECT @name AS [Full Name];
5> GO
Full Name
----------------------
Gertrude Monay

(1 rows affected)

You can also use the composite addition operation is to add one variable to another. To do this, include the += operator between the operands. Here is an example:

DECLARE @name nvarchar(50);
DECLARE @lastName nvarchar(20);

SET @name = N'Paul';
SET @lastName = N' Yamaguchi';
SELECT @name;
SELECT @lastName;

SET @name += @lastName;
SELECT @name;

When the operation has been performed, the left operand now holds its value and that of the other variable:

Composite Operations

In the same way, you can perform this operation as many time as you want by adding right operands to a left operands. Here are examples:

DECLARE @name nvarchar(50);
DECLARE @middleName nvarchar(20);
DECLARE @lastName nvarchar(20);

SET @name = N'Paul';
SET @middleName = N' Bertrand';
SET @lastName = N' Yamaguchi';

SET @name += @middleName;
SELECT @name;

SET @name += @lastName;
SELECT @name;

Composite Operations

One important thing you must keep in mind is the storage capacity of the left operand: It must be able to hold all values added to it.

Other Operations

The Subtraction

The subtraction operation, is used to take out or subtract one value from another value. The subtraction is performed with the - sign. Here is an example:

PRINT 1240 - 608

Unlike the addition, the subtraction operation is not associative. Consider the following example:

PRINT 128 - 42 - 5
PRINT 5 - 42 - 128

This would produce:

81
-165

The Multiplication

The multiplication allows adding one value to itself a certain number of times. The multiplication is performed with the * sign. Here is an example:

PRINT 128 * 42

This would produce 5376

The Division

The division is used to get the fraction of one number in terms of another. The division is performed with the forward slash /. Here is an example:

PRINT 128 / 42

This would produce 3

When performing the division, be aware of its many rules. Never divide by zero (0).

The Modulo

The modulo operation is used to get the remainder of a division as a natural number. The remainder operation is performed with the percent sign (%). Here is an example:

PRINT 128 % 42

This would produce 2.

Parentheses

Like most computer languages, Transact-SQL uses parentheses to isolate a group of items that must be considered as belonging to one entity. Here is an example:

PRINT (154 - 12) + 8
PRINT 154 - (12 + 8)

This would produce:

150
134

As you can see, using the parentheses controls how the whole operation would proceed. Consider the following example:

SELECT 
    (SELECT 448.25 * 3) + 
    (SELECT 82.28 - 36.04);
GO

Bit Manipulations

Introduction

Bit manipulation or a bit related operation allows you to control how values are stored in bits.

Bits Operators: The Bitwise NOT Operator ~

One of the operations you can perform on a bit consists of reversing its value. That is, if a bit holds a value of 1, you may want to change it to 0 and vice-versa. This operation can be taken care of by the bitwise NOT operator that is represented with the tilde symbol ~

The bitwise NOT is a unary operator that must be placed on the left side of its operand as in

~Value

Here is an example:

PRINT ~158

Bits Comparison: The Bitwise AND Operator &

The bitwise & is a binary operator that uses the following syntax

Operand1 & Operand2

This operator considers two values and compares the bit of each with the corresponding bit of the other value. If both corresponding bits are 1, the comparison produces 1. Otherwise, that is, if either bit is 0, the comparison produces 0.

Bits Comparison: The Bitwise OR Operator |

You can perform another type of comparison on bits using the bitwise OR operator that is represented by |. Its syntax is:

Value1 | Value2

Once again, the interpreter compares the corresponding bits of each operand. If at least one of the equivalent bits is 1, the comparison produces 1. The comparison produces 0 only if both bits are 0.

Bits Comparison: The Bitwise-Exclusive XOR Operator ^

Like the previous two operators, the bitwise-exclusive OR operator performs a bit comparison of two values. It syntax is:

Value1 ^ Value2

The interpreter compares the bit of one value to the corresponding bit of the other value. If one of the bits is 0 and the other is 1, the comparison produces 1. In the other two cases, that is, if both bits have the same value, the comparison produces 0. Here is an example:

PRINT 187 ^ 242;

This would produce 73.

Other Binary operations

The concept of composite operation can be applied to all arithmetic binary operations. As seen above, strings also support the addition composite operation. Composite operations are also available on all bit manipulation operations. The most important thing to remember is that not all data types support all operations. Overall:

All arithmetic binary operators support the addition, the subtraction, the multiplication, the division, and the remainder binary operators. These are +=, -=, *=, /=, and &= respectively
Bit manipulation operations support:
- The composition bitwise AND operator &= that compares the bits of the left operand to those of the right operand, for equality:
```
DECLARE @number int;

SET @number = 215;
SET @number &= 15;
SELECT @number;
```
- The composite bitwise OR operator |= that compares the bits of the left operand to those of the right operand, for inequality:
```
DECLARE @number int;

SET @number = 215;
SET @number |= 15;
SELECT @number;
```
- The composite bitwise-exclusive OR operator ^=
```
DECLARE @number int;

SET @number = 215;
SET @number ^= 15;
SELECT @number;
```

You should know that these operations can be performed on natural or decimal numbers.

Practical Learning: Ending the Lesson

Close Microsoft SQL Server
If asked whether you want to save the file, click No


Previous	Copyright © 2000-2025, FunctionX	Last Update: Saturday 05 April 2025, 12:22	Next