When analyzing data or if you are creating a query using the Table window, you can type an expression that uses one or more logical operators. Here is an example of an expression > N'12/31/1993' This means that the dates that occur after 1993 would be selected.
You can use a WHEN conditional statement to refine data selection. Consider the following table: using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
Button btnDatabase;
DataGridView dgvEmployees;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
btnDatabase = new Button();
btnDatabase.Text = "Database";
btnDatabase.Location = new Point(12, 12);
btnDatabase.Click += new EventHandler(btnDatabaseClick);
dgvEmployees = new DataGridView();
dgvEmployees.Location = new Point(12, 44);
dgvEmployees.Size = new System.Drawing.Size(470, 270);
dgvEmployees.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
Text = "Video Collection";
Size = new System.Drawing.Size(500, 350);
Controls.Add(btnDatabase);
Controls.Add(dgvEmployees);
}
void CreateDatabase()
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Integrated Security=SSPI;"))
{
SqlCommand command =
new SqlCommand("IF EXISTS (SELECT name " +
"FROM sys.databases " +
"WHERE name = N'VideoStore1') " +
"DROP DATABASE VideoStore1 " +
"CREATE DATABASE VideoStore1 " +
"ON PRIMARY " +
"( NAME = StoreRepository, FILENAME = 'C:\\Video Store\\Video1.mdf') " +
"LOG ON " +
"( NAME = StoreLog, FILENAME = 'C:\\Video Store\\Video1.ldf');",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A database named VideoStore1 has been created.",
"Video Store",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoStore1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("CREATE TABLE Employees(" +
"FirstName nvarchar(20), " +
"MI nchar(2), " +
"LastName nvarchar(20), " +
"HourlySalary smallmoney, " +
"Status tinyint);",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A table named \"Employees\" has been created.",
"Video Store",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoStore1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("INSERT INTO Employees " +
"VALUES(N'Christopher', N'L', N'Larsen', 14.50, 3), " +
" (N'Henry', N'C', N'Jonathan', 12.85, 1), " +
" (N'Lance', N'A', N'Seagal', 16.95, 1); " +
"INSERT INTO Employees(FirstName, LastName, Status) " +
"VALUES(N'Roberta', N'Ortez', 1); " +
"INSERT INTO Employees(FirstName, LastName, HourlySalary) " +
"VALUES(N'Paul', N'Swanson', 10.90); " +
"INSERT INTO Employees " +
"VALUES(N'Kristofer', N'L', N'Michaels', 12.85, 2); " +
"INSERT INTO Employees " +
"VALUES(N'Ivan', N'N', N'Sanders', 15.00, 2), " +
" (N'David', N'P', N'Monahan', 13.05, 1);",
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)
{
CreateDatabase();
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
Imagine you want to select the Status column: void ShowRecords()
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoStore1';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("SELECT * FROM Employees;",
connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter sdaEmployees =
new SqlDataAdapter(command);
BindingSource bsEmployees = new BindingSource();
DataSet dsEmployees = new DataSet("EmployeesSet");
sdaEmployees.Fill(dsEmployees);
bsEmployees.DataSource = dsEmployees.Tables[0];
dgvEmployees.DataSource = bsEmployees;
}
}
void btnDatabaseClick(object sender, EventArgs e)
{
// CreateDatabase();
ShowRecords();
}
If a column has values that are difficult to identify, you can use a CASE conditional statement to customize the result(s). Here is an example: void ShowRecords()
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoStore1';" +
"Integrated Security=yes;"))
{
SqlCommand command = new SqlCommand(
"SELECT FirstName, MI, LastName, HourlySalary, Status = " +
"CASE Status " +
" WHEN 1 THEN N'Full-Time' " +
" WHEN 2 THEN N'Part-Time' " +
"END " +
"FROM Employees;",
connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter sdaEmployees =
new SqlDataAdapter(command);
BindingSource bsEmployees = new BindingSource();
DataSet dsEmployees = new DataSet("VideosSet");
sdaEmployees.Fill(dsEmployees);
bsEmployees.DataSource = dsEmployees.Tables[0];
dgvEmployees.DataSource = bsEmployees;
}
}
Remember that WHEN can use an ELSE section if no CASE matches the values you provided. Here is an example of using it: void ShowRecords()
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoStore1';" +
"Integrated Security=yes;"))
{
SqlCommand command = new SqlCommand(
"SELECT FirstName, MI, LastName, HourlySalary, Status = " +
"CASE Status " +
" WHEN 1 THEN N'Full-Time' " +
" WHEN 2 THEN N'Part-Time' " +
" ELSE N'Contractor/Seasonal' " +
"END " +
"FROM Employees;",
connection);
connection.Open();
command.ExecuteNonQuery();
SqlDataAdapter sdaEmployees = new SqlDataAdapter(command);
BindingSource bsEmployees = new BindingSource();
DataSet dsEmployees = new DataSet("VideosSet");
sdaEmployees.Fill(dsEmployees);
bsEmployees.DataSource = dsEmployees.Tables[0];
dgvEmployees.DataSource = bsEmployees;
}
}
If you have a long group of records, you can specify that you want to see only a certain number of records. To do this, after the SELECT operator, type TOP followed by an integral number. Continue the SELECT statement as you see fit. For example, instead of this: using System;
using System.Drawing;
using System.Data;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
Button btnDatabase;
DataGridView dgvVideos;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
btnDatabase = new Button();
btnDatabase.Text = "Database";
btnDatabase.Location = new Point(12, 12);
btnDatabase.Click += new EventHandler(btnDatabaseClick);
dgvVideos = new DataGridView();
dgvVideos.Location = new Point(12, 44);
dgvVideos.Size = new System.Drawing.Size(470, 270);
dgvVideos.Anchor = AnchorStyles.Left | AnchorStyles.Top|
AnchorStyles.Right | AnchorStyles.Bottom;
Text = "Video Collection";
Size = new System.Drawing.Size(500, 350);
Controls.Add(btnDatabase);
Controls.Add(dgvVideos);
}
void CreateDatabase()
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Integrated Security=SSPI;"))
{
SqlCommand command =
new SqlCommand("CREATE DATABASE VideoCollection2 " +
"ON PRIMARY " +
"( NAME = VideosRepository, FILENAME = 'C:\\Video Store\\Video2.mdf') " +
"LOG ON " +
"( NAME = VideosLog, FILENAME = 'C:\\Video Store\\Video2.ldf');",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A database named VideoCollection2 has been created.",
"Video Collection",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoCollection2';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("CREATE TABLE Videos(" +
"Title nvarchar(80), " +
"CopyrightYear smallint, " +
"Length nvarchar(30), " +
"Rating nvarchar(6), " +
"WideScreen bit);",
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='VideoCollection2';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("INSERT INTO Videos " +
"VALUES(N'A Few Good Men', 1992, N'138 Minutes', N'R', 0); " +
"INSERT INTO Videos(Title, CopyrightYear, Length) " +
"VALUES(N'Silence of the Lambs (The)', 1991, N'118 Minutes'); " +
"INSERT INTO Videos(Rating, Title, Length, CopyrightYear) " +
"VALUES(N'R', N'Wall Street', N'126 Minutes', 1987); " +
"INSERT INTO Videos(Title, Length, CopyrightYear) " +
"VALUES(N'Michael Jackson Live in Bucharest', N'122 Minutes', 1992); " +
"INSERT INTO Videos(Title, WideScreen, Length) " +
"VALUES(N'Distinguished Gentleman (The)', 0, N'112 Minutes'); " +
"INSERT INTO Videos " +
"VALUES(N'Her Alibi', 1998, N'94 Minutes', N'PG-13', 0), " +
" (N'Memoirs of a Geisha', 2006, N'145 Minutes', N'PG-13', 1), " +
" (N'Two for the Money', 2008, N'2 Hrs. 3 Mins.', N'R', 1); " +
"INSERT INTO Videos(Title, Length, WideScreen) " +
"VALUES(N'Lady Killers (The)', N'104 Minutes', 0); " +
"INSERT INTO Videos(Title, Length) " +
"VALUES(N'Ghosts of Mississippi', N'130 Minutes'); " +
"INSERT INTO Videos " +
"VALUES(N'Platoon', 1986, N'120 Minutes', N'R', 1), " +
" (N'Armageddon', 1998, N'150 Mins', N'PG-13', 0), " +
" (N'The People vs. Larry Flynt', 1996, N'129 Minutes', N'R', 0); " +
"INSERT INTO Videos(Rating, Title, Length) " +
"VALUES(N'PG-13', N'Sneakers', N'2 Hrs. 6 Mins.'), " +
" (N'R', N'Soldier', N'99 Mins.'); " +
"INSERT INTO Videos(CopyrightYear, Rating, Title) " +
"VALUES(1995, N'R', N'Bad Boys'), " +
" (2007, N'PG-13', N'Transformers');",
connection);
connection.Open();
command.ExecuteNonQuery();
MessageBox.Show("A few records have been created.",
"Video Collection",
MessageBoxButtons.OK,
MessageBoxIcon.Information);
}
}
void ShowRecords()
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoCollection2';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("SELECT * 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 btnDatabaseClick(object sender, EventArgs e)
{
// CreateDatabase();
ShowRecords();
}
public static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
To show only the first 10 records, you would write this: void ShowRecords()
{
using (SqlConnection connection =
new SqlConnection("Data Source=(local);" +
"Database='VideoCollection2';" +
"Integrated Security=yes;"))
{
SqlCommand command =
new SqlCommand("SELECT TOP 10 * 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;
}
}
You can also include the number in parentheses. Here is an example: SELECT TOP(10) * FROM Videos; Just as done here, you can apply the TOP operator to any of the statements we will see for the rest of our lessons.
If you are writing your SELECT statement, to formulate a condition, you use the WHERE keyword with a basic formula as follows: SELECT What FROM WhatObject WHERE Expression; The expressions used in conditions are built using algebraic, logical, and string operators. The Expression factor is called a criterion(the plural is criteria). The expression is written using the formula: ColumnName=Value The ColumnName factor must be an existing column of a table. It is followed by the assignment operator. The Value factor is the value that would set the condition. If the value is a word or a group of words (also called a string), you must include it in single-quotes. If it is a number, you can type its numeric value. To apply a WHERE condition, if you are working from a table in the Microsoft SQL Server Management Studio or in Microsoft Visual Studio, in the Criteria section and under the Filter column, click the box that corresponds to the field on which the condition will be applied, and type the value of the expression (only the value). Here is an example:
If you are writing writing the SQL statement to specify the condition, after FROM table, enter WHERE followed by the whole Expression. Here is an example: SELECT DateOfBirth, LastName, FirstName,
Gender, ParentsNames
FROM Students
WHERE Gender = N'Female';
If you are working in a table window and specify the expression in the Criteria section, the WHERE condition would be created in the SQL section and, to make it easier to read, it would be included in parentheses:
When creating a condition, you can sort it if you want. If you are working in a table window, in the Criteria section, under the Sort Type column, click the box corresponding to the field that will be used as the basis. In the Filter column, click the box that corresponds to the column that will hold the condition and enter the expression. Here is an example:
If you are writing the SQL statement, after the WHERE condition, enter the ORDER BY expression. Here is an example: SELECT DateOfBirth, LastName, FirstName,
Gender, State, ParentsNames
FROM Students
WHERE State = 'MD'
ORDER BY LastName;
This would produce:
In our SELECT statements so far, we were selecting the columns we needed to display. When formulating such a statement, you can apply a condition to a column without including that column in the result. For example, imagine you create a statement that produces a list of female students. Since we know that the result would show only the girls, it becomes redundant to include the Sex column in the statement. In this case, you can hide that column in the result. To hide a column from a SELECT statement, if you are working from the table in Microsoft SQL Server Management Studio or in Microsoft Visual Studio, in the Diagram or in the Criteria sections, select the column. Then, in the Criteria section, under the Output column, uncheck the box corresponding to the field you want to hide. If you are writing a SQL statement, omit the column in the SELECT statement but involve it in the WHERE condition. Here is an example: SELECT DateOfBirth, LastName,
FirstName, State, ParentsNames
FROM Students
WHERE Gender = N'Female';
GO
This would produce:
Notice that the SELECT statement doesn't have the Gender column and the resulting query doesn't show the Gender column.
Depending on how it was created, a field can have actual or null values. To check whether a field is holding a null value, use the following formula for the WHERE statement: WHERE ColumnName IS NULL
In this case, only the records that are NULL on the ColumnName will be considered in the result.
In Lesson 12, we saw that you could use the NOT operator to negate the validity of a Boolean expression. Consider the following statement: SELECT DateOfBirth, LastName, FirstName,
State, ParentsNames
FROM Students
WHERE Gender = N'Female';
When this statement is executed, a list of female students would display. Instead of girls, to get a list of male students, you can negate the WHERE condition. To do this, type NOT before the expression. Here is an example: SELECT DateOfBirth, LastName, FirstName,
Gender, State, ParentsNames
FROM Students
WHERE NOT Gender = N'Female';
GO
To make this condition easier to read, you should include the positive expression in parentheses. This would be done as follows: SELECT DateOfBirth, LastName, FirstName,
Gender, State, ParentsNames
FROM Students
WHERE NOT (Sex = N'Female');
This clearly indicates that it is the expression in the parentheses that is being negated. In the same way, you can use the IS NOT NULL to find the records that are not null. For example, you can create a list of only records that don't have a null value on a certain column. Here is an example: SELECT DateOfBirth, LastName, FirstName,
State, ParentsNames
FROM Students
WHERE State IS NOT NULL;
When this statement is executed, the table would display only the records that include a state for each student.
|
|||||||||||||||||||||||||||||||||
