The primary Boolean operation you can perform on a field consists of checking its nullity. As mentiond already, this operation can be performed by using IS NULL in its expression. Here is an example: using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
Button btnSelect;
DataGridView dgvVideos;
Button btnCreateTableAndRecords;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
btnCreateTableAndRecords = new Button();
btnCreateTableAndRecords.AutoSize = true;
btnCreateTableAndRecords.Location = new Point(12, 12);
btnCreateTableAndRecords.Text = "Create Table & Records";
btnCreateTableAndRecords.Click += new EventHandler(btnDatabaseClick);
btnSelect = new Button();
btnSelect.Text = "Select";
btnSelect.Location = new Point(160, 12);
btnSelect.Click += new EventHandler(btnSelectClick);
dgvVideos = new DataGridView();
dgvVideos.Location = new Point(12, 44);
dgvVideos.Size = new System.Drawing.Size(465, 145);
Controls.Add(btnSelect);
Text = "Video Collection";
Controls.Add(dgvVideos);
Controls.Add(btnCreateTableAndRecords);
Size = new System.Drawing.Size(500, 230);
StartPosition = FormStartPosition.CenterScreen;
dgvVideos.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
}
void CreateTableAndRecords()
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoCollection1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("CREATE TABLE Videos(" +
"[Shelf #] nchar(7) null," +
"Title nvarchar(50) not null," +
"Director nvarchar(50)," +
"[Length] int," +
"Rating nchar(10)," +
"[Year] int);",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A table named \"Videos\" has been created.",
"Video Collection",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoCollection1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("INSERT INTO Videos " +
"VALUES('DHE-927', 'Two for the Money', 'D.J. Caruso', 123, 'R', 2008)," +
"('CGM-683', 'Her Alibi', 'Bruce Beresford', 94, 'PG-13', 1998)," +
"('FQT-973', 'Memoirs of a Geisha', 'Rob Marshall', 145, 'PG-13', 2006)," +
"('DBT-395', 'Wall Street', 'Oliver Stone', 126, 'R', 2000)," +
"(NULL, 'Stealing Harvard', 'Bruce McCulloch', 85, 'PG-13', NULL)," +
"('TPH-973', 'A Few Good Men', 'Rob Reiner', 138, NULL, 1992)," +
"(NULL, 'The Silence of the Lambs', 'Jonathan Demme', 118, NULL, 1991)," +
"('DZV-737', 'The Lady Killers', 'Joel Coen & Ethan Coen', 104, 'R', NULL)," +
"(NULL, 'Sneakers', 'Phil Alden Robinson', 126, 'PG-13', 1992)," +
"(NULL, 'Annie', 'John Huston', 126, 'G', 1982)," +
"(NULL, 'Dave', 'Ivan Reitman', 110, 'PG-13', 1993)," +
"('ADR-737', 'Incredibles (The)', 'Brad Bird', 133, 'PG', 2004);",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A few records have been created.",
"Video Collection",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
void btnDatabaseClick(object sender, EventArgs e)
{
CreateTableAndRecords();
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoCollection1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("SELECT ALL * FROM Videos;",
connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
BindingSource bsVideos = new BindingSource();
DataSet dsVideos = new DataSet("VideosSet");
sdaVideos.Fill(dsVideos);
bsVideos.DataSource = dsVideos.Tables[0];
dgvVideos.DataSource = bsVideos;
}
}
void btnSelectClick(object sender, EventArgs e)
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoCollection1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("SELECT ALL * FROM Videos " +
"WHERE [Shelf #] IS NULL;",
connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
BindingSource bsVideos = new BindingSource();
DataSet dsVideos = new DataSet("VideosSet");
sdaVideos.Fill(dsVideos);
bsVideos.DataSource = dsVideos.Tables[0];
dgvVideos.DataSource = bsVideos;
}
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
This would produce:
As mentioned already, to make the condition easier to read, you should include the expression in parentheses. This would be done as follows: SELECT ALL * FROM Videos WHERE ([Shelf #] IS NULL); GO When the statement executes, the table would display only the records that don't have a value for the state. On the other hand, to get the records that are not null, you would use IS NOT NULL. Here is an example: void btnSelectClick(object sender, EventArgs e)
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoCollection1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("SELECT ALL * FROM Videos " +
"WHERE [Shelf #] IS NOT NULL;",
connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
BindingSource bsVideos = new BindingSource();
DataSet dsVideos = new DataSet("VideosSet");
sdaVideos.Fill(dsVideos);
bsVideos.DataSource = dsVideos.Tables[0];
dgvVideos.DataSource = bsVideos;
}
}
This would produce:
Another common operation performed on a field consists of finding out whether it holds a specific value. This is done using the equality "=" operator. Therefore, to find out whether a field holds a certain value, compare it with that value. You must include the value in single-quotes. Here is an example: SELECT ALL * FROM Videos
WHERE Rating = N'R';
GO
In a WHERE statement, you can also use the ORDER BY expression to sort a list of records based on a column of your choice. Here is an example: using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
Button btnOrder;
DataGridView dgvVideos;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
btnOrder = new Button();
btnOrder.Text = "Select";
btnOrder.Location = new Point(12, 12);
btnOrder.Click += new EventHandler(btnSelectClick);
dgvVideos = new DataGridView();
dgvVideos.Location = new Point(12, 44);
dgvVideos.Size = new System.Drawing.Size(465, 145);
Controls.Add(btnOrder);
Text = "Video Collection";
Controls.Add(dgvVideos);
Size = new System.Drawing.Size(500, 230);
StartPosition = FormStartPosition.CenterScreen;
dgvVideos.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
ShowVideos();
}
void ShowVideos()
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoCollection1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("SELECT ALL * FROM Videos " +
"WHERE Rating = 'R';",
connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
BindingSource bsVideos = new BindingSource();
DataSet dsVideos = new DataSet("VideosSet");
sdaVideos.Fill(dsVideos);
bsVideos.DataSource = dsVideos.Tables[0];
dgvVideos.DataSource = bsVideos;
}
}
void btnSelectClick(object sender, EventArgs e)
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoCollection1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("SELECT ALL * FROM Videos " +
"WHERE Rating = 'R' " +
"ORDER BY Director;",
connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
BindingSource bsVideos = new BindingSource();
DataSet dsVideos = new DataSet("VideosSet");
sdaVideos.Fill(dsVideos);
bsVideos.DataSource = dsVideos.Tables[0];
dgvVideos.DataSource = bsVideos;
}
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
This would produce:
To check whether a field doesn't hold a certain value, you can use the <> operator. Here is an example: using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
Button btnOrder;
DataGridView dgvVideos;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
btnOrder = new Button();
btnOrder.Text = "Select";
btnOrder.Location = new Point(12, 12);
btnOrder.Click += new EventHandler(btnSelectClick);
dgvVideos = new DataGridView();
dgvVideos.Location = new Point(12, 44);
dgvVideos.Size = new System.Drawing.Size(465, 145);
Controls.Add(btnOrder);
Text = "Video Collection";
Controls.Add(dgvVideos);
Size = new System.Drawing.Size(500, 230);
StartPosition = FormStartPosition.CenterScreen;
dgvVideos.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
ShowVideos();
}
void ShowVideos()
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoCollection1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("SELECT ALL * FROM Videos;",
connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
BindingSource bsVideos = new BindingSource();
DataSet dsVideos = new DataSet("VideosSet");
sdaVideos.Fill(dsVideos);
bsVideos.DataSource = dsVideos.Tables[0];
dgvVideos.DataSource = bsVideos;
}
}
void btnSelectClick(object sender, EventArgs e)
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoCollection1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("SELECT ALL * FROM Videos " +
"WHERE Rating <> N'R';",
connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
BindingSource bsVideos = new BindingSource();
DataSet dsVideos = new DataSet("VideosSet");
sdaVideos.Fill(dsVideos);
bsVideos.DataSource = dsVideos.Tables[0];
dgvVideos.DataSource = bsVideos;
}
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
Remember (from Lesson 08) that, besides <>, Transact-SQL also supports the != operator used to perform a comparison for inequality. Therefore, the above statement can also be written as: SELECT ALL * FROM Videos
WHERE Rating != N'R';
GO
As an alternative, instead of <> or !=, use the equality operator but precede the expression with NOT. Here is an example: void btnSelectClick(object sender, EventArgs e)
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoCollection1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("SELECT ALL * FROM Videos " +
"WHERE NOT Rating = N'R';",
connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
BindingSource bsVideos = new BindingSource();
DataSet dsVideos = new DataSet("VideosSet");
sdaVideos.Fill(dsVideos);
bsVideos.DataSource = dsVideos.Tables[0];
dgvVideos.DataSource = bsVideos;
}
}
Notice that the result is the same as if only the comparison for equality was used. Of course, you can precede the <> operation with NOT. Here is an example: SELECT StudentNumber, FirstName, LastName, Gender, ParentsNames FROM Registration.Students WHERE NOT (Gender <> 'Male'); GO In this case, the result would include not the records that are not equal to the value, which would be equivalent to using = and not NOT.
As done for strings, if a field holds both numeric and null values, to find out whether a field holds a null value, apply the IS NULL expression to its condition. Here is an example: SELECT [Shelf #], Title, [Year] FROM Videos WHERE [Year] IS NULL; GO Unlike strings, number-based fields use all Boolean operators supported both by ISO SQL anbd Transact-SQL. They are:
Here is an example: void btnSelectClick(object sender, EventArgs e)
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoCollection1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("SELECT ALL * FROM Videos " +
"WHERE [Length] > 125;",
connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter sdaVideos = new SqlDataAdapter(command);
BindingSource bsVideos = new BindingSource();
DataSet dsVideos = new DataSet("VideosSet");
sdaVideos.Fill(dsVideos);
bsVideos.DataSource = dsVideos.Tables[0];
dgvVideos.DataSource = bsVideos;
}
}
This would produce:
There are various ways you can find the negation of a number-based comparison. As seen previously, to negate a comparison, you can precede the expression with the NOT operator. Otherwise, by definition, each Boolean operator has an opposite. They are:
Based on this, to find the negativity of a comparision, you can use the opposite operator.
As you may know already, a Boolean field is one whose type is BIT. A Boolean field can hold only one of two values: 0 or 1 (representing False or True, No or Yes, On or Off). As seen for the other types, a Boolean field can hold a null value if it didn't receive an actual value during data entry. This means that you can compare its value to IS NULL. Here is an example: SELECT StudentNumber, FirstName, LastName,
Gender, City, SingleParentHome
FROM Registration.Students
WHERE SingleParentHome IS NULL;
GO
In the same way, you can use IS NOT NULL, exactly as done for the other data type, to negate IS NULL. In Microsoft SQL Server, a Boolean or bit value is treated as a small integer. That is, it behaves as if it can use a value from 0 up. In this case, 0 means that the value of the field is false and 1 means the value of the field is true. When filtering Boolean records, you can use the 0 or the 1 value applied to a column with the equality operator. If you use any other number, or although you can also use any integer, you may get get only empty records.
Transact-SQL provides various data types to support dates, times, and combinations of dates and times. Like a string, the value of a date or time is provided or initialized in single-quotes. Unlike a string, the value of a date or time must follow strict rules inside the single-quotes, otherwise the value would be invalid. When performing comparisons on date/time-based fields, you must keep those rules in mind. Because a string-based field can contain anything, its comparison allows only equality or inequality. Unlike strings but like numbers, date and time values support all Boolean operators.
Boolean operators used in date/time fields work as follows:
Here is an example: SELECT StudentNumber, FirstName, LastName, DateOfBirth,
Gender, City, SingleParentHome
FROM Registration.Students
WHERE DateOfBirth >= '1995-01-01';
GO
When it comes to comparisons, date and time values follow the same approach as numbers, but date and time values add some logical rules. To negate a date/time comparison, you can precede it with the NOT operator. If you negate an equality comparison, the expression would mean that the date or time doesn't occur on the indicated value, but it doesn't indicate whether the date/time occurs before or after that date. Therefore, in most cases, to negate a date/time comparison, you should use the opposite of the operator. Because date/time values support all Boolean operators, the opposites of those operators apply in the same logic we reviewed for numeric values:
You can use all or some records from an existing table to create a new table that contains those records. To do this, you use the following formula: SELECT Columns INTO NewTableName FROM ExistingTable [WHERE Condition] To use all columns and all records, start with the SELECT operator, followed by *, followed by INTO, followed by a name for the table you want to create, followed by FROM, and the name of the original table that contains the records. Here is an example: void CopyVideos()
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoCollection2';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("SELECT * INTO SavedMovies FROM Videos;",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("The Videos table (along with its records) has been backed up.",
"Video Collection",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
Instead of using all columns, you can specify only the desired columns after the SELECT keyword. Here is an example: SELECT Title, Rating INTO ByRatings FROM Videos; GO Instead of using all records, you can use a condition by which the records would be selected and added to the new table you are creating. To set the condition, you can create a WHERE statement as the last in the whole expression. Here is an example: SELECT * INTO AllMovies FROM Videos; GO In the same way, you can select what columns and what records to put into the new table. To specify the records, you can use a WHERE condition. Here is an example: void CreateWideScreenList()
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoCollection2';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("SELECT CopyrightYear, Title, WideScreen " +
"INTO WideScreenList FROM Videos " +
"WHERE WideScreen = 1;",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A table of wide screen videos hse been created.",
"Video Collection",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
To refine your data analysis, you can use functions, whether functions you create yourself or the Transact-SQL built-in functions. As mentioned previously, the first candidates of functions you should try to use are the built-in functions, some of which we reviewed in Lesson 23. To use a built-in function, in the placeholder of the column, type the name of the function, followed by its parentheses. If the function takes some parameters, remember to follow the rules of calling a parameterized function. Here is an example that uses some date-based built-in functions to display the ages of the students: SELECT FirstName,
LastName,
Sex,
DATEDIFF(year, DateOfBirth, GETDATE()) AS Age,
City,
State
FROM Students
This would produce:
You can also include a function in any of the operators we have reviewed so far. Here is an example: SELECT FirstName, LastName, Gender, DateOfBirth, SPHome FROM Students WHERE (DateOfBirth BETWEEN CONVERT(DATETIME, N'1995-01-01', 102) AND CONVERT(DATETIME, N'1999-12-31', 102)) This would produce:
If you are working on a Windows application, you can assign the condition to the Filter property of the binding source.
If none of the built-in functions satisfies your needs, you can create your own and use it during data analysis. Obviously, you should first create the function. Here is an example of two functions created in the ROSH database: /* =============================================
Author: FunctionX
Create date: Friday 6 April, 2007
Description: This function is used
to get the full name of a student
=============================================*/
CREATE FUNCTION GetFullName
(
@FName varchar(20),
@LName varchar(20)
)
RETURNS varchar(41)
AS
BEGIN
RETURN @LName + N', ' + @FName;
};
GO
/* =============================================
Author: FunctionX
Create date: Saturday 7 April, 2007
Description: This function is used
to display Yes or No
============================================= */
CREATE FUNCTION ShowYesOrNo
(
@SPHomeStatus bit
)
RETURNS varchar(3)
AS
BEGIN
DECLARE @Result varchar(3);
IF @SPHomeStatus = 0
SET @Result = N'No';
ELSE
SET @Result = N'Yes';
RETURN @Result;
};
GO
Once a function is ready, in the placeholder of your SQL statement, type dbo., followed by the name of the function, its parentheses, and its paremeter(s), if any, inside of the parentheses. Here is an example: SELECT StudentID,
dbo.GetFullName(FirstName, LastName) AS [Student's Name],
Gender,
dbo.ShowYesOrNo(SPHome) AS [Live's in a Single Parent Home?],
ParentsNames AS [Parents' Names]
FROM Students;
GO
This would produce:
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
