Home

Introduction to SELECT

 

Introduction to SQL Operators and Operands

An operation is an action performed on one or more values either to modify the value held by one or both of the variables or to produce a new value by combining values. Therefore, an operation is performed by using at least one symbol and one value. The symbol used in an operation is called an operator. A value involved in an operation is called an operand.

 
     

Practical LearningPractical Learning: Introducing Database Operations

  1. Start Microsoft Visual C# and create a Windows Application named Exercise4
  2. From the Common Controls section of the Toolbox, add a Button to the form
  3. Double-click the button and implement its Click event as follows:
     
    private void button1_Click(object sender, EventArgs e)
    {
        string strConnection =
    	"Data Source=(local);Integrated Security=yes";
    
        using (SqlConnection connection = new SqlConnection(strConnection))
        {
    	SqlCommand command =
    	    new SqlCommand("CREATE DATABASE Exercise4;", connection);
    
    	connection.Open();
    	command.ExecuteNonQuery();
    
    	MessageBox.Show("A database named \"Exercise4\" has been created.");
        }
    }
  4. Execute the application
  5. Click the button
  6. Click OK
  7. Close the form and return to your programming environment
  8. Delete the button on the form and design it (the form) as follows:
     
    Seven-Locks Flower Shop - Employee Payroll
    Control Text Name Other Properties
    Label First Name:    
    TextBox   txtFirstName  
    Label Last Name:    
    TextBox   txtLastName  
    Label Full Name:    
    TextBox   txtFulName  
    Label Date Hired:    
    DateTimePicker   dtpDateHired  
    Label Employment Status:    
    ComboBox   cbxEmploymentStatus Items:
    Full Time
    Part Time
    Contractor
    Seasonal
    Intern
    Label Weekly Status:    
    TextBox   txtWeeklyStatus TextAlign: Right
    Label Hourly Salary:    
    TextBox   txtHourlyStatus TextAlign: Right
    Button Operate btnOperate  
    Label Weekly Salary:    
    TextBox   txtWeeklySalary TextAlign: Right
    Button Close btnClose  
  9. Save the form

PRINT Something

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

PRINT WhatToPrint

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

SELECT Something

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

SELECT What

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

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

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

SELECT Something

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

SELECT 226.75;

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

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

SELECT N'Hourly Salary', 24.85

Practical LearningPractical Learning: Selecting Something

  1. On the form, double-click the Operate button
  2. To use a SELECT statement, implement the Click event as follows:
     
    private void btnOperate_Click(object sender, EventArgs e)
    {
        SqlConnection connection =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='Exercise4';" +
    			  "Integrated Security=yes;");
        SqlCommand command = new SqlCommand("SELECT N'William';",
    					connection);
        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();
    
        while (rdr.Read())
    	txtFirstName.Text = rdr[0].ToString();
    
        rdr.Close();
        connection.Close();
    }
  3. Return to the form and double-click the Close button
  4. Implement its Click event as follows:
     
    private void btnClose_Click(object sender, EventArgs e)
    {
        Close();
    }
  5. Execute the application
  6. Click the Operate button
  7. After checking that a first name displays, close the form and return to your programming environment
  8. To select more than one value, change the code of the Operate button as follows:
     
    private void btnOperate_Click(object sender, EventArgs e)
    {
        SqlConnection connection =
    	new SqlConnection("Data Source=(local);" +
    			  "Database='Exercise4';" +
    			  "Integrated Security=yes;");
        SqlCommand command = new SqlCommand("SELECT N'William', 'Godetsky';",
    					connection);
        connection.Open();
        SqlDataReader rdr = command.ExecuteReader();
    
        while (rdr.Read())
        {
    	txtFirstName.Text = rdr[0].ToString();
    	txtLastName.Text = rdr[1].ToString();
        }
    
        rdr.Close();
        connection.Close();
    }
  9. Execute the application
  10. Click the Operate button
     
    SELECTing more than one value
  11. Close the form and return to your programming environment

Nesting a SELECT Statement

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

SELECT (SELECT 448.25);
GO

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

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

SELECT (SELECT (SELECT 1350.75));
GO

SELECT This AS That

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

SELECT 24.85 AS HourlySalary;

This would produce:

SELECT This AS That

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

SELECT 24.85 AS 'HourlySalary';

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

SELECT 24.85 AS 'Hourly Salary';

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

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

This would produce:

SELECT this AS that

The above statement could also be written as follows:

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

Published on Monday 25 December 2007

 

Home Copyright © 2007 FunctionX, Inc.