Home

Introduction to Data Analysis

 

Fundamentals of Data Analysis

 

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.

In Microsoft SQL Server, data analysis can be performed using SQL Server Management Studio or in a query window.

 

The Data in the Table Window

To visually analyze data, in the Object Explorer, you can right-click a table and click Open Table. Here is an example:

Data in the Table Window

By default, when you open a table, the Query Designer toolbar comes up also:

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:

  1. Diagram: The top section is referred to as the Diagram window. It 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.
  2. Criteria: Under the table, the second section is called Criteria. It displays a list of columns used to visually build the SQL statement.
  3. SQL: The third section from top, called SQL, displays the SQL statement that results from selections in the Diagram or the Criteria sections
  4. Results: The bottom section, called Results, displays the result produced by the SQL statement when it is executed

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

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 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 Analysis

 

Introduction

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

  • If you haven't yet decided what table holds the data you want to analyze, in the Object Explorer, you can right-click a database and click New Query. Then, in the query window, enter the necessary SQL statement
  • If you know the table that holds the data you want to analyze, in the Object Explorer, right-click the table, position the mouse on Script Table As, followed by SELECT To, and click New Query Editor Window. A query window would be opened with sample SQL code

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

To specify how you want to show the results of your SQL statement, you have two options:

  • To show the result as text, on the SQL Editor toolbar, you can click the Result To Text button . Alternatively, you can right-click somewhere in the table, position the mouse on Results To, and click Results To Text.

    The results would appear in two columns of text. Here is an example:
     
    Result To Text
  • To show the result as a spreadsheet, on the SQL Editor toolbar, you can click the Result To Grid button Result to Grid. Alternatively, you can 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 either the Table window or the query window, you are expected to write appropriate code that would constitute a SQL statement.

Practical Learning Practical 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
  4. Right-click RealEstate1 and click New Query

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, 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, 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 Learning Practical Learning: Selecting Data

  1. In the query window, type the following:
     
    USE RealEstate1;
    GO
    SELECT p.* FROM Properties p;
    GO
  2. Press F5 to execute the statement
  3. To get a list of the properties numbers and their type, change the statement as follows:
     
    SELECT dbo.Properties.PropertyNumber,
           dbo.Properties.PropertyType
    FROM Properties;
    GO
  4. Press F5 to execute
  5. To get a list of the types of properties, the year each was built, and its market value, change the statement as follows:
     
    SELECT house.PropertyNumber,
           house.PropertyType,
           house.YearBuilt,
           house.MarketValue
    FROM Properties house;
    GO
  6. Right-click somewhere in the table, position the mouse on Results To, and click Results To Text
  7. Press F5 to execute

Using an Alias Name for a Column

 

Introduction

In your SELECT statement, after specifying the column(s) as we have done so far, when you execute the SQL statement, the name of each column would appear as the column header. Fortunately, you can display any string of your choice for a column header.

To specify a column header other than the name of the column, if you are using the Table window, type the desired string in the Alias column corresponding to the column. Here is an example:

Alias Name of a Column

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,
       HomePhone AS PhoneNumber,
       ParentsNames AS NamesOfParents
FROM   Students;
GO

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 is an example:

SELECT FirstName AS [First Name],
       LastName AS [Last Name],
       HomePhone AS [Phone Number],
       ParentsNames AS [Names of Parents]
FROM   Students;
GO

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.HomePhone AS [Phone Number],
       Students.ParentsNames AS [Names of Parents]
FROM   Students;
GO

It can also be written as follows:

SELECT dbo.Students.FirstName AS [First Name],
       dbo.Students.LastName AS [Last Name],
       dbo.Students.HomePhone AS [Phone Number],
       dbo.Students.ParentsNames AS [Names of Parents]
FROM   Students;
GO

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

Practical Learning Practical Learning: Using Alias Names

  1. To use the alias names of columns, change the statement as follows:
     
    SELECT house.PropertyNumber AS [Prop #],
           house.ZIPCode AS [Location],
           house.YearBuilt AS [Year Built],
           house.PropertyType AS [Type],
           house.Bedrooms AS [Beds],
           house.Bathrooms AS [Baths],
           house.MarketValue AS [Value]
    FROM Properties house;
    GO
  2. Right-click somewhere in the table, position the mouse on Results To, and click Results To Grid
  3. Press F5 to execute

A Combination or Expression of Columns

Using the SELECT keyword, we have learned to create a list of isolated columns. These columns were rendered 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. Here is an example:

SELECT FirstName + ' ' + LastName
FROM   Students;
GO

This would produce:

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

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',
       EmrgName + ' ' + EmrgPhone AS [Emergency Contact]
FROM   Students;
GO

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 analysis or a name for an expression. This is done using the assignment operator "=".

To change the name of a column during data analysis, 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;
GO

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

This would produce:

Assignment

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

This would produce:

Assignment in an Expression

 

Previous Copyright © 2007-2012 FunctionX Next