SQL Selection

Introduction to SQL Operators and Operands

1. Start Microsoft Visual Basic and create a Windows Application named FlowerShop
2. From the Common Controls section of the Toolbox, add a Button to the form
3. Double-click the button and implement its Click event as follows:
    

   Imports System.Data.SqlClient Public Class Exercise Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles Button1.Click Dim strConnection As String = _ "Data Source=(local);Integrated Security=yes" Using connection As SqlConnection = _ New SqlConnection(strConnection) Dim command As SqlCommand = _ New SqlCommand("CREATE DATABASE FlowerShop;", connection) connection.Open() command.ExecuteNonQuery() MsgBox("A database named ""FlowerShop"" has been created.") End Using End Sub End Class

4. Execute the application
5. Click the button
6. Click OK
7. Close the form and return to your programming environment
8. Delete the button on the form and design it (the form) as follows:
    

   Control Text Name Other Properties Label First Name:     TextBox   txtFirstName   Label Last Name:     TextBox   txtLastName   Label Full Name:     TextBox   txtFulName   Label Date Hired:     DateTimePicker   dtpDateHired   Label Employment Status:     ComboBox   cbxEmploymentStatus Items: Full Time Part Time Contractor Seasonal Intern Label Weekly Status:     TextBox   txtWeeklyStatus TextAlign: Right Label Hourly Salary:     TextBox   txtHourlyStatus TextAlign: Right Button Operate btnOperate   Label Weekly Salary:     TextBox   txtWeeklySalary TextAlign: Right Button Close btnClose

9. Save the form

Like every language, SQL ships with some words used to carry its various operations. One of these words is PRINT. To display something in plain text as a result of a statement, type PRINT followed by what to display. Therefore, PRINT uses the following formula:

PRINT WhatToPrint

The item to display can be anything that is allowed and it is provided on the right side of PRINT. If it is a regular constant number, simply type it on the right side of PRINT. The item to display can also be an operation or the result of an operation. You can also display an expression as a combination of number(s) and sentences.

The SELECT operator can be used, among other things, to display a value. The SELECT keyword uses the following syntax:

SELECT What

Based on this, to use it, where it is needed, type SELECT followed by a number, a word, a string, or an expression. The item to display follows some of the same rules as PRINT. One of the differences between PRINT and SELECT is that:

• PRINT is mostly used for testing a simple value, a string, or an expression. Therefore, it displays its results in a regular white window under a tab labeled Messages. PRINT can be used with only one value
• SELECT is the most regularly used SQL operator. We will see that it is used to retrieve records from a table. For this reason, SELECT displays its results in an organized window made of categories called columns, under a tab labeled Results. SELECT can be used with more than one value

As done for PRINT, to display a sentence using SELECT, type it in single-quotes on the right side of this operator. Here is an executed example:

When you create a SELECT statement, what is on the right side of SELECT must be a value. Here is an example:

SELECT 226.75;

Based on this definition, instead of just being a value, the thing on the right side of SELECT must be able to produce a value. As we will see in the next sections, you can create algebraic operations on the right side of SELECT.

As mentioned already, unlike PRINT, SELECT can be used to display more than one value. The values must be separated by commas. Here is an example:

SELECT N'Hourly Salary', 24.85

1. On the form, double-click the Operate button
2. To use a SELECT statement, implement the Click event as follows:
    

   Private Sub btnOperate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles btnOperate.Click Dim connect As SqlConnection = _ New SqlConnection("Data Source=(local);" & _ "Database='FlowerShop';" & _ "Integrated Security=yes;") Dim command As SqlCommand = _ New SqlCommand("SELECT N'William';", _ connect) connect.Open() Dim rdr As SqlDataReader = Command.ExecuteReader() While rdr.Read() txtFirstName.Text = rdr(0) End While rdr.Close() connect.Close() End Sub

3. In the Class Name combo box, select btnClose
4. In the Method Name combo box, select Click and implement the event as follows:
    

   Private Sub btnClose_Click(ByVal sender As Object, _ ByVal e As System.EventArgs) _ Handles btnClose.Click End End Sub

5. Execute the application
6. Click the Operate button
7. After checking that a first name displays, close the form and return to your programming environment
8. To select more than one value, change the code of the Operate button as follows:
    

   Private Sub btnOperate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles btnOperate.Click Dim connect As SqlConnection = _ New SqlConnection("Data Source=(local);" & _ "Database='FlowerShop';" & _ "Integrated Security=yes;") Dim command As SqlCommand = _ New SqlCommand("SELECT N'William', 'Godetsky';", _ connect) connect.Open() Dim rdr As SqlDataReader = Command.ExecuteReader() While rdr.Read() txtFirstName.Text = rdr(0) txtLastName.Text = rdr(1) End While rdr.Close() connect.Close() End Sub

9. Execute the application
10. Click the Operate button
     
    
11. Close the form and return to your programming environment

Because we mentioned that the thing on the right side must produce a result, you can as well use another SELECT statement that it itself evaluates to a result. To distinguish the SELECT sections, the second one should be included in parentheses. Here is an example:

SELECT (SELECT 448.25);
GO

When one SELECT statement is created after another, the second is referred to as nested.

Just as you can nest one SELECT statement inside of another, you can also nest one statement in another statement that itself is nested. Here is an example:

SELECT (SELECT (SELECT 1350.75));
GO

In the above introductions, we used either PRINT or SELECT to display something in the query window. One of the characteristics of SELECT is that it can segment its result in different sections. SELECT represents each value in a section called a column. Each column is represented with a name also called a caption. By default, the caption displays as "(No column name)". If you want to use your own caption, on the right side of an expression, type the AS keyword followed by the desired caption. The item on the right side of the AS keyword must be considered as one word. Here is an example:

SELECT 24.85 AS HourlySalary;

This would produce:

You can also include the item on the right side of AS in single-quotes. Here is an example:

SELECT 24.85 AS 'HourlySalary';

If the item on the right side of AS is in different words, you should include it in single-quotes or put them in inside of an opening square bracket "[" and a closing square bracket "]". Here is an example:

SELECT 24.85 AS 'Hourly Salary';

If you create different sections, separated by a comma, you can follow each with AS and a caption. Here is an example:

SELECT N'James Knight' As FullName, 20.48 AS Salary;

This would produce:

The above statement could also be written as follows:

SELECT N'James Knight' As [Full Name], 20.48 AS [Hourly Salary];

A unary operator is an operator that performs its operation on only one operand.

As you may know from the Visual Basic language, to express a positive number, you can write + on its left side. As a mathematical convention, when a value is positive, you do not need to express it with the + operator.

The - sign must be typed on the left side of a number to make it negative.

To add two numbers, you use the addition operator. Here is an example:

PRINT 125 + 4088

In Transact-SQL, you can also perform the addition on text. Here is an example:

PRINT 'Henry ' + 'Kono'

To subtract two numbers, you use the - operator. Here is an example:

PRINT 1240 - 608

Unlike the addition, the subtraction operation is not associative. This means that a - b - c is not necessarily equal to c - b - a. This is illustrated in the following statements:

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

This would produce:

81
-165

Notice that both operations of the addition convey the same result.

To multiply two numbers, you use the * operator. Here is an example:

PRINT 128 * 42

This would produce 5376

To divide two numbers you use the / operator. 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). Make sure that you know the relationship(s) between the numbers involved in the operation.

To get the remainder of a division operation between two numbers, you use the percent operator %. Here is an example:

PRINT 128 % 42

This would produce 2.

Like most computer languages, Transact-SQL uses parentheses to isolate a group of items that must be considered as belonging to one entity. For example, as we will learn soon, parentheses allow a function to delimit the list of its arguments. Parentheses can also be used to isolate an operation or an expression with regards to another operation or expression. For example, when studying the algebraic operations, we saw that the subtraction is not associative and can lead to unpredictable results. In the same way, if your operation involves various operators such as a mix of addition(s) and subtraction(s), you can use parentheses to specify how to proceed with the operations, that is, what operation should (must) be performed first. 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. This difference can be even more accentuated if your operation includes 3 or more operators and 4 or more operands. Here is another example of a nested SELECT statement that uses parentheses:

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

When you use a value in your database or application, the value must be stored somewhere in the computer memory using a certain amount of space. A value occupies space that resembles a group of small boxes. In our human understanding, it is not always easy to figure out how a letter such as as B is stored in 7 seven small boxes when we know that B is only one letter.

Bit manipulation or a bit related operation allows you to control how values are stored in bits. This is not an operation you will need to perform very often, especially not in the early stages of your database. Nevertheless, bit operations (and related overloaded operators) are present in all or most programming environments, so much that you should be aware of what they do or what they offer.

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

To perform this operation, the Transact-SQL interpreter considers each bit that is part of the operand and inverts the value of each bit from 1 to 0 or from 0 to 1 depending on the value the bit is holding. This operation can be resumed in the following table:

Consider a number with a byte value such as 248. In our study of numeric systems, we define how to convert numbers from one system to another. Based on this, the binary value of decimal 248 is 1111 1000 (and its hexadecimal value is 0xF8). If you apply the bitwise NOT operator on it to reverse the values of its bits, you would get the following result:

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. This comparison is resumed as follows:

Imagine you have two byte values represented as 187 and 242. The binary value of decimal 187 is 1011 1011 (and its hexadecimal value is 0xBB). The binary value of decimal 242 is 1111 0010 (and its hexadecimal value is 0xF2). Let’s compare these two values bit by bit, using the bitwise AND operator:

Most of the times, you will want the interpreter to perform this operation and use the result in your program. This means that you can get the result of this operation and possibly display it to the user. The above operation can be performed by the following program:

PRINT 187 & 242

This would produce 178

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. This operation is resumed as follows:

Once again, let’s consider decimals 187 and 242. Their bitwise OR comparison would render the following result:

You can also let the compiler perform the operation and produce a result. Here is an example:

PRINT 187 | 242

This would produce 251

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

Value1 ^ Value2

The compiler 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. This operation is resumed as follows:

We will again consider decimals 187 and 242. Their bitwise-exclusive XOR comparison would render the following result:

If the interpreter performs this operation, it can produce a result as in the following example:

PRINT 187 ^ 242;

This would produce 73.

In the previous sections, we used some values such as 242 or 'James Knight'. These are constant values because we certainly know them before using them and we did not change them in our statements. If you intend to use a certain category of value over and over again, you can declare a variable for it. Like the Visual Basic language, the SQL supports variables.

To declare a variable, use the DECLARE keyword using the following formula:

DECLARE Options

The DECLARE keyword lets the interpreter know that you are declaring a variable. The DECLARE keyword is followed by a name for the variable. In Transact-SQL, the name of a variable starts with the @ sign. The name of a variable allows you to identify the area of memory where the value of the variable is stored. While other languages like C/C++, Pascal, Java, C#, etc, impose strict rules to names, Transact-SQL is extremely flexible. A name can be made of digits only. Here is an example:

DECLARE @264

Such a name made of digits can create confusion with a normal number. A name can also be made of one or more words.

To avoid confusion, to name our variable, here are the rules we will follow:

• A name will start with either an underscore or a letter. Examples are @_n, @act, or @Second
• After the first character as an underscore or a letter, the name will have combinations of underscores, letters, and digits. Examples are @_n24 or @act_52_t
• A name will not include special characters such as !, @, #, $, %, ^, &, or *
• If the name is a combination of words, each word will start in uppercase. Examples are @DateHired, @_RealSport, or @DriversLicenseNumber

To declare a variable, as we will see in the next sections, after giving a name to a variable, you must also specify the amount of memory that the variable would need. The amount of memory is also called a data type. Therefore, the declaration of a variable uses the following formula:

DECLARE @VariableName DataType;

You can also declare more than one variable. To do that, separate them with a comma. The formula would be:

DECLARE @Variable1 DataType1, @Variable2 DataType2, @Variable_n DataType_n;

Unlike many other languages like C/C++, C#, Java, or Pascal, if you declare many variables that use the same data type, the name of each variable must be followed by its own data type.

After declaring a variable, the interpreter reserves a memory for it but the space does not necessarily hold a recognizable value. This means that, at this time, the variable is null. One way you can change this is to give a value to the variable. This is referred to as initializing the variable.

Remember that a variable's name starts with @ and whenever you need to refer to the variable, you must make sure you include the @ sign. To initialize a variable, in the necessary section, type the SELECT or the SET keyword followed by the name of the variable, followed by the assignment operator "=", followed by an appropriate value. The formula used is:

SELECT @VariableName = DesiredValue

or

SET @VariableName = DesiredValue

Once a variable has been initialized, you can make its value available or display it. This time, you can type the name of the variable to the right side of PRINT or SELECT.

After setting the name of a variable, you must specify the amount of memory that the variable will need to store its value. Since there are various kinds of information a database can deal with, the SQL provides a set of data types.

Like the Visual Basic language, the SQL supports Boolean values. To declare a Boolean variable, you use the BIT or bit keyword. Here is an example:

DECLARE @IsOrganDonor bit;

After declaring a Boolean variable, you can initialize it with 0 or another integral value. If the variable is initialized with 0, it receives the Boolean value of False. If it is initialized with any other number, it receives a True value. Here is an example of using a Boolean variable:

1. To declare and use a Boolean variable, change the code of the Operate button as follows:
    

   Private Sub btnOperate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles btnOperate.Click Dim connect As SqlConnection = _ New SqlConnection("Data Source=(local);" & _ "Database='FlowerShop';" & _ "Integrated Security=yes;") Dim cmd As SqlCommand = New SqlCommand("DECLARE @IsMarried bit; " & _ "SET @IsMarried = 1; " & _ "SELECT @IsMarried; ", _ connect) connect.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader() While rdr.Read() MsgBox("Is Married? " & rdr(0)) End While rdr.Close() connect.Close() End Sub

2. Execute the application and click the Operate button
3. Close the form and return to your programming environment

Like the Visual Basic language, the SQL supports integers. If a variable would hold natural numbers in the range of -2,147,483,648 to 2,147,483,647, you can declare it with the int keyword as data type. Here is an example:

The length of an integer is the number of bytes its field can hold. For an int type, that would be 4 bytes.

Remember that you can also declare and use more than one variable. Here is an example:

DECLARE @IsMarried bit, @EmplStatus int;
SET @IsMarried = 1;
SET @EmplStatus = 2;
SELECT @IsMarried AS [Is Married?],
       @EmplStatus AS [Employment Status];
GO

If you want to use very small numbers such as student's ages, or the number of pages of a brochure or newspaper, apply the tinyint data type to such a field. A variable with the tinyint data type can hold positive numbers that range from 0 to 255. Here is an example:

The smallint data type is used to store numbers that range between -32,768 and 32,767. Here is an example:

The bigint data type follows the same rules and principles as the the Visual Basic language's Long data type and can hold numbers from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. Here is an example:

The binary data type is used for a variable that would hold hexadecimal numbers. Examples of hexadecimal numbers are 0x7238, 0xFA36, or 0xAA48D. Use the binary data type if all values of the variable would have the exact same length (or quantity).

If you anticipate that some entries would be different than others, then use the alternative varbinary data type. The varbinary type also is used for hexadecimal numbers but allows dissimilar entries, as long as all entries are hexadecimals.

1. To declare and use an integer variable, change the code of the Operate button as follows:
    

   Private Sub btnOperate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles btnOperate.Click Dim connect As SqlConnection = _ New SqlConnection("Data Source=(local);" & _ "Database='FlowerShop';" & _ "Integrated Security=yes;") Dim command As SqlCommand = _ New SqlCommand("DECLARE @IsMarried bit; " & _ "SET @IsMarried = 1; " & _ "SELECT @IsMarried AS [Married?]; ", _ connect) connect.Open() Dim rdr As SqlDataReader = Command.ExecuteReader() While rdr.Read() MsgBox("Is Married? " & rdr(0)) End While rdr.Close() command = New SqlCommand("DECLARE @EmplStatus int; " & _ "SET @EmplStatus = 2; " & _ "SELECT @EmplStatus; ", _ connect) rdr = Command.ExecuteReader() While rdr.Read() MsgBox("Employment Status: " & rdr(0)) cbxEmploymentStatus.SelectedIndex = CInt(rdr(0)) End While rdr.Close() connect.Close() End Sub

2. Execute the application and click the Operate button
3. Close the form and return to your programming environment

To support decimal numbers, the SQL provides various data types. To declare a variable that can hold decimal numbers, you can use the numeric or the decimal data types (either decimal or numeric would produce the same effect in Microsoft SQL Server). Here is an example:

To declare a variable that would hold decimal numbers with single precision, you can use the float or the real data type. Here is an example:

A precision is the number of digits used to display a numeric value. For example, the number 42005 has a precision of 5, while 226 has a precision value of 3. If the data type is specified as an integer (the int and its variants) or a floating-point number (float and real), the precision is fixed by the database and you can just accept the value set by the Microsoft SQL Server interpreter. For a decimal number (decimal or numeric data types), Microsoft SQL Server allows you to specify the amount of precision you want. The value must be an integer between 1 and 38 (28 if you are using SQL Server 7).

The scale of a number if the number of digits on the right side of the period (or the character set as the separator for decimal numbers for your language, as specified in Control Panel). The scale is used only for numbers that have a decimal part, which includes currency (money and smallmoney) and decimals (numeric and decimal). If a variable is declared with the decimal or numeric data type, you can specify the amount of scale you want. The value must be an integer between 0 and 18. Here is an example:

Remember that you can declare an use various variables. Here is an example: 

DECLARE @IsMarried bit,
        @EmplStatus int,
        @WeeklyHours Decimal(6,2);
SET @IsMarried = 1;
SET @EmplStatus = 2;
SET @WeeklyHours = 36.50;
SELECT @IsMarried AS [Is Married?],
       @EmplStatus AS [Employment Status],
       @WeeklyHours AS Hours;
GO

1. To declare and use a decimal variable, change the code of the Operate button as follows:
    

   Private Sub btnOperate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles btnOperate.Click Dim connect As SqlConnection = _ New SqlConnection("Data Source=(local);" & _ "Database='FlowerShop';" & _ "Integrated Security=yes;") Dim command As SqlCommand = _ New SqlCommand("DECLARE @EmplStatus int; " & _ "SET @EmplStatus = 2; " & _ "SELECT @EmplStatus; ", _ connect) connect.Open() Dim rdr As SqlDataReader = command.ExecuteReader() While rdr.Read() cbxEmploymentStatus.SelectedIndex = CInt(rdr(0)) End While rdr.Close() command = New SqlCommand("DECLARE @WeeklyHours Decimal(6,2); " & _ "SET @WeeklyHours = 36.50; " & _ "SELECT @WeeklyHours; ", _ connect) rdr = command.ExecuteReader() While rdr.Read() MsgBox("Weekly Hours: " & rdr(0)) txtWeeklyHours.Text = rdr(0) End While rdr.Close() connect.Close() End Sub

2. Execute the application and click the Operate button
3. Close the form and return to your programming environment

If a variable would hold monetary values, you can declare it with the money data type. A variable with a money data type can hold positive or negative values from -922,337,203,685,477.5808 to +922,337,203,685,477.5807. Here is an example:

While the money data type can be used for a variable that would hold large quantities of currency values, the smallmoney data type can be applied for a variable whose value cannot be lower than -214,748.3648 nor higher than 214,748.3647.

The precision and scale of a money or smallmoney variable are fixed by Microsoft SQL Server. The scale is fixed to 4.

Once again, remember that you can declare and use various variables. Here is an example: 

DECLARE @EmplStatus int,
        @IsMarried bit,
        @WeeklyHours Decimal(6,2),
        @HourlySalary SmallMoney,
        @WeeklySalary SmallMoney;
SET @IsMarried = 1;
SET @EmplStatus = 2;
SET @WeeklyHours = 36.50;
SET @HourlySalary = 15.72;
SET @WeeklySalary = @WeeklyHours * @HourlySalary;
SELECT @EmplStatus AS [Empl Status],
       @IsMarried AS [Married?],
       @WeeklyHours AS Hours,
       @HourlySalary AS Hourly,
       @WeeklySalary AS Weekly;
GO

This would produce:

1. To declare and use a currency variable, change the code of the Operate button as follows:
    

   Private Sub btnOperate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles btnOperate.Click Dim connect As SqlConnection = _ New SqlConnection("Data Source=(local);" & _ "Database='FlowerShop';" & _ "Integrated Security=yes;") Dim command As SqlCommand = _ New SqlCommand("DECLARE @WeeklyHours Decimal(6,2), " & _ "@HourlySalary SmallMoney, " & _ "@WeeklySalary SmallMoney; " & _ "SET @WeeklyHours = 36.50; " & _ "SET @HourlySalary = 15.72; " & _ "SET @WeeklySalary = " & _ "@WeeklyHours * @HourlySalary; " & _ "SELECT @WeeklySalary; ", _ connect) connect.Open() Dim rdr As SqlDataReader = Command.ExecuteReader() While rdr.Read() MsgBox("Weekly Salary: " & rdr(0)) txtWeeklySalary.Text = rdr(0) End While rdr.Close() connect.Close() End Sub

2. Execute the application and click the Operate button
3. Close the form and return to your programming environment

A DATETIME data type is used for a column whose data would consist of date and/or time values, just like the DateTime structure of the .NET Framework or the Date data type in the Visual Basic language. The entries must be valid date or time values but Microsoft SQL Server allows a lot of flexibility, even to display a date in a non-traditional format. The date value of a datetime field can be comprised between January 1st, 1753 and December 31, 9999.

To initialize a DATETIME variable, include its value between single-quote. If the value is a date, separate the components of the value with the symbol recognized in Control Panel as the Date Separator:

Here is an example:

If the value is a time period, still include it in single-quotes. Inside of the quotes, follows the rules and formats specified in the Control Panel:

Here is an example:

DECLARE @DateHired DateTime,
        @EmplStatus int,
        @IsMarried bit,
        @WeeklyHours Decimal(6,2),
        @HourlySalary SmallMoney,
        @WeeklySalary SmallMoney;
SET @DateHired = '12/05/1998';
SET @IsMarried = 1;
SET @EmplStatus = 2;
SET @WeeklyHours = 36.50;
SET @HourlySalary = 15.72;
SET @WeeklySalary = @WeeklyHours * @HourlySalary;
SELECT @DateHired AS [Date Hired],
       @EmplStatus AS [Empl Status],
       @IsMarried AS [Married?],
       @WeeklyHours AS Hours,
       @HourlySalary AS Hourly,
       @WeeklySalary AS Weekly;
GO

This would produce:

The smalldatetime data type is an alternative to datetime. It follows the same rules and principles as the datetime data type except that a date value must be comprised between January 1st, 1900 and June 6, 2079.

1. To use a date value, change the code of the Operate button as follows:
    

   Private Sub btnOperate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles btnOperate.Click Dim connect As SqlConnection = _ New SqlConnection("Data Source=(local);" & _ "Database='FlowerShop';" & _ "Integrated Security=yes;") Dim cmd As SqlCommand = _ New SqlCommand("DECLARE @DateHired DateTime; " & _ "SET @DateHired = '12/02/1998'; " & _ "SELECT @DateHired;", _ connect) connect.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader() While rdr.Read() MsgBox("Date Hired: " & rdr(0)) dtpDateHired.Value = CDate(rdr(0)) End While rdr.Close() connect.Close() End Sub

2. Execute the application and click the Operate button
3. Close the form and return to your programming environment

Like the Visual Basic language, Transact-SQL supports character variables. To declare such a variable, use the char data type. Here is an example:

DECLARE @Gender char;

By default, the char data type can be applied to a variable that would hold one character at a time. After declaring the variable, when initializing it, include its value in single-quotes. Here is an example:

1> DECLARE @Gender char;
2> SET @Gender = 'M';
3> SELECT @Gender AS Gender;
4> GO
Gender
------
M

(1 rows affected)

If you include more than one character in the single-quotes, only the first (most left) character would be stored in the variable. Here is an example:

1> DECLARE @Gender char;
2> SET @Gender = 'Male';
3> SELECT @Gender AS Gender;
4> GO
Gender
------
M

(1 rows affected)

If a variable will hold strings of different lengths, declare it with the varchar data type. The maximum length of text that a field of varchar type can hold is equivalent to 8 kilobytes.

In some circumstances, you will need to change or specify the number of characters used in a string variable. Although a First Name and a Book Title variables should use the varchar type, both variables would not have the same length of entries. As it happens, people hardly have a first name that is beyond 20 characters and many book titles go beyond 32 characters. In this case, both variables would use the same data type but different lengths.

To specify the maximum number of characters that can be stored in a string variable, on the right side of char or varchar, type an opening and a closing parentheses. Inside of the parentheses, type the desired number.

 Here are examples:

DECLARE @FirstName varchar(20),
        @LastName varchar(20),
        @FullName varchar(40),
        @DateHired DateTime,
        @EmplStatus int,
        @IsMarried bit,
        @WeeklyHours Decimal(6,2),
        @HourlySalary SmallMoney,
        @WeeklySalary SmallMoney;
SET @FirstName    = 'Samuel';
SET @LastName     = 'Weinberg';
SET @FullName     = @LastName + ', ' +@FirstName;
SET @DateHired    = '12/05/1998';
SET @IsMarried    = 1;
SET @EmplStatus   = 2;
SET @WeeklyHours  = 36.50;
SET @HourlySalary = 15.72;
SET @WeeklySalary = @WeeklyHours * @HourlySalary;
SELECT @FullName As [Full Name],
       @DateHired AS [Date Hired],
       @EmplStatus AS [Empl Status],
       @IsMarried AS [Married?],
       @WeeklyHours AS Hours,
       @HourlySalary AS Hourly,
       @WeeklySalary AS Weekly;
GO

This would produce: 

To initialize the variable, if you are using the Command Prompt (SQLCMD.EXE), include its value between double-quotes. Here is an example:

If you are using a query window, do not include the string value in double-quotes; otherwise, you would receive an error:

Therefore, if using the query window, include the string in single-quotes:

The text data type can be used on a variable whose data would consist of ASCII characters. As opposed to a varchar type, a text type can hold text that is longer than 8 kilobytes.

The nchar, nvarchar, and ntext types follow the same rules as the char, varchar, and text respectively, except that they can be applied to variables that would hold international characters, that is, characters of languages other than US English. This is done following the rules of Unicode formats.

1. To declare and use some string variables, change the code of the Operate button as follows:
    

   Private Sub btnOperate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles btnOperate.Click Dim connect As SqlConnection = _ New SqlConnection("Data Source=(local);" & _ "Database='FlowerShop';" & _ "Integrated Security=yes;") Dim command As SqlCommand = _ New SqlCommand("DECLARE @FirstName varchar(20); " & _ "SET @FirstName = 'Samuel'; " & _ "SELECT @FirstName; ", connect) connect.Open() Dim rdr As SqlDataReader = Command.ExecuteReader() While rdr.Read() txtFirstName.Text = rdr(0) End While rdr.Close() command = New SqlCommand("DECLARE @LastName varchar(20); " & _ "SET @LastName = 'Weinberg'; " & _ "SELECT @LastName; ", connect) rdr = Command.ExecuteReader() While rdr.Read() txtLastName.Text = rdr(0) End While rdr.Close() command = New SqlCommand("DECLARE @FirstName varchar(20), " & _ " @LastName varchar(20), " & _ " @FullName varchar(40); " & _ "SET @FirstName = 'Samuel'; " & _ "SET @LastName = 'Weinberg'; " & _ "SET @FullName = @LastName & " & _ "', ' +@FirstName; " & _ "SELECT @FullName; ", _ connect) rdr = command.ExecuteReader() While rdr.Read() MsgBox("Full Name: " & rdr(0)) txtFullName.Text = rdr(0) End While rdr.Close() connect.Close() End Sub

2. Execute the application and click the Operate button
3. Close the form and return to your programming environment
4. To declare and use all variables at the same time, change the code of the Operate button as follows:
    

   Private Sub btnOperate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles btnOperate.Click Dim connect As SqlConnection = _ New SqlConnection("Data Source=(local);" & _ "Database='FlowerShop';" & _ "Integrated Security=yes;") Dim cmd As SqlCommand = _ New SqlCommand("DECLARE @FirstName varchar(20), " & _ "@LastName varchar(20), " & _ "@FullName varchar(40), " & _ "@DateHired DateTime, " & _ "@EmplStatus int, " & _ "@WeeklyHours Decimal(6,2), " & _ "@HourlySalary SmallMoney, " & _ "@WeeklySalary SmallMoney; " & _ "SET @FirstName = 'Samuel'; " & _ "SET @LastName = 'Weinberg'; " & _ "SET @FullName = @LastName & " & _ "', ' + @FirstName; " & _ "SET @DateHired = '12/02/1998'; " & _ "SET @EmplStatus = 2; " & _ "SET @WeeklyHours = 36.50; " & _ "SET @HourlySalary = 15.72; " & _ "SET @WeeklySalary = " & _ "@WeeklyHours * @HourlySalary; " & _ "SELECT @FirstName, @LastName, @FullName, " & _ "@DateHired, @EmplStatus, @WeeklyHours, " & _ "@HourlySalary, @WeeklySalary; ", _ connect) connect.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader() While rdr.Read() txtFirstName.Text = rdr(0) txtLastName.Text = rdr(1).ToString() txtFullName.Text = rdr(2).ToString() dtpDateHired.Value = CDate(rdr(3)) cbxEmploymentStatus.SelectedIndex = CInt(rdr(4)) txtWeeklyHours.Text = FormatNumber(rdr(5)) txtHourlySalary.Text = FormatCurrency(rdr(6)) txtWeeklySalary.Text = FormatCurrency(rdr(7)) End While rdr.Close() connect.Close() End Sub

5. Execute the application and click the Operate button
    
   
6. Close the form and return to your programming environment
7. Change the code of the Operate button as follows:
    

   Private Sub btnOperate_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles btnOperate.Click Dim strConnection As String = _ "Data Source=(local);Integrated Security=yes" Using connect As SqlConnection = _ New SqlConnection(strConnection) Dim command As SqlCommand = _ New SqlCommand("DROP DATABASE [FlowerShop];", _ connect) connect.Open() Command.ExecuteNonQuery() MsgBox("The FlowerShop database has been deleted from the server.") End Using End Sub

8. Execute the application and click the Operate button
    
   
9. Close the form and return to your programming environment

1. Write a statement that, when given the yearly salary of a person, can evaluate the hourly salary (consider that the yearly salary is based on 40 hours a week)
2. Write a statement so that, given the base and the height of a triangle, it calculates and displays the area (the area of a triangle is b * h / 2)