Introduction to Operations
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 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 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:
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:
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;
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:
DECLARE @number int; SET @number = 215; SET @number &= 15; SELECT @number;
DECLARE @number int; SET @number = 215; SET @number |= 15; SELECT @number;
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
|
|||
Previous | Copyright © 2000-2025, FunctionX | Last Update: Saturday 05 April 2025, 12:22 | Next |
|