Home

Introduction to 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 stored in it. One of the most commonly performed operations by the users of a database is to look for data or to isolate data that responds to a particular criterion. Looking for data that is conform to a criterion 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 using some special reserved words. You can perform data selection using the Microsoft SQL Server Management Studio, a query window in Microsoft SQL Server Management Studio, a query window in Microsoft Visual Studio, a Windows application.

Practical LearningPractical Learning: Introducing Data Selection

  1. Start Microsoft Visual C# and create a Windows Application named spr3
  2. In the Solution Explorer, right-click Form1.cs and click Rename
  3. Type RentalProperties.cs and press Enter.
    If you followed the previous lesson, move to the next step. If not, follow the instructions in the previous lesson to create the SolasPropertyRental1 database
  4. Design the form as follows:
     
    Solas Property Rental: Form Design
    Control Text Name Other Properties
    DataGridView   dgvProperties Anchor: Top, Bottom, Left, Right
    GroupBox Fields to Show grpFieldsToShow Anchor: Bottom, Left, Right
    RadioButton Show all Fields rdoShowAllFields  
    RadioButton Show Only rdoShowSomeFields  
    CheckedListBox   clbColumns CheckOnClick: True
    MultiColumn: True
    Anchor: Bottom, Left, Right
    Button Execute btnExecute Anchor: Bottom, Right
    Button Close btnClose Anchor: Bottom, Right
  5. Double-click the checked list box and implement its event as follows:
     
    private void clbColumns_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (clbColumns.CheckedItems.Count < 1)
    	rdoShowAllFields.Checked = true;
        else
    	rdoShowSomeFields.Checked = true;
    }
  6. Execute the application to preview the form
  7. Close the form and return to your programming environment

The Data in the Table Window

To visually analyze data in Microsoft Visual Studio, in the Server Explorer, you can right-click a table and click Show Table Data. By default, when you open a table, the Query Designer toolbar comes up also:

Query Designer Toolbar

Once the table is opened, on the main menu, you can click Query Designer. Alternatively, you can right-click anywhere on the table. In both cases

  1. On the menu that appears, position the mouse on Pane and click Diagram
  2. Once again, open the Pane menu and click Criteria
  3. Again, open the Pane menu and click SQL:

The Data in the Table Window

Alternatively, on the Query Designer toolbar, you can click the Show Diagram Pane button Show Diagram Pane, the Show Criteria Pane button Show Criteria Pane, and the Show SQL Pane button Show SQL Pane.

The Table window is divided in four sections:

If you do not want a particular section or you want to hide some sections, you can right-click anywhere in the table, position the mouse on Pane and click the name of the section. To hide any section, you can right-click anywhere in the window, position the mouse on Show Panes and click one of the selections:

Using Panes

When a section is displaying, its menu option is surrounded.

Column Selection

A SQL statement is primarily built by selecting one or more columns whose data you want to view. To select a column, in the Diagram section of the Table window, you can click the check box on the left side of the name:

Column Selection

After clicking the check box of a column, it becomes selected in the Criteria section also and its name appears in the SQL section. Another technique used to select a column consists of clicking a box under the Column header of the Criteria section. This would reveal that it is a combo box. You can then click the arrow of the combo box to display the list and select a column from that list:

Selecting a Column

In the Criteria section, if you click a combo box that already contains a column but select another, the previous one would be replaced by the new one. Also, after selecting a column in the Criteria section, its check box becomes selected in the Diagram section and its name gets added to the SQL expression. If you know the name of a column that you want to add, which you can see in the Diagram section, you can directly enter it in the SQL statement.

Any of the above three techniques allows you to select one or more columns to build the desired SQL statement.

SQL Statement Execution

After creating a SQL statement, you can view its result, which you can get by executing the statement. To do this, you can right-click anywhere in the Table window and click Execute SQL. Alternatively, on the Query Designer toolbar, you can click the Execute SQL button .

After executing the statement, the bottom section gets filled with data from only the selected column(s) of the table. Here is an example:

Query Execution

Transact-SQL and Data Selection

 

Introduction

Data selection is actually performed using SQL code that contains one or more criteria. To prepare for data selection, you have various options:

Showing the Results of SQL Data Selection

After entering the SQL statement, you can execute it to see the result. In Microsoft SQL Server Management Studio, this would display the Table window. The result would be displayed in the bottom section. There are two ways you can display the result. To have access to these options, you can first display the SQL Editor toolbar. In Microsoft SQL Server Management Studio, to display the SQL Editor toolbar:

In Microsoft SQL Server Management Studio, to specify how you want to show the results of your SQL statement, you have two options:

Result To Text

 

Result To Grid

In either the Table window or the query window, you are expected to write appropriate code that would constitute a SQL statement.

Table Field Selection

 

Selecting all Fields

From the columns of a table, you can use all fields if you want. If you are working in the table view:

Using * to Select all Columns 

If you are creating a connection using the Data Source Configuration Wizard, to select all columns from a table, you can click the check box of the table that holds the fields. Here is an example:

Data Source Configuration Wizard

The SQL provides its own means of performing this operation.

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;
As stated already, SQL is not case-sensitive. That means SELECT, Select, and select represent the same word.

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:

Data Source Configuration Wizard 

If you are writing your SQL statement, you can use the asterisk as the What factor of your SELECT statement. Here is an example:

SELECT * FROM Students;

You can also qualify the * selector. If you are working in the table view, you have various alternatives:

If you are writing your SQL statement, to qualify the * selector, precede * with the name of the table followed by the period operator. Here is an example:

SELECT Students.* FROM Students;

In Lesson 24, we saw that you could create an alias for a table by preceding a 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. If you are working in the Microsoft SQL Server Management Studio, in the Table window, type the desired alias string in the Alias column corresponding to the column. If you are manually writing your SQL statement, on the left side of .*, type an alias name for the table. Then, after FROM, on the right side of the table, enter the alias name again. Here is an example:

SELECT std.* FROM Students std;

After writing the expression, if you are working in the table window, you must execute the SQL statement to see its result.

Practical LearningPractical Learning: Selecting all Fields

  1. Double-click an unoccupied area of the form and implement the Load event as follows:
     
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Windows.Forms;
    using System.Data.SqlClient;
    
    namespace spr3
    {
        public partial class RentalProperties : Form
        {
    	public RentalProperties()
    	{
    	    InitializeComponent();
    	}
    
    	private void RentalProperties_Load(object sender, EventArgs e)
    	{
    	    using (SqlConnection cnnProperties =
    		new SqlConnection("Data Source=(local);" +
    				  "Database='SolasPropertyRental1';" +
    				  "Integrated Security='SSPI';"))
    	    {
    		string strSelect = "SELECT * FROM RentalProperties;";
    
    		SqlCommand cmdProperties = new SqlCommand(strSelect,
    						cnnProperties);
    		SqlDataAdapter sdaProperties = 
    			new SqlDataAdapter(cmdProperties);
    		BindingSource bsProperties = new BindingSource();
    
    		DataSet dsProperties = new DataSet("PropertiesSet");
    		sdaProperties.Fill(dsProperties);
    
    		cnnProperties.Open();
    		bsProperties.DataSource = dsProperties.Tables[0];
    
    		dgvProperties.DataSource = bsProperties;
    		foreach (DataColumn col in dsProperties.Tables[0].Columns)
    		    clbColumns.Items.Add(col.ColumnName);
    	    }
    
    	    rdoShowAllFields.Checked = true;
    	}
        }
    }
  2. Execute the application to see the result
     
    Solas Property Rental

  3. Close the form and return to your programming environment

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. If you are working from the table design:

Selecting some fields

If you are creating a database connection using the Data Source Configuration Wizard, to select one or more columns from a table, first expand the table. Then click the check box of each of the columns you want to include. Here is an example where the LastName, the Sex, the City, and the State boxes are  checked:

Data Source Configuration Wizard: Selecting Some Fields 

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;

You can also qualify a column by preceding it with the name of the table followed by the period operator. Here is an example:

SELECT Students.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;

After specifying the column(s) or after including them in your SELECT statement, when you execute the SQL statement, the name of each column would appear as the column header. Here is an example:

Selecting Some Fields 

Once again, you can qualify each column by preceding it with the name of the table followed by the period operator. Here is an example:

SELECT Students.FirstName,
       Students.LastName,
       Students.Sex,
       Students.City,
       Students.State
FROM Students;

You do not 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.Sex,
       City,
       State
FROM 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:

SELECT std.FirstName, std.LastName, std.Sex, std.City, std.State
FROM Students std;

Practical LearningPractical Learning: Selecting Data

  1. On the form, double-click the Execute button and implement its Click event as follows:
     
    private void btnExecute_Click(object sender, EventArgs e)
    {
        if (clbColumns.CheckedItems.Count < 1)
    	return;
    
        using (SqlConnection cnnProperties =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='SolasPropertyRental1';" +
    			  "Integrated Security='SSPI';"))
       {
    	string strColumns = "";
    
    	foreach (string str in clbColumns.CheckedItems)
    	    strColumns = strColumns + ", " + str;
    
    	string strResult = "";
    
    	if (rdoShowAllFields.Checked == true)
    	    strResult = "SELECT * FROM RentalProperties";
    	else
    	    strResult = "SELECT " +
    			strColumns.Substring(1) +
    			" FROM RentalProperties";
    
    	SqlCommand cmdProperties =
    		new SqlCommand(strResult, cnnProperties);
    	SqlDataAdapter sdaProperties =
    		new SqlDataAdapter(cmdProperties);
    	BindingSource bsProperties = new BindingSource();
    
    	DataSet dsProperties = new DataSet("PropertiesSet");
    	sdaProperties.Fill(dsProperties);
    
    	cnnProperties.Open();
    	bsProperties.DataSource = dsProperties.Tables[0];
    
    	dgvProperties.DataSource = bsProperties;
        }
    }
  2. Return to the form and double-click the Close button
  3. Implement its event as follows:
     
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  4. Execute the application to see the result
  5. Click a few check boxed in the bottom control
  6. Click the Execute button
     
    Solas Property Rental
  7. Close the form and return to your programming environment

Using an Alias Name for a Column

 

Introduction

If you create a SELECT statement, when you select a column, you must specify its name. If you are working in the table view in Microsoft SQL Server Management Studio or in Microsoft Visual Studio, the name of a column would display as the column header in the Results section. The string that displays in the column header is called a caption. As an alternative, if you want, you can display the caption of your choice.

To create the alias of a column if you are working in the table view of Microsoft SQL Server Management Studio or in Microsoft Visual Studio, in the Criteria section, under the Column header, select a column. Under the Alias column header, enter the desired name of the alias.

If you are using a query window or if you are writing your SELECT statement, on the right side of the column name, type AS followed by the desired name of the column header. If the desired column header is in one word, you can simply type it. Here is an example:

SELECT FirstName,
       LastName,
       City AS City
FROM   Students;

If you want the column header to appear with more than one word, you can provide the words as a string in single-quotes or between the square brackets: [ and ] . Here are examples:

SELECT FirstName AS [First Name],
       LastName AS [Last Name],
DateOfBirth AS [Date of Birth],
       City AS [City],
State
FROM   Students;

This would produce:

The Alias Name of a Column

By qualifying each column, the above statement can also be written as follows:

SELECT Students.FirstName AS [First Name],
       Students.LastName AS [Last Name],
       Students.DateOfBirth AS [Date of Birth],
       Students.City AS [City],
Students.State
FROM   Students;

It can also be written as follows:

SELECT dbo.Students.FirstName AS [First Name],
       dbo.Students.LastName AS [Last Name],
       dbo.Students.DateOfBirth AS [Date of Birth],
       dbo.Students.City AS [City],
dbo.Students.State
FROM   Students;

It can also be written as follows:

SELECT std.FirstName AS [First Name],
       std.LastName AS [Last Name],
       std.HomePhone AS [Phone Number],
       std.ParentsNames AS [Names of Parents]
FROM   Students std;
GOSELECT std.FirstName AS [First Name],
       std.LastName AS [Last Name],
       std.DateOfBirth AS [Date of Birth],
       std.City AS [City],
std.State
FROM   Students;

A Combination or Expression of Columns

Using the SELECT keyword, we have learned to create a list of isolated columns. These columns were separate of each other. Instead of having separate columns, you can combine them 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. If you are working in the table view of Microsoft SQL Server Management Studio or Microsoft Visual Studio, under the Column header, type the expression. Here is an example:

Expression

If you are writing your SQL statement, after SELECT, type the expression. After FROM, you must still specify what table holds the column(s) involved in the expression. Here is an example:

SELECT FirstName + ' ' + LastName
FROM   Students;

This type of expression would appear without a caption in the Results section. To specify the caption of the column, use the alias features we reviewed in the previous section. Here is an example:

A Combination or Expression of Columns

The addition can also be used on numeric values. All other arithmetic operators can be used. For example, you can multiply a weekly hours value 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

Just as you can create one expression, you can add as many expressions as you want in the SELECT operation of a table. 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 earlier, if the alias is in more than one word, include it in either single quotes or square brackets. Here is an example:

SELECT FirstName + ' ' + LastName AS [Full Name], 
       Address + ', ' + City + ' ' + State + 
       ' ' + ZIPCode AS [Home Address], 
EmrgName + N' ' + EmrgPhone AS [Emergency Contact]
FROM Students

This would produce:

Expressions

The Assignment Operator

If you just create a regular expression using arithmetic operators, the new column would not have a name. The SQL allows you to specify a different name for any column during data selection or a name for an expression. This is done using the assignment operator "=".

To change the name of a column during data selection in the table view in Microsoft SQL Server Management Studio or in Microsoft Visual Studio, in your SQL statement, on the right side of SELECT, type the desired name, followed by the assignment operator, followed by the actual name of the column. Here is an example:

SELECT EmergencyName = EmrgName
FROM   Students;

If you want to use more than one column, type each and assign it the desired name, separate them with commas. Here is an example:

SELECT LastName,
       EmergencyName = EmrgName,
       EmergencyPhone = EmrgPhone
FROM   Students;

You can also include the name between single-quotes or the square brackets. Here are examples:

SELECT LastName + ', ' + FirstName AS [Full Name],
       [Emergency Name] = EmrgName,
       'Emergency Phone' = EmrgPhone
FROM   Students;
 

Previous Copyright © 2008-2009 Yevol.com Next