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 records that respond 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.

In Microsoft SQL Server, data analysis can be performed using a query window in Microsoft SQL Server Management Studio, at the Command Prompt, or in PowerSheel.

The Data in the Table Window

To prepare a window that assists you with data selection, in the Object Explorer, you can right-click a table and click Select Edit Top 200 rows. When you do this, the interface becomes equipped with the Query Designer toolbar.

The Query Designer window can be made of four sections:

  • Diagram: The top section is referred to as the Diagram window. To get it:
    • On the main menu, click Query Designer -> Pane -> Diagram
    • Right-click an area of the window -> Pane -> Diagram
    • On the Query Designer toolbar, click the Show Diagram Pane button Show Diagram Pane

      The Diagram section displays the table(s) that contain(s) the columns you want to query. Each column displays a check box on its left and the name of the column on the right. The first item of the list has a name made of an asterisk and the (All Columns) expression.
      If the list of items is too long for the allocated rectangle to display, the table would be equipped with a vertical scroll bar.
  • Criteria: Under the Diagram section, you can use the Criteria section. To get it:
    • On the main menu, click Query Designer -> Pane -> Criteria
    • Right-click an area of the window -> Pane -> Criteria
    • On the Query Designer toolbar, click the Show Criteria Pane button Show Criteria Pane


    The Criteria section displays a list of columns used to visually build the SQL statement.

  • SQL: Under the Criteria section, you can get the SQL. To get it:
    • On the main menu, click Query Designer -> Pane -> SQL
    • Right-click an area of the window -> Pane -> SQL
    • On the Query Designer toolbar, click the Show SQL Pane button Show SQL Pane


    The SQL section displays the SQL statement that results from selections in the Diagram or the Criteria sections

  • The bottom section of the window can display the Results section. To get it:
    • On the main menu, click Query Designer -> Pane -> Results
    • Right-click an area of the window -> Pane -> Results
    • On the Query Designer toolbar, click the Show Results Pane button Show Results Pane

If you don't 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:

Query Designer

As an alternative to these techniques, to prepare a window for data selection:

  • In the Object Explorer, you can right-click a table and click Select Top 1000 rows
  • In the Object Explorer, position the mouse on Script Table As -> SELECT To -> New Query Editor Window
  • Open a Query window. Right-click inside the Query window and click Design Query in Editor...
     
    Query Designer
     
    In the Add Table dialog box, click the name of the table, click Add, and click Close

This would display a window made of three sections:

Query Designer

Column Selection

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

Query Designer

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:

Query Designer

You can then click the item desired in the list to select it. 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 section.

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.

After making your selections in the Query Designer, click OK. This would display a Query with a SQL statement. Also, the SQL Editor toolbar would be added under the Standard toolbar. To display the SQL Editor toolbar:

  • On the main menu, you can click View -> Toolbars -> SQL Editor
  • You can right-click any toolbar and click SQL Editor

SQL Statement Execution

After creating a SQL statement, you can view its result, which you can get by executing the statement. To do this:

  • Right-click anywhere in the Query window and click Execute SQL
  • On the SQL Editor toolbar, click the Execute button Execute or Execute
  • On the main menu, click Query or Query Designer and click Execute SQL or Execute

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 columns. To start, proceed as we saw previously to display a Query window.

Practical LearningPractical Learning: Introducing Data Analysis
  1. Start Microsoft SQL Server with the SQL Server Management Studio and connect to the server
  2. On the main menu, click File -> New -> Query With Current Connection
  3. To prepare a database for our exercises, copy and paste the following code in the Query window:
      
    -- =============================================
-- Database: RealEstate1
-- Author:   FunctionX
-- Date:     Saturday 05 September 2009, 22:46
-- =============================================
IF EXISTS (
  SELECT * 
    FROM sys.databases 
   WHERE name = N'RealEstate1'
)
  DROP DATABASE RealEstate1
GO
CREATE DATABASE RealEstate1;
GO

-- =============================================
-- Author:   FunctionX
-- Database: RealEstate1
-- Table:    Properties
-- =============================================
USE RealEstate1;
GO
CREATE TABLE Properties
(
  PropertyNumber nchar(6),
  Address nvarchar(50),
  City nvarchar(50),
  State nchar(2),
  ZIPCode nvarchar(12),
  PropertyType nvarchar(40),
  Condition nvarchar(32),
  Bedrooms smallint,
  Bathrooms float,
  FinishedBasement bit,
  IndoorGarage bit,
  Stories smallint,
  YearBuilt smallint,
  MarketValue money
);
GO

INSERT INTO Properties
VALUES(N'524880', N'1640 Lombardo Ave', N'Silver Spring', N'MD',
       N'20904', N'Single Family', N'Good', 4, 2.5, 3, 1, 3, 1995, 495880.00),
	  (N'688364', N'10315 North Hacht Rd', N'College Park', N'MD',
       '20747', N'Single Family', N'Excellent', 4, 3.5, 3,
       1, 2, 2000, 620724.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyType, Condition, FinishedBasement,
  Stories, MarketValue)
VALUES(N'611464', N'6366 Lolita Drive', N'Laurel', N'MD',
       N'20707', N'Single Family', N'Good', 1, 2, 422625.00);
GO
INSERT INTO Properties(Address, City, PropertyType,
  Bedrooms, MarketValue)
VALUES(N'9002 Palasko Hwy', N'Tysons Corner',
       N'Condominium', 2, 422895.00);
GO
INSERT INTO Properties(PropertyNumber, State,
  ZIPCode, Bedrooms, YearBuilt, MarketValue)
VALUES(N'420115', N'DC',
       N'20011', 2, 1982, 312555);
GO     
INSERT INTO Properties(PropertyNumber, City, ZIPCode,
 PropertyType, Bedrooms, YearBuilt, MarketValue)
VALUES(N'917203', N'Alexandria', N'22024',
       N'Single Family', 3, 1965, 345660.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  PropertyType, Condition, Bedrooms, Bathrooms, MarketValue)
VALUES(N'200417', N'4140 Holisto Crt', N'Germantown', N'MD',
       N'Condominium', N'Excellent', 2, 1, 215495.00);
GO
INSERT INTO Properties 
VALUES(N'927474', N'9522 Lockwood Rd', N'Chevy Chase', N'MD',
       N'20852', N'Townhouse', N'Bad Shape', 3, 2.5, 3, 0, 3,
       1992, 415665.00),
      (N'207850', N'14250 Parkdoll Rd', N'Rockville', N'MD',
       N'20854', N'Townhouse', N'Good', 3, 2.5, 2, 1, 2,
       1988, 325995.00);
GO    
INSERT INTO Properties(City, PropertyType, Bedrooms,
  YearBuilt, MarketValue)
VALUES(N'Washington', N'Townhouse', 4, 1975, 366775.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms,
  YearBuilt, MarketValue)
VALUES(N'288540', N'10340 Helmes Street #408', N'Silver Spring', N'MD',
       N'20906', N'Condominium', N'Good', 1, 1, 2000, 242775.00);
GO
INSERT INTO Properties 
VALUES(N'247472', N'1008 Coppen Street', N'Silver Spring', N'MD',
       N'20906', N'Single Family', N'Excellent',
       3, 3, 3, 1, 3, 1996, 625450.00);
GO
INSERT INTO Properties(City, ZIPCode, PropertyType, 
   Stories, YearBuilt, MarketValue)
VALUES(N'Chevy Chase', N'20956', N'Single Family', 
       3, 2001, 525450.00);
GO
INSERT INTO Properties(Address, City, State,
  PropertyType, Condition, Bedrooms, MarketValue)
VALUES(N'686 Herod Ave #D04', N'Takoma Park', N'MD',
       N'Condominium', N'Excellent', 2, 360885.00);
GO
INSERT INTO Properties 
VALUES(N'297446', N'14005 Sniders Blvd', N'Laurel', N'MD',
       N'20707', N'Townhouse', N'Needs Repair',
       4, 1.5, 3, 1, 2, 2002, 412885.00);
GO
INSERT INTO Properties(City, ZIPCode, Condition, Bedrooms,
  Stories, YearBuilt)
VALUES(N'Silver Spring', N'20905', N'Good',
       4, 2, 1965);
GO
INSERT INTO Properties 
VALUES(N'924792', N'680 Prushia Rd', N'Washington', N'DC',
       N'20008', N'Single Family', N'Good',
       5, 3.5, 3, 0, 3, 2000, 555885.00),
      (N'294796', N'14688 Parrison Street', N'College Park', N'MD',
       N'20742', N'Single Family', N'Excellent',
       5, 2.5, 2, 1, 2, 1995, 485995.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyType, Condition, Bedrooms, Bathrooms,
  YearBuilt, MarketValue)
VALUES(N'811155', N'10340 Helmes Street #1012', N'Silver Spring',
       N'MD', N'20906', N'Condominium', N'Good',
       1, 1, 2000, 252775.00);
GO
INSERT INTO Properties 
VALUES(N'447597', N'4201 Vilamar Ave', N'Hyattsville', N'MD',
       N'20782', N'Townhouse', N'Excellent',
       3, 2, 2, 1, 3, 1992, 365880.00);
GO
INSERT INTO Properties(Address, ZIPCode, Bathrooms)
VALUES(N'1622 Rombard Str', 20904, 2.5);
GO
INSERT INTO Properties 
VALUES(N'297415', N'980 Phorwick Street', N'Washington', N'DC',
       N'20004', N'Single Family', N'Good',
       4, 3.5, 3, 3, 1, 2004, 735475.00),
      (N'475974', N'9015 Marvin Crow Ave', N'Gaithersburg', N'MD',
       '20872', N'Single Family', N'Needs Repair',
       4, 2.5, 3, 1, 1, 1965, 615775.00),
       (N'836642', N'3016 Feldman Court', N'Rockville', N'MD',
       N'20954', N'Single Family', N'Bad Shape',
       5, 3, 3, 1, 3, 1960, 528555.00);
GO
INSERT INTO Properties(City, State, PropertyType, Stories)
VALUES(N'Rockville', N'MD',
       N'Townhouse', 1);
GO
INSERT INTO Properties 
VALUES(N'208304', N'7307 Everett Hwy', N'Washington', N'DC',
       N'20012', N'Townhouse', N'Excellent',
       2, 2.5, 2, 0, 4, 2006, 420550.00);
GO
INSERT INTO Properties(PropertyNumber, Address, City, State,
  ZIPCode, PropertyType, Condition, Bedrooms,
  Bathrooms, YearBuilt, MarketValue)
VALUES(N'644114', N'10340 Helmes Street#1006', N'Silver Spring',
       N'MD', N'20906', N'Condominium', N'Good',
       2, 2, 2000, 258445.00);
GO
INSERT INTO Properties 
VALUES(N'937966', N'7303 Warfield Court', N'Tysons Corner', N'VA',
       N'22131', N'Single Family', N'Good',
       3, 2.5, 3, 1, 4, 2006, 825775.00);
GO
INSERT INTO Properties(City, ZIPCode, Condition, Bedrooms, 
  Stories, YearBuilt)
VALUES(N'Fairfax', N'22232', N'Good', 3, 3, 1985);
GO
INSERT INTO Properties 
VALUES(N'297497', N'12401 Conniard Ave', N'Takoma Park', N'MD',
       N'20910', N'Townhouse', N'Good',
       3, 2.5, 3, 1, 3, 2004, 280775.00);
GO
INSERT INTO Properties(City, ZIPCode, PropertyType,
            Bedrooms, Bathrooms, MarketValue)
VALUES(N'Alexandria', N'22035', N'Condominium', 
       2, 2, 425775.00);
GO
INSERT INTO Properties(PropertyNumber, City, ZIPCode,
  PropertyType, Condition, Bedrooms, Bathrooms,
  YearBuilt, Stories, MarketValue)
VALUES(N'855255', N'Laurel', N'20707', N'Single Family',
       N'Needs Repair', 3, 2, 1962, 2, 342805.00);
GO
INSERT INTO Properties(PropertyNumber, City, ZIPCode, PropertyType,
  Condition, Bedrooms, Bathrooms, MarketValue)
VALUES(N'225227', N'Rockville', N'20857', N'Condominium', N'Good',
       1, 1, 525885.00);
GO
INSERT INTO Properties 
VALUES(N'469750', N'6124 Falk Rd', N'Arlington', N'VA',
       N'22031', N'Single Family', N'Needs Repair',
       4, 3.5, 3, 1, 1, 1982, 635995.00),
      (N'826927', N'5121 Riehl Ace', N'Fairfax', N'VA',
       N'22232', N'Townhouse', N'Excellent',
       3, 1.5, 2, 0, 1, 2002, 325620.00),
      (N'287064 ', N'9533 Pensulian Rd', N'Silver Spring', N'MD',
       N'20904', N'Single Family', N'Bad Shape',
       3, 1.5, 3, 1, 2, 1992, 485775.00);
GO
INSERT INTO Properties(PropertyNumber, City, ZIPCode,
  PropertyType, Condition, Bedrooms, YearBuilt, Stories)
VALUES(N'724001 ', N'Washington', N'20004',
       N'Townhouse', N'Bad Shape', 3, 1974, 4);
GO
INSERT INTO Properties 
VALUES(N'209275', N'944 Fryer Ave', N'Chevy Chase', N'MD',
       N'20852', N'Single Family', N'Excellent',
       5, 2.5, 3, 0, 2, 2002, 625665.00),
      (N'204759', N'1950 Galego Street', N'Germantown', N'MD',
       N'20874', N'Single Family', N'Excellent',
       4, 3.5, 2, 1, 4, 2007, 428665.00);
GO
INSERT INTO Properties(City, State, PropertyType, 
  Bedrooms, Bathrooms, YearBuilt, MarketValue)
VALUES(N'Takoma PArk', N'MD',
       N'Conbominium', 2, 2, 2000, 225885.00);
GO
INSERT INTO Properties 
VALUES(N'937259', N'12366 Fowler Ave', N'Alexandria', N'VA',
       N'22031', N'Townhouse', N'Good',
       3, 1.5, 3, 1, 3, 2007, 402815.00);
GO
  4. Press F5 to execute
  5. Save the file as RealEstate1 and close the query window
 

 
 

Showing the Results of SQL Data Analysis

After entering the SQL statement in a Query window, you can execute it to see the result. 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. 

To specify how you want to show the results of your SQL statement, if you are using the Query window, you have two options:

  • To show the result as text:
    • On the SQL Editor toolbar, click the Results To Text button
    • Right-click somewhere in the table, position the mouse on Results To, and click Results To Text.
    The results would appear in columns of text. Here is an example:
     
    Result To Text
  • To show the result as a spreadsheet:
    • On the SQL Editor toolbar, click the Result To Grid button Result to Grid
    • Right-click somewhere in the table, position the mouse on Results To, and click Results To Grid
    The results would appear as a spreadsheet of one or various columns. Here is an example:
     
    Result To Grid

In the Query window, you are expected to write appropriate code that would constitute a SQL statement.

Practical LearningPractical Learning: Creating a Query
  1. In the Object Explorer, right-click the Databases node and click Refresh
  2. Expand the Databases node
  3. Expand RealEstate1 and expand its Tables node
  4. Right-click dbo.Properties and click Edit Top 200 Rows
  5. On the Query Designer toolbar, click the Show Diagram Pane button Show Diagram Pane
  6. Click the Show Criteria Pane button Show Criteria Pane
  7. Click the Show SQL Pane button Show SQL Pane
  8. In the Diagram section, click the check boxes of all fields to remove the check marks
  9. In the SQL section, delete TOP (200)

Field Selection

The most fundamental keyword used by SQL is SELECT. In order to process a request, you must specify what to select. To perform data analysis, 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, you can use the asterisk as the range of values. For example, to display all records from a table called Students, you can type:

SELECT * FROM Students;

After writing the expression in a Query window, you must execute the SQL statement to see its result. Here is an example:

The Result of a Query

You can also qualify the * selector by preceding it with the name of the table followed by the period operator. The above statement is equivalent to:

SELECT Students.* FROM Students;

In Lesson 8, 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. Using this feature, the above statement can be written as:

SELECT std.* FROM Students std;

As opposed to viewing all data, you can also select one particular column whose fields you want to view. To do this, you can replace the What in our syntax 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 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 Students;

When you execute the statement, it would display only the column that contains the last names. To consider more than one column in a statement, you can list them in the What factor of our syntax, 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 Students;

Once again, 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:

SELECT Students.FirstName,
       Students.LastName,
       Students.Gender,
       Students.EmailAddress,
       Students.HomePhone
FROM Students;

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

When executed in a Query window, this expression would produce:

A SELECT Query

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.Gender, std.EmailAddress, std.HomePhone
FROM Students std;

Practical LearningPractical Learning: Selecting Data
  1. In the Diagram section, click the check box of City
  2. On the Query Designer toolbar, click the Execute SQL button Execute SQL to execute the statement and see the result:
     
    Real Estate
  3. Notice that you get 41 records and some cities are listed more than once.
    In the Diagram section, remove the check box on City
  4. To get all properties, in the Diagram section, click the check box of the * field
  5. On the main menu, click Query Designer -> Execute SQL
  6. In the Diagram section, remove the check box of *
  7. To get a list of the properties numbers and their type, in the Diagram section, click the check boxes of PropertyNumber and PropertyType
  8. Right-click somewhere in the window and click Execute SQL to execute
     
    Real Estate
  9. To get a list of the types of properties, the year each was built, and its market value, click the check boxes of YearBuilt and MarketValue 
  10. Right-click somewhere in the window and click Execute SQL
  11. In the Diagram section, remove all check boxes

Distinct Field Selection

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 again. 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.

Practical LearningPractical Learning: Producing Distinct Values
  1. In the Diagram section, click the check box of City
  2. In the SQL section, on the right side of SELECT, type DISTINCT followed by the Space bar: 
  3. On the Query Designer toolbar, click the Execute SQL button Execute SQL to see the result:
     
    Real Estate
  4. Notice that, this time, the name of each city appears only once
 
 
   
 
Previous Copyright © 2007-2009 FunctionX, Inc. Next