Home

Introduction to Queries

 

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 in the SQL Server Enterprise Manager, you can right-click and table, position the mouse on Open Table, and click Returns All Rows. To actually perform data analysis, on the toolbar, you can click the Show/Hide SQL Pane button Show/Hide SQL Pane. This would display the Data In Table window:

Another technique you can use for data analysis consists of opening a Query window in the SQL Query Analyzer.

SQL Query Analyzer

In either the Data In Table window of the SQL Server Enterprise Manager or the Query window of the SQL Query Analyzer, you are expected to write code that would constitute a SQL statement.

 

Field Selection

The most fundamental keyword used by SQL is SELECT. In order to process a request, you must specify what to select. Therefore, 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 query. This can be done by click the Run button . Alternatively, you can right-click anywhere in the window and click Run:

The Result of a Query

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

When you execute the query, it would display only the column that contains the last names. To consider more than one column in a query, 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

When executed, this expression would produce:

A SELECT Query

 

Practical LearningPractical Learning: Performing Data Analysis

  1. Start Microsoft SQL Server Enterprise Manager and switch to SQL Query Analyzer.
  2. Enlarge the Query window as much as you can. In the combo box of the toolbar, select Northwind (if necessary)
     
    SQL Query Analyzer
  3. To select everything from the Customers table, in the empty window, type
     
    SELECT * FROM Customers
  4. To execute the query, click the Execute Query button Execute Query to see the result
  5. As opposed to viewing all data, you can also select one particular column whose fields you want to view. For example, to get a list of countries where the Northwind customers reside, change the statement as follows:
     
    SELECT Country FROM Customers
  6. To execute the query, press F5
  7. You can also make a list of some specific columns whose data you want to see. For example, to get information about the companies that Northwind is doing business with, you can list the company names and their contact information. To get it, change the statement as follows:
     
    SELECT CompanyName, ContactName, ContactTitle FROM Customers
  8. Execute it to see the result:
     
    A query with selected columns
  9. It is sometimes a good idea to divide a statement on different lines to make it a little easier to read.
    As an example, change the statement as follows:
     
    SELECT CompanyName, ContactName, ContactTitle
    FROM Customers
  10. Execute it.
 

Previous Copyright © 2004-2005 FunctionX, Inc. Next