Data Selection
|
Data Selection |
Fundamentals of Data Selection
Introduction
After creating a table and populating it with records, you may want to see what you can do with data. One of the most commonly performed operations by the users of a database is to look for data or to isolate data that respond to a particular criterion. Looking for data is referred to as querying. The result of retrieving data based on a criterion is called a query.
As a database developer, you perform queries by passing instructions to the database engine. This is done with some special reserved words.
|
SELECTing a Column |
As opposed to selecting all records, you may be interested in only one particular column whose fields you want to view. To select a column, you can replace the WhatField(s) placeholder of our formula with the name of the desired column. For example, to get a list of last names of students, you would execute the following statement:
SELECT LastName FROM Registration.Students;
GO
You can also qualify a column by preceding it with the name of the table followed by the period operator. The above statement is equivalent to:
SELECT Students.LastName FROM Registration.Students;
When you execute the statement, it would display only the column that contains the last names.
|
Selecting Some Fields |
As opposed to selecting all fields, you can select one particular column or a few columns whose data you want to view. Once again, the SQL provides its own means of selecting some columns from a table. To do this, you can replace the What factor in our formula with the name of the desired columns. To select one column, in the What factor, specify the name of that column. For example, to get the list of last names of students, you would create the following statement:
SELECT LastName FROM Students;
When you execute the statement, it would display only the column that contains the last names.
To create a SELECT statement that includes more than one column, in the What factor of our syntax, enter the name of each column, separating them with a comma except for the last column. The syntax you would use is:
SELECT Column1, Column2, Column_n FROM WhatObject;
For example, to display a list that includes the first name, the last name, the sex, the email address, and the home phone of records from a table called Students, you would create the SQL statement as follows:
SELECT FirstName, LastName, Sex, City, State FROM Students;
Consider the following Employees table:
CREATE DATABASE Corporation;
GO
USE Corporation;
GO
CREATE TABLE Employees
(
EmployeeNumber int unique not null,
FirstName nvarchar(20),
LastName nvarchar(20) not null,
Department nvarchar(50) null,
EmploymentStatus nvarchar(30),
HourlySalary money
);
GO
INSERT INTO Employees
VALUES(84628, N'Anthony', N'Holms', N'Corporate', N'Full Time', 24.72),
(40574, N'William', N'Wilson', N'Information Technology', N'Full Time', 21.18),
(27462, N'Peter', N'Swanson', N'Corporate', N'Full Time', 22.84),
(52835, N'Edward', N'Johansen', N'Information Technology', N'Consultant', 15.50),
(93075, N'Sharon', N'Edson', N'Accounting', N'Full Time', 28.74),
(82718, N'Yanuh', N'Hadhah', N'Corporate', N'Full Time', 14.86),
(29174, N'Hamin', N'Souleyman', N'Public Relations', N'Consultant', 18.76);
GO
You can use all or some records from an existing table to create a new table that would contain those existing records. To do this, 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:
USE Corporation; GO SELECT * INTO CompanyRecipients FROM Employees; GO
Instead of using all columns, you can specify only the desired columns after the SELECT keyword. Here is an example:
USE Corporation; GO SELECT EmployeeNumber, LastName, FirstName, EmploymentStatus INTO Salaried FROM Employees; 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:
USE Corporation; GO SELECT * INTO FullTimeEmployees FROM Employees WHERE EmploymentStatus = N'Full Time'; GO
|
Data Selection and Windows Controls |
|
Introduction to Data Binding |
As you may know already, ADO.NET is not a library (unlike its ancestor ADO). ADO.NET is a set of techniques to create and manage databases in the .NET Framework. The .NET Framework provides many Windows controls that allow a user to interact with the computer. Furthermore, the .NET Framework provides functionality to allow every control to be used in a graphical database application. On one hand, every control has characteristics (methods and especially properties) inherited from the Control class to be able to directly use a database without any special code. On the other hand, the.NET Framework provides additional classes that provide easier and/or additional (extra) functionality.
|
The DataSet Class |
As you may know already, DataSet is an intermediary class that can get tables and records from a database and make them available to a Windows control. To start, you must declare and initialize a DataSet variable. Then you must indicate how it would get values (or records). To do this, you can declare a data adapter variable from which you would indicate a command. Use that data adapter to "fill" the data set. The data set is then ready. You can get records from it. Use its properties, namely the indexed property, to retrieve the desired value and assign it to the right Windows control.
|
A Data Reader |
A command is an object that gets values from accessing a database through a connection. The command specifies the records to get from one or more tables. A data reader is an object that gets or "reads" the records of a command. From the data reader, you can locate the value of a column and pass that value to the desired Windows control.
|
The Binding Class |
The Binding class allows a Windows control to connect to a column of a table of a database. To make it possible, the class is equipped with various constructors. The Binding class doesn’t have a default constructor. This means that, to use a variable of this class, you must specify how the binding will be done. The most fundamental constructor of the Binding class uses the following syntax:
public Binding(
string propertyName,
object dataSource,
string dataMember
);
The first argument of this constructor is the name of the control’s property that will hold the value from the table. The argument is passed as a string. For a text-based control such as text box, this argument can be passed as Text. For a date/time-based control, this argument can be specified as Value. In more cases, this argument is the default property that holds that value of a control.
The second argument can be a DataSet variable that holds the records from a table from a database. The data set must have been initialized and equipped with the desired records.
The third argument is the name of the column from the table from the database. The argument is passed as a string. The name of the column should be qualified with the name of the table and possibly its schema.
Specifying What to Select
|
Selecting All Fields |
From the columns of a table, you can use all fields if you want. The most fundamental keyword used by SQL is SELECT. In order to process a request, you must specify what to select. To perform data selection, the SELECT keyword uses the following syntax:
SELECT What FROM WhatObject;
To select everything from a table, if you are working in the table view, in the SQL section, you can type * after the SELECT operator. If you are writing your SQL statement, you can use the asterisk as the What factor of your SELECT statement. Here is an example:
using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;
public class Exercise : System.Windows.Forms.Form
{
Button btnShowRecords;
Button btnCreateDatabase;
DataGridView dgvVideos;
public Exercise()
{
InitializeComponent();
}
void InitializeComponent()
{
btnCreateDatabase = new Button();
btnCreateDatabase.Text = "Create Database";
btnCreateDatabase.Location = new Point(12, 12);
btnCreateDatabase.Width = 100;
btnCreateDatabase.Click += new EventHandler(btnCreateDatabaseClick);
btnShowRecords = new Button();
btnShowRecords.Text = "Show Records";
btnShowRecords.Width = 120;
btnShowRecords.Location = new Point(120, 12);
btnShowRecords.Click += new EventHandler(btnShowRecordsClick);
dgvVideos = new DataGridView();
dgvVideos.Location = new Point(12, 46);
Text = "Video Collection";
Controls.Add(btnCreateDatabase);
Controls.Add(btnShowRecords);
Controls.Add(dgvVideos);
StartPosition = FormStartPosition.CenterScreen;
dgvVideos.Width = this.Width - 30;
dgvVideos.Height = this.Height - 80;
dgvVideos.Anchor = AnchorStyles.Left | AnchorStyles.Top |
AnchorStyles.Right | AnchorStyles.Bottom;
}
void btnCreateDatabaseClick(object sender, EventArgs e)
{
using (SqlConnection cntVideos =
new SqlConnection("Data Source='EXPRESSION';" +
"Integrated Security=SSPI;"))
{
SqlCommand cmdVideos =
new SqlCommand("CREATE DATABASE VideoCollection2;",
cntVideos);
cntVideos.Open();
cmdVideos.ExecuteNonQuery();
MessageBox.Show("A database named VideoCollection2 has been created.",
"Video Collection",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
using (SqlConnection cntVideos =
new SqlConnection("Data Source='EXPRESSION';" +
"Database='VideoCollection2';" +
"Integrated Security=yes;"))
{
SqlCommand cmdVideos =
new SqlCommand("CREATE TABLE Videos(" +
"Title nvarchar(80), " +
"CopyrightYear smallint, " +
"Length nvarchar(30), " +
"Rating nvarchar(6), " +
"WideScreen bit);",
cntVideos);
cntVideos.Open();
cmdVideos.ExecuteNonQuery();
MessageBox.Show("A table named Videos has been created.",
"Video Store",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
using (SqlConnection cntVideos =
new SqlConnection("Data Source='EXPRESSION';" +
"Database='VideoCollection2';" +
"Integrated Security=yes;"))
{
SqlCommand cmdVideos =
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');",
cntVideos);
cntVideos.Open();
cmdVideos.ExecuteNonQuery();
MessageBox.Show("A few records have been created.",
"Video Store",
MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
private void btnShowRecordsClick(object sender, EventArgs e)
{
using (SqlConnection cntVideos =
new SqlConnection("Data Source='EXPRESSION';" +
"Database='VideoCollection2';" +
"Integrated Security=yes;"))
{
SqlCommand cmdVideos =
new SqlCommand("SELECT * FROM Videos;",
cntVideos);
cntVideos.Open();
cmdVideos.ExecuteNonQuery();
SqlDataAdapter sdaVideos = new SqlDataAdapter(cmdVideos);
BindingSource bsVideos = new BindingSource();
DataSet dsVideos = new DataSet("VideosSet");
sdaVideos.Fill(dsVideos);
bsVideos.DataSource = dsVideos.Tables[0];
dgvVideos.DataSource = bsVideos;
}
}
}
public class Program
{
[STAThread]
static int Main()
{
System.Windows.Forms.Application.Run(new Exercise());
return 0;
}
}
|
Qualifying the Names of Fields |
Qualifying the name(s) of (a) column(s) consists of indicating what table to which it (they) belongs. The way you do this depends on some issues.
The primary way to qualify the name of a column is to precede it with the name of the table followed by a period.
If you are using the asterisk * to select all records, precede it with the name of the table followed by a period. Here is an example:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace DataSelection2
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void Exercise_Load(object sender, EventArgs e)
{
using (SqlConnection cntStudents =
new SqlConnection("Data Source=(local);" +
"Database='ROSH';" +
"Integrated Security=yes;"))
{
SqlCommand cmdStudents =
new SqlCommand("SELECT Students.* FROM Registration.Students;,
cntStudents);
cntStudents.Open();
cmdStudents.ExecuteNonQuery();
SqlDataAdapter sdaStudents = new SqlDataAdapter(cmdStudents);
BindingSource bsStudents = new BindingSource();
DataSet dsStudents = new DataSet("StudentsSet");
sdaStudents.Fill(dsStudents);
bsStudents.DataSource = dsStudents.Tables[0];
dgvStudents.DataSource = bsStudents;
}
}
}
}
You can also qualify the name of a column using a schema. To do this, use the following formula:
SchemaName.TableName.ColumnName
An alias is another name for an object. You can create an alias name for a table to use in an expression that involves a column.
To create an alias of a table using code, use a letter or a word that will represent the table. First type the name of the table, followed by space, and followed by the letter or the word. An example would be Employee empl. If the table belongs to a schema, precede the name of the table with it. Here is an example:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace DataSelection2
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void Exercise_Load(object sender, EventArgs e)
{
using (SqlConnection cntStudents =
new SqlConnection("Data Source=(local);" +
"Database='ROSH';" +
"Integrated Security=yes;"))
{
SqlCommand cmdStudents =
new SqlCommand("SELECT * FROM Registration.Students pupils;",
cntStudents);
cntStudents.Open();
cmdStudents.ExecuteNonQuery();
SqlDataAdapter sdaStudents = new SqlDataAdapter(cmdStudents);
BindingSource bsStudents = new BindingSource();
DataSet dsStudents = new DataSet("StudentsSet");
sdaStudents.Fill(dsStudents);
bsStudents.DataSource = dsStudents.Tables[0];
dgvStudents.DataSource = bsStudents;
}
}
}
}
We mentioned that you could qualify the name of a column or * with the name of the table. If the table has an alias, you can qualify the column using the alias. This:
SELECT Registration.Students.* FROM Registration.Students;
Is the same as
SELECT pupils.* FROM Registration.Students pupils;
You can also use an alias that is made or more than one word. If you are visually creating the alias, select the table and type the words in the Properties window. As soon as you press Enter, square brackets would be added to the left and the right of the name. If you are using SQL code, you can include the alias name in double-quotes. Here is an example:
SELECT FirstName
FROM Registration.Students "Little Angels";
GO
Then, to qualify a column, use the quoted alias name. Here are examples:
SELECT "Little Angels".FirstName FROM Registration.Students "Little Angels"; GO
Instead of using double-quotes, an alterntive is to include the alias in square brackets []. When qualifying the name(s) of the column(s), you can use the alias either in double-quotes or in square brackets.
When creating an alias, you can include the AS keyword between the name of the table and its alias. Here is an example:
SELECT * FROM Registration.Students AS pupils;
GO
This is the same as:
SELECT pupils.* FROM Registration.Students AS pupils; GO
A synonym is another name for an object that already has a name. That is, a synonym is a pseudo-name for an existing object. A synonym can be valuable if you want to provide a shorter name for an object. At first glance, a synonym for a table is like an alias. The difference is that an alias is created locally where you want to use it and it can be used only in the code where you create or define it. A synonym is a name you create globally as an object and you can use that name throughout the database.
The formula to programmatically create a synonym is:
CREATE SYNONYM [ schema_name_1. ] synonym_name FOR <object>
<object> :: =
{
[ server_name.[ database_name ] . [ schema_name_2 ].| database_name . [ schema_name_2 ].| schema_name_2. ] object_name
}
In the Synonym Name text box or placeholder, type the desired name. It can be anything to follows the rules of names in Transact-SQL. If the object belongs to a schema and if you want to represent that schema with a synonym, specify it as the schema_name. This is optional. As another option, if you want to indicate the server, type it as the server_name. If the synonym belongs to a schema other than dbo, specify it.
Here is an example of creating a synonym:
CREATE SYNONYM Staff FOR Personnel.Employees; GO
To use a synonym, in the place where you would have used the name of the object, use the synonym. Here is an example that uses the synonym created visually:
SELECT FirstName FROM Pupils;
GO
Here is an example that uses the synonym created with code:
SELECT FirstName FROM Staff;
GO
You can also use the synonym to qualify the names of the columns. Here is an example:
SELECT Staff.FirstName FROM Staff; GO
You can also create a local alias for the synonym and use it. Here is an example:
SELECT kids.FirstName FROM Pupils kids; GO
If you specify a column to select from a table, every record would come up. This can cause the same value to repeat over and over. Sometimes you want to show each value only once. To get such a result, you can use the DISTINCT keyword before the name of the column in the SELECT statement.
In most cases, you would get a better result if you select only one column. Still, you can use as many columns as you want.
|
SELECTing Many Fields of the Same Table |
To consider more than one column in a statement, you can list them in the WhatField(s) placeholder of our formula, separating them with a comma except for the last column. The syntax you would use is:
SELECT Column1, Column2, Column_n FROM WhatObject;
For example, to display a list that includes only the names, gender, email address and home phone of records from a table called Students, you would type:
SELECT FirstName, LastName, Gender, EmailAddress, HomePhone FROM Registration.Students;
You can qualify each column by preceding it with the name of the table followed by the period operator. The above statement is equivalent to:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace DataSelection2
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void Exercise_Load(object sender, EventArgs e)
{
using (SqlConnection cntStudents =
new SqlConnection("Data Source=(local);" +
"Database='ROSH';" +
"Integrated Security=yes;"))
{
SqlCommand cmdStudents =
new SqlCommand("SELECT Students.FirstName, " +
" Students.LastName, " +
" Students.Gender, " +
" Students.EmailAddress, " +
" Students.HomePhone " +
"FROM Registration.Students;",
cntStudents);
cntStudents.Open();
cmdStudents.ExecuteNonQuery();
SqlDataAdapter sdaStudents = new SqlDataAdapter(cmdStudents);
BindingSource bsStudents = new BindingSource();
DataSet dsStudents = new DataSet("StudentsSet");
sdaStudents.Fill(dsStudents);
bsStudents.DataSource = dsStudents.Tables[0];
dgvStudents.DataSource = bsStudents;
}
}
}
}
You don't have to qualify all columns, you can qualify some and not qualify some others. The above statement is equivalent to:
SELECT Students.FirstName,
LastName,
Students.Gender,
EmailAddress,
HomePhone
FROM Registration.Students;
Once again, remember that you can use an alias name for a table by preceding each column with a letter or a word and a period operator, and then entering the name of the table followed by that letter or word. Here is an example:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace DataSelection2
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void Exercise_Load(object sender, EventArgs e)
{
using (SqlConnection cntStudents =
new SqlConnection("Data Source=(local);" +
"Database='ROSH';" +
"Integrated Security=yes;"))
{
SqlCommand cmdStudents =
new SqlCommand("SELECT std.FirstName, " +
" std.LastName, " +
" std.Gender, " +
" std.EmailAddress, " +
" std.HomePhone " +
"FROM Registration.Students std;",
cntStudents);
cntStudents.Open();
cmdStudents.ExecuteNonQuery();
SqlDataAdapter sdaStudents = new SqlDataAdapter(cmdStudents);
BindingSource bsStudents = new BindingSource();
DataSet dsStudents = new DataSet("StudentsSet");
sdaStudents.Fill(dsStudents);
bsStudents.DataSource = dsStudents.Tables[0];
dgvStudents.DataSource = bsStudents;
}
}
}
}
Remember that if the alias is in more than one word, you can include it in either double-quotes or between square brackets. Here are examples:
SELECT [Little Angels].FirstName, "Little Angels".LastName, [Little Angels].HomePhone, "Little Angels".ParentsNames FROM Registration.Students [Little Angels]; GO
|
Selecting the TOP Number of Records |
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. Here us an example:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace DataSelection2
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void Exercise_Load(object sender, EventArgs e)
{
using (SqlConnection cntStudents =
new SqlConnection("Data Source=(local);" +
"Database='ROSH';" +
"Integrated Security=yes;"))
{
SqlCommand cmdStudents =
new SqlCommand("SELECT TOP 10 * FROM Registration.Students;",
cntStudents);
cntStudents.Open();
cmdStudents.ExecuteNonQuery();
SqlDataAdapter sdaStudents = new SqlDataAdapter(cmdStudents);
BindingSource bsStudents = new BindingSource();
DataSet dsStudents = new DataSet("StudentsSet");
sdaStudents.Fill(dsStudents);
bsStudents.DataSource = dsStudents.Tables[0];
dgvStudents.DataSource = bsStudents;
}
}
}
}
You can also include the number in parentheses. Here is an example:
SELECT TOP(10) * FROM Videos;
|
Selecting the TOP PERCENT Records |
Instead of selecting a specific number of records, you can ask the database engine to produce a percentage of records.
The formula to specify a percentage of records using code is:
SELECT TOP Value PERCENT WhatColumns FROM WhatObject
After the TOP keword type a number. To indicate that you want a percentage of values, use the PERCENT keyword. Here is an example:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace DataSelection2
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void Exercise_Load(object sender, EventArgs e)
{
using (SqlConnection cntStudents =
new SqlConnection("Data Source=(local);" +
"Database='ROSH';" +
"Integrated Security=yes;"))
{
SqlCommand cmdStudents =
new SqlCommand("SELECT TOP 25 PERCENT * FROM Registration.Students;",
cntStudents);
cntStudents.Open();
cmdStudents.ExecuteNonQuery();
SqlDataAdapter sdaStudents = new SqlDataAdapter(cmdStudents);
BindingSource bsStudents = new BindingSource();
DataSet dsStudents = new DataSet("StudentsSet");
sdaStudents.Fill(dsStudents);
bsStudents.DataSource = dsStudents.Tables[0];
dgvStudents.DataSource = bsStudents;
}
}
}
}
As an alternative, you can include the Value in parentheses:
SELECT TOP(25) PERCENT * FROM Videos;
This statement would produce a quarter (25%) the number of records in the table. For example, if the table has 12 records, the interpreter would produce 3 records. In reality, the database engine would divide the number of records to the number in the parentheses and convert the value to the closest integer, then produce that number of records. For example, if the table has 11 records and you ask for 25%, the interpreter would produce 11 / (100/25) = 11 / 4 = 2.75. The closest high integer is 3. So the database engine would produce 3 records.
|
SELECTing Fields From Different Tables |
If you have more than one table in your database, you can use a statement that selects any field(s) you want from those tables. Neither the tables nor the columns need to have anything in common. The formula to follow is:
SELECT WhatField(s) FROM Table_1, Table_2, Table_n
You start with SELECT followed by the list of fields from the tables. If the tables have columns with different names, you can simply list the name of each column. Consider the following tables:
CREATE DATABASE Exercise;
GO
USE Exercise;
GO
CREATE TABLE Employees
(
[Empl #] nchar(7),
[First Name] nvarchar(20),
[Last Name] nvarchar(20),
[Hourly Salary] money
);
GO
CREATE TABLE Products
(
Number int,
Name nvarchar(50),
UnitPrice money,
);
GO
INSERT INTO Employees
VALUES(N'207-025', N'Julie', N'Flanell', 36.55),
(N'926-705', N'Paulette', N'Simms', 26.65),
(N'240-002', N'Alexandra', N'Ulm', 12.85),
(N'847-295', N'Ellie', N'Tchenko', 11.95);
GO
INSERT INTO Products
VALUES(217409, N'Short Black Skirt', 55.85),
(284001, N'Pencil Skirt', 49.00);
GO
Here is an example of selecting columns from those tables:
SELECT [Empl #], [First Name], [Last Name], Name, UnitPrice FROM Employees, Products; GO
When you select fields from different tables, in the result, each of the records of the first table would display, each showing the first record (combination of the selected columns) of the second table. Then each of the records of the first table would show again, followed by the second record (combination of the selected columns) of the second table. This will continue until all records of the second table have displayed. Consequently, the resulting query would contain (Number of Records of First Table) x (Number of Records of Second Table). For example, if the first table contains 4 records and the second table contains 2 records, the statement would produce 4 x 2 = 8 records.
Imagine your tables have each a column with the same name:
DROP TABLE Employees;
GO
DROP TABLE Products;
GO
CREATE TABLE Employees
(
[Empl #] nchar(7),
Name nvarchar(50),
[Hourly Salary] money
);
GO
CREATE TABLE Products
(
Number int,
Name nvarchar(50),
UnitPrice money,
);
GO
INSERT INTO Employees
VALUES(N'207-025', N'Julie Flanell', 36.55),
(N'926-705', N'Paulette Simms', 26.65),
(N'240-002', N'Alexandra Ulm', 12.85),
(N'847-295', N'Ellie Tchenko', 11.95);
GO
INSERT INTO Products
VALUES(217409, N'Short Black Skirt', 55.85),
(284001, N'Pencil Skirt', 49.00);
GO
When selecting the columns, you must qualify at least the column(s) with the same name. Otherwise you would receive an error. Therefore, the above Name columns can be accessed as follows:
SELECT [Empl #], empl.Name, prod.Name, UnitPrice FROM Employees empl, Products prod; GO
Of course, you can qualify all columns of the tables. Here are examples:
DROP TABLE Employees;
GO
DROP TABLE Products;
GO
CREATE TABLE Employees
(
[Empl #] nchar(7),
[First Name] nvarchar(20),
[Last Name] nvarchar(20),
[Hourly Salary] money
);
GO
CREATE TABLE Products
(
Number int,
Name nvarchar(50),
UnitPrice money,
);
GO
INSERT INTO Employees
VALUES(N'207-025', N'Julie', N'Flanell', 36.55),
(N'926-705', N'Paulette', N'Simms', 26.65),
(N'240-002', N'Alexandra', N'Ulm', 12.85),
(N'847-295', N'Ellie', N'Tchenko', 11.95);
GO
INSERT INTO Products
VALUES(217409, N'Short Black Skirt', 55.85),
(284001, N'Pencil Skirt', 49.00);
GO
SELECT empl.[Empl #], empl.[First Name], empl.[Last Name],
prod.Name, prod.UnitPrice
FROM Employees empl, Products prod;
GO
Just as we have used only two tables, you can select records from three or more tables, following the same formula.
|
Selecting Many Tables |
In the Query Editor or the Query Designer, you can show the records of as many tables as you want and those tables don't need to have anything in common. They don't even have to belong to the same database. Consider the following database named Cruise equipped with a table named Cabins:
CREATE DATABASE Cruise;
GO
USE Cruise;
GO
CREATE TABLE Cabins(
CabinType nvarchar(20) not null,
Deck nchar(20),
Size int,
Rate1Passenger money,
Rate2Passengers money);
GO
INSERT INTO Cabins -- Size in sq/ft
VALUES(N'Inside', N'Riviera', 215, 289.00, 578.00),
(N'Outside', N'Riviera', 185, 319.00, 638.00),
(N'Outside', N'Riviera', 225, 389.00, 778.00),
(N'Suite', N'Verandah', 295, 1009.00, 2018.00),
(N'Inside', N'Upper', 185, 379.00, 758.00),
(N'Inside', N'Main', 215, 359.00, 718.00),
(N'Outside', N'Riviera', 185, 349.00, 698.00),
(N'Suite', N'Main', 300, 885.00, 1680.00);
GO
Here is another database named Video Collection with a table named Videos:
CREATE DATABASE VideoCollection
GO
USE VideoCollection
GO
CREATE TABLE Videos
(
Title nvarchar(50),
Director nvarchar(50),
WideScreen bit,
Rating nchar(10),
YearReleased int
)
GO
INSERT INTO Videos
VALUES(N'War of the Roses (The)', N'Dany de Vito', 0, N'R', 2001),
(N'Memoirs of a Geisha', N'Rob Marshall', 1, N'PG-13', 2006),
(N'Last Castle (The)', N'Rod Lurie', 1, N'', 2001),
(N'Sneakers', N'Phil Alden Robinson', 1, N'PG-13', 2003);
GO
To show the records of more than one table, in the Query Editor, write a SELECT statement for each table and execute it. If the tables belong to different databases, make sure you indicate this. Here is an example:
USE Cruise;
GO
SELECT CabinType AS [Cabin Type], Deck, Size AS [Size in sq/ft],
Rate1Passenger AS [Rate for 1 Passenger],
Rate2Passengers AS [Rate for 2 Passengers]
FROM Cabins;
GO
USE VideoCollection;
GO
SELECT Title, Director, WideScreen As [Has Wide Screen],
Rating, YearReleased AS [(c) Year]
FROM Videos;
GO
|
Data Selection and Expressions |
|
Using an Alias Name for a Column |
If you are writing a SELECT statement, on the right side of the column name, leave an empty space. Then type a letter or a word as the alias name of the column. Here are examples:
USE VideoCollection1; GO SELECT Title Name, Director Master, Rating Exclusion FROM Videos; GO
If you want an alias name that uses more than one word and you want the words separate, you can put them in double-quotes. Here is an example:
USE VideoCollection1;
GO
SELECT Title Name,
Director Master,
Rating Exclusion,
YearReleased "Copyright Year"
FROM Videos;
GO
Another solution is to include the alias name between [ and ]. Whether the alias is in one or many words, you can inlude it in double-quotes or in square brackets [ ].
Instead of using just an empty space between the column name and its alias, you can type AS. Here are examples:
SELECT FirstName,
LastName,
HomePhone AS PhoneNumber,
ParentsNames AS NamesOfParents
FROM Registration.Students;
GO
Remember that if the alias is in more than one word and they are separate, you can either include it in double-quotes or between [ and ]. Here are examples:
SELECT FirstName [First Name],
LastName "Last Name",
HomePhone AS [Phone Number],
ParentsNames AS "Names of Parents"
FROM Registration.Students;
GO
If you create an alias for a column and you use a data reader to to bind the columns, you can pass the alias name to the data reader. Here are examples:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WinFormsApp2
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void btnLoad_Click(object sender, EventArgs e)
{
lvwStudents.Items.Clear();
using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
"Database='rosh';" +
"Integrated Security=Yes;"))
{
SqlCommand cmdStudents = new SqlCommand("SELECT StudentNumber, " +
" FirstName AS [First Name], " +
" LastName AS [Last Name], " +
" Gender, " +
" ParentsNames AS [Names of Parents]" +
"FROM Registration.Students;",
scFunDS);
scFunDS.Open();
SqlDataAdapter sdaStudents = new SqlDataAdapter(cmdStudents);
DataSet dsStudents = new DataSet("EmployeesSet");
sdaStudents.Fill(dsStudents);
foreach (DataRow drStudent in dsStudents.Tables[0].Rows)
{
ListViewItem lviEmployee = new ListViewItem(drStudent["StudentNumber"].ToString());
lviEmployee.SubItems.Add(drStudent["First Name"].ToString());
lviEmployee.SubItems.Add(drStudent["Last Name"].ToString());
lviEmployee.SubItems.Add(drStudent["Gender"].ToString());
lviEmployee.SubItems.Add(drStudent["Names of Parents"].ToString());
lvwStudents.Items.Add(lviEmployee);
}
txtNumberOfStudents.Text = dsStudents.Tables[0].Rows.Count.ToString();
}
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}
You can also qualify a column using the name of the table. Here are examples:
SELECT Students.FirstName AS [First Name],
Students.LastName "Last Name",
Students.HomePhone AS [Phone Number],
Students.ParentsNames [Names of Parents]
FROM Registration.Students;
GO
By specifying a schema, the statement can also be written as follows:
SELECT Registration.Students.FirstName AS [First Name],
Registration.Students.LastName AS [Last Name],
Registration.Students.HomePhone AS [Phone Number],
Registration.Students.ParentsNames AS [Names of Parents]
FROM Registration.Students;
GO
We have already seen how to create an alias for a table. If you are working in the Query Designer and if you create an alias in the Properties window, as soon as you do this, the alias is written immediately in both the Criteria and the SQL sections. Otherwise, you can directly create an lias in your SQL statement. After doing this, you can qualify each column by preceding it with the name of the alias and a period. Here is an example:
SELECT std.FirstName AS [First Name],
std.LastName AS [Last Name],
std.HomePhone AS [Phone Number],
std.ParentsNames AS [Names of Parents]
FROM Registration.Students std;
GO
You can also create an alias for some or all columns. Here are examples:
SELECT [Little Angels].FirstName [First Name], "Little Angels".LastName AS [Last Name], Gender, [Little Angels].EmailAddress "Email Address", ParentsNames [Parents Names], [Little Angels].HomePhone AS "Home Phone" FROM Registration.Students [Little Angels]; GO
|
A Combination or Expression of Columns |
In our review of string-based functions, we saw how to concatenate strings. The operation is also available in a SELECT statement. This means that you can combine the values of separate columns to create a string or a value that is in fact an expression. For example, you can combine a first name and a last name to produce a full name as an expression. Another expression can use a date on the table, add a number to it to get a date on another day. An expression can also be used to perform a calculation on two or more columns such as employees weekly hours multiplied by their hourly salary to get their weekly salary.
The most common operator used is the addition. It can be used to combine two or more strings to get a new one. Here is an example:
SELECT FirstName + N' ' + LastName FROM Registration.Students; GO
The addition can also be used on numeric values. All other arithmetic operators can be used. For example, you can multiply an employee's weekly time to an hourly salary to get a weekly salary. The statement of such an expression can be written as follows:
SELECT WeeklyHours * HourlySalary FROM Payroll; GO
You can also create an alias for an expression to give it the desired name. To do this, on the right side of the expression, type AS followed by the name. As we learned already, if the alias is in more than one word, include it in either single quotes or square brackets. Here is an example:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
namespace WinFormsApp2
{
public partial class Exercise : Form
{
public Exercise()
{
InitializeComponent();
}
private void btnLoad_Click(object sender, EventArgs e)
{
lvwStudents.Items.Clear();
using (SqlConnection scFunDS = new SqlConnection("Data Source=(local);" +
"Database='rosh';" +
"Integrated Security=Yes;"))
{
SqlCommand cmdStudents =
new SqlCommand("SELECT StudentNumber, " +
" FirstName + N' ' + LastName AS [Full Name], " +
" Gender, " +
" ParentsNames AS [Names of Parents]" +
"FROM Registration.Students;",
scFunDS);
scFunDS.Open();
SqlDataAdapter sdaStudents = new SqlDataAdapter(cmdStudents);
DataSet dsStudents = new DataSet("EmployeesSet");
sdaStudents.Fill(dsStudents);
foreach (DataRow drStudent in dsStudents.Tables[0].Rows)
{
ListViewItem lviEmployee = new ListViewItem(drStudent["StudentNumber"].ToString());
lviEmployee.SubItems.Add(drStudent["Full Name"].ToString());
lviEmployee.SubItems.Add(drStudent["Gender"].ToString());
lviEmployee.SubItems.Add(drStudent["Names of Parents"].ToString());
lvwStudents.Items.Add(lviEmployee);
}
txtNumberOfStudents.Text = dsStudents.Tables[0].Rows.Count.ToString();
}
}
private void btnClose_Click(object sender, EventArgs e)
{
Close();
}
}
}
In the same way, you can create a longer and more complex expression that contains SQL keywords, the table's columns, and regular words. Here is an example:
SELECT PropertyType + N' in ' + City + N', ' + State + N', in ' + Condition +
N' condition. Equipped with ' + CAST(Bedrooms AS nvarchar(20)) +
N' bedrooms, ' + CAST(Bathrooms AS nvarchar(20)) +
N' bathrooms. Built in ' + CAST(YearBuilt AS nvarchar(20)) +
N' and selling for ' + CAST(MarketValue AS nvarchar(20))
AS [Property Description]
FROM Listing.Properties
Remember that if you are adding strings to each other, you can use the CONCAT() function.
|
WHEN a Field's Value Meets a Criterion |
You can use a WHEN conditional statement to refine data selection. Consider the following Persons table:
USE Exercise; GO CREATE TABLE Persons(FirstName nvarchar(20), LastName nvarchar(20), GenderID int); GO INSERT INTO Persons VALUES(N'Gertrude', N'Monay', 2), (N'Horace', N'Taylor', 1), (N'Marc', N'Knights', 2), (N'Tiernan', N'Michael', 3), (N'Paul', N'Yamo', 1), (N'Mahty', N'Shaoul', 3), (N'Hélène', N'Mukoko', 2); GO
Imagine you want to select the GenderID column. 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:
SELECT FirstName, LastName, Gender =
CASE GenderID
WHEN 1 THEN N'Male'
WHEN 2 THEN N'Female'
ELSE N'Unknown'
END
FROM Persons